ssignment question

20 marks

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