Requirement 1

P9.39 (appendix) Completion of budget schedules: manufacturer LO 9.5 , 9.9 D School Days Furniture Ltd manufactures a variety of desks, chairs, tables and book shelves that are sold to public schools in Western Australia. The financial accountant of the desk division is currently preparing the budget for the third quarter of this year. The following sales have been forecast for the third quarter. July 5 000 desks August 6 000 desks September 7 500 desks Each desk requires 3 metres of wooden planks and 1.5 hours of direct labour. Each desk sells for $200. Wooden planks costs $18 per metre, and at the end of each month there is enough wood remaining to cover 10 per cent of the next month's production requirements. The division incurs a cost of $25 per hour for direct labour, including on-costs. The division ends each month with enough finished goods to satisfy 20 per cent of the following month's sales. Required: Page 468 1. In an Excel spreadsheet construct and complete the following budget schedules. (a) Sales budget July August September Sales units 5 000 Selling price per unit $ 200 Sales revenue $1 000 000 (b) Production budget (units) July August September Sales units 5 000 Add ending inventory 1 200 1 500 Total units required 6 200 Less opening inventory 1 000 Planned production 5 200 (c) Raw materials purchases July August S Planned production 5 200 Raw material per unit Raw material required for production 15 600 Add ending inventory of raw materials 1 890 Total requirements 17 490 Less ending inventory of raw materials 1 560 Planned purchases of raw material in metres 15 930 Cost per metre $ 18 Planned purchases of raw material in dollars $286 740 (d) Direct labour budget July August September Sales units 5 000 Direct labour per unit 1.5 Direct labour hours required 7500 Cost per hour $25 Planned direct labour cost $187 500 2. Use your spreadsheet to show how your budget schedules will change if the following budget assumptions change. (Treat each of the two parts as separate scenarios.) (a) Competitors are expected to reduce their selling price, so School Days will reduce their selling price from $200