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 years budgeted unit production. Finished goods inventory levels are kept at the equivalent of two (2) weeks of the current years 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)