Phase would take three years. It is proposed that in year 0, $10 million would be spent. In year 1, $30 million would be spent. In year 2, $60 million would be spent.
Revenues would be generated beginning in year 3. In year 3, revenues would be $10 million. In year 4, revenues would be $30 million. In years 5 through 13, revenues would be $60 million per year. In year 14, revenues would be $30 million. At the end of year 14, the project would be scrapped. In year 15, the project would return $20 million from selling the equipment.
Expenses would be as follows: Annual operating expenses for years 3 through 14 are figured at $12.5 million for raw materials, $10 million for maintenance and repairs, $5 million for labor, and $2.5 million for other expenses.
a. Develop a worksheet that figures out the internal rate of return of this project. You should have a separate input area with separate cells for each of the assumptions. You should have columns for each of the years from 0 through 15. You should have separate rows for each of the revenue and expense items and for the total revenues and total expenses. The bottom row should be annual cash flow. You should have a separate well-labeled cell for the rate of return. Sketch the worksheet on paper before entering it into the computer. Have your worksheet calculate the internal rate of return using the preceding assumptions.
b. Draw a chart in Excel that shows the projected revenues, expenses, and cash flow over the life of the project.
ADDITIONAL REQUIREMENT: Calculate the net present value (NPV) using a required rate of return of 14%.
SECOND ADDITIONAL REQUIREMENT: Assuming that the company requires a 14% rate of return, state whether or not the project should be accepted or rejected. Explain why. Put this answer in a text box. All 4 requirements (part a, part b, ADDITIONAL REQUIREMENT, and SECOND ADDITIONAL REQUIREMENT) must be on separate worksheets.
