Please Show Formulas Used. Follow the special instructions. Answer by letter/Put answer by the letter . Chegg does not accept pdf file so please upload by picture so that formulas can used can be shown
14-13. 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 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 (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 construction costs are $100 million in year 2? Suppose labor costs are double those expected? Complete all parts of this assignment on a single worksheet. In addition, calculate the NPV for this project. Use 14.00% as the required rate of return to calculate the NPV. In a text box, explain why this project should be accepted or rejected based on your IRR and NPV calculations. 14-13. 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 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 (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 construction costs are $100 million in year 2? Suppose labor costs are double those expected? Complete all parts of this assignment on a single worksheet. In addition, calculate the NPV for this project. Use 14.00% as the required rate of return to calculate the NPV. In a text box, explain why this project should be accepted or rejected based on your IRR and NPV calculations
