Required information (The following information applies to the questions displayed below.) Donaldson Company's contribution format income statement for the most recent month is shown below: Sales Variable expenses Contribution margin Fixed expenses Net operating income Total $500,000 300,000 200,000 180,000 $ 20,000 Per Unit $12.50 7.50 $ 5.00 The company sold 40,000 units in its most recent month and its relevant range of production is 30,000 to 50.000 units. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on Goal Seek in Excel Click here for a briefitutorial on SOLVER in Excel. Click here for a brief tutorial on Charts in Excel 4. Using Charts: o. Prepare a profit graph for Donaldson Company to prepare your graph use sales volumes of zero units and 50.000 units as shown in cells B23 and 24 AM first step create a formula in cell c23 that computes the profit when unit sales are reto. Your formula should reference data from the contribution format home statement provided in rows 4 through 18 of the worksheet As a second step, copy your formula from cell C23 to cell C24. The profit (or loss shown in cells 23 and C24 provide the two data points that you need to create a profit graph b. Prepare a CVP graph for Donaldson Company, To prepare your graph use sales volumes of zero units and 50.000 units as shown in cells B29 and 30. As first step, create formulas in cells C29.029, and E29 that compute the total sales dollars, total foed expenses, and total expenses when unif sales are zero. Y formulas should reference data from the contribution format income statement provided in rows 4 through 18 of the worksheet. As a second step, copy your formulas from cells C29.029, and E29. to cells C30, D30, and E30. The information shown in rows 29 and 30 provide the data points that you need to create a CVP graph 5. Next, assume the company can decrease its variable expense per unit from $2.50 to $200 and decrease its fixed expenses from $180.000 To $150.000 Based on these assumptions: a. Prepare a profit graph for Donaldson Company b. Prepare a CVP profit graph for Donaldson Company lima ws"Neut click "Says." CVP graphi 5. Next, assume the company can decrease its variable expense per unit from $7.50 to $7.00 and decrease its fixed expenses from $180,000 to $150,000. Based on these assumptions a. Prepare a profit graph for Donaldson Company b. Prepare a CVP profit graph for Donaldson Company Save your completed worksheet to your computer and then upload it here by clicking "Browse." Next, click "Save." N 1 2 3 4 6 7 . 10 11 1 000 19.500 UNO 27.000 TE Last Year: es ption Series 5 VS 2.500 3,000 1,500 9.000 ON 29.00 12955 SESSO IN 0.005 SS5 V NO ST $ ME BOE 50058 1.005 $ SN PODS 11.55 115.00 92. 7 DOS 05 5 DOS 05 SOS 2000 2 2005 12. OC 65.00 2005 20 $ 26. 1650 $ 619 10.0005 2.1.500 5 255.000 100 255.000 5 558.000 15.00 11000 27 2,400 55 72 LED 400 5.500 2 15 14.OS 27 1164.000 2015 WAR Vibes Contribution 10 11 Net 11 DOO 200 07.03 SO S41 S. 15 SER SO ON 1 Projections for This Year: 14 Lewe 15 16 Changes 17 Change Charente 1 2 3 4 5 9 7 . 10 11 11 ET 21 S IL NO 5 06 1.000 2005 12.555 16.053 COO'S 5098 SOC 1600 93.00 68555 30.455 et Weber 19. 1000 S 35.00 20.005 400 19.00 55 1255 7500 29.00 27 11900 9033 2005 1000 3D $ 14 10 15. 95.00 7. 10000 5313 15.45 $ $ SOR S29 5 5 ADOS 2005 DO 46.00 12 10 505 S Se 25 Chart Conditions Requirement1 DO K 1 M N O P O R 8 9 10 11 12 13 14 15 Total Weighted Average CM per Unit 10,500 150,000 100% ,000 2% 9.00 $ 4.30 S 7,500 SS 79,00 5 33.18 5 9,000 65 69.00 5 30:36 S 15,000 10% 95.00 5 77.60 1,500 15 65.00 5 29.00 $ 3.000 2x 1.005 12.405 5.000 4% 0.005 13.45 12.000 EX 89.00 61.83 59.00 25.40 S 000 $ 592,500 5 248, 250 343,650 621,000 S 1425,000 $ 619.500 5 271,700 1 16.000 266,700 347,7605 261,000 $352,800 $ 97.500 5 41.500 50.000 132.0005 37,200 94,8005 294,000 5 1,000,000 $ 12,795.000 741.060 3.521.930 211.1203 326,000 4,223,070 1.000.000 $1,221070 100.ON 7. 11. 1005 15 150.000 SES ON 10 11 13 12 14 B ST 9 Total Weighted Average CM per Unit TOON 150.000 000 2% 5,000 -9.00 $ 4.30 $ 4.70 5 5% 7,500 79.00 $ 33.18 S 45.825 6X 9,000 69,00 5 30.365 38.64 $ 10% 15,000 95.00 $ 77.60S 17.40 $ 0058 IX 1,500 65.00 $ 29.00 $ 36.00 $ 10,500 59.00 5 25.40 5 33.60 $ 25 3,000 44.00 $ 12.40 $ 31.60 $ 4% 6.000 19.00 $ 13.485 35.52 61-3 27.17 H 1 M N P 6 7 8 whe 9 OT 11 12 13 14 Total 15 150,000 NO ON 6 10 7 9 11 12 13 Total 14 Weight CM ST 9 100% 150.000 15.000 18% 27,000 119.00 S 92.00 S 27.00 5 2 3,000 39.00 S 14.30 $ 24.70 $ 5% 7,500 79.00 5 33 18 45.25 $ $ $ 9,000 69.00 30:36 18.65 95.00 77.60 $ 12.40 X 10100 59.00 $ 25.40 33.60 5 1% 1,500 65.00 5 29.00 $ 36.00 $ 2x AN 1000 6.000 100 9005 12.40 S 1345 3160515525 XX 12.000 9.00 61.83 27.17 100.0% S 1.213.000 5 2.484.000 5 729,000 $ WO 117 000 $ 12,900 74,100 $ 592,500 $ 2480RSO 343,6505 621000 S1425,000 273,240 1 164,000 347.760 5 261,000 $ 619 500$ 264,700 352.00 5 97.500S 43.500 56.000 5 12000 37,200 96,300 $ 254000 5 1,00.00 $ 12,15,000 NO 741. KO 15713330 2131205 326,00 221070 1.000.000 5 1.223.070 33.0 Sales mix percentages G H K L M N 0 P D 6 7 8 9 10 11 12 ET 14 15 Total 12,000 150,000 100% 08 27,000 18% 119.00 $ 92.00 $ 3,000 2x 39.00 $ 14.30 $ 7,500 5% 79.00 S 33.18S 9,000 6% 69.00 $ 30.36 S 15,000 10% 95.00 $ 77.60 $ 10,500 7% 59.00 25.40 S 1,500 1% 65.00 $ 29.00 5 3,000 2 44.00 12.405 6,000 4% 49.00 S 13.485 S S 89.00 6133 S 3,213,000 S 2.484,000 $ 729,000 S 117.000 $ 42,900 74,100 $ 592,500 5 248,850 343,650 $ 621,000 S 1425.000 273,240 1,164,000 347,760 $ 261,000 $ 619,500 $ 266,700 352,800 s 92,500 $ 43,500 54,000 S 132,000 $ 37,200 94,8005 1000 67.00 294,000 5 1,068,000 5 12.795.000 BOBO 741.960 3,571,930 213,1205 326,040 4.223,070 3,000,000 $ 1,223,070 150,000 ON O OX ON