Question 3 Comprehensive Manufacturing Budget (25 marks)
This question specifically addresses all 5 of the subject’s learning outcomes.
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 and are also available in the Resources section of the subject interact site in the Excel Resources folder.
On your manager Deirdre Lynch’s return from China she has asked you to undertake a 3 month secondment to the Mercury Inc. wholly-owned subsidiary AusMilk Ltd where you will be based at their Australian Head Office in Albury Wodonga. Prior to your commencement in this role you have been asked to develop a comprehensive production and manufacturing income budget for the export baby formula business unit and the Chinese market for the next five years from 2018 to 2022 inclusive. The budget should include a sales budget, production budget, purchases budget and include a schedule of cost of goods manufactured, a schedule of cost of goods sold, and gross profit calculation. There is no need to produce a budgeted income statement.
The budget should be based on the following information:
| AusMilk Ltd 2017 Financial Information | Units | $ |
| Sales | 51,850,500 | 116,663,625 |
| Wholesale Price (per unit) | 2.25 | |
| Raw Material Cost (per unit) | 0.6050 | |
| Direct labour (per unit) | 0.0800 | |
| Manufacturing Overhead (per unit) | 1.4575 | |
| Inventory 30/06/2017 | ||
| Closing Inventory Raw Materials | 2,000,000 | 1,210,000 |
| Closing Finished Goods Inventory Units | 985,000 | 2,100,000 |
| Other Expenses: | ||
| Sales commissions | 1,300,000 | |
| Administration Wages & Salaries | 1,875,000 | |
| General Expenses | 465,000 | |
| Factory Manager Salary | 150,000 | |
| Non-manufacturing Utilities Costs | 361,250 | |
| Motor vehicle expenses | 365,750 | |
| Dep’n: Factory Plant & Equipment (Straight line) | 765,000 | |
| Dep’n: Office Equipment (Straight line) | 87,500 |
Deirdre Lynch is very excited about the export prospects to China through the new distributor network she recently inspected and believes that sales of high quality milk products, particularly baby formula, in China will increase dramatically over the coming years. She has provided the following assumptions upon which you are to base your budget estimates:
| 2018 – 2022 Budget assumptions: | |||
| Unit Sales are expected to increase at 10% per annum | |||
| Selling Price is expected to increase at 2% above the inflation rate | |||
| Raw Material and Manufacturing Overhead costs are budgeted to increase at 1% above the inflation rate | |||
| Labour cost increases including management salaries are expected to be held at the rate of inflation | |||
| The Australian Company tax rate is predicted to remain at 30% | |||
| Non-manufacturing expenditure will increase at the rate of inflation | |||
| Target Raw Material Inventory is the equivalent of 2 weeks of the expected production for the year. | |||
| Target Finished Goods Inventory is the equivalent of 1 week of the current year’s sales | |||
| The production capacity of the Kiewa baby formula factory is est. 65 million units pa. | |||
| The long range inflation forecast for Australia is 2.25% pa over the 5 year budget period. | |||
Required:
(a) Five Year Budget (15 marks)
For the 5 year budget period prepare:
- Sales, Production and Purchases budget
- Budgeted schedule of cost of goods manufactured
- Budgeted schedule of cost of goods sold and Gross Profit calculation
Please note that marks will be awarded based both on the accuracy of your answer and on your spreadsheet design and formula use. The solution should incorporate the use of the IF, ROUND and ‘Absolute Referencing’ functions in Excel. 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, then Production budgets, COGM, through to Cost of Goods Sold and Gross Profit calculation..
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).
(b) Increased Production Constraint (5 marks)
You are advised that an upgrade to the drying and packaging production line will increase the baby formula production capacity of the Kiewa factory by 25%. The upgrade will have a one off cost of AUD $5 million and can be installed ready for use from January 1, 2019. In a new worksheet adjust the budget spreadsheet developed in part (a) to reflect this change and show the results. Assume the $5 million upgrade cost will be paid on January 1, 2019 and that AusMilk Ltd has a 12% cost of capital for assessing capital budgeting items.
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.