Data File needed for this Case Problem: NP_EX_9-4.xlsx Midwest Copper Linda Rubin is a project analyst at Midwest Copper, a mining company in northern Minnesota. The company is considering investing in a copper mine near Spirit River. Linda wants you to help develop a financial workbook that analyzes the cost of opening the mine, running it for 25 years, and then cleaning up the mine site after its useful life is over. Complete the following.

1. Open the NP_EX_9-4.xlsx workbook located in the Excel9 > Case2 folder included with your Data Files. Save the workbook as NP_EX_9_Mine in the location specified by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Project Analysis worksheet, enter the following initial assumptions for the project: a. In cell B5, enter $12.30 as the startup costs for the project (in millions). b. In cell B6, enter 32.0% as operation costs as a percentage of the mine's revenue. c. In cell B7, enter $13.25 as the cleanup cost in current dollars (in millions). d. In cell B8, enter 25 as the years of operation of the proposed copper mine. e. In cell B9, enter 3.4% as the projected annual inflation rate over the course of the mine's existence. Explore

4. In cell B12, use the FV function to calculate the cleanup cost in 25 years, using the inflation rate in cell B9, the number of years in cell B8, a payment value of 0, and the present value of the cleanup cost in cell B7. Change the sign of the result so it appears as a positive value.

5. In cell G6, enter the startup cost of the mine using the value in cell B5.

6. Enter the following projected annual income values that the mine will generate: a. In cell E7, enter $0.75 as the projected earnings for Year 1 (in millions). b. In cell E16, enter $18.00 as the projected earnings for Year 10 (in millions). c. In cell E26, enter $4.00 as the projected earnings for Year 20 (in millions). d. In cell E31, enter $1.00 as the projected earnings for Year 25 (in millions).

7. Fill in the missing income values in column E: a. Interpolate the rising income values between cells E7 and E16 assuming a growth trend. b. Interpolate the declining income values between cells E16 and E26 assuming a growth trend. c. Interpolate the declining income values between cells E26 and E31 assuming a linear trend.

8. In the range F7:F31, calculate the annual operational costs of the mine by multiplying the income value for each year by the operational cost percentage in cell B6.

9. Linda estimates the copper mine will have $1.80 million in fixed costs in Year 1. Enter $1.80 in cell G7.

10. Linda projects that fixed costs will initially grow at a rate of 4% per year. Extrapolate the Year 1 fixed cost value through Year 20 in the range G8:G26.

11. From Year 21 to Year 25, Linda projects that fixed costs will decline by 10% per year (so that each year's fixed cost is 90% of the previous year). Extrapolate the Year 21 fixed-cost values through Year 25 in the range G27:G31 using a growth value of 0.9.

12. In cell G32, enter the cleanup cost using the value in cell B12.

13. In the range H6:H32, calculate the copper mine's gross profit by subtracting the sum of the annual cost of goods and fixed costs from the mine's annual income. AutoFill the values without formatting.

14. In the range I6:I32, calculate the running total of gross profit for each year.

15. Create a line chart of the range D5:D32,I5:I32 to show the cumulative profit of the mine. Move and resize the chart to cover the range K5:Q20. The payback period is indicated where the line chart crosses the horizontal axis.

16. In cell B13, calculate the total income from the copper mine by adding all the values in column E. In cell B14, calculate the total cost of the mine by adding all the values in columns F and G. Note that by the raw totals, the mine appears to lose money because the total expenses are greater than the total income. Explore

17. Because the cash flow from the mine changes between positive and negative several times during its 25-year projected history, there are different possible internal rates of return. Calculate two possible rates of return from the copper mine: a. In cell A17, enter 1.0% as your guess for the rate of return. In cell B17, calculate the internal rate of return using the profit values in the range H2:H36 and your guess in cell A17. b. In cell A18, enter 10.0% as your guess for the rate of return. In cell B18, calculate the internal rate of return using the cash flow values in the range H2:H36 and your guess in cell A18.

18. Supplement your calculations of the internal rate of return with calculations of the net present value of the copper mine investment under different discount rates: a. In the range A21:A39, enter the discount rates from 1% to 10% in steps of 0.5%. b. In the range B21:B39, add the value of cell $H$6 to the present value of the cash flows in the range $H$7:$H$32 using the NPV function with the corresponding discount rate in column A.

19. Create a scatter chart with smooth lines of the data in the range A20:B39. Move and resize the chart to cover the range K22:Q32. Note that the chart crosses the horizontal axis twice indicating that there are two possible rates of return.

20. Save the workbook, and then close it.