Finished Goods inventory purposes the plant aims to maintain a finished goods inventory equivalent of one (1) week of the current year's budgeted unit sales. As per the data provided above, on December 31, 2023 there was enough raw materials on hand to manufacture 15,000 units (valued at $1,760,000) and 14,600 completed units (valued at $1,626,000) were in Finished Goods Inventory. Panda group management believes these trends will hold for the foreseeable future and wish you to develop a 5 year budget of the firm's operations. Zorba has a practical production capacity of 200,000 units per annum but may rent additional factory space from the commencement of the 2024 financial year for an extra $200,000 per annum. This additional production space will allow it to increase its production by 50%. Inflation is forecast at 2.5% per annum over the budget period. Factory depreciation is calculated on a straight-line basis and adjusted for inflation to reflect the replacement cost of capital. All other costs including direct labour, raw material costs, and other overhead and administration costs are expected to increase annually at the rate of inflation. The FIFO method is used for cost of goods sold. Required (i) 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 January 1, 2024) (advice on the form of these budgets will be provided by the CSU lecturer). This budget must also take into account the current practical capacity production constraint of the Zorba Inc. Your spreadsheet must be flexible and so 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 so that 'what if' analysis to be undertaken. Hint: First calculate production without the capacity constraint. Then use Excel's "IF" command to derive budgeted production with the capacity constraint. You will then need to derive sales and end-of-year inventories based on budgeted production with the capacity constraint. (Excel resources are provided in "Readings and Resources". Your CSU lecturer will also be working through Excel applications in class) (ii) Using the model developed in part (1) calculate the impact on sales and profit if the option of renting extra factory space is exercised. (Submit results as a separate worksheet) (5 marks) (iii) Given your findings from part (i) and (ii) write a report for the CEO of Zorba Inc recommending whether to take up the option to increase production. In your report consider the strategic implications of the firm having extra productive capacity and advise the CEO of potential strategic actions that could be undertaken. (iii) Given your findings from part (i) and (ii) write a report for the CEO of Panda Group Ltd who operate the Zorba Inc. factory 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) The Panda Group Ltd has recently acquired Zorba Inc. which manufactures concrete imitations of ancient Greek artefacts which are sold to garden and home decorator retail outlets. You have been asked to prepare a 5 year budget forecast for Zorba Inc. For cost accounting reporting and budgeting, the Zorba Inc. factory employs a traditional manufacturing cost flow inventory and accounting system, however the company does not operate a Work-in-Process Inventory account. Financial and production data from the Zorba Inc plant's 2023 calendar year trading results are as follows: 2023 Year data Sales (Units) Price (average 2023 price received) Prime Costs (per unit) Raw Materials Direct Labour Closing Inventory: Raw Materials (15,000 units) Finished Goods (14,600 units) 172,680 $65.00 $16.65 $11.95 $1,760,000 $1,626,000 Variable Manufacturing Costs (per unit) $6.10 Factory Management Salaries (per annum) $313,000 Factory Plant & Equipment Depreciation (per annum) $75,000 Sales and Marketing Costs (per annum) $268,000 Finance Costs (per annum) $226,000 Non-Factory Administration Costs (per annum) $519,000 In the 2023 calendar year Zorba recorded sales of 172,680 units at $65 per unit. Zorba has consistently achieved year on year sales growth of 6% and it increases its selling price each year in line with the projected rate of inflation. The Zorba factory has a target safety stock of raw materials inventory for production amounting to the equivalent of one (1) week of the current year's budgeted unit sales. For