can you solve the maths which are provided in doc file

Question 1 Manufacturing Cost Schedule and Income Statement (10marks). This question relates to learning material and objectives from Online Topics 1, 2 and 3. As part of its expansion into foreign markets Mercury Australasia Ltd has recently purchased well known garden implement manufacturer 'Winner Mowers'. Mercury believe that with the rapid growth in the Chinese middle class famous brands like the Australian 'Winner' lawn mower will have a large market in China when marketed through Mercury's extensive Chinese distribution channels. As part of the due diligence around the purchase of 'Winner Mowers' you have been asked to prepare an up to date Cost of Manufacturing Schedule and Income Statement. Mr Lorne Greene, the management accountant at 'Winner', has provided you with the following data which relates to the 2016 financial year which has just completed. The firm manufactures the body and mechanics of the mowers and installs Briggs & Stratton motors to drive them. On June 30th, 2016 the following balances were recorded for 'Winner Mowers' inventory accounts (with comparative 1/7/2015 Opening Balance figures): Inventory Account: July 1, 2015 Jun 30, 2016 Work in Process (WIP) Inventory: $ Opening $ Closing Raw Materials 215,000 320,000 Briggs & Stratton Motors 364,000 387,000 Direct Labour 124,000 136,000 35,000 36,500 Raw Material Inventory 962,000 813,000 Briggs & Stratton Motors Inventory 465,000 127,000 1,871,000 1,927,000 Manufacturing Overhead Finished Goods Inventory Other balances recorded in the accounts of 'Winner Mowers' included the following: Account: $ Sales Revenue 12,688,000 Head Office Salaries and On-Costs Factory Management Salaries 1,037,000 275,000 Factory Wages (Direct) 2,126,000 Raw Material & Parts Purchases 1,688,000 Briggs & Stratton Motors 2,751,000 Interest & Financing Charges 545,000 Office Consumable Expenses 123,000 Miscellaneous Factory Expenses 93,000 Accounting & Audit costs 260,000 Office Heat Light & Power Costs 42,000 Factory Heat Light & Power Costs 1,624,000 Inwards Freight 365,000 Outwards Freight 863,000 Sales Department Expenses (including commissions) Depreciation of Manufacturing Plant Depreciation of Office Equipment Other Factory Overhead & Maintenance 1,713,000 288,000 22,000 421,000 The company is incorporated in Australia and pays tax at the Australian corporate rate of 30%. Because the Briggs & Stratton motors installed in the Winner lawnmowers are such a significant line item cost, the company likes to account for this cost separately from other materials in the Manufacturing Schedule. Required: 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 Winner Mowers Ltd for the 2016 financial 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 Activity-Based Costing (ABC) (15 marks) This question relates to learning material and objectives from Online Module 6 Whilst seconded to the Winner Mowers subsidiary of Mercury Australasia Limited you are also requested to conduct an analysis on the comparative costs of two of Winner Mower's products in the important lawnmower product range. The standard model lawnmower manufactured by the firm wholesales to retailers such as Bunnings and other outlets for $200. The firm has developed a more environmentally friendly product it brands as the 'GreenEdge' mower which wholesales for 50% more than the Standard at $300. Currently the cheaper Standard model lawnmower outsells the GreenEdge model at a rate of approximately 10 to 1. The Managing Director of Winner Mowers has reviewed the comparative margins earned on the two models using the following calendar year data estimates: 2016 Sales and Cost estimates Standard Model Enviro Model Sales (Units) 200,000 20,000 Selling price per unit($) $200 $300 Prime Costs per unit $100 $120 DLH per Unit 5 5 The firm has always applied overhead to product costs based on Direct Labour Hours as a cost driver. For 2016 it is expected that OH will be applied to each product at the rate of $15.00 per direct labour hour. The Standard model lawnmower built by Winner is superior to others on the market, however it has come under price pressure from competitors and is losing market share. Whilst the manufacture of the GreenEdge mower is more complicated than for the Standard model, both the Managing Director and Marketing Manager of Winner Mowers are convinced that the Gross Profit on sales of the GreenEdge model are achieved at a higher margin than the Standard model. Consequently, they believe that a greater focus should be placed on manufacturing and marketing the GreenEdge mower. Further, because of its perceived lower margin, the company does not believe that it is sustainable to discount the Standard model to match its competitors and compete for greater market share. The Marketing Manager believes it is time for the firm to focus on the more profitable niche market for the GreenEdge model. Whilst you are relatively new to Mercury Australasia Limited you are not convinced that the traditional approach to applying overhead at Winner Mowers is appropriate. You decide to prepare a report utilising Activity Based Costing techniques using the following overhead cost data: Activity Cost Cost Driver Machining Set Ups $2,600,000 Manufacturing Amount of Cost Driver Standard Enviro Number of Set ups 100 150 $6,900,000 Machine Hours 150,000 40,000 Finishing $4,500,000 Labour Hours 95,000 15,000 Packing $2,500,000 Number of Orders 100 50 Total Activity Costs $16,500,000 Required: (i) Using the current Factory-wide method of applying overhead at Winner Mowers Ltd develop a spreadsheet to calculate for each model the expected: i. ii. iii. iv. Gross Profit per unit, Gross Profit margin ($GP/$Sales), Total Gross Profit per Model, and Total Firm Gross Profit. (5 marks) (ii) Using the activity and cost data provided above conduct the same analysis utilising Activity Based costing techniques to again calculate for each model the expected: i. ii. iii. iv. Gross Profit per unit, Gross Profit margin ($GP/$Sales), Total Gross Profit per Model, and Total Firm Gross Profit. (5 marks) (iii) Write a brief report outlining your findings and making a recommendation to the management of Winner Mowers Ltd. (5 marks) 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. Mercury Australasia Ltd recently purchased a business which manufactures deck and camping chairs in Australia. You have been asked to prepare a 5 year manufacturing budget (2017 to 2021 inclusive) for the company Oze Eze Chair Ltd. Further information relating to the recently completed 2016 financial year includes: 2016 data Per Unit $ Average Chair Wholesale Selling Price 20.00 Tubular Steel Cost (per chair) 4.00 Canvas Cost (per chair) 1.85 Total Direct Material (per chair) 5.85 Direct Labour (per chair) 6.20 Variable Manufacturing Over Head (per chair) $1.0550 Factory Management Salaries (per annum) $365,000 Factory Plant & Equipment Depreciation (per annum) $420,000 Sales and Marketing Costs (per annum) $868,000 Finance Costs (per annum) $167,000 Non-Factory Administration Costs (per annum) $251,000 The Sales & Marketing team at Oze Eze Chair are very confident that sales will grow at approximately 6% pa over the budget period from their base of 307,500 in 2016. They also expect that they will be able to achieve price increases of 2.5% above the predicted inflation rate. Raw materials, direct labour and all other expenses (except depreciation which is straight line) are expected to increase at the rate of inflation on a per annum basis. Inflation is expected to remain steady at 2.5% over the budget period. Oze Eze Chair maintain a target buffer stock of Finished Goods inventory equivalent to 1 month of the current year's sales and have 26,800 finished chairs in inventory on July 1st, 2016 with a cost value of $415,400. Raw Material inventory levels equivalent to 5% of current year production requirements are also maintained. Commencing raw material inventory at July 1st, 2016 was valued at $95,500 and was enough material to manufacture 16,300 chairs. The company does not account for its very low level of incomplete work-in-process and so does not utilise WIP inventory accounts. The factory is approaching its practical manufacturing capacity of 350,000 chairs per annum however production capacity can be improved to 450,000 chairs per annum by expending $45,000 per annum (fixed) renting an adjoining factory space. If taken up the extra space must be rented from January 1, 2017 or the landlord will seek other tenants. (i) Using Excel develop a Sales, Production and Purchases 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 financial years in the budget period (commencing 2017). This budget must also take into account the current factory capacity production constraint of 350,000 units. Your spreadsheet must include a data section which enables inputs 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) Using the model developed in part (i) calculate the impact on sales and profit if the option of renting the adjoining factory space is exercised and the practical capacity of the factory is increased. (Submit results as a separate worksheet) (5 marks) (iii) Given your findings from part (i) and (ii) write a report for the CEO of Oze Eze Chair Ltd recommending whether to take up the rental option to increase production. 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 which management should consider when making their decision (approx. 300 words). (10 marks) Question 4 Strategic Management Accounting Case Study (25 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). Saturn Chocolate Bars STRATEGIC MARKET ANALYSIS You have been asked to join the Strategic Management Committee of Mercury Australasia Ltd 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 subsidiary divisions of the Mercury Australasia. The Saturn Chocolate Energy Bar, one of the original products of the Saturn Confectionery Co, has been losing market share to its major competitor the UK-based Yorkie Bar. The Saturn Bar has been the dominant chocolate snack bar sold through supermarkets, corner stores, petrol stations, and vending machines in Australia for more than twenty years. A generation of Australians know the brand's familiar marketing jingle and its accompanying advertising slogans. The competitor Yorkie Bar, originally produced in the UK by Rowntree near York (hence the name), is now manufactured by Nestle and is supported by an extensive and innovative marketing campaign. The overall market for chocolate bars has been relatively steady, however over the past 24 months sales of the Saturn Bar have fallen significantly from 60% of the total market to just 40%. This drop in sales volume coincided with the Australian launch of Yorkies in 2014 which has rapidly grown to now claim 30% of the total market in less than three years. Whilst the Saturn Bar remains the flagship product for the Saturn Group there is concern amongst members of the Strategic Committee that it is in decline and that its Return on Total Assets (a key company metric) may have fallen below the required rate of 20%. Product lines which do not meet the 20% Return on Total Assets (ROTA) benchmark may be discontinued. As the Management Accounting representative you have provided the Strategic Management Committee with the following breakdown of revenues and costs for the Saturn Chocolate Bar product line for the just completed 2016 financial year: Saturn Bar Saturn Factory Total Assets $15m Total Sales (Volume in Units) 4.0m Regular Retail Price (per unit) (price sold in store) $2.95 Gross Sales Value (per unit) (Price received by Saturn) $1.85 Prime Ingredient Costs $0.50 Manufacturing Costs $0.48 Logistic Costs $0.25 Total Costs (per unit) $1.23 Gross Profit (per unit) $0.62 Total Gross Profit $2,480,000 ROTA 16.53% The Saturn Marketing team have done a lot of work on developing a new branding strategy and new wrapping aimed at appealing to a new and younger target consumer. By slightly re-formulating the product ingredients the Research and Development team believe that they will save $0.10 per unit in prime ingredient costs. The Marketing Division have carried out some initial market research and believe that by re-launching the Saturn Bar with an extensive marketing campaign spending $0.25 per unit (approx. $1.5 million total) that the sales of the Saturn Bar will return to previous levels and increase by 50% on last year's unit sales. The Chair of the Strategic Management Committee has reviewed the marketing proposal and advised that even after allowing for the $0.10 per unit drop in ingredient costs, the proposed $0.25 per unit increase in marketing expenditure means that the products ROTA will fall further below the corporate required rate of 20% and the product may be facing discontinuation. As the management accounting representative you have established that since the fall in sales over recent years the Saturn Bar factory is only running at 50% of its manufacturing and logistics capacity. You are also aware that for the Saturn Bar its Prime Ingredient Costs are 100% Variable, whilst Manufacturing Costs and Logistic Costs are made up of 80% Fixed costs and 20% 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. Required: (i) Using Excel prepare a 'before and after' budget comparative analysis of the revenues and costs of the Saturn Bar product line. The analysis should incorporate the 50% predicted sales increase, the ingredient savings of $0.10 per unit, and the additional $0.25 per unit Marketing expenditure. Ensure you include in your analysis any impact of the budgeted production increase on other per unit manufacturing and logistics costs. (10 marks) (ii) It can be assumed that the 80% Fixed and 20% Variable break-down between variable and fixed costs will hold consistently across the industry (including for competitor Yorkie Bar). Assume that 75% of the predicted Saturn Bar unit sales increase will be made at the expense of the unit sales of their main competitor Yorkie. Whilst we know nothing about the price that Yorkie Bar receive for their product, assume that Saturn Bar and Yorkie have identical manufacturing and logistics cost structures at the commencement of the 2016 calendar year. Allowing for the change in sales volumes in Yorkie Bars, use Excel to calculate the expected impact of the drop in sales on Yorkie's per unit product cost. (5 marks) (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 the Saturn Bar product line (derived from your answer to (i)). b. the likely impact of the changes on the cost structure of Yorkie Bar (derived from your answer to (ii)). c. make a recommendation to the Strategy Committee on whether to go ahead with the planned changes. Include any other advice that you consider relevant to the Committee's decision making (including potential for further strategic action). (Please ensure that your answer adequately addresses ALL of the points above) (10 marks) Question 5 Service Cost Allocation (15 marks) This question relates to learning material and objectives from Online Module 6 The Pet Food Division of Mercury Australasia Ltd operates seven factories making individual brands across Australia. Two of the company's canned dog food brands are manufactured from the Division's Head Office site in Wodonga, Victoria. Whilst the factories operate completely separately they share centralised Catering, Information Technology (IT) and Human Resources (HR) support at the Wodonga site. The following information has been provided about costs in the Catering, IT, and HR Service Departments and services provided to Buddy and Friend the two dog food factories: Department Costs Catering IT $2,080,500 $1,608,000 Costs for the Catering and HR Service Departments are driven by the number of staff in each division, whilst costs of IT are allocated based on the number of computers requiring maintenance in each department. Cost Driver Catering No. of Employees No. of Computers 5 IT Buddy Friend 20 50 30 30 15 (i) Using the Direct Method of overhead allocation, distribute the Overhead from the Service departments to the two production departments (show all workings). (3marks) (ii) Using the Step Method of overhead allocation, distribute the Overhead from the Service departments to the two production departments (show all workings). (5 marks) (iii) Using the Reciprocal Method of overhead allocation, distribute the Overhead from the Support departments to the two production departments (show all workings). (7 marks) Question 6 Just-in-time (5 marks) This question relates to learning material and objectives from Online Module 7 What are the benefits of operating a JIT manufacturing system? What are the business risks associated with such an approach? Rationale This assignment directly addresses some of the key learning outcomes for ACC210 including that on successful completion of the subject students will: be able to demonstrate technical, computational, and analytical skills associated with the design and operation of product costing and accounting control systems; be able to demonstrate appropriate levels of critical thinking and judgement in identifying and solving management accounting problems; be able to review, critically analyse, and communicate their understanding of contemporary issues in qualitative and quantitative cost performance measurement; and be able to efficiently and effectively use computer spreadsheets as an aid to product costing, budgeting and performance evaluation. The requirements of this assignment cover up to and including Topic7 of the Online Learning materials. The assignment is designed to develop your problem solving, spreadsheet (Excel) design, and written communication skills. The questions require you to apply the knowledge and tools covered in the subject topics in order to demonstrate your understanding of the subject content and also to illustrate your capacity for strategic thinking. The assignment will also test your ability to communicate and explain the impacts of your findings whether through quantitative or written reports. The ability to communicate effectively has been identified by the accounting professional bodies as being critical to your future role as an accountant