- HINTS FOR THE ASSIGNMENT: Each of the assignment questions refer you directly or indirectly to the Topics they are related to.
Hints for Q1.
• Use the word version of the Assignment in the Assignment folder in Resources as a basis to copy and paste the data directly into Excel...this will remove the possibility of simple transcription errors.
• Topic module 2 provides guidance to an example from the text of the format you can use for the schedules and income statement
• My preference is when WIP is given broken up into separate amounts for RM, DL and OH (as it is in the assignment) that the WIP adjustment should be conducted separately for each of those accounts (not in a lump sum as in the text book example)
• Be careful with categorising freight costs ... this is assumed knowledge from your prior studies and is not covered in this text book...look it up on the net or in another text but understand that manufacturing overhead is a cost of 'the process of manufacturing'. Neither Freight In or Freight out are such a cost.
Hints for Q2.
• Undertake the Mars Pet food reflection task case study towards the end of Topic Module 2.
• Both the MP4 and the supporting XL file from that case study are available to you in the Reflection Tasks folder in Resources and will be useful in completing Q. 2
• In making your recommendations on the assignment case think strategically about how the information you have developed can be used to the advantage of your firm against their competitors. Remember that anyone can crunch the nubmers...knowing what the numbers mean in terms of strategic action is where real value is added by a management accountant.
Hints for Q3.
• This is a major budgeting question which requires you to construct a five year budget.
• Watch the ACC210 Excel tutorial and view the Excel IF file ... both are available in the Excel Resources folder in ResourcesReview Exhibit 9 in the Text in the context of the example Sales Production Budget XL file provided in the Resources folder as you enter it.
• Understand about production constraints and using an IF formula to set an Excel file up that deals with it
• Use the Round function when appropriate (which is usually any multiplication function)
• In analysing your recommendation ... again think strategically! What are the risks of hitting our production constraint
Question 1 Manufacturing Cost Schedule and Income Statement (10 marks).
This question relates to learning material and objectives from Online Topics 1, 2 and 3.
FoodMasters Australia is a wholly owned subsidiary of Jupiter manufacturing in its Australian factories a range of market-leading consumer food products including cooking sauces, herbs and spices, mustards, relishes, marinades and spreads. FoodMasters use a traditional manufacturing cost flow accounting system.
The Cost Accountant for the FoodMasters factory has just gone on maternity leave and you have been asked to finalise the reporting for the FoodMaster product line. As you are based in Wodonga, Victoria and the FoodMasters factory is based on the NSW mid-North coast near Port Macquarie, you have been emailed the following information about FoodMasters trading for the 2014 calendar year.
Sales Revenue 19,585,000
Sales & Marketing Expenses 1,225,000
Accounting & Audit costs 206,000
Interest & other finance charges 750,000
Purchases of Raw Materials & Packaging 6,387,000
Factory Direct Labour Cost 1,429,000
Factory Indirect Labour Cost 276,000
Factory Manufacturing Overhead 1,852,000
Depreciation of Factory Plant, Equipment & Machinery 1,250,000
Depreciation of Office Equipment & Furniture 38,000
Heat Light & Power Costs (75% Factory/25% Admin) 1,000,000
Office Salaries and Costs 246,000
Freight Outwards 482,000
Freight Inwards 68,000
On December 31st, 2014 selected inventory account balances of FoodMasters were as follows (with comparative 1/1/2014 Opening Balance figures):
Inventory Account: Jan 1, 2014 Dec 31, 2014
Work in Process (WIP) Inventory: $ Opening $ Closing
Raw Materials 17,500 20,500
Direct Labour 4,000 5,000
Manufacturing Overhead 5,000 6,500
- Raw Material Inventory 124,000 133,000
Finished Goods Inventory 650,000 1,107,000
Using Excel, from the information provided prepare the following financial reports:
Schedule of Cost of Goods Manufactured, Schedule of Cost of Goods Sold, and an after tax income statement for FoodMasters for the 2014 calendar year. (10 marks)
Note: Your Excel model should include a data input section and appropriate formulae. An example of the Manufacturing reports required can be found in the text book on p. 52 (6th edn. p.58) and an Excel example is available in Resources on the subject Interact site.
- Question 2 Strategic Management Accounting Case Study (20 marks)
This question builds on prior studies of Cost Volume Profit (CVP) analysis and relates to learning material and objectives from Online Modules 1 and 2.
(For assistance on this question you are advised to undertake the case study from Mars Petcare which is provided online (with solution) in Topic 2 as the Reflection Task).
‘TeaserMalts’ Chocolate Balls
STRATEGIC MARKET ANALYSIS
You have been asked to join the Strategic Management Committee of Jupiter Australia as the management accounting representative. The main task of the Committee is to carry out ongoing reviews of the profitability and viability of various product lines across all of the divisions of Jupiter Australia.
You are very surprised to see that TeaserMalts chocolate confectionery, one of the most iconic Jupiter brands, is under review. The TeaserMalt chocolate balls are sold through major supermarket chains in Australia and New Zealand and has been the clear market leader in its category since the product was introduced to Australian markets more than 40 years ago.
Over the last few years a number of competitor products which are close imitations of the original TeaserMalt chocolate ball have been released on the market, and these competitors have started to have an impact on the market share of TeaserMalts. The major competitor is the brand ChockoBalls which retails at a significantly lower price than the TeaserMalts. Market research indicates that price is a significant factor in why consumers are switching from TeaserMalts to ChockoBalls. Whilst the overall market for Chocolate Ball confectionery continues to grow rapidly, in the last two years TeaserMalts sales have fallen from 80% of the total market to 60% of the market. In the same time competitor ChockoBalls has grown from zero to 25% (see chart below). The total unit sales of chocolate ball confectionery in the Australia and New Zealand market for 2014 was equivalent to 100 million 50 pack units*.
Whilst the TeaserMalt product is still profitable the Strategic Committee is concerned about the downward trend in sales and profitability. Jupiter has a base expected return on investment of 25% (calculated as Gross profit divided by Total Assets).
*Adjusted for different pack sizes on offer estimated 5 billion individual choc balls
- The Marketing Department advises you that since their arrival on the market ChockoBalls has been discounting heavily and appear to be given a more favoured position on the major supermarket shelves. Marketing representatives have queried this and been advised by the supermarkets that prime shelf space is given to brands that deliver the highest profit to the supermarket. They advised that until recently, in the Chocolate Ball confectionery category, this had been TeaserMalts, but this was not the case at the moment.
As the Management Accounting representative you have provided the Strategic Management Committee with the following breakdown of revenues and costs for the ‘TeaserMalt’ product line for the just completed 2014 calendar year:
Total AssetsTeaserMalt Factory - Bendigo, Victoria $52m
Total Sales (Volume)(50 pack units) 60m
Regular Retail Price (per unit) (price sold in supermarket) $4.95
Retail Margin (per unit)(Supermarket Gross Profit per unit)(30%) $1.4850
Gross Sales Value (per unit) (Price received by TeaserMalts) $3.4650
Supermarket Rebates (unit)(Paid by TeaserMalts to Supermarkets) $0.15
Net Sales Value $3.3150
Prime Costs $1.25
Manufacturing Costs $1.20
Logistic Costs $0.65
Total Costs (per unit) $3.10
Gross Profit (per unit) $0.2150
Total Gross Profit $12,900,000
The Return on Total Assets (calculated by dividing Gross Profit by Total Assets) for TeaserMalts for the 2014 year was 24.81%. This is marginally below the required ROTA of Jupiter which is 25%. If the product continues to lose market share it may not be viable.
The Marketing Department has carried out research into the chocolate ball confectionery market which indicates that by discounting the recommended retail price of TeaserMalts by $0.40 per unit to $4.55 per unit*, unit sales of TeaserMalts will increase by 25% from their current level. In an attempt to simultaneously lower TeaserMalts product costs the research and development (R&D) team have identified that by slightly altering the ingredients quality and mix a saving of 20% of prime costs can be made.
However, the Chair of the Strategic Management Committee advises that even after allowing for the 20% savings in prime costs, discounting the product by $0.40 per unit will mean that the product will no longer achieve the firm’s long term required return on total assets (ROTA) of 25%,. The CEO argues that if this remains the case, the previously successful TeaserMalts product line may have to be discontinued.
You advise the Committee that you are aware that the ‘TeaserMalts’ manufacturing facility in Bendigo is currently running at 69% of its practical capacity and that the warehouse facility (logistics) is running at 54% capacity. You are also aware that whilst the TeaserMalts product’s Prime Costs are 100% Variable, other Manufacturing Costs and Logistic Costs are made up of 90% Fixed costs and 10% Variable costs.
You ask if you can be given time to prepare a report for the Strategic Management Committee on the Management Accounting cost and profit implications of the changes proposed by Marketing and R&D based on the budgeted costs and increases in sales and production.
*Remember that the manufacturer does not receive the retail price. The discounted wholesale price will be $3.0650 per unit, down from $3.4650 per unit.
(i) Using Excel prepare a ‘before and after’ budget comparative analysis of the revenues and costs of the TeaserMalts product line. The analysis should incorporate the $0.40 cent drop in price, the 20% predicted savings in prime costs, and include the 25% predicted sales increase. Ensure you include in your analysis any impact of the budgeted production increase on other per unit manufacturing and logistics costs.
(ii) It can be assumed that 90% Fixed 10% Variable cost break-down between variable and fixed costs will hold consistently across the industry (including for competitor ChockoBalls). Assume that 75% of the predicted TeaserMalts unit sales increase will be made at the expense of the unit sales of their main competitor ChockoBalls (meaning ChockoBalls unit sales will fall by 75% of the TeaserMalts sales increase). Assume that TeaserMalts and ChockoBalls have identical manufacturing and logistic costs structures at the commencement of the 2015 calendar year.
Allowing for the change in sales volumes use Excel to calculate the expected impact of the drop in sales on the per unit product costs of ChockoBalls
(iii) Prepare a brief report (maximum 300 words) for the Strategic Management Committee outlining the key points of your findings. Include some discussion on:
a. the likely impact of the changes on the cost and profit structure of TeaserMalts (derived from your answer to (i)).
b. the likely impact of the changes on the cost and profit structure of ChockoBalls (derived from your answer to (ii))..
c. Make a recommendation to the Committee on whether to go ahead with the planned changes. Include any other strategic advice that you consider relevant to the Committee’s decision making (for example profitability for supermarkets).
(Please ensure that your answer adequately addresses ALL of the points above)
- 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.
(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).
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 25% 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 25% (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 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).
Question 4 Overhead Cost Allocation (15 marks)
This question relates to learning material and objectives from Online Module 5.
You have been seconded to one of Jupiter’s sister companies Mazundai Ltd. Mazundai manufacture the Wouldn’tai range of small to medium cars. The company’s production line is highly automated. For management accounting reporting purposes manufacturing costs are aggregated into two production departments, Manufacturing and Assembly. Mazundai operates two separate service departments, Maintenance and Robotics, which support the main production departments. The company has decided that the most appropriate basis to allocate Maintenance costs is the number of maintenance jobs the department is required to undertake. The allocation of the Robotics department costs is based on the number of computer driven automated and robotic machines in each department.
Costs for the different cost centres were as follows:
Department No. of Maintenance Jobs No. of Robotic Machines
Manufacturing 265 16
Assembly 75 52
Maintenance 42 10
Robotics 28 12
Answer the following questions using spreadsheet models:
(i) Using the Direct Method allocate the service centre costs (2 marks)
(ii) Using the Step Method allocate the service centre costs (4marks)
(iii) Using the Reciprocal Method allocate service centre costs (6 marks)
(iv) Discuss the implications of your results and why such an analysis is important (3 marks)
- Question 5 Activity Based Costing (ABC) (15 marks)
This question relates to learning material and objectives from Online Module 6.
New Moon Pty Ltd, a subsidiary of Jupiter Australia, manufactures two models of garden mulching machines, the Standard model and a more expensive Eco-green model. The standard model retails for $300 whilst the Eco-green model sells for 50% more at $450. Currently the cheaper Standard model outsells the environmentally friendly model at a rate of 10 to 1.
Because of your expertise in accounting costing systems you have been asked by the Financial Controller of New Moon to provide advice. The Managing Director and Marketing Manager believe that New Moon receives a higher margin Gross Profit on sales of the Eco-green model than from the Standard model. The Marketing Manager is arguing that a greater focus should therefore be placed on promoting and selling the Eco-green model. The Standard model is superior to others on the market however it has come under price pressure from competitors and is starting to lose market share. Because of its low margin the company does not believe that it is sustainable to discount the Standard model. The Marketing Manager believes it is time for the firm to focus on the more profitable niche market for the Eco-green model.
The Managing Director of New Moon has reviewed the comparative margins earned on the two models using the following data:
2015 Sales and Cost estimates Standard Model Eco-Green Model
Forecast Sales (Units) 100,000 10,000
Selling price per unit($) $300 $450
Prime Costs per unit $120 $180
DLH per Unit 10 10
*The firm has always applied overhead to product costs based on Direct Labour Hours as a cost driver. For 2015 it is expected that OH will be applied to each product at the rate of $10 per direct labour hour.
You have reviewed the production costing break-downs for New Moon and you are not convinced that the traditional approach to applying overhead is appropriate and you decide to prepare a report utilising Activity Based Costing techniques using the following overhead cost data:
OH Activity OH Cost Cost Driver Amount of Cost Driver
Machining Set Ups $200,000 Number of Set ups 100 200
Laser Cutter $200,000 Machine Hours 150,000 50,000
Assembly $500,000 Labour Hours 100,000 25,000
Packing $200,000 Number of Orders 1000 500
Total $ Overhead $1,100,000
(i) Using New Moon’s current method of applying overhead develop a spreadsheet to calculate for each model the expected:
i. Gross Profit per unit,
ii. Gross Profit margin ($GP/$Sales),
iii. Total Gross Profit per Model, and
iv. Total Firm Gross Profit.
(ii) Using the overhead activity and cost data provided conduct the same analysis utilising Activity Based costing techniques to again calculate for each model the expected:
i. Gross Profit per unit,
ii. Gross Profit margin ($GP/$Sales),
iii. Total Gross Profit per Model, and
iv. Total Firm Gross Profit.
(iii) Given your analysis prepare a brief written response to the Managing Director of New Moon regarding the proposal to increase production and marketing of the Eco-green model. Include any factors (evidence) that you believe are appropriate to assist the Managing Director’s decision. You are encouraged to think strategically (competitively).