Solve using Excel

Your company is evaluating a replacement project, following are the available information.

Equipment needed to replace existing one has a price of $80,000. Shipping charges will be an additional $6,000 and installation will be $14,000. The equipment will fall under the 3-year MACRS depreciation and is expected to have $2,000 salvage at the end of 5 years when the company plans to sell it.

Replacing the existing equipment will require the following changes in net working capital in the initial period: an increase in inventory of $15,000, an increase in accounts receivable of $20,000, an increase in the cash balance of $15,500 and an increase in accounts payable of $16,000.The change in net working capital is expected to be recovered at the end of the project's life.

The new equipment will increase sales by 3000 units the first year and sales is expected to grow by 4% annually. Sale price is expected to be $50 per unit and variable cost $30 per unit. General and administrative expense is expected to increase by $1,500.

The old equipment was purchased 3 years ago for $30,000 and is being depreciated using straight line depreciation over 5 years and has a current market value of $18,000. If not replaced it will be used for another 5 years and expected to have a no market value, then.

Marginal tax rate is 21% and cost of capital is 11%

1)Build a model to estimate cash flows from year 0-5 and calculate NPV and IRR.

2)Develop a Scenario summary reflecting the following three cases effect on NPV and IRR

Best case:Cost of capital = 9%,Sales growth rate = 6%

Expected Case:Cost of capital = 11%, Sales growth rate = 4%

Worst case:Cost of capital = 14%, Sales growth = 0

3)Using data table perform sensitivity analysis using price per unit and growth rate in sale as your inputs and NPV as your output.

For unit price, Start with $40 per unit stepping by $2 and stopping at $60

For growth rate, start with -4% stepping by 1% and stopping at 5%

Check points:

Incremental depreciation year 1:27,300

OCF year 2:$56,196

PI:1.87