You have been asked to prepare a 5 year budget forecast for High Power International’s Bathurst production facility. This facility specialises in the production of the firm’s sports range of vehicles.

For cost accounting reporting and budgeting, the firm 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 plant’s results for the year ended 30 June 2021 were as follows:

2021 Year data
Sales (Units)	34,650
Price (average 2021 price received per unit)	$117,500
Prime Costs (per unit)
Raw Materials	$42,100
Direct Labour	$8,900
Closing Inventory:
Raw Materials (300 units)	$12,630,000
Finished Goods (450 units)	$26,880,780
Variable Manufacturing Costs (per unit)	$24,650
Factory Management Salaries (p.a.)	$16,654,000
Factory Plant & Equipment Depreciation (p.a.)	$250,746,000
Other Depreciation and Amortisation (p.a.)	$35,270,000
Advertising and Marketing Expenses (p.a.)	$16,240,000
Finance Costs (p.a.)	$14,960,000
General Administration Expenses (p.a.)	$15,430,000
Other Expenses (p.a.)	$8,680,000

Key company policies and other details pertaining to the next five years are provided below:

• The company has the following target safety stocks for inventories:

Inventory	Minimum safety stock
Raw materials	1 weeks
Finished goods	2 weeks

• Based on future sales projections, the firm expects sales and production to grow by 3.5% p.a. over the next six years.
• The plant is currently operating at 91% capacity.
• To remain cost-competitive, the firm expects to reduce the selling price by 1.5% p.a. over the next five years.
• Depreciation and amortisation costs are calculated on a straight-line basis.
• Raw materials costs are expected to grow by 1.8% p.a. over the next five years.
• As a result of the firm’s focus on production efficiency, conversion costs are expected to reduce by 3.1% p.a. over the next five years.
• All other costs are all expected to increase in line with expected inflation (i.e. 1.65%) over the next six years.

Required:

1. Using Excel, develop Sales, Production and Purchase budgets 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 next 5 years in the budget period (commencing 1 July 2021).
   1. Your spreadsheet must be professionally presented and 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).
2. It is apparent that if sales continue to grow as forecast that the factory will quickly reach its practical production capacity in future years. The company has the option of undertaking a major expansion of the plant to increase its capacity by 18%. Engineering estimates are that (if undertaken and completed in the year ended 30 June 2023) the expansion will cost $280 million dollars. The cost of this new plant would be expensed as additional plant depreciation over the next 8 years. As a result of the upgrades, conversion costs are expected to be reduced by a further 0.7% p.a. (in addition to the original 3.1% p.a. that had been factored in). Using the Excel model developed in part (i):
   1. 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.
   2. Discuss an alternative strategic option that the firm could consider to improve profitability through either revenue and/or cost management and calculate the impact of this alternative strategy on profit.
3. Prepare a report (no more than 1,000 words) which discusses and summarises your budget projections. Include a discussion of the nature of the budget reports and the key highlights regarding the firm’s expected performance and the impact of the alternative courses of action to improve profitability. Include relevant tables/charts/figures developed through Excel to illustrate these results. Outline your recommendation on the appropriate course of action and. In your discussion consider all of the strategic and financial implications to the firm. 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.

Answer rating: 100% (QA)

To solve this problem we will use Excel to create a budget for High Power Internationals Bathurst production facility for the next five years We will create a flexible spreadsheet that allows for easy ... View the full answer