management accounting
Question 1` 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. The Neptune International Ltd Strategy Group recently identified an opportunity to purchase a large dairy factory in regional North East Victoria which has virtually shut down as a result of financial problems which have beset its owner Murray Goulburn (see, for example, https://www.abc.net.au/news/2017-05-02/kiewa-devastated-by-murray-goulburn-closure/8489866). Kiewa Milk is located in the small township of Tangambalanga in the picturesque and pristine Kiewa Valley just outside the regional centre of Albury Wodonga. The valley is renowned for its world-leading dairy farms and the high quality milk and milk derivative products the Kiewa Milk factory has produced over a long period of time. The Kiewa factory produces high quality milk and yoghurt varieties and a variety of powdered-milk baby formulae all using the rich and creamy milk sourced from dedicated Kiewa Valley dairy farms. Neptune International Ltd is planning to purchase the Kiewa Milk business from Murray Goulburn and focus on the production of baby infant formula which will be exported to the burgeoning Chinese market. It is planned that 100% of production will be sold through the ChinaSouth Dairy Co which is a wholly-owned subsidiary of Neptune with offices in Hong Kong and Guangzhou. You have been asked to develop a comprehensive production and manufacturing income budget for the next 5 calendar years (2019 to 2023 inclusive). Your budget will be based on historical data provided by Kiewa Milk combined with future sales and cost estimates provided by the Neptune Strategy Group. Because of the current low production base of Kiewa Milk’s Infant Formula it is expected that unit sales will increase at 30% as the product is launched into the Chinese export market. 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. Kiewa Milk 2018 Financial Information Units $ Sales 24,525,000 61,312,500 Wholesale Price (per unit) 2.50 Raw Material Cost (per unit) 0.6250 Direct labour (per unit) 0.0500 Variable Manufacturing Overhead (per unit) 1.6000 Factory Fixed Costs (per annum) 5,000,000 Inventory 30/06/2017 Closing Inventory Raw Materials 950,000 590,000 Closing Finished Goods Inventory 938,000 2,500,000 The following are estimates by the Neptune International strategy group of costs for 2018 based on the re-configured Kiewa Milk facility after the purchase and re-structure. These should form the basis for the 5 year budget. Other Expenses (2018 Budget Estimates) Administration Wages & Salaries 750,000 General Administrative Expenses 500,000 Factory Manager Salary (including on-costs) 200,000 Non-manufacturing Utilities Costs 67,000 Depreciation: Office Equipment (Straight line) 5,000 2019 – 2023 Budget assumptions: Unit Sales are expected to increase at 30% per annum Selling Price is expected to increase at 3% above the inflation rate Raw Material and Manufacturing Overhead costs (incorporating power costs) are budgeted to increase at 1% above the inflation rate To ensure quality supply and to overcome issues of dairy farm viability Neptune plan to provide a 5 cents ($0.05) per unit direct rebate to dairy farmers based on quantity of milk provided to Neptune. This rebate will be production based and will not increase with inflation over the 5 year budget period. 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% Factory Fixed Cost manufacturing expenditure will not change over the budget period. Both Target Raw Material Inventory and Target Finished Goods Inventory are the equivalent of 2 weeks of the expected production for the year. The current production capacity of the Kiewa baby formula factory is est. to be 50 million units pa. (this is more than double its current production). The long range inflation forecast for Australia is 2.00% pa over the 5 year budget period. Required: (a) Five Year Budget (20 marks) For the 5 year budget period prepare: · Sales, Production and Purchases budget · Budgeted schedule of Cost of Goods Manufactured (COGM) · Budgeted schedule of Cost of Goods Sold (COGS) 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. Use the IF formula to constrain unit sales to the production constraint. 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, Cost of Goods Manufactured (COGM), through to Cost of Goods Sold (COGS) 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) After providing your initial budget to the Neptune International strategy group they are concerned by the growth of export sales in Kiewa Milk Baby Formula being constrained by the factory’s current theoretical limit of 50 million units per annum. They advise that a conversion of the former milk and yoghurt sections of the current Kiewa Milk factory will potentially increase the current production limit of the infant formula processing factory by 100%. This upgrade can be completed by the end of the 2020 year (for production in 2021) and can be achieved for an ongoing finance fixed cost of $1 million per annum (commencing at completion of the upgrade). The Strategy Group ask you to prepare a new budget showing the proposed changes. (c) Strategic Management Report (5 marks) Write a report for the Strategy Group of Neptune recommending whether you believe the option to upgrade and increase the productive capacity of the Kiewa Milk factory makes the purchase of the business more financially attractive. In your answer address any other issues (strategic and financial) which you believe the company should consider in making such a decision. In your report consider the strategic and financial implications of the firm reaching its production constraint and the alternative of having extra productive capacity. Your grade will depend on the accuracy and depth of your analysis, and your capacity to identify strategic issues (risks and opportunities) which management should consider when making their decision (approx. 300 words). Question 2 ‘Nutty Nut’ Candy Coated Chocolate STRATEGIC MARKET ANALYSIS You have joined the cross-discipline Strategic Management Committee of Neptune International Ltd as the management accounting representative. The key issue facing this top level management committee at the moment is how to improve profitability in several key product categories. The product currently under discussion is the ‘Nutty Nut’ line of sugar coated chocolates sold by Neptune through the major supermarket chains in Australia and New Zealand. The product has been a great success story for the Neptune Confectionery Division however lately it has come under increased price competition and the sales and market share of ‘Nutty Nut’ chocolates have fallen dramatically. The major competition comes from a similar product branded as ‘N&N’s’ which is manufactured by a multinational rival. The Marketing Department for the Neptune Confectionery Division has provided the following information about the sugar coated chocolate market during 2018: The Marketing Department advises you that at the end of the 2016 year Nutty Nut’s market share had been 80% and N & N’s had been only 10%. Since that time N & N’s have been advertising heavily and aggressively pricing their product in the market, increasing their market share to the current level of 30%. The marketing department believes that, by discounting the wholesale sale price by $0.25 from $3.20 to $2.95, gross unit sales will increase by 20%. The research and development team have identified that by slightly altering the raw material mix a saving of 10% of prime costs can also be made without impacting on the quality and taste of the product. As the Management Accounting representative you have provided the Strategic Management Committee with the following breakdown of revenues and costs for the ‘Nutty Nut’ product line for the just completed 2018 year: Nutty Nut Total Assets ‘Nutty Nut’ Factory $30m Total Sales (Volume in Units) 18m Regular Retail Price (per unit retail price) $3.99 Gross Sales Value Received (per unit wholesale price) $3.20 Supermarket Advertising Rebates (per unit) $0.20 Net Sales Value Received (per unit) $3.00 Prime Costs (per unit) $0.75 Other Manufacturing Costs (per unit) $1.25 Logistic Costs (per unit) $0.75 Gross $ Margin (Gross Profit) (per unit) $0.25 Total $ Margin (Gross Profit) $4.5m % Margin on Net Sales Value 8.33% % Return on Total Assets (ROTA) 15% The CEO of Neptune Confectionery, who is the Chair of the Strategic Committee, advises that even allowing for the 10% reduction in prime costs, discounting the product by $0.25 per unit will mean that the product will no longer achieve the firm’s required return on total assets (ROTA) of 17.5%. ROTA is calculated by dividing Gross Profit by Total Assets and currently sits at 15%. The CEO argues that if this remains the case, the previously successful ‘Nutty Nut’ product line may have to be discontinued. You advise the Committee that you are aware that the ‘Nutty Nut’ manufacturing facility is currently running at 53% of its practical capacity and that the warehouse facility (logistics) is running at 62% capacity. You are also aware that whilst the ‘Nutty Nut’ product’s Prime Costs are 100% Variable, Other Manufacturing Costs and Logistic Costs are made up of 80% Fixed and 20% Variable cost. You ask if you can be given time to prepare a report for the Strategic Committee on the cost and profit implications of the proposed changes and resultant increase in sales and production. For the purpose of your analysis it can be assumed that this cost break-down between variable and fixed costs will