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:
Variable cost per DMLH of Moulding and welding
Total fixed costs for six months (allocated equally p.m.)
1/10/2016
1/4/2017
1/10/2017
$4.00
$4.20
$4.40
$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.
4
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.
Please refer to the following Marking criteria sheet for the allocation of marks.
Marking Criteria Sheet
Marks available
Section A Design of input sheet
4
Section B
1 Revenue budget
4
2 Production budget in units
8
3 Direct material usage in quantities and dollars budget
15
4 Direct manufacturing labour hours and cost budget
9
5 Manufacturing overhead costs
5
Design of Spreadsheets
Comply with formatting requirements
5
Assignment Total
50 marks
Assignment Total out of 20
20 marks
5
Marks awarded
