You own a coal mining company and are considering opening a new mine. The mine will cost $120 million to open. If this money is spent immediately, the mine will generate $20 million for the next 10 years. After that, the coal will run out and the site must be cleaned and maintained at environmental standards. The cleaning and maintenance are expected to cost $2 million per year in perpetuity. What does the IRR rule say about whether you should accept this opportunity? If the cost of capital is 8%, what does the NPV rule say? Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. $120 $20 Cost of new mine (million) Annual cash flows (million) Number of periods (years) Cleaning costs (million) Cost of capital 10 S2 8% Cost of Capital 1% NPV (million) 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 18% 19% 20% How many IRR are there? Can you trust the IRR rule? Is the NPV positive if the cost of capital is 8%? Would you take the opportunity? Requirements 1. Start Excel - completed. 2. In cells E14:E33, by using cell references and the function PV, write an expression to calculate the NPV of the investment opportunity for cost of capital 1% to 20%, respectively (20 pt.). 3. In cells C35:F46, insert a Line Chart to plot the NPV profile of the investment opportunity. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Line or Area Chart and choose Line Chart Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the NPV profile of the investment opportunity using cells E14:E33 for the series values. Do not name the series. Edit Chart Elements Then in Select Data Source window, edit Horizontal Axis Labels using cells D14:D33. horizontal axis with Cost of Capital and Axis Title for the vertical axis with NPV (million). (3 pt.) 4. In cell E48, select Zero, One, Two, or Three depending on the number of IRRs that you see on the NPV profile (1 pt.). 5. In cell E49, select Yes or No depending on whether you can trust the IRR rule in this case (1 pt.). 6. In cell E51, select Yes or No depending on whether the NPV is positive if the cost of capital is 8% (1 pt.). 7. In cell E52, select Yes or No depending on whether you take the investment opportunity based on the NPV rule (1 pt.). 8. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed