You have been asked to prepare a 3 year budget forecast for the manufacturing of Donas Sisters dolls. Donas Sisters outsources the production of the dolls to the company ‘Dolled Up’.

‘Dolled Up’ utilises a traditional manufacturing cost flow inventory and accounting system and has provided you with the following information as at December 31^st 2019 to create a budget from.

‘Dolled Up ‘maintains a target safety stock of raw materials inventory and finished goods inventory amounting to the equivalent of three (3) weeks of the current year’s budgeted unit sales. At the end of the 2019 calendar year there were 175,000 completed units of ‘Uno Dolls’ in the warehouse as Finished Goods. There are enough raw materials on hand to manufacture 250,000 units of Donas Sisters dolls.

The Research and Marketing Department at ‘Donas Sisters’ predict that unit sales of the Donas Sisters doll will continue to grow for the next 3 years at a rate of 5% above the 2.95% current long term rate of inflation (budgeted 7.95% increase per annum), however after the 3 years sales will drop off significantly. The company is budgeting to achieve a year on year price increase of 4.5% over the long term inflation rate (7.45% annual increase). All other costs including direct labour, material costs, and other overhead and administration costs are expected to increase annually at the rate of inflation. The company pays tax at the Australian Corporate tax rate which is expected to hold at 30%. The inflation rate of 2.95% is expected to hold over the 3 year budget period.

‘Dolled Up’ recently had to modify an old factory to be able to manufacture the Donas Sisters dolls. However due to the expected growth in sales, the factory is nearing its practical manufacturing capacity of 950,000 dolls.

Required:

1. Using Excel develop a Sales, Production and Purchase budget as well as a budgeted Schedule of Cost of Goods Manufactured, Schedule of Cost of Goods Sold, and an Income Statement for each of the 3 years in the budget period (commencing January 1, 2019) (advice on the

form of these budgets is linked through the online topic modules and in the Interact Resources folder and is also available in the Appendix to Chapter 9 of the text book). This budget must also take into account the manufacturing facility practical capacity production constraint. Your spreadsheet must include a data section which enables inputs (such as the inflation rate, budgeted cost and sales increases, and the production limit) to be simply altered and ‘what if’ analysis to be undertaken. (Excel resources are provided on your Interact site to guide students on the use of the ‘IF’ formula which can be used for the budget production constraints

2. If sales continue to grow beyond the 3 years as the trend shows, the practical production capacity of 950,000 units will constrain ‘Dolled Up’ in a couple of years. The CEO of ‘Dolled Up’ has the option of investing in new plant equipment now and will be ready to start manufacturing by 2017. The new plant will have the capacity to manufacture 1.1 million dolls. A consulting engineering firm has advised that an investment of $20 million dollars will increase the life of the current factory by 15 years and lift production capacity 1.1 million units per annum. It is expected that the upgrade will be completed by the commencement of the 2017 calendar year and the extra investment will be depreciated on a straight line basis over its 15 year useful life.Using the excel model developed in part (i) calculate the impact on sales and profit if the option of upgrading the manufacturing facility is exercised and the practical production capacity of the factory is increased to 1.1 million (Include the additional factory depreciation expense as a manufacturing cost. Submit results as a separate worksheet).

2019 Year data Sales (Units) Price (average 2019 pricereceived) 825,000 $24.95 Prime Costs (per unit) Ingredients & Packing (including various fabric, wading and plastic head plus packaging costs) $12.00 Direct Labour $7.50 Variable Manufacturing Costs (per unit) Factory Management Salaries (per annum) Factory Plant & Equipment Depreciation (per annum) Sales and Marketing Costs (per annum) Finance Costs (per annum) $1.10 $650,000 $350,000 $925,000 $862,000 Non-Factory Administration Costs (per annum) $85,000 Inventory on Hand (at valuation): Ingredients & Packaging (250,000 units) Finished Goods (175,000 units) $3,750,000 $4,366,250