AutoSave OFF Q AF ? C ... Project3ExcelWorkbook Home Insert Draw Page Layout Formulas Data Review View Automate Acrobat Tell me Comments LE Share Calibri (Body) do v General Conditional Formatting v Insert 2 Format as Table x Delete Paste B A $ ~ % 9 Sort & Find & Analyze Sensitivity Create and Share Cell Styles v Format Filter Select Data Adobe PDF A18 X v fx D F G H M N O Q R 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 percenatge portion each product has of the total sales voume 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 Standard Boxes Deluxe Boxes Total Volumes (per Month) 9 1.5 10.5 Volumes per year (Millions) 108 18 126 Calculate the portion of Sales Volume (percentage sales volume) 8 Calculate how much fixed costs are allocated to each product. Standard Boxes Total Boxes($ 10 New Profit Calculation ($Millions) Deluxe Boxes ($Millions) Millions) 11 Revenue Subtract Variable Costs Equals: Contribution Margin Subtract Fixed Costs Equals: Operating Profit Operating Profit % (based on Revenue) Tab 1 Lumpsum Analysis Tab 2 Sales Volume Analysis Tab 3 ABC Costing Tab 4 CVP + Ready Accessibility: Investigate 100%B C D E F G H 11 M 12 Standard Boxes Profit Maximization ( obtain Column C to H from Project 2) Annual information ( for 12 Months Standard boxes sold per month Revenue (pricex Variable Cost per Variable Cos Total Cost (millions) Price Fixed cost per Monthly Profit (revenue - 13 volume) Standard box (cost per unit x (Fixed + volume) month (millions) Variable) all costs) Annual Revenue Annual VC Annual FC Annual Total Costs 14 5 22.00 $ 110.00 $ (millions) 15 10.00 $ 50.00 $ (millions) (millions) 5.5 10.00 $ 60.00 $ (millions) Annual Profit 21.60 $ 50.00 $ 1,320.00 $ 600.00 $ 120.00 $ 720.00 $ 600.00 16 18.80 $ 10.00 $ 6 55.00 $ 10.00000 $ 21.20 $ 65.0000 $ 53.80 $ 27.20 $ 10.00 $ ,425.60 $ 60.00 $ 660.00 120.00 $ 780.00 $ 645.60 6.5 20.80 $ 10.00000 $ 70.0000 $ 135.20 $ 57.20 $ 10.00 $ 65.00 $ 1,526.40 $ 720.00 $ 18 7 20.40 $ 10.00000 $ 120.00 $ 75.0000 $ 686.40 142.80 $ 60.20 $ 840.00 $ 10.00 $ 70.00 $ 1,622.40 $ 780.00 $ 120.00 $ 900.00 $ 722.40 19 7.5 20.00 $ 10.00000 $ 80.0000 $ 50.00 $ 52.80 $ 10.00 $ 75.00 $ 1,713.60 $ 8 19.60 $ 10.00000 $ 340.00 $ 20.00 $ 960.00 $ 753.60 20 56.80 $ 85.0000 $ $5.00 $ 10.00 5 1,800.00 $ 80.00 $ 900.00 $ 8.5 19.20 $ 10.00000 $ 90.0000 $ 20.00 $ 1,020.00 S 56.80 $ 780.00 163.20 $ 10.00 $ 1,881.60 $ 85.00 $ 960.00 $ 20.00 $ 1,080.00 $ 9 801.60 18.80 $ 10.00000 $ 169.20 $ 95.0000 $ 68.20 $ 10.00 $ 90.00 $ 1,958.40 $ 10.00000 $ 1,020.00 $ 100.0000 $ 120.00 $ 1,140.00 $ 9 .5 59.20 $ $18.40 18.40 $ 174.80 $ 10.00 $ 95.00 $ 2,030.40 $ 10.00000 $ 1,080.00 $ 120.00 $ 1,200.00 $ 10 830.40 18.00 $ 105.0000 $ 180.00 $ 59.80 $ 10.00 $ 100.00 $ 2,097.60 $ 10.00000 $ 1,140.00 $ 110.0000 $ 120.00 $ 70.00 $ 1,260.00 $ 837.60 10.5 7.60 5 184.80 $ 10.00 $ 105.00 $ 2,160.00 $ 10.00000 $ 1,200.00 $ 120.00 $ 115.0000 $ 1,320.00 | $ 840.00 17.20 $ 189.20 $ 59.80 $ 10.00 $ 110.00 $ 2,217.60 $ ,260.00 $ 120.00 $ 16.80 $ 10.00000 $ 120.0000 $ 1,380.00 | $ 837.60 193.20 $ 59.20 $ 10.00 $ 115.00 $ 2,270.40 $ 1,320.00 $ 10.00000 $ 120.00 $ 1,440.00 $ 196.80 $ 58.20 $ 830.40 16.40 $ 125.0000 $ 10.00 $ 12.5 120.00 $ 2,318.40 $ 16.00 5 10.00000 $ 1,380.00 $ 120.00 $ 130.0000 $ 1,500.00 $ 818.40 200.00 $ 66.80 $ 10.00 $ 125.00 $ ,361.60 $ 1,440.00 $ 120.00 $ 1,560.00 $ 30 10.00000 $ 135.0000 $ 801.60 w 15.60 $ 202.80 $ 65.00 $ 10.00 $ 130.00 $ 2,400.00 $ 1,500.00 $ 20.00 $ 1,620.00 S 780.00 13.5 15.20 $ 10.00000 $ 140.0000 $ 205.20 $ 10.00 $ 62.80 $ 135.00 $ 2,433.60 $ ,560.00 $ 10.00000 $ 145.0000 $ 120.00 $ 1,680.00 5 753.60 14 14.80 $ 207.20 $ 50.20 $ 10.00 $ 2,462.40 $ 140.00 $ 1,620.00 $ 10.00000 |$ 120.00 $ 150.0000 $ 1,740.00 $ 722.40 57.20 $ 2,486.40 $ 1,680.00 $ 120.00 $ 1,800.00 $ 686.4034 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 (price x Variable Cost per Variable Cost Total Cost (Fixed + Monthly Profit (revenue - (millions) 36 volume) (cost per unit x Fixed cost per Deluxe box Annual Revenue Annual VC Annual FC Annual Total Costs volume) month (millions) Variable) all costs) 37 (millions) (millions) 1 30.00 $ (millions) 30.00 $ (millions) 20.00 $ 20.00 $ 3.00 $ 23.00 $ 38 7.00 $ 1.2 360.00 $ 240.00 $ 29.50 $ 35.40 $ 36.00 $ 276.00 20.00 $ 24.00 $ 3.00000 $ 39 27.0000 $ 8.40 $ 1.35 424.80 $ 288.00 $ 29.00 $ 39.15 $ 36.00 $ 324.00 20.00 $ 27.00 $ .00000 $ 40 30.0000 $ 9.15 $ 469.80 $ 1.5 324.00 $ 28.50 $ 42.75 $ 36.00 $ 360.00 20.00 $ 30.00 $ 3.00000 $ 41 1.55 83.0000 $ 9.75 $ 28.00 $ 513.00 $ 43.40 5 360.00 $ 20.00 $ 36.00 $ 396.00 31.00 $ 42 3.00000 $ 34.0000 $ 9.40 $ 1.6 520.80 $ 27.50 $ 44.00 $ 372.00 $ 36.00 $ 408.00 20.00 $ 32.00 $ 43 3.00000 $ 9.00 S 1.65 35.0000 $ 27.00 $ 528.00 $ 84.00 $ 44.55 $ 36.00 $ 20.00 $ 33.00 $ 420.00 3.00000 $ 1.7 36.0000 $ 3.55 5 26.50 $ 45.05 $ 534.60 $ 396.00 $ 36.00 $ 432.00 20.00 $ 34.00 $ 3.00000 45 1.75 37.0000 $ 26.00 $ 8.05 540.60 $ 45.50 $ 408.00 $ 36.00 $ 444.00 20.00 $ 35.00 $ 3.00000 46 38.0000 $ 1.8 7.50 5 25.50 $ 546.00 $ 45.90 $ 420.00 $ 36.00 $ 456.00 20.00 $ 36.00 $ 47 3.00000 39.0000 5.90 $ 1.85 550.80 $ 432.00 25.00 $ 46.25 $ 36.00 $ 468.00 20.00 $ 48 37.00 $ 3.00000 $ 40.0000 $ 6.25 5 555.00 $ 1.9 444.00 $ 24.50 $ 46.55 $ 36.00 $ 480.00 49 20.00 $ 38.00 $ 3.00000 $ 41.0000 $ 5.55 $ 1.95 24.00 $ 558.60 $ 46.80 $ 456.00 $ 492.00 20.00 $ 36.00 $ 39.00 $ 3.00000 $ 50 42.0000 $ 2 4.80 $ 23.50 $ 561.60 $ 47.00 $ 468.00 $ 504.00 20.00 $ 36.00 $ 10.00 $ 3.00000 $ 43.0000 $ 4.00 $ 2.05 23.00 $ $64.00 $ 480.00 $ 47.15 $ 36.00 $ 20.00 $ 516.00 41.00 $ 3.00000 $ 2.1 14.0000 $ 3.15 5 22.50 $ $65.80 $ 492.00 $ 7.25 $ 36.00 $ 528.00 20.00 $ 12.00 $ 3.00000 |$ 53 15.0000 $ 2.15 22.00 $ 2.25 $ 567.00 $ 17.30 $ 504.00 $ 36.00 $ $40.00 20.00 $ 43.00 $ 3.00000 $ 54 2.2 46.0000 $ 1.30 $ 567.60 $ 21.50 $ 47.30 $ 516.00 $ 36.00 $ 552.00 20.00 $ 44.00 $ 55 3.00000 $ 47.0000 0.30 $ 567.60 $ 2.25 21.00 $ 528.00 $ 47.25 $ 36.00 $ 564.00 20.00 $ 45.00 $ 3.00000 $ 48.0000 $ 0.75) $ 567.00 $ 540.00 $ 36.00 $ 576.00