The meeting with the analyst went well. However, you want to crunch the numbers yourself to ensure accuracy. Furthermore, you need to consider the project in the broader context of how the new production facility can help the company increase output and, more importantly, profits. You know that the CFO will ask you to analyze the project at different hurdle rates, determine the implication on earnings and cash flow, and articulate why this project was chosen over the multitude of options that exist.
Create an Excel spreadsheet for a production plant with the following criteria:
- The company will lease for 5 years at $1,500,000 per year.
- It will cost the firm $4,000,000 in capital (straight-line depreciation, 5 year life) in year 0.
- It will cost the firm an additional $150,000 per year after the new production plant is brought online for other expenses.
- It will generate incremental revenue of $3,500,000 per year.
Use a 40% tax rate, a 10% cost of capital, and a 12% reinvestment rate. Assume the company will use cash flow to finance the project.
In a separate worksheet /tab, indicate how the project would fair under hurdle rate scenarios of 10%, 15%, and 20% (based on MIRR).