Question 3 Comprehensive Manufacturing Budget (30 marks)
This question builds on prior studies and relates to learning material and objectives from Online Modules 1, 2 and 3. Links to specific resources provided for this question relating to Manufacturing Budgets and Excel spreadsheets can be found in the Online Topic Modules.
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 warehouse as 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).
(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 ‘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).
(5 marks)
(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 production facility. 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 (approx. 300 words).
(10 marks)