You have been asked to prepare a 5 year budget

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:

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