Question

You have been asked to analyze the proposed Skopi Project. Initial investment in the project would be $100 million, which would be spent on planning, design, and construction. This 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 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 receding assumptions.

b) Draw a chart in Excel that shows the projected revenues, expenses and cash flows over the life of the project.

c) Next try some sensitivity analysis. You might want to put a copy of the full worksheet for each of these scenarios on a different sheet. suppose revenues for years 5 through 13 are only $40 million. What would be the internal rate of return? Suppose the construction costs are $100 million in year 2? Suppose labor costs are double those expected?

Answer 1

IRR

The internal rate of return (IRR) is a discounting cash flow technique that gives a rate of return earned by a project. The internal rate of return is the discounting rate where the total of initial cash outlay and discounted cash inflows are equal to zero. In other words, it is the discounting rate at which the net present value (NPV) is equal to zero.

For this project, The discounting factors