How do you complete an NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges? A specific formula for the cells, not how to compute NPV, ATSV, etc. Additional detail is below table if needed.

A capital budgeting decision is being considered that would involve an expansion and simultaneous replacement of old equipment. The project is expected to have a 6 year life for the firm.

This project will replace some existing equipment which currently has a book value (BV) of $200k and an estimated market salvage value of $375k. The new project will require new equipment costing $2000k, which will be depreciated straight-line to a book value of $200k at the end of 6 years. Due to new energy efficient technology, replacing the old equipment with the new more efficient equipment will generate an immediate tax credit of 5% of the equipments cost. The expansion will require an additional investment in NWC of $200k.

Sales are expected to increase by $1000k the first year and grow by 15% in years 2 and 3, then by 5% annually during the remaining 6 year life. Cost of goods sold is forecasted to be 45% of the increased sales, and other selling and general administrative expenses are forecasted to be 10% of the increased sales.

It is forecasted that the new equipment will have a salvage value of $300k at the end of the projects 6 year life.

The firms weighted average cost of capital (WACC) for projects of this risk level is 8%. The firms marginal tax rate is T = 40%.

90,000 300,000 390,000 130,500 300,000 430,500 177,075 300,000 477,075 194,929 300,000 494,929 213,675 300,000 513,675 233,359 300,000 533,359 NPV Scenario / Risk Analysis: Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted 22 = Net Income 23 + Depreciation 24 = Operating CF 25 26 Time 0 Investments 27 Equipment 28 ATSV old 29 Tax credit -2,000,000 305,000 100,000 -200,000 30 NWC NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV-> SV-20% SV-10% SV Base SV+10% SV-20% 240,000 270,000 300,000 330,000 360,000 ATSV-> ATSV-20% ATSV-10% ATSV Base ATSV+10% ATSV+20% SNPV in Cells: 208,000 234,000 260,000 286,000 312,000 Cost of Capital 4% $687,696 $702,023 $716,350 $730,677 $745,004 6% $517,754 $534,278 $550,802 $567,326 $583,850 8% $369,119 $385,167 $401,216 $417,265 $433,313 10% $239,125 $252,410 $265,695 $278,979 $292,264 12% $125,490 $134,046 $142,602 $151,158 $159,7141 31 32 Terminal Non-OCF: 33 ATSV new @t=6 34 NWC 35 = Net Cash Flow 36 = Cummulative CF 37 38 Cost of Capital 39 NPV 40 IRR = 41 PBP = -$1,795,000 $390,000 $430,500 $477,075 -$1,795,000 $1,405,000 $974,500 $497,425 260,000 -200,000 $593,359 $1,104,538 $494,929 -$2,496 $513,675 $511,179 8% $371,496 14.6% 4.005 NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 1.21 800,000 42 PI = 43 44 NPV Data Table 45 46 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sale Yr.1 Sales Yr.1 -20% -10% Base +10% 20% SNPV in Cells: 900,000 1,000,000 1,100,000 1,100,00 1,200,000 Cost of Capital 4% $716,350 6% $550,802 8% $401,216) 10% $265,695 12% $142,602 47 48 49 50 51 52 53 54 55 56 Rates NPV 0% $1,104,537.88 2% $900,069.40 4% $716,350.31 6% $550,801.92 8% $401,215.88 10% $265,694.62 12% $142,602.18 14% $30,523.77 16% ($71,767.95) 18% ($165,340.79) 20% ($251,126,43) Notes / Reminders: NPV Profile Use NPVs from table on left to populate grids for different scenarios 90,000 300,000 390,000 130,500 300,000 430,500 177,075 300,000 477,075 194,929 300,000 494,929 213,675 300,000 513,675 233,359 300,000 533,359 NPV Scenario / Risk Analysis: Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted 22 = Net Income 23 + Depreciation 24 = Operating CF 25 26 Time 0 Investments 27 Equipment 28 ATSV old 29 Tax credit -2,000,000 305,000 100,000 -200,000 30 NWC NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV-> SV-20% SV-10% SV Base SV+10% SV-20% 240,000 270,000 300,000 330,000 360,000 ATSV-> ATSV-20% ATSV-10% ATSV Base ATSV+10% ATSV+20% SNPV in Cells: 208,000 234,000 260,000 286,000 312,000 Cost of Capital 4% $687,696 $702,023 $716,350 $730,677 $745,004 6% $517,754 $534,278 $550,802 $567,326 $583,850 8% $369,119 $385,167 $401,216 $417,265 $433,313 10% $239,125 $252,410 $265,695 $278,979 $292,264 12% $125,490 $134,046 $142,602 $151,158 $159,7141 31 32 Terminal Non-OCF: 33 ATSV new @t=6 34 NWC 35 = Net Cash Flow 36 = Cummulative CF 37 38 Cost of Capital 39 NPV 40 IRR = 41 PBP = -$1,795,000 $390,000 $430,500 $477,075 -$1,795,000 $1,405,000 $974,500 $497,425 260,000 -200,000 $593,359 $1,104,538 $494,929 -$2,496 $513,675 $511,179 8% $371,496 14.6% 4.005 NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 1.21 800,000 42 PI = 43 44 NPV Data Table 45 46 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sale Yr.1 Sales Yr.1 -20% -10% Base +10% 20% SNPV in Cells: 900,000 1,000,000 1,100,000 1,100,00 1,200,000 Cost of Capital 4% $716,350 6% $550,802 8% $401,216) 10% $265,695 12% $142,602 47 48 49 50 51 52 53 54 55 56 Rates NPV 0% $1,104,537.88 2% $900,069.40 4% $716,350.31 6% $550,801.92 8% $401,215.88 10% $265,694.62 12% $142,602.18 14% $30,523.77 16% ($71,767.95) 18% ($165,340.79) 20% ($251,126,43) Notes / Reminders: NPV Profile Use NPVs from table on left to populate grids for different scenarios