You have a business making widgets. You are considering buying a new machine which costs $1,000,000. You expect to be able to sell it for $50,000 at the end of its useful life in 7 years and will straight-line depreciate it.

You are going to take a robot arm off an old machine to use to run the new one. The old machine could be sold for $30,000 today if you didn’t take parts off it, but is worthless without the robot arm. You also have some old material that you’re not using, which you had bought for $20,000 to make a prototype for some possible new products.

You think you will be able to sell a new type of widget that you will make on this machine. You expect to sell 50,000 per year at $25 each.

You expect $60,000 in fixed costs, and variable cost of 70% of sales on the new widgets. You also expect to need additional Net Working Capital to start the project of $75,000, which you will recover in Year 7.

Your tax rate is 35% and your cost of capital is 10%.

For the following questions showcase Step by step how to complete this using microsoft EXCEL.

Calculate the Initial Investment of the project, and the Terminal Cash Flow of the project.           

Calculate the Operating Cash Flow (OCF) for each year of the project. Remember to calculate the EBIT and show each item that goes into the OCF on its own row, and label each row you use.

Calculate the Total Cash Flow for each year of the project.

Calculate the project’s NPV (NOTE: The Excel NPV function may not work the same way as your calculator’s NPV function. If you use it, make sure to use the NPV function correctly).

State whether the firm should accept or reject the project based on NPV.

Explain why you accepted or rejected the project based on NPV. In other words, what does it mean for the NPV to be positive or negative (whichever you calculated)?

Calculate the project’s IRR (use the Excel IRR function). Based on IRR, should the firm accept the project?

State whether the firm should accept or reject the project based on IRR.

Explain why you accepted or rejected the project based on IRR (i.e., what does IRR measure?).

Calculate the Payback Period in years (to 1 decimal place).

The Comptroller has a 3-year maximum Payback Period. State whether they would accept or reject the project based on the Payback Period.

Cite one reason why either NPV or IRR are better for evaluating projects than Payback Period.