Reference no: EM13333609

Southern Star Ltd manufactures two types of skateboards for the Australian market: Junior skateboards and Senior skateboards. The Senior skateboard is first of its kind in the world and has a wireless speed control system, which allows the rider to control the speed by hand. Southern Star Ltd buys the direct materials (wood) for the boards in metres and the wheels in boxes of 56 wheels per box. They use the same wood to manufacture the boards of both types of skateboards. The Junior skateboard is smaller and lighter than the Senior skateboard and hence uses less wood. Only two layers of wood are glued together to manufacture the Junior skateboard whereas three layers of wood are glued together to manufacture the board of the Senior skateboard. The Junior skateboard also uses smaller wheels than the Senior skateboard. The following data are available for the January 2013 to December 2013 budget:

The direct-cost inputs for each skateboard are:

The Marketing manager of Southern Star Ltd projects the following monthly sales and estimated selling prices of the two skateboards for 2013:

The actual inventory of finished goods as at 31 December 2012 is as follows:

The actual inventory of raw material as at 31 December 2012 is as follows:

The firm has a policy to keep the following level of finished goods inventory at the end of any particular month: 100% of the following month's sales plus 50% of the second following month's sales. It is also company policy to keep raw material inventory at the end of a particular month at levels sufficient to manufacture the required number of skateboards for the following month. For example, the level of raw material inventory at the end of January will be the raw material required to manufacture the number of skateboards 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 wood, the wheels of the two types of boards, and for the Speed control system are as follows:

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

Manufacturing overhead (both variable and fixed) is allocated to each skateboard on the basis of budgeted direct manufacturing labour hours (DMLH) per skateboard. The budgeted variable manufacturing overhead rate will be $2.50 per DMLH hour up to 31 May 2013 and is estimated to be $2.80 per DMLH from 1 June 2013. The fixed manufacturing overhead will be $15 000 per month up to 30 April 2013 and is budgeted to be $18 000 per month from 1 May 2013. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods.

Southern Star Ltd aims to stay ahead of its competitors and does ongoing research and development of its boards. They employ a full-time engineer to do research and development and it cost the company $8 000 per month to employ the engineer. The cost to employ the engineer will also increase with 3.5% on 1 June 2013. The fixed component of budgeted marketing costs is $120 000 for the year ending 31 December 2013. In addition to the fixed cost for the marketing department, the company pays sales people a commission of 5% of revenues earned in a particular month. The distribution costs consist of a fixed component of $27 000 for the six months ending 30 June 2013 and a variable component of $4 per skateboard sold for a particular month. All fixed costs are allocated equally per month for budgeting purposes.

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:

All budgets must be linked to the input sheet and where necessary, to the other budgets. Do not enter any numerical values in the output sheets.

(i) Design an input sheet that is the only source of data entry, link the input sheet with all the output sheets (budgets) and design and use formulae for calculating the figures in all the output spreadsheets. Please refer to the marking criteria sheet regarding the allocation of the 15 marks for the design and use of the spreadsheets and formulae. You will only receive maximum marks if the marker can follow your logic in the design of your spreadsheets and hence if it is not too difficult and time-consuming to mark.

(ii) Prepare the following seven (7) budgets by month for April, May and June of 2013 and the totals for these three months:
1. Revenue budget
2. Production budget in units
3. Direct material usage budget in units. Calculate the cost budget in dollars for each of the following five direct materials used for the quarter only: Wood (Junior), Wood (Senior, Wheels (junior), Wheels (Senior) and Speed control system. You do not have to show these cost budgets either per month or the opening inventory for the period.

4. Direct material purchases budget in units and in dollars. Show the number of boxes of wheels to be purchased and their costs.

5. Direct manufacturing labour hours and cost budget (show DMLH per product per month)

6. Manufacturing overhead budget (show total DMLH for both products per month)

7. Non-manufacturing costs budget

(iii) Prepare the following four (4) budgets for the quarter ending 30 June 2013 only. (Show the total of the three months in these budgets only. Do not show the monthly figures. Round your numbers for these budgets up to zero decimal places):

8. Opening and ending inventories budget for direct materials for the three months ending 30 June 2013.

9. Opening and ending inventories budget for finished goods for the three months ending 30 June 2013.

10. Cost of good sold budget for the three months ending 30 June 2013.

11. Income Statement budget for the three months ending 30 June 2013. Show the gross margin and the operating profit in this budget.