1 Evaluate project.
- 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.
- Use the IRR function in C18 to calculate the internal rate of return
- 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:
- Per unit price is plus/minus $7
- Quantity sold is plus/minus 9,000 units
- 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
