PART 3A: Use the Excel cells from Part 1 above to run a sensitivity analysis on revenue growth. Recall from above the high case has 8% revenue growth, base case is 5% and low case is 2%. Re-calculate NPV, IRR and Payback period for the high and low case and correctly fill out this table Sensitivity Analysis Table Growth (%) NPV ($'s) IRR (%) Payback Low Growth 2% Base Case (from part 1A) 5% $165,423 32% 2.41 High Growth 8% PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Then solve for the NPV, IRR and Payback period for the BASE CASE. CF(0) = Cash Flow at Time 0 CF(0) CF(1) CF(2) CF(3) CF(4) Year 0 1 2 3 4 Investments: 1) Equipment cost -200,000 2) Shipping and Install cost -35,000 3) Start up expenses -5,000 Total Equipment Cost Basis (1+2+3) -240.000 4) Net Working Capital -20.000 Total Initial Outlay -260,000 Operations: Revenue 200,000 210,000 220,500 231.525 Operating Cost -120,000 -72,000 -43,200 -25,920 Depreciation -60.000 -60,000 -60.000 -60,000 EBIT 20,000 78,000 117.300 145,605 Taxes 4,200 16.380 24.633 30,577 05 Net Income 15,800 61,620 92.667 115,027.95 75,800 121,620 152,667 175,027.95 75,8001 121.620 152.667 175,027.95 Add back Depreciation Total Operating Cash Flow Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) = Salvage value before tax * (1-1)] Total 0 0 0 20,000 11.850 31,850 Project Net Cash Flows -260,000 75,800 121,620 152,667 206,877.95 NPV (Base Case) - $165.423 IRR = 32.35% Payback 2.41 PART 3A: Use the Excel cells from Part 1 above to run a sensitivity analysis on revenue growth. Recall from above the high case has 8% revenue growth, base case is 5% and low case is 2%. Re-calculate NPV, IRR and Payback period for the high and low case and correctly fill out this table Sensitivity Analysis Table Growth (%) NPV ($'s) IRR (%) Payback Low Growth 2% Base Case (from part 1A) 5% $165,423 32% 2.41 High Growth 8% PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Then solve for the NPV, IRR and Payback period for the BASE CASE. CF(0) = Cash Flow at Time 0 CF(0) CF(1) CF(2) CF(3) CF(4) Year 0 1 2 3 4 Investments: 1) Equipment cost -200,000 2) Shipping and Install cost -35,000 3) Start up expenses -5,000 Total Equipment Cost Basis (1+2+3) -240.000 4) Net Working Capital -20.000 Total Initial Outlay -260,000 Operations: Revenue 200,000 210,000 220,500 231.525 Operating Cost -120,000 -72,000 -43,200 -25,920 Depreciation -60.000 -60,000 -60.000 -60,000 EBIT 20,000 78,000 117.300 145,605 Taxes 4,200 16.380 24.633 30,577 05 Net Income 15,800 61,620 92.667 115,027.95 75,800 121,620 152,667 175,027.95 75,8001 121.620 152.667 175,027.95 Add back Depreciation Total Operating Cash Flow Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) = Salvage value before tax * (1-1)] Total 0 0 0 20,000 11.850 31,850 Project Net Cash Flows -260,000 75,800 121,620 152,667 206,877.95 NPV (Base Case) - $165.423 IRR = 32.35% Payback 2.41