You have been asked to prepare a 5 year budget forecast for the 'Cat 'n'Kitty' Dried Cat Food factory in Wodonga.
The 'Cat'n'Kitty' division of the Jupiter Australia company utilises a traditional manufacturing cost flow inventory and accounting system.
The following previous years 'Cat'n'Kitty' financial and trading data was provided as at December 31st 2014:
|
2014 Year data
|
|
|
Sales (Units)
|
48 million
|
|
Price (average 2014 price received)
|
$5.50
|
|
|
|
Prime Costs (per unit)
|
|
|
Ingredients & Packing (including various meats, vegetables, flavour enhancers, packaging costs)
|
$2.2500
|
|
Direct Labour
|
$0.1515
|
|
Variable Manufacturing Costs (per unit)
|
$0.9875
|
|
Factory Management Salaries (per annum)
|
$1,325,000
|
|
Factory Plant & Equipment Depreciation (per annum)
|
$2,500,000
|
|
Sales and Marketing Costs (per annum)
|
$10,497,000
|
|
Finance Costs (per annum)
|
$5,625,000
|
|
Non-Factory Administration Costs (per annum)
|
$3,450,000
|
|
Inventory on Hand (at valuation):
|
|
|
Ingredients & Packaging (1,000,000 units)
|
$2,475,000
|
|
Finished Goods (985,000 units)
|
$3,340,000
|
'Cat'n'Kitty' maintains a target safety stock of raw materials inventory and finished goods inventory amounting to the equivalent of one (1) week of the current year's budgeted unit sales. At the end of the 2014 calendar year there were 985,000 completed units of 'Cat'n'Kitty' in the warehouseas Finished Goods. There was enough raw materials on hand to manufacture 1,000,000 units of 'Cat'n'Kitty'.
The Research and Marketing Department at 'Cat'n'Kitty' predict that unit sales of the company's pet food will continue to grow indefinitely at a rate of 3% above the 2.75% current long term rate of inflation (budgeted 5.75% increase per annum). The company is budgeting to achieve a year on year price increase of 1% over the long term inflation rate (3.75% annual increase). The Wodonga Cat'n'Kitty plant was built in the year 2000 for a cost of $50 million and is being depreciated straight line over its 20 year expected useful life. All other costs including direct labour, ingredient costs, and other overhead and administration costs are expected to increase annually at the rate of inflation. The company pays tax at the Australian Corporate tax rate which is expected to hold at 30%. The inflation rate of 2.75% is expected to hold over the 5 year budget period.
The 'Cat'n'Kitty' factory has been operating at its current site in Wodonga, Victoria since the late 1970s, with the current automated factory commencing operation 15 years ago. However due to the consistent growth in sales of the 'Cat'n'Kitty' pet food, the factory is nearing its practical manufacturing capacity of 55 million packets of cat food per annum.
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, 2015) (advice on the form of these budgets is linked through the online topic modules and in the Interact Resources folder 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 (such as the inflation rate, budgeted cost and sales increases, and the production limit) 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).
(ii) It is apparent that if sales continue to grow as forecast that the 'Cat'n'Kitty' factory will reach its practical production capacity of 55 million units in a couple of years. The CEO of 'Cat'n'Kitty' has the option of investing in new plant to expand the capacity of the factory or to simply limit costs and maximise profits within the 55 million production and sale limit. A consulting engineering firm has advised that an investment of $20 million dollars in new technology will increase the life of the current factory by 10 years and lift production capacity by 30% to 71,500,000 units per annum. It is expected that the upgrade will be completed by the commencement of the 2016 calendar year and the extra investment will be depreciated on a straight line basis over its 10 year useful life.
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 30% (Include the additional factory depreciation expense as a manufacturing cost. Submit results as a separate worksheet).
(iii) Given your findings from part (i) and (ii) write a report for the CEO of 'Cat'n'Kitty' recommending whether to take up the option to upgrade the productionfacility. 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.