AutoSave OFF Q AF ? C ... Project3ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate Acrobat Tell me Comments LE Share Calibri (Body) A do v General Conditional Formatting v Insert 2 Paste Format as Table x Delete BI UV A $ ~ % 9 Cell Styles v Sort & Find & Analyze Format Sensitivity Create and Share Filter Select Data Adobe PDF v fx B D E G H M N 0 n Project 3 you will analyse managerial and costing information to improve the company's EBITDA. You will use what you have learned about cost behavior and apply activity-based W N costing and cost-volume-profit analysis to make recommendations about LGI's operational productivity. Step 1: Use the information you calculated in Project 2 Tab 3 Profit Maximization to populate has Columns Cto Hin Question 1. 5 Step 2: Assume the company operates for 12 months of the year convert the information you populated in Columns C to H to annual information and populate Columns I to M for both 6 the Standard and Deluxe Boxes. Step 3: Assume for this project that the only variable costs in this company are materials and labour. All other overhead costs will be assumed to be fixed. 10 11 12 Standard Boxes Profit Maximization ( obtain Column C to H from Project 2) Annual information ( for 12 Months) Standard boxes sold per month Variable Cost Total Cost (millions) Price Revenue (pricex Variable Cost per Fixed cost per Monthly Profit (revenue volume Standard box (cost per unit x (Fixed + Annual Revenue Annual VC Annual FC 13 all costs) Annual Total Costs volume month (millions) Variable) (millions) (millions) 5 22.00 $ (millions) 110.00 (millions) Annual Profit 10.00 $ 50.00 $ 15 10.00 $ 60.00 $ 50.00 5.5 21.60 $ 18.80 $ 10.00 $ 6 55.00 $ 10.00000 | $ 65.0000 $ 53.80 21.20 $ 127.20 $ 10.00 $ 60.00 $ 10.00000 $ 70.0000 $ 57.20 6.5 20.80 $ 135.20 $ 10.00 $ 65.00 $ 10.00000 |$ 75.0000 $ 60.20 20.40 $ 142.80 $ 10.00 $ 70.00 5 10.00000 | $ 20.00 $ 150.00 $ 80.0000 5 62.80 10.00 $ 75.00 $ 10.00000 5 19.60 $ 85.0000 $ 65.00 56.80 $ 10.00 $ 80.00 $ 10.00000 $ 90.0000 $ 66.80 19.20 $ 163.20 $ 10.00 $ 85.00 $ 10.00000 $ 95.0000 $ 68.20 18.80 $ 69.20 $ 9 .5 10.00 $ 90.00 $ 10.00000 $ 100.0000 $ 69.20 18.40 $ 174.80 $ 10.00 S 95.00 $ 10 10.00000 $ 105.0000 5 69.80 18.00 $ 180.00 $ 10.00 5 100.00 $ 10.00000 $ 110.0000 5 70.00 10.5 17.60 $ 184.80 $ 11 10.00 105.00 $ 10.00000 |$ 115.0000 $ 69.80 17.20 $ 189.20 $ 10.00 $ 110.00 $ 11.5 10.00000 $ 120.0000 $ 69.20 16.80 $ 12 93.20 $ 10.00 $ 115.00 $ 10.00000 $ 125.0000 $ 68.20 16.40 $ 196.80 10.00 5 120.00 $ 10.00000 $ 12 .5 130.0000 5 66.80 16.00 5 200.00 $ 10.00 $ 125.00 $ 13 10.00000 $ 135.0000 $ 65.00 15.60 $ 202.80 $ 13.5 10.00 5 130.00 $ 10.00000 $ 140.0000 $ 62.80 15.20 $ 14 205.20 $ 10.00 $ 135.00 $ 10.00000 $ 145.0000 $ 60.20 14.80 $ 207.20 $ 10.00 $ 140.00 $ 10.00000 $ 150.0000 $ 57.20 34 35 Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2) Annual information ( for 12 Months Deluxe boxes sold per month Price Revenue (pricex Variable Cost per Variable Cost Total Cost (millions) (cost per unit x Fixed cost per volume) Deluxe box month (millions) (Fixed + Monthly Profit (revenue - volume) Variable) all costs) Annual Revenue Annual VC 36 Annual FC Annual Total Costs Annual Profit 37 (millions) (millions) 30.00 $ (millions) 30.00 $ (millions) (millions) 20.00 $ 38 20.00 $ 3.00 $ 23.00 $ 1.2 29.50 $ 35.40 $ 1.00 20.00 $ 24.00 $ 3.00000 $ 39 27.0000 $ 8.40 1.35 29.00 5 39.15 $ 10.00 $ 27.00 3.00000 $ 30.0000 $ 9.15 1.5 28.50 $ 42.75 $ 20.00 $ 30.00 $ 3.00000 $ 33.0000 9.75 Tab 1 Lumpsum Analysis Tab 2 Sales Volume Analysis Tab 3 ABC Costing Tab 4 CVP + Ready Ix Accessibility: Investigate 100%AutoSave OFF Q AF ? C ... Project3ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate Acrobat Tell me Comments LE Share Calibri (Body) AA do v General Conditional Formatting v Insert v 2 Paste BIUv Format as Table A x Delete $ ~ % " Cell Styles v Sort & Find & Format Analyze Sensitivity Create and Share Filter Select Data Adobe PDF V fx B D E F G H M N 0 34 35 Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2) Annual information ( for 12 Months Deluxe boxes sold per month Revenue (pricex Variable Cost per Variable Cost Fixed cost per Total Cost (millions) Price volume) (cost per unit x Fixed + Monthly Profit (revenue - Annual VO 36 Deluxe box volume) month (millions) Variable) all costs) Annual Revenue Annual FC Annual Total Costs Annual Profit 37 (millions) 30.00 $ 30.00 $ (millions) (millions) (millions 20.00 $ 20.00 $ (millions 1.2 3.00 $ 23.00 $ 7.00 9.50 $ 35.40 $ 20.00 $ 24.00 $ 1.35 3.00000 $ 29.00 5 27.0000 $ 8.40 39.15 5 20.00 40 27.00 $ 3.00000 $ 9.15 1.5 30.0000 $ 28.50 $ 12.75 $ 20.00 $ 41 30.00 $ .00000 $ 33.0000 $ 9.75 1.55 28.00 $ 43.40 | $ 20.00 $ 31.00 $ 3.00000 $ 1.6 27.50 $ 34.0000 $ 9.40 44.00 20.00 $ 32.00 $ 3.00000 $ 1.65 7.00 5 35.0000 $ 9.00 44.55 $ 20.00 5 33.00 $ 3.00000 S 1.7 26.50 5 36.0000 5 8.55 45.05 5 20.00 S 34.00 $ 3.00000 $ 37.0000 $ 1.75 26.00 $ 8.05 45.50 $ 46 20.00 $ 35.00 $ 1.8 3.00000 $ 38.0000 $ 7.50 25.50 $ 45.90 5 47 20.00 $ 1.85 36.00 $ 3.00000 $ 39.0000 $ 25.00 $ 6.90 48 1.9 46.25 $ 20.00 $ 37.00 $ 3.00000 $ 24.50 5 40.0000 $ 6.25 46.55 $ 20.00 5 38.00 $ 3.00000 S 1.95 24.00 5 41.0000 5 5.55 46.80 $ 20.00 $ 39.00 $ 3.00000 $ 2 42.0000 $ 4.80 23.50 $ 47.00 $ 20.00 $ 40.00 $ 2.05 3.00000 $ 43.0000 $ 4.00 23.00 5 47.15 5 20.00 $ 41.00 $ 3.00000 $ 2.1 44.0000 $ 22.50 $ 47.25 $ 3.15 20.00 $ 42.00 $ 3.00000 $ 2.15 45.0000 $ 22.00 5 2.25 47.30 $ 20.00 5 43.00 $ 3.00000 5 2.2 21.50 5 46.0000 5 1.30 47.30 20.00 $ 44.00 $ 3.00000 $ 47.0000 $ 0.30 2.25 21.00 $ 47.25 $ 20.00 $ 45.00 $ 3.00000 $ 48.0000 -$ 0.75 57 Question The Company currently operates by selling 9 Million Standard Boxes and 1.5 Million Deluxe Boxes per month. The CEO is convinced that under the current cost allocation which allocates fixed costs on a lump sum method (arbitrarily using a monthly allocation basis) Deluxe boxes is not contributing much to company profit and with recent threats from environmental groups thinks that LGI should consider to no longer produce Deluxe Boxes. Required (place answers in the in the Grey Spaces provided) 1)Calculate how much operating profit each product makes? 2)Calculate the Operating Profit percentage (based on sales)for each product HINT Use the annual information calculated in Question 1 to complete Question 2 58 59 60 61 Standard Boxes Deluxe Boxes Total Number of Boxes per month (in 62 Millions) 9 1.5 10.5 Number of Boxes per year 63 (millions) 108 18 126 Tab 1 Lumpsum Analysis Tab 2 Sales Volume Analysis Tab 3 ABC Costing Tab 4 CVP + Ready Tx Accessibility: Investigate 100%AutoSave OFF NAE YC ... Project3ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate Acrobat Tell me Comments LE Share Calibri (Body) General Conditional Formatting v Insert v 2 Format as Table x Delete Paste BIUV A $ ~ % 9 :08 Sort & Find & Analyze Sensitivity Create and Share Cell Styles v Format Filter Select Data Adobe PDF A2 X V fx The sustainability manager is concerned about the long term sustainability implications of Deluxe Boxes on the environment and suggests changing to sustainable materials for the production of a Sustainable Deluxe Box. G M Question 1 The sustainability manager is concerned about the long term sustainability implications of Deluxe Boxes on the environment and suggests changing to sustainable materials 2 for the production of a Sustainable Deluxe Box. If the company switches the current quantity of Deluxe Boxes sold, to Sustainable Deluxe Boxes, there will be some cost implications 1)The Sustainable Deluxe Boxes could be made cheaper, and the sustainability manager believes that the company could sell the Sustainable Deluxe Boxes for $23 per box and end up making substantially higher profit than they ever did on the Deluxe Boxes. Based on knowledge of price elasticity of demand s/he/they suggest that it may in ime even result in much higher sales volumes. The marketing manager believes that a lower selling price will also entice current Deluxe Box customers to accept the switch over to the Sustainable Deluxe Box. 2)The new Sustainable Deluxe Boxes will still attract 60% of the fixed costs allocated to the old Deluxe Box under the ABC method used in tab 3. 3)The number of boxes sold will not currently be affected by this new selling price, as this is a very select group of customers for LGI. 4)The Standard Box costs and revenue will remain the same as that calculated under the ABC method 5)Because of the cheaper materials the variable costs for the Sustainable Deluxe Boxes will be reduce to $11 per box vice $20 per box previously. Required (complete the grey spaces) 1)Determine the profit and profit percentage for the Standard and Sustainable Deluxe Boxes Sustainable Deluxe Standard Boxes Boxes Total Quantity 108.0 18.00 126.00 Selling price per unit S 18.80 23 Revenue 0 Subtract: Variable Costs 11 Equals: Contribution Margin 12 Subtract: Fixed Cost 13 Equals: Operating Profit 14 Operating Profit % (based on revenue) 15 16 17 Question 2 8 The CEO is not convinced and still thinks that no form of a Deluxe Box, sustainable or not should be produced. The CEO indicates that consideration of the production of a Sustainable Deluxe Boxes will only be considered if it can achieve at least the same operating profit percentage for the Sustainable Deluxe Boxes as the operating profit percenatge indicated under the ABC costing method for Standard Boxes (See Tab 3). Required (Complete the grey spaces) 1)How much additional operating profit (in percentage) will be required from the Sustainable Deluxe Boxes to meet the same percentage as the Standard Boxes are generating, given the percentage that can currently be achieved on Sustainable Deluxe Boxes 19 20 21 % 22 Required profit See Question 1 23 Subtract: Existing profit See Q 1 above Tab 1 Lumpsum Analysis Tab 2 Sales Volume Analysis Tab 3 ABC Costing Tab 4 CVP + Ready Accessibility: Investigate - IUU 70AutoSave OFF NAE YC ... Project3ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate Acrobat Tell me Comments LE Share Calibri (Body) General Conditional Formatting v Insert v Format as Table x Delete Paste BIUV A $ ~ % 9 Sort & Find & Analyze Sensitivity Create and Share Cell Styles v Format Filter Select Data Adobe PDF A2 X V fx The sustainability manager is concerned about the long term sustainability implications of Deluxe Boxes on the environment and suggests changing to sustainable materials for the production of a Sustainable Deluxe Box. G M U 17 18 The CEO is not convinced and still thinks that no form of a Deluxe Box, sustainable or not should be produced. The CEO indicates that consideration of the production of a Sustainable Deluxe Boxes will only be considered if it can achieve at least the same operating profit percentage for the Sustainable Deluxe Boxes as the operating profit percenatge indicated under the ABC costing method for Standard Boxes (See Tab 3). Required (Complete the grey spaces). 1)How much additional operating profit (in percentage) will be required from the Sustainable Deluxe Boxes to meet the same percentage as the Standard Boxes are generating, given the percentage that can currently be achieved on Sustainable Deluxe Boxes 19 20 % 22 Required profit See Question 1 23 Subtract: Existing profit See Q 1 above Equals: Difference in additional profit 24 required 25 26 Question 3 27 Required: Work out the percentage that the company should mark up on the costs of Sustainable Deluxe Boxes to achieve the same profit % as for the Standard 28 boxes. (Complete the grey spaces % 30 Revenue % 100.00% Subtract: Required Operating Profit Equals: Cost % 35 Question 4 36 Assume the company can still sell the same quantity of the Sustainable Deluxe Boxes as for the Deluxe Boxes Required (Complete the grey spaces) Use the percentage calculated in Question 3 to determine at which price the company should sell the Sustainable Deluxe Boxes to reach the same profit 37 percentage as for the Standard Boxes. 38 Totals $ 39 Variable Costs 40 Plus : Fixed Costs 41 Equals: Total Costs 42 Determine Revenue 45 Units sold (per year) 46 Selling Price(Revenue) per unit 47 48 49 Question 5 Tab 1 Lumpsum Analysis Tab 2 Sales Volume Analysis Tab 3 ABC Costing Tab 4 CVP + Ready Accessibility: Investigate -AutoSave OFF NAE YC ... Project3ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate Acrobat Tell me Comments LE Share Calibri (Body) General Conditional Formatting Insert v Format as Table x Delete Paste BIUV A $ ~ % 9 Sort & Find & Analyze Sensitivity Create and Share Cell Styles v Format Filter Select Data Adobe PDF A2 X V fx The sustainability manager is concerned about the long term sustainability implications of Deluxe Boxes on the environment and suggests changing to sustainable materials for the production of a Sustainable Deluxe Box. A G M O 48 49 Question 5 50 Required: Prove that your calculation in Q 4 is correct. Complete the grey boxes 51 Proof: Total $ 52 Revenue 53 Subtract: Variable Costs 54 Equals: Contribution Margin 55 Subtract: Fixed Costs 56 Operating Profit Operating Profit % 58 59 Question 6 60 The marketing manger is concerned that the change could have a significant impact on sales as customers may see the sustainable boxes as an inferior product for which they still have to pay only a little bit less than the original price of the Deluxe Boxes. How many boxes would the company have to sell to break even on the new Sustainable Deluxe Boxes based on the 61 new selling price? Complete the grey boxes. $ Per unit Sustainable Deluxe 62 Boxes 63 Selling price 64 Subtract: Variable cost 65 Equals: Unit Contribution Margin 66 67 Total $ Fixed Costs (in total for Sustainable Deluxe 68 69 70 Breakeven Quantity Break-even Value 80 81 82 83 Tab 1 Lumpsum Analysis Tab 2 Sales Volume Analysis Tab 3 ABC Costing Tab 4 CVP + Ready Accessibility: Investigate