1 Evaluate project.

1. Use the NPV function in D18 to calculate the Net Present Value and use an IF statement to return accept or reject depending on NPV calculation.
2. Use the IRR function in C18 to calculate the internal rate of return
3. Construct a sensitivity analysis for units sold (quantities are listed in column B starting in Row 19). Define a data table using Excels What -If Analysis. The table should include the IRR, NPV, and Decision for each quantity.

2 Define a base-case, best-case, and worst-case scenario using the scenario manager in Excels What-If Analysis. Use the following value ranges:

1. Per unit price is plus/minus $7
2. Quantity sold is plus/minus 9,000 units
3. Marginal cost of producing a widget is plus/minus $2.85

B D E Year Revenues Variable Casts Fixed Costs/Exerans EBITDA Depreciation EBIT Taxes Channe NWC Net Capital Sperdit CERA Yaar 1 $1,950,000.00 $390,000.00 $120,000.00 $1,410,000.00 $1,333,333.33 $106,666 67 S36,23 31.403,723.23 $0.00 50.00 $1,403,733.33 Year 2 $1,950,000.00 $350,000.00 S120 GOD.CO $1,440,00D.CO $868,888.89 $551,111.11 $187,877.78 $1.252,622.22 $0.00 SOCIO $1.252,622.22 Year $1,950,000.00 $390,000.GID $120,000.00 $1,440 40000 $592 592.59 $84740741 $289,114.52 $1,151,881.46 SCI.OR $0.00 $1,181,281.48 G Year $1,950,000.00 $390,000.00 $120.000.00 $1,440,XID.CO $395.051.73 $1,044,438.77 $355,279.02 $1,084,720.99 $0.00 S! On $1.034.720.99 H Years $1,950,000.00 $350,000.00 $120,000.00 $1,440,000 DO $315,061.73 $1,124,938.27 $342.429.01 $1,057,520.39 SU.CO $0.00 $1,047,520,49 1 Yaar 6 $1,950,000.00 5390,000.00 $120,0X1O.CO $1,410,000.00 $315,06 1.23 51,124.936.27 $37,479.101 51,057.520.99 SSC,1x10.00 -$160,000.00 $1,767,570.99 M N Information/Variables $6,000...O Company's Other Taxable income 65,000 Units Sala Project Lile years $30.00 Price per unit 54,000,000.00 Machinery $120, Don Machinery maintenance costs Depreciabla $161, DADOS Value $500,000.00 Increase in Inventory $50,Danar esse in Cash Balance S6.0 Marginal Cas (per unit) 14% Cost of Capital 34.00% Marginal Tax Rate 513, 12:0I $4,000,DEX.CO $3,450,000.00 ICFFAI $3.450,000.00 $1,231,345.00 S943,852.13 5777,487.29 $642.24190 $549,248.26 5805,258 78 JRR NPV Decision Units Sold 55000 60000 GS000 70000 75000 80000 85 SOODU PHI) Taxable income $0 $50,nan 550,000 $75,000 $25.00 $100,000 $100,000 $35,001 $335,000 $10,000,000 $10,00D COD $15,000,000 $15,00D.GOD S18334 $18,333,333 Abow $ 18,333333 Tax Rate 15% 7555 2476 39% 34%. 3596 388 35% Note: Corporate taxes are now hat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In andirian personal taxes are still based on similar tables, as are state corporate taxes. B D E Year Revenues Variable Casts Fixed Costs/Exerans EBITDA Depreciation EBIT Taxes Channe NWC Net Capital Sperdit CERA Yaar 1 $1,950,000.00 $390,000.00 $120,000.00 $1,410,000.00 $1,333,333.33 $106,666 67 S36,23 31.403,723.23 $0.00 50.00 $1,403,733.33 Year 2 $1,950,000.00 $350,000.00 S120 GOD.CO $1,440,00D.CO $868,888.89 $551,111.11 $187,877.78 $1.252,622.22 $0.00 SOCIO $1.252,622.22 Year $1,950,000.00 $390,000.GID $120,000.00 $1,440 40000 $592 592.59 $84740741 $289,114.52 $1,151,881.46 SCI.OR $0.00 $1,181,281.48 G Year $1,950,000.00 $390,000.00 $120.000.00 $1,440,XID.CO $395.051.73 $1,044,438.77 $355,279.02 $1,084,720.99 $0.00 S! On $1.034.720.99 H Years $1,950,000.00 $350,000.00 $120,000.00 $1,440,000 DO $315,061.73 $1,124,938.27 $342.429.01 $1,057,520.39 SU.CO $0.00 $1,047,520,49 1 Yaar 6 $1,950,000.00 5390,000.00 $120,0X1O.CO $1,410,000.00 $315,06 1.23 51,124.936.27 $37,479.101 51,057.520.99 SSC,1x10.00 -$160,000.00 $1,767,570.99 M N Information/Variables $6,000...O Company's Other Taxable income 65,000 Units Sala Project Lile years $30.00 Price per unit 54,000,000.00 Machinery $120, Don Machinery maintenance costs Depreciabla $161, DADOS Value $500,000.00 Increase in Inventory $50,Danar esse in Cash Balance S6.0 Marginal Cas (per unit) 14% Cost of Capital 34.00% Marginal Tax Rate 513, 12:0I $4,000,DEX.CO $3,450,000.00 ICFFAI $3.450,000.00 $1,231,345.00 S943,852.13 5777,487.29 $642.24190 $549,248.26 5805,258 78 JRR NPV Decision Units Sold 55000 60000 GS000 70000 75000 80000 85 SOODU PHI) Taxable income $0 $50,nan 550,000 $75,000 $25.00 $100,000 $100,000 $35,001 $335,000 $10,000,000 $10,00D COD $15,000,000 $15,00D.GOD S18334 $18,333,333 Abow $ 18,333333 Tax Rate 15% 7555 2476 39% 34%. 3596 388 35% Note: Corporate taxes are now hat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In andirian personal taxes are still based on similar tables, as are state corporate taxes