In Project 3 you will analyze 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 C to 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 labor. All other overhead costs will be assumed to be fixed. Note: The Total Fixed Cost of 156 is supposed to be constant for the production of total boxes including Standard Box and Deluxe Box on Tabs 1, 2, and 3. On Tab 1, the total fixed costs of 156 are allocated based on a lump sum method (arbitrarily using a monthly allocation basis). On Tab 2, the total fixed costs of 156 are allocated based on the sales volume (the number of boxes sold). On Tab 3. the total fixed costs of 156 are allocated based on the cost drivers. Question 1 Annual information (for 12 Months) Standard boxes sold per Price month (millions) Revenue (price x volume) Variable Cost per Standard Variable Cost Fixed cost per (cost per unit box x volume) month (millions) Total Cost (Fixed + Monthly Profit (revenue - all costs) Variable) Annual Revenue Annual VC (millions) (millions) 5 $ 22.00 $ 110.00 $ 10.00 $ 50.00 $ 10.00 $ 60.00 $ 50.00 $ 1,320.00 $ 5.5 $ 21.60 $ 118.80 $ 10.00 $ 55.00 $ 10.00 $ 65.00 $ 53.80 $ 1,425.60 $ 120.00 $ Annual FC Annual Total (millions) Costs (millions) Annual Profit 600.00 $ 120.00 $ 720.00 $ 660.00 $ 600.00 780.00 $ 645.60 6 $ 21.20 $ 127.20 $ 10.00 $ 60.00 $ 10.00 $ 70.00 $ 57.20 $ 1,526.40 $ 720.00 $ 120.00 $ 840.00 $ 686.40 6.5 $ 20.80 $ 135.20 $ 10.00 $ 56.00 $ 10.00 $ 75.00 $ 60.20 $ 1,622.40 $ 780.00 $ 120.00 $ 900.00 $ 722.40 7 $ 20.40 $ 142.80 $ 10.00 $ 70.00 $ 10.00 $ 80.00 $ 62.80 $ 1,713.60 $ 840.00 $ 120.00 $ 960.00 $ 753.60 7.5 $ 20.00 $ 150.00 $ 10.00 $ 75.00 $ 10.00 $ 85.00 $ 65.00 $ 1,200.00 $ 900.00 $ 120.00 $ 1,020.00 $ 780.00 8 $ 19.60 $ 156.80 $ 10.00 $ 80.00 $ 10.00 $ 90.00 $ 66.80 $ 1,881.60 $ 960.00 $ 120.00 $ 1,080.00 $ 801.60 8.5 $ 19.20 $ 136.20 $ 10.00 $ 85.00 $ 10.00 $ 95.00 $ 68.20 $ 1,958.40 $ 1,020.00 $ 120.00 $ 1,140.00 $ 818.40 9 $ 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.60 10 $ 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 $ 840.00 10.5 $ 17.60 $ 184.80 $ 10.00 $ 105.00 $ 10.00 $ 115.00 $ 69.80 $ 2,217.60 $ 1,260.00 $ 120.00 $ 1,380.00 $ 837.00 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 $ 801.60 12.5 $ 16.00 $ 200.00 $ 10.00 $ 125.00 $ 10.00 $ 135.00 $ 65.00 $ 2,400.00 $ 1,500.00 $ 120.00 $ 1,620.00 $ 780.00 13 $ 15.60 $ 202.80 $ 10.00 $ 130.00 $ 10.00 $ 140.00 $ 62.80 $ 2,433.60 $ 1,560.00 $ 120.00 $ 1,680.00 $ 753.60 13.5 $ 15.20 $ 205.20 $ 10.00 $ 135.00 $ 10.00 $ 145.00 $ 60.20 $ 2,462.40 $ 14 $ 14.80 $ 207.20 $ 10.00 $ 140.00 $ 10.00 $ 150.00 $ 57.20 $ 2,486.40 $ 1,620.00 $ 1,680.00 $ 120.00 $ 120.00 1,740.00 $ 722.40 $ 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 Price (millions) 1 Revenue (price x volume) $ 30.00 $ 30.00 $ Variable Cost per Deluxe box 20.00 $ (cost per unit x volume) Variable Cost Fixed cost per month Total Cost Monthly Profit (Fixed + Annual Revenue Annual VC (revenue - all costs) (millions) Variable) (millions) (millions) Annual FC (millions) Annual Total Annual Profit 20.00 $ 3.00 $ 23.00 $ 7.00 $ 360.00 $ 240.00 $ 36.00 Costs (millions) (millions) $ 276.00 $ 84.00 1.2 $ 29.50 $ 35.40 $ 20.00 $ 24.00 $ 3.00000 $ 27.0000 $ 8.40 $ 424.80 $ 288.00 $ 36.00 $ 324.00 $ 100.80 1.35 $ 29.00 $ 39.15 $ 20.00 $ 27.00 $ 3.00000 $ 30.0000 $ 9.15 $ 469.80 $ 324.00 $ 36.00 $ 360.00 $ 109.80 1.5 $ 28.50 $ 42.75 $ 20.00 $ 30.00 $ 3.00000 $ 33.0000 $ 9.75 $ 513.00 $ 360.00 $ 36.00 $ 396.00 $ 117.00 1.55 $ 28.00 $ 43.40 $ 20.00 $ 31.00 $ 3.00000 $ 34.0000 $ 9.40 $ 520.80 $ 372.00 $ 36.00 $ 408.00 $ 112.80 1.6 $ 27.50 $ 44.00 $ 20.00 $ 32.00 $ 3.00000 $ 35.0000 $ 9.00 $ 528.00 $ 384.00 $ 36.00 $ 420.00 $ 108.00 1.65 $ 27.00 $ 44.55 $ 20.00 $ 33.00 $ 3.00000 $ 36.0000 $ 8.55 $ 534.60 $ 396.00 $ 36.00 $ 432.00 $ 102.60 1.7 $ 26.50 $ 45.05 $ 20.00 $ 34.00 $ 3.00000 $ 37.0000 $ 8.05 $ 540.60 $ 408.00 $ 36.00 $ 444.00 $ 96.60 1.75 $ 26.00 $ 45.50 $ 20.00 $ 35.00 $ 3.00000 $ 38.0000 $ 7.50 $ 546.00 $ 420.00 $ 36.00 $ 456.00 $ 90.00 1.8 $ 25.50 $ 45.90 $ 20.00 $ 36.00 $ 3.00000 $ 39.0000 $ 6.90 $ 550.80 $ 432.00 $ 36.00 $ 468.00 $ 82.80 1.85 $ 25.00 $ 46.25 $ 20.00 $ 37.00 $ 3.00000 $ 40.0000 $ 6.25 $ 555.00 $ 444.00 $ 36.00 $ 480.00 $ 75.00 1.9 $ 24.50 $ 46.55 $ 20.00 $ 38.00 $ 3.00000 $ 41.0000 $ 5.55 $ 558.60 $ 456.00 $ 36.00 $ 492.00 $ 66.60 1.95 $ 24.00 $ 46.80 $ 20.00 $ 39.00 $ 3.00000 $ 42.0000 $ 4.80 $ 561.60 $ 468.00 $ 36.00 $ 504.00 $ 57.60 2 $ 23.50 $ 47.00 $ 20.00 $ 40.00 $ 3.00000 $ 43.0000 $ 4.00 $ 564.00 $ 480.00 $ 36.00 $ 516.00 $ 48.00 2.05 $ 23.00 $ 47.15 $ 20.00 $ 41.00 $ 3.00000 $ 44.0000 $ 3.15 $ 565.80 $ 492.00 $ 36.00 $ 528.00 $ 37.80 2.1 $ 22.50 $ 47.25 $ 20.00 $ 42.00 $ 3.00000 $ 45.0000 $ 2.25 $ 567.00 $ 504.00 $ 36.00 $ 540.00 $ 27.00 2.15 $ 22.00 $ 47.30 $ 20.00 $ 43.00 $ 3.00000 $ 46.0000 $ 1.30 $ 567.60 $ 516.00 $ 36.00 $ 552.00 $ 15.60 2.2 $ 21.50 $ 47.30 $ 20.00 $ 44.00 $ 3.00000 $ 47.0000 $ 0.30 $ 567.60 $ 528.00 $ 36.00 $ 564.00 $ 3.60 2.25 $ 21.00 $ 47.25 $ 20.00 $ 45.00 $ 3.00000 $ 48.0000 $ 0.75 $ 567.00 $ 540.00 $ 36.00 $ 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 they should consider to no longer produce Deluxe Boxes. Required (place answers in the in the Grey Spaces provided) 1)Calculate how much profit each product makes? 2)Calculate the Profit percentage (based on sales) for each product. HINT Use the annual information calculated in Question 1 to complete Question 2 Question 1 A new intern thinks that the profit for Deluxe Boxes are higher than those calculated using the lump sum method (as in Tab1). The intern suggests calculating the profits using an allocation method for fixed costs based on sales volume(the number of boxes sold) to split the Fixed Costs between the Standard and Deluxe Boxes. Required: (Complete the grey spaces): 1) First calculate the percentage portion each product has of the total sales volume 1) How much fixed costs are allocated to each product based on the sales volume method suggested by the intern? 2) Also calculate the new operating profit percentage (based on sales) for each product. Volumes (per Month) Volumes per year (millions) Calculate the portion of Sales Volume (percentage sales volume) Calculate how much fixed costs are allocated to each product. New Profit Revenue Less VC Contribution Margin Less Fixed Costs Equals: Operating Profit Operating Profit % (based on Revenue) Standard Boxes Deluxe Boxes Total 9 1.5 10.5 108 18 126 Standard Boxes ($Millions) Deluxe Boxes ($Millions) Total Boxes($ Millions) Standard Boxes Deluxe Boxes Total Number Of Boxes (in Millions per month) Volume per year (millions) 9 1.5 10.5 108 18 126 $ (in millions) $ (in millions) $ (in millions) Revenue $ 2,030.40 $ 513.00 $ 2,543.40 Less: Variable Costs $ 1,080.00 $ 360.00 $ 1,440.00 Equals: Contribution Margin $ 950.40 $ 153.00 $ 1,103.40 Less: Fixed Costs $ 120.00 $ 36.00 $ 156.00 Equals: Operating Profit $ 830.40 $ 117.00 $ 947.40 Contribution Margin Ratio % Operating Profit % (based on revenue) 40.90% 22.81% 37.25%