Comprehensive Manufacturing Budget (25 marks) You have been asked to prepare a 5 year budget forecast for the 'Chumpy' Dog Food canned product. This division of the Neptune Australia company utilises a traditional manufacturing cost flow inventory and accounting system. As at June 30th, 2021 the following financial and trading data was provided: 2021 Year data Sales (Units) 52.7 million Price (average 2021 price received per unit) $1.2500 Prime Costs (per unit) Ingredients & Canning $0.2150 Labour $0.0150 Other Manufacturing Costs (per unit) $0.5450 Inventory on Hand (at valuation): Ingredients & Packaging (1,013,000 units) $223,000 Finished Goods (2,020,500 units) $1,580,500 Sales and Marketing Costs $3,275,000 Finance Costs $2,865,200 Administration Costs $3,865,000 Market Research has indicated that the canned dog food market will continue to grow faster than the general economy. Sales of the Chumpy product are expected to increase in unit terms at a rate of 6% above the current long term inflation rate of 2.5%. Sales Price increases for Chumpy are expected to be kept in line with inflation during the budget period. All other manufacturing costs including direct labour and ingredient costs are expected to increase at the rate of inflation. All manufacturing costs (including overhead) are assumed to vary directly with production (unless otherwise stated). The company pays tax at the Australian Corporate tax rate which is expected to hold at current levels. The inflation rate of 2.5% is expected to hold over the 5 year budget period. The 'Chumpy' canned dog food factory maintains target safety stock of raw materials inventory and tin can inventory amounting to the equivalent of one (1) week of the current year's budgeted unit production. Finished goods inventory levels are kept at the equivalent of two (2) weeks of the current year's budgeted unit sales. At the end of the 2021 financial year there were 2,020,500 completed units of Finished Goods in the warehouse. There was enough raw materials and tin can inventory on hand at June 30th to manufacture 1,013,000 units. The Chumpy dog food division does not utilise a Work in Process inventory account. The Chumpy factory has been operating out of its site in Wodonga for nearly 40 years and has undergone several upgrades. The manufacturing facility is currently operating at approximately 85% of its total practical capacity of 62 million cans of dog food per annum. Required: a. 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 5 years in the budget period (commencing 2014) (advice on the form of these budgets will be provided on the subject Interact site 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 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 constraint). (15 marks) Hint: All 5 years of each budget should be shown side by side (1 column per year) for ease of comparison by management. All of the budgets should be presented on one worksheet together, working down the page commencing with the Sales and then Production budgets. You should be able to drag the formula across for the whole of the budget if the first years are properly constructed with a data input section and using absolute referencing. This makes the process much quicker and easier. An Excel help file has been placed on the Interact site to assist students. b. The Neptune Australia Strategic Management Committee has before them two options for consideration. Option 1 is to leave the facility as it is and incur normal repair and maintenance costs which are already budgeted for in factory running costs. Option 2 is to undertake major capital works and upgrade the cooking ovens and production line technology which will increase practical production capacity by 25% in the 2023 financial year. The upgrade can be completed by the end of the 2022 financial year. If the upgrade is undertaken it will cost the firm $600,000 in financing costs per annum commencing in the 2022 year. (i) Using the excel model developed in part (a) 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 by 25% (Submit results as a separate worksheet). (5 marks) (ii) Given your findings from part a. and part b.(i) write a report for the Strategic Management Committee of Neptune Australia recommending whether to take up the option to upgrade the production facility. In your report consider all of the strategic and financial implications to the firm of reaching its production constraint and any implications or opportunities arising from upgrading the facility and having extra productive capacity. Your grade will depend on the accuracy and depth of your analysis, and your capacity to identify strategic issues which management should consider when making their decision (approx. 300 words)