Reference no: EM131705577

Assignment question

Ressano Ltd produces patio heaters for household use. The materials and labour requirements per patio heater are as follows:

	Quantity	Cost for 2017	Cost for 2016
Stainless steel	5 kilograms	$12 per kilogram	$10 per kilogram
Gas cylinder	1	$18	$16
Moulding and welding	2 hours	$22 per hour	$20 per hour
Powder coating	0.5 hour	$18 per hour	$16 per hour

Management prefers a finished goods ending inventory for each quarter that equals 25% of expected sales for the next quarter. There were 195 heaters in inventory on 1 October 2016. Also, the ending inventory balance of direct materials should equal 25% of the next quarter's production requirements. The inventory balances on 1 January 2017 are forecast as:

Stainless steel	250 kilograms
Gas cylinders	103

The forecast quarterly sales in units and selling price per unit for 2017 are as follows:

	Quarter 1	Quarter 2	Quarter 3	Quarter 4
Estimated unit sales	960	1 120	480	212
Sales price per unit	$350	400	300	300

The forecast quarterly sales for the fourth quarter of 2016 was 780 heaters at a selling price of $350 each.
The manufacturing overhead costs change twice a year: on the 1st of April and on the 1st of October. The variable cost rate is based on the Direct Manufacturing Labour Hour (DMLH) per heater for Moulding and welding. The following table summarises the manufacturing overhead costs relevant to the period of the budget, effective from the following dates:

	1/10/2016	1/4/2017	1/10/2017
Variable cost per DMLH of Moulding and welding	$4.00	$4.20	$4.40
Total fixed costs for six months (allocated equally p.m.)	$33 000	$34 800	$36 000

Assume the following in your answer:
- Direct materials inventory and finished goods inventory are costed using the FIFO method.
- There is no work-in-progress inventory at any given point in time.

REQUIRED:

A. Design one input and the five output sheets as required in B below. Ensure your spreadsheets meet the "design of spreadsheets" and "formatting" requirements as stated above.

B. Prepare the following five (5) budgets for the first and the second quarter of 2017 and also the totals for the first half of the year. Also show at the bottom of each of these 5 budgets the total dollar value that will appear on the Income Statement (although you are not required to prepare an Income Statement).

1. Revenue

2. Production budget in units. Include a column to the left of the Q1 2017 column and show the figures for Q4 of 2016.

3. Direct material usage in both quantity/units and dollars. Show the calculations of the quantity used for Q4 2016 in a column on the left hand side of the Q1 2017 column. Do the Stainless steel direct materials calculations (both quantity and dollars) separate from that of the Gas cylinders direct materials. Also show the total cost of direct materials used and the total cost of direct materials purchased for the period ending 30 June 2017. (Refer to Schedule 3A on page 383 of the prescribed textbook for guidance as to how to calculate the quantity and dollar amounts of the direct materials that need to be purchased and the cost to be used for the 6 months ending 30 June 2017).

4. Direct manufacturing labour costs. Show the DML costs for the Moulding and welding separate to the DML costs for the Powder coating.

5. Manufacturing overhead costs.