Question 2 Comprehensive Manufacturing Budget (30 marks) You have been asked to prepare a 3-year budget forecast for the manufacturing of an organic lipstick. Royal Paints utilises a traditional manufacturing cost flow inventory and accounting system and has provided you with the following information for 2019.

Royal Paints maintains a target safety stock of raw materials inventory and finished goods inventory amounting to the equivalent of three (3) weeks of the current years budgeted unit sales. At the end of the 2019 calendar year, there were 175,000 completed units of Organic Lipstick in the warehouse as Finished Goods. There are enough raw materials on hand to manufacture a further 250,000 units of organic lipstick. The Marketing Department at Royal Paint predicts that unit sales of the organic lipstick will continue to grow for the next 3 years at a rate of 5%, however after those 3 years sales will drop off significantly. The company is budgeting to achieve a year on year price increase of 3.5%. All other costs including direct labour, material costs, and other overhead and administration costs are expected to increase annually at the same rate of 3.5%. The company pays tax at the Australian corporate tax rate which is expected to hold at 30%. Royal Paint recently had to modify an old Dulux paint factory to be able to manufacture organic lipstick. However, due to the expected growth in sales, the factory is nearing its practical manufacturing capacity of 825,000 lipsticks.

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, 2020. (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 textbook).

This budget must also take into account the manufacturing facility's practical capacity production constraint. Your spreadsheet must include a data section which enables inputs (such as the 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 you on the use of the IF formula which can be used for the budget production constraint).(15 marks)

2019 Year data Sales (units) 750,000 Price (average 2015 price received) $15.95 Prime Costs (per unit) Ingredients & Packing $4.50 Direct Labour $3.50 Manufacturing Costs (per unit) $4.00 Factory Management Salaries (per annum) $250,000 Factory Plant & Equipment Depreciation (per annum) $350,000 Sales and Marketing Costs (per annum) $700,000 Finance Costs (per annum) $888,000 Non-Factory Administration Costs (per annum) $685,000 Inventory on Hand (at valuation): Ingredients & Packaging (250,000 units) $1,125,000 Finished Goods (175,000 units) $2,790,000 2019 Year data Sales (units) 750,000 Price (average 2015 price received) $15.95 Prime Costs (per unit) Ingredients & Packing $4.50 Direct Labour $3.50 Manufacturing Costs (per unit) $4.00 Factory Management Salaries (per annum) $250,000 Factory Plant & Equipment Depreciation (per annum) $350,000 Sales and Marketing Costs (per annum) $700,000 Finance Costs (per annum) $888,000 Non-Factory Administration Costs (per annum) $685,000 Inventory on Hand (at valuation): Ingredients & Packaging (250,000 units) $1,125,000 Finished Goods (175,000 units) $2,790,000