1. How much of the fixed costs were allocated between the Standard and Deluxe Boxes based on the Lumpsum Analysis Method? Is the CEO correct that the Deluxe Box is not contributing much to the company operating profit? Please elaborate on your answer and include evidence from Tab 1 of the Excel workbook.

14-Mar-22 In 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 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 H in Question 1. 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 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. Standard Boxes Profit Maximization ( obtain Column C to H from Project 2) Annual information ( for 12 Months) Standard boxes sold per Revenue (price x Variable Cost Variable Cost Fixed cost per Total Cost Price month (millions) volume) per Standard (cost per unit month (Fixed + Monthly Profit box x volume) (millions) Variable) (revenue - all costs) Annual Revenue Annual VC Annual FC Annual Total (millions) (millions) (millions) Costs (millions) Annual Profit 22.00 110.00 5 10.00 $ 50.00 S 10.00 $ 60.00 S 50.00 | S 1,320.00 $ 600.00 $ 120.00 $ 720.00 $ 600.00 5.5 21.60 $ 118.80 $ 10.00 $ 55.00 $ 10.00 $ 65.00 $ 53.80 $ 1,425.60 $ 660.00 $ 6 120.00 $ 780.00 $ 645.60 21.20 $ 127.20 $ 10.00 $ 60.00 $ 10.00 $ 6.5 70.00 $ 57.20 5 1,526.40 $ 720.00 $ 120.00 $ 840.00 $ 686.40 20.80 $ 135.20 10.00 $ 65.00 $ 10.00 $ 75.00 $ 60.20 $ 1,622.40 $ 780.00 S 120.00 $ 900.00 S 722.40 20.40 $ 10.00 $ 70.00 $ 10.00 $ 80.00 $ 2.80 $ 840.00 7.5 1, 713.60 $ 120.00 $ 960.00 20.00 $ 150.00 753.60 10.00 $ 75.00 $ 10.00 $ 85.00 $ 65.00 $ 1,800.00 5 900.00 120.00 $ 1,020.00 780.00 19.60 $ 156.80 $ 10.00 $ 80.00 $ 90.00 $ 8.5 10.00 $ 66.80 $ 1,881.60 $ 960.00 $ 120.00 S 1,080.00 $ 801.60 19.20 $ 163.20 $ 10.00 $ 85.00 10.00 $ 95.00 $ 9 68.20 $ 1,958.40 $ 1,020.00 $ 120.00 $ 1,140.00 $ 818.40 18.80 $ 169.20 $ 10.00 $ 90.00 $ 10.00 $ 100.00 $ 69.20 $ 2,030.40 $ 1,080.00 $ 120.00 $ 1,200.00 $ 830.40 9.5 $ 18.40 $ 174.80 $ 10.00 $ 95.00 $ 10.00 $ 105.00 $ 69.80 $ 2,097.60 $ 1,140.00 $ 120.00 $ 1,260.00 $ 837.6 10 S 18.00 $ 180.00 $ 10.00 $ 100.00 $ 10.00 $ 110.00 $ 70.00 $ 2,160.00 $ 1,200.00 $ 120.00 $ 1,320.00 $ 10.5 17.60 $ 184.80 $ 10.00 $ 840.00 105.00 $ 10.00 $ 115.00 $ 69.80 $ 2,217.60 $ 1,260.00 $ 120.00 $ 1,380.00 $ S 837.60 11 17.20 $ 189.20 $ 10.00 $ 110.00 $ 10.00 $ 120.00 $ 69.20 $ 2,270.40 $ 1,320.00 $ 120.00 $ 1,440.00 $ 830.40 11.5 16.80 $ 193.20 $ 10.00 $ 115.00 $ 10.00 $ 125.00 $ 68.20 $ 2,318.40 $ 1,380.00 $ 120.00 $ $ 1,500.00 $ 818.40 12 16.40 $ 196.80 $ 10.00 $ 120.00 $ 10.00 $ 130.00 $ 66.80 $ 2,361.60 $ 1,440.00 $ 120.00 $ 1,560.00 $ 12.5 16.00 $ 200.00 $ 10.00 $ 125.00 $ 801.60 10.00 $ 35.00 $ 65.00 $ 2,400.00 $ 1,500.00 $ 120.00 $ 1,620.00 $ 13 780.00 15.60 S 202.80 $ 10.00 $ 130.00 $ 10.00 $ 140.00 | $ 62.80 $ 13.5 2,433.60 $ 1,560.00 $ 120.00 $ 1,680.00 $ 753.60 15.20 $ 205.20 S 10.00 $ 135.00 $ 10.00 $ 145.00 $ 2,462.40 $ 14 60.20 $ 1,620.00 $ 120.00 $ 1,740.00 $ 722.40 14.80 $ 207.20 $ 10.00 $ 140.00 $ 10.00 $ 150.00 S 57.20 $ 2,486.40 $ 1,680.00 $ 120.00 5 1,800.00 $ 686.40 Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2) Annual information ( for 12 Months) Deluxe boxes sold per month Revenue ( price x Variable Cost Variable Cost Fixed cost per Total Cost Monthly Profit (millions) Price volume) Der Deluxe box (cost per unit month ( Fixed + Annual Revenue Annual VC Annual FC Annual Total Annual Profit x volume) (millions Variable) (revenue - all costs) (millions) (millions) (millions) Costs (millions) (millions) 30.00 S 30.00 S 20.00 $ 20.00 S 3.00 $ 23.00 $ 7.00 S 360.00 $ 240.00 $ 36.00 $ 276.00 $ 84.00 1.2 29.50 $ 5.40 $ 20.00 $ 24.00 3.00 $ 27.00 $ 8.40 $ 424.80 $ 288.00 $ 36.00 $ 100.80 1.35 29.00 $ 324.00 $ 39.15 $ 20.00 S 27.00 3.00 $ 30.00 S 9.15 | $ 469.80 $ 324.00 S 36.00 $ 360.00 S 109.80 1.5 28.50 $ 42.75 20.00 $ 30.00 S 3.00 $ 33.00 $ 9.75 $ 513.00 $ 360.00 $ 36.00 $ 396.00 $ 117.00 1.55 $ 28.00 $ 43.40 $ 20.00 $ 31.00 $ 3.00 $ 34.00 $ 9.40 $ 520.80 $ 372.00 $ 36.00 $ 408.00 $ 1.6 27.50 $ 44.00 $ 20.00 $ 112.80 32.00 $ 3.00 $ 35.00 $ 9.00 $ 528.00 S 384.00 $ 36.00 $ 420.00 $ 1.65 S 108.00 27.00 $ 44.55 $ 20.00 $ 33.00 $ 3.00 $ 36.00 S 8.55 $ 534.60 396.00 $ 36.00 $ 432.00 $ 1.7 102.60 26.50 $ 45.05 $ 20.00 $ 34.00 $ 3.00 $ 37.00 8.05 $ 540.60 $ 408.00 $ 36.00 $ 1.75 S 444.00 $ 96.60 26.00 $ 45.50 $ 20.00 $ 35.00 $ 3.00 $ 38.00 $ 7.50 $ 546.00 $ 420.00 $ 36.00 $ 456.00 $ 45.90 $ 90.00 1.8 25.50 $ 20.00 $ 36.00 $ 3.00 $ 39.00 $ 6.90 $ 550.80 $ 432.00 $ 36.00 $ 468.00 $ 82.80 1.85 $ 25.00 $ 46.25 $ 20.00 $ 37.00 $ 3.00 $ 40.00 $ 6.25 $ 555.00 $ 444.00 $ 36.00 $ 480.00 $ 75.00 1.9 24.50 S 46.55 $ 20.00 $ 38.00 $ 3.00 S 41.00 $ 5.55 $ $58.60 $ 456.00 $ 36.00 $ 492.00 $ 66.60 1.95 $ 24.00 $ 46.80 $ 20.00 $ 39.00 $ 3.00 $ 42.00 $ 4.80 $ 561.60 |$ 468.00 $ 36.00 $ 47.00 $ 504.00 $ 57.60 2 23.50 $ 20.00 $ 40.00 $ 3.00 $ 43.00 $ 4.00 $ 564.00 $ 480.00 $ 36.00 $ 516.00 $ 48.00 2.05 S 23.00 5 47.15 | $ 20.00 S 41.00 $ 3.00 S 44.00 $ 3.15 $ 565.80 S 492.00 $ 37.80 2.1 22.50 $ 47 .25 5 20.00 $ 42.00 $ 3.00 $ 45.00 5 36.00 5 528.00 $ 567.00 5 504.00 $ 27.00 2.15 22.00 $ 47.30 5 2 .25 5 36.00 $ 20.00 $ 43.00 5 540.00 $ 3.00 5 46.00 $ 567.60 $ 21.50 $ 15.60 2.2 47.30 5 1.30 $ 516.00 $ 36.00 $ 552.00 $ 20.00 $ 44.00 $ 3.00 $ O S 2.25 0.30 5 567.60 $ 528.00 3.60 47.25 $ 36.00 $ 564.00 21.00 S 20.00 $ 45.00 $ 3.00 $ 48.00 $ (0.75) $ 567.00 $ 540.00 $ 36.00 5 576.00 $ (9.00) Question 2 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 Standard Boxes Deluxe Boxes Total Number of Boxes per month (in Millions) 1.5 10.5 Number of Boxes per year (millions) 108 18 126 $ (in millions) $ (in millions) $ (in millions) Revenue S 2,030.40 $ 513.00 $ 2,543.40 Subtract: Variable Costs $ 1,080.00 $ 860.00 $ 1,440.00 Equals: Contribution Margin $ 950.40 $ 153.00 $ 1,103.40 Subtract: Fixed Costs 's 120.00 $ 36.00 S 156.00 Equals: Operating Profit 830.40 17.00 947.40 Operating Profit % (based on