Reference no: EM13490614

Budgeting

Steelworks Ltd manufactures two types of shopping trolleys for the Australian market: Standard trolley and Child carry trolley (which includes a plastic seat for a baby). Steelworks Ltd buys the direct materials in metres to manufacture the frames of the trolleys and they buy the wheels in boxes of 100 wheels per box. They use rod steel to manufacture the frames of both types of trolleys. The Standard trolley is almost twice as big as the Child carry trolley. The Child carry trolley is also fitted with a plastic seat. The following data are available for the July 2012 to June 2013 budget:

The direct-cost inputs for each trolley are:

The Marketing manager of Steelworks Ltd projects the following monthly sales and estimated selling prices of the two trolleys:

The actual inventory of finished goods as at 30 June 2012 is as follows:

The actual inventory of raw material as at 30 June 2012 is as follows:

The firm has a policy of carrying an end-of-month finished goods inventory of 100% of the following month's sales plus 25% of the second following month's sales. It is also company policy to keep raw material inventory at the end of a month at levels sufficient so that they have enough inventory on hand to manufacture the required number of trolleys for the following month. For example, the level of raw material inventory at the end of January will be the level of raw material required to enable the company to manufacture the number of trolleys required for February.

Unit costs of direct materials purchased and unit costs of finished goods sold remain unchanged throughout each budget year but can change from year to year. The actual and estimated unit costs for the two types of frames, for fabric and for Plastic seat are as follows:

Steelworks Ltd has a labour contract that calls for a wage increase of 4% per hour on 1 December every year. The current wage rate is $14.50 per hour. In addition to wages, direct manufacturing labour-related costs include superannuation contributions of $0.725 per hour and worker's compensation insurance of $0.30 per hour. These costs also increase by 4% per hour on 1 December every year. The cost of employee benefits paid by Steelworks Ltd for its employees is treated as a direct manufacturing labour cost.

Manufacturing overhead (both variable and fixed) is allocated to each trolley on the basis of budgeted direct manufacturing labour hours (DMLH) per trolley. The budgeted variable manufacturing overhead rate will be $4.50 per DMLH hour up to 30 November 2012 and is estimated to be $5.10 per DMLH from 1 December 2012. The fixed manufacturing overhead will be $14 000 per month up to 31 October 2012 and is budgeted to be $16 000 per month from 1 November 2012. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods.

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.

Use 3 (three) decimal places in your calculations and round the figures shown in your output sheets to the nearest dollar. You must use formulae to do your calculations in your output sheets.  However, please show the calculation of your figures on your output sheets. If your figures in your output sheets are incorrect and you do not show how you arrived at your figures you may not receive any marks or partial marks. The markers will carry forward any mistakes you made in earlier budgets but we cannot guess where figures come from if your figures are incorrect and you do not show how you calculated them.

Required:

(i) Prepare the following six (6) budgets by month for October, November and December of 2012 and the totals for these three months:

1. Revenue budget

2. Production budget in units

3. Direct material usage budget (both units to be used and the cost budget in dollars)

4. Direct material purchases budget in units and in dollars

5. Direct manufacturing labour hours and cost budget

6. Manufacturing overhead budget (you are not required to calculate the totals for the three months, only the individual months)

(ii) Prepare the following three (3) budgets for the three months ending 31 December 2012 only. (Show these budgets in total for the quarter only, not per month. Round your numbers for these budgets up to zero decimal places):

7. Opening and ending inventories budget for direct materials for the three months ending  31 December 2012.

8. Opening and ending inventories budget for finished goods for the three months ending   31 December 2012.

9. Income Statement budget for the three months ending 31 December 2012. Show the detail of how cost of goods sold was calculated in this budget.

(iii)  Print and submit the following for your answer to Part A (worth 12 marks - see marking criteria sheet):

• The input sheet
• The formulae spreadsheet(s) of all nine budgets in (i) above (i.e. showing the rows and columns together with any formula that has been used in each cell of the spreadsheet).
• The nine (9) budgets you prepared in (i) and (ii) above.

Please note: the 12 marks allocated for the use of spreadsheets are for the design and layout of your input and output spreadsheets and for the design and use of formulae. The formulae sheets are not considered to be calculations and are not marked or looked at to ascertain how you derived at your figures. Your formulae sheets will be marked merely to ascertain the linkages in your spreadsheets and the logical flow of the design of your spreadsheets. Please show your calculations in your output spreadsheets of the nine budgets required in (i) and (ii) above to enable the markers to award you partial marks if your figures are incorrect.

Hint: To help you with calculating some figures, it may be useful to design some spreadsheets in such a way to include a few months before and after the three months required in this assignment.