Question 3Comprehensive Manufacturing Budget(30 marks)

5 year budget forecast for the Empire Group's Australian Millenium Falcon drone factory which is based in Wagga, New South Wales.

For cost accounting reporting and budgeting, the Millenium Falcon 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 Millenium Falcon plant's 2021 trading results are as follows:

2021 Year data

Sales (Units)

5,750,000

Price (average 2021 price received)

$18.00

Prime Costs (per unit)

Raw Materials

$6.6500

Direct Labour

$1.9580

Closing Inventory:

Raw Materials (265,000 units)

$1,760,000

Finished Goods (128,000 units)

$1,626,000

Variable Manufacturing Costs (per unit)

$4.1000

Factory Management Salaries (per annum)

$1,023,000

Factory Plant & Equipment Depreciation (per annum)

$1,075,000

Sales and Marketing Costs (per annum)

$5,068,000

Finance Costs (per annum)

$3,276,000

Non-Factory Administration Costs (per annum)

$2,519,000

The Millenium Falcon factory has a target safety stock of raw materials inventory for production amounting to the equivalent oftwo (2) weeksof the current year's budgeted unitsales.For Finished Goods inventory purposes the plant aims to maintain a finished goods inventory equivalent ofone (1) weekof the current year's budgeted unitsales.As per the data provided above, on June 30, 2021 there was enough raw materials on hand to manufacture 265,000 drones (valued at $1,760,000) and 128,000 completed drones (valued at $1,626,000) were in Finished Goods Inventory.

Over the past 5 years of production, sales of Millenium Falcon drones have grown steadily at an average of 10% per annum as a result of the success of the 2019 Rise of Skywalker film and the success of the Mandalorian streaming series. The production facilities at the Wagga factory have a practical production capacity to manufacture 7.5 million drones per year and the factory is currently operating at slightly more than 75% of its manufacturing capacity.

The Marketing Division predicts that sales for Star Wars-themed drones will increase from the 2021 level at a rate of 10% per annum for at least the next 5 years. The Marketing Division also predict that the ongoing interest in Star Wars will allow the company to achieve a annual price increases of 3% above the forecast long term rate of inflation (inflation plus 3%).

Factory depreciation is calculated on a straight-line basis. 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 long-term inflation rate is predicted to hold at 1.25% over the 5 year budget period. The company pays tax at the Australian Corporate tax rate which is expected to remain at 30%.

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 (commencingJuly 1, 2021) (advice on the form of these budgets is linked through the online topic modules and in the Interact subject site Resources folder).This budget must also take into account the current practical capacity production constraint of the Millenium Falcon factory. 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. (Excel resources are provided on the subject 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 and video which deals with the formula required has been placed in the Resources folder in the subject Interact site to assist students (linked through Online Module 3).

ii. It is apparent that if sales continue to grow as forecast that the dronefactory will reach its practical production capacity of 7.5 million units in a couple of years. The company has the option of undertaking a major expansion of the plant to increase its capacity by one third. Engineering estimates are that (if undertaken in 2021) the expansion will cost $30 million dollars. The cost of this new plant would be expensed asadditionalplant depreciation over the next 10 years. It is expected that the upgrade will be completed by the commencement of the 2022 financial year.

Using the excel model developed in part (i) 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 33.33% from the beginning of the 2022 financial year. (Include the additional factory depreciation expense as a manufacturing cost. Submit results as a separate worksheet)(5 marks).