Question 3 Comprehensive Manufacturing Budget (20 marks) On your return from China you will undertake a 3 month secondment to wholly-owned subsidiary DairyCo based at their Australian Head Office in Albury Wodonga. Prior to your commencement in this role you have been asked to develop comprehensive production and income budget for the baby formula business unit for the next five years from 2018 to 2022 inclusive. The budget should include a sales budget, production budget, purchases budget and include a schedule of cost of goods manufactured, a schedule of cost of goods sold, and an income statement. The budget should be based on the following information: 2017 Financial Information sales Wholesale Price (per unit) Raw Material Cost (per unit) Direct labour (per unit) Manufacturing Overhead (per unit) Inventory 30/06/2017 Closing Inventory Raw Materials units 795,650 Administration Wages & Salaries General Expenses Sales commissions Utilities 886,800 Closing Finished Goods Inventory Units 663,000 WIP is not considered for the purpose of budgeting in this question Other Expenses: $ 37,793,375 5 1 1 1 665,100 1,969,275 1,200,000 375,000 1,300,000 361,250 Labour cost increases are expected to be held at the rate of inflation Utility expenses (Power & Water) are predicted to increase at 10% per annum whilst other non- manufacturing i. ii. Selling Price is expected to increase at 2% above the inflation rate Raw Material and Manufacturing Overhead costs are budgeted to increase at the inflation rate plus 1% iii. Required: 1. Five Year Budget (15 marks) For the 5 year budget period prepare: iv. The Australian Company tax rate is predicted to remain at 30% 2. expenditure will increase at the rate of inflation Target Raw Material Inventory is 10% of the expected production for the year Target Finished Goods Inventory is the equivalent of 1 month of the current year's sales The theoretical production capacity of the factory is 11,000,000 units and the practical production capacity for budgeting purposes is 10,000,000 units The long range inflation forecast for Australia is 2.5% over the 5-year budget period. Sales, Production and Purchases budget Budgeted schedule of cost of goods manufactured Budgeted schedule of cost of goods sold Budgeted income statement (including tax effect) Increased Production Constraint (5 marks) You are advised that the purchase of a new manufacturing machine can double the production capacity of the firm which is currently limited to 10,000,000 units. The extra cost of the new machine which can be installed for the commencement of the 2019 will be fixed at $200,000 per annum (before tax). In a new worksheet adjust the budget spreadsheet developed in part (a) to reflect this change and show the results.