In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial details are as follows: 1) Useful life of the equipment (years) 4 2) New equipment cost $240,000.00 5) Inventory increase $25,000 6) Accounts payable increase $5,000 7) Equipment salvage value (end of life) $15,000 8) Projected sales for year 1 $200,000 9) Projected sales growth per year Low Case 2% Base Case 5% High Case 8% 10) Operating cost (as a % of sales) 60% 11) Depreciation (straight line) per year 12) Corporate marginal tax rate (T) 21% 13) Cost of capital or discount rate (r) 10% PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Depreciation is straight line over the useful life of the equipment. Solve for the NPV, IRR and Payback period. Round off NPV to the nearest dollar, IRR to 1 decimal place and Payback to 2 decimals. CF(0) = Cash Flow at Time 0 CF(0) CF(1) CF(2) CF(3) CF(4) Year 0 1 2 3 4 Investments: New equipment cost $240,000.00 Net Working Capital needs at start Total Initial Outlay $- $- $- $- Operations: Revenue sale*1+base case $200,000 $210,000 $220,500 $231,525 Operating Cost sale*operating cost% $120,000 $126,000 $132,300 $138,915 Depreciation equipcost-salvage/#year $56,250 $56,250 $56,250 $56,250 EBIT $23,750 $27,750 $31,950 $36,360 Taxes EBIT*tax21% $4,987.50 $5,827.50 $6,709.50 $7,635.60 Net Income EBIT-tax $18,763 $21,923 $25,241 $28,724 Profit Margin (%) income/revenue 9.4% 10.4% 11.4% 12.4% Add back Depreciation $56,250 $56,250 $56,250 $56,250 Total Operating Cash Flow income+depreciation $75,013 $78,173 $81,491 $84,974 Terminal Cash Flows (end of Year 4) Change in net WC Salvage value (after tax) [= Salvage value before tax * (1-T)] Total Project Net Cash Flows NPV (Base Case) = IRR = Payback = Part 1B: Answer the PART 1B questions on the "Additional Questions" tab PART 2A: Do a similar analysis as in Part 1 to analyze a new scenario where the equipment is fully depreciated in the first year. All other inputs remain the same. Re-calculate NPV, IRR and Payback period of the project. HINT: negative EBIT creates a tax credit! CF(0) = Cash Flow at Time 0 CF(0) CF(1) CF(2) CF(3) CF(4) Year 0 1 2 3 4 Investments: New equipment cost Net Working Capital needs at start Total Initial Outlay $- $- $- $- Operations: Revenue Operating Cost Depreciation EBIT Taxes Net Income Profit Margin (%) 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-T)] Total Project Net Cash Flows NPV (New Scenario) = IRR = Payback = PART 2B: Answer the PART 2B questions on the "Additional Questions" tab PART 3A: Use the Excel cells from Part 1A 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%. Edit your Part 1A spreadsheet to account for these different growth rates and observe the changes to NPV, IRR and Payback period. Record the results in the table below. When you are done, put your Part 1A spreadsheet back to Base Case before you submit your assignment. Sensitivity Analysis Table Growth (%) NPV ($'s) IRR (%) Payback Low Growth 2% Base Case (from part 1A) 5% High Growth 8% Part 3B: Answer the PART 3B questions on the "Additional Questions" tab