Question 2 Comprehensive Manufacturing Budget (40 marks) This question builds on prior studies and relates to learning material and objectives from Topics 1, 2, 3 and 4. Attached is an example of a 'Sale Production Budget - Schedule of Manufacturing Costs' which may assist you in approaching this question. You have been asked to prepare a 5 year budget forecast for the Kiewa Milk Dried Infant Formula canned product. The recently purchased Kiewa Milk Coutilises a traditional manufacturing cost flow inventory and accounting system. Unit sales of this product have been rapidly increasing over the past three years with year on year growth of more than 20% per annum partly driven by Chinese Daigou shoppers who are satisfying the demand amongst the increasingly wealthy Chinese upper and middle class for high quality dairy products after several scandals involving Chinese-made produce. The Kiewa Milk Co has also been able to increase its per unit price of its infant formula by more than double the rate of inflation for each of the last three years. The marketing department are confident that these perannum increases will hold for the next five years. The Strategic Planning Committee of Jupiter Australasia Ltd are finalising plans for the new division and have asked youto prepare a comprehensive 5 year budgetforthe Dried Infant Formula product line. As at June 30th, 2020 the following financial and trading data was provided: 2020 Financial Year data Sales (Units) 27.65 million Price (average 2020 price per unit received) $2.300 $0.725 $0.040 $1.250 Prime Casts (per unit) Ingredients & Canning Direct Labour Other Variable Manufacturing Costs (per unit) Annual Fixed Manufacturing Overhead Inventory on Hand (at valuation): Ingredients & Packaging (335,000 equivalent units) Finished Goods (325,000 units) $5,000,000 $235,625 $727,500 All variable manufacturing costs including direct labour and ingredient costs are expected to increase annually at the rate of inflation. All manufacturing costs are variable and are assumed to vary directly with production (other than fixed manufacturing overhead). The current inflation rate of 2.0% is expected to hold over the year budget period. The Dried Infant Formula 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 one (1) week of the current year's budgeted unit sales. The Dried Infant Formula division does not utilise a Work in Charles Sturt University SubjectOutline AC 1220X AF Process inventoryaccount. The Dried Infant Formula factory has been operating out of its site in the small town of Tangambalanga in the Klewa Valley for almost 100 years and has undergone numerous upgrades. The manufacturing facility is currently operating at almost 80% of its estimated total practical manufacturing capacity of 35 million cans of baby formula per year. Required: (a) Five Year Budget For the 5 year budget period prepare: (25 marks) 1. Sales, Production and Purchases budget ii. Budgeted schedule of Cost of Goods Manufactured (COGM) il. Budgeted schedule of Cost of Goods Sold (COGS) and Gross Profit calculation Please note that marks will be awarded based both on the accuracy of your answer and on your spreadsheet design and formula use. The solution should incorporate the use of the IF, ROUND and 'Absolute Referencing functions in Excel. Use the IF formula to constrain unit sales to the production constraint. 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, then Production budgets, COGM, through to cost of Goods Sold and Gross Profit calculation. 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. (b) The Jupiter Australasia Strategic Management Committee has developed plans to have the factory completely overhauled in the next year (2021) which will double the capacity of the factory. The cost of the upgrade will be incurred as an additional $4 million Foed Overhead manufacturing cost per annum. Using the flexibility of the excel model developed in part (1) calculate the impact on sales and gross profit if the option of upgrading the manufacturing facility is exercised and the practical production capacity of the factory is increased by 100% and an extra manufacturing cost of $2 million is incurred each year from 2022. (Submit results as a separate worksheet) (5 marks) (c) Given your findings from part (I) and (ill) above, write a report for the Strategic Management Committee of Jupiter Australasia 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). (10 marks) Question 2 Comprehensive Manufacturing Budget (40 marks) This question builds on prior studies and relates to learning material and objectives from Topics 1, 2, 3 and 4. Attached is an example of a 'Sale Production Budget - Schedule of Manufacturing Costs' which may assist you in approaching this question. You have been asked to prepare a 5 year budget forecast for the Kiewa Milk Dried Infant Formula canned product. The recently purchased Kiewa Milk Coutilises a traditional manufacturing cost flow inventory and accounting system. Unit sales of this product have been rapidly increasing over the past three years with year on year growth of more than 20% per annum partly driven by Chinese Daigou shoppers who are satisfying the demand amongst the increasingly wealthy Chinese upper and middle class for high quality dairy products after several scandals involving Chinese-made produce. The Kiewa Milk Co has also been able to increase its per unit price of its infant formula by more than double the rate of inflation for each of the last three years. The marketing department are confident that these perannum increases will hold for the next five years. The Strategic Planning Committee of Jupiter Australasia Ltd are finalising plans for the new division and have asked youto prepare a comprehensive 5 year budgetforthe Dried Infant Formula product line. As at June 30th, 2020 the following financial and trading data was provided: 2020 Financial Year data Sales (Units) 27.65 million Price (average 2020 price per unit received) $2.300 $0.725 $0.040 $1.250 Prime Casts (per unit) Ingredients & Canning Direct Labour Other Variable Manufacturing Costs (per unit) Annual Fixed Manufacturing Overhead Inventory on Hand (at valuation): Ingredients & Packaging (335,000 equivalent units) Finished Goods (325,000 units) $5,000,000 $235,625 $727,500 All variable manufacturing costs including direct labour and ingredient costs are expected to increase annually at the rate of inflation. All manufacturing costs are variable and are assumed to vary directly with production (other than fixed manufacturing overhead). The current inflation rate of 2.0% is expected to hold over the year budget period. The Dried Infant Formula 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 one (1) week of the current year's budgeted unit sales. The Dried Infant Formula division does not utilise a Work in Charles Sturt University SubjectOutline AC 1220X AF Process inventoryaccount. The Dried Infant Formula factory has been operating out of its site in the small town of Tangambalanga in the Klewa Valley for almost 100 years and has undergone numerous upgrades. The manufacturing facility is currently operating at almost 80% of its estimated total practical manufacturing capacity of 35 million cans of baby formula per year. Required: (a) Five Year Budget For the 5 year budget period prepare: (25 marks) 1. Sales, Production and Purchases budget ii. Budgeted schedule of Cost of Goods Manufactured (COGM) il. Budgeted schedule of Cost of Goods Sold (COGS) and Gross Profit calculation Please note that marks will be awarded based both on the accuracy of your answer and on your spreadsheet design and formula use. The solution should incorporate the use of the IF, ROUND and 'Absolute Referencing functions in Excel. Use the IF formula to constrain unit sales to the production constraint. 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, then Production budgets, COGM, through to cost of Goods Sold and Gross Profit calculation. 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. (b) The Jupiter Australasia Strategic Management Committee has developed plans to have the factory completely overhauled in the next year (2021) which will double the capacity of the factory. The cost of the upgrade will be incurred as an additional $4 million Foed Overhead manufacturing cost per annum. Using the flexibility of the excel model developed in part (1) calculate the impact on sales and gross profit if the option of upgrading the manufacturing facility is exercised and the practical production capacity of the factory is increased by 100% and an extra manufacturing cost of $2 million is incurred each year from 2022. (Submit results as a separate worksheet) (5 marks) (c) Given your findings from part (I) and (ill) above, write a report for the Strategic Management Committee of Jupiter Australasia 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). (10 marks)