Financial Analysis for Copper Mine Investment

Part 1: Financial Tools and Functions

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. Go to the Project Analysis worksheet, and review the initial assumptions for the project in cells A5:B9 (no action needed).

2. In cell B12, use the FV function to calculate the cleanup cost in 25 years, using the data in the initial assumptions section. Change the sign of the result so it appears as a positive value. EX 9-3b Calculating a Future Value with the FV Function 3. In cell G6, enter the startup cost of the mine using the value in cell B5. 4. 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).

5. Fill in the missing income values in column E: a. Interpolate the rising income values between cells E7 and E16 assuming a growth trend. EX 9-6b Interpolating from a Starting Value to an Ending Value 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.

6. 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.

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

8.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. EX 9-6d Extrapolating from a Series of Values

9. 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

Part 2: What-If Analysis

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

11. 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. EX 2-10b Copying Formats with the Paste Options Button

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

13. 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:R20. Improve the chart formatting. For instance, ensure the text direction of the horizontal axis labels are not shown at an angle by changing the intervals to once every 3 years (ie: Startup, Year 3, Year 6, Year 9, etc). Assume you are creating this chart for explanatory purposes to share with others. The payback period is indicated where the line chart crosses the horizontal axis.

14. 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.

15. 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. EX 9-13 Calculating the Internal Rate of Return

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 column H.

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 profit values in column H.

16. 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. EX 9-12b Using the NPV Function 3 | P a g e

17. 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:R32. Improve the chart formatting. Note that the chart crosses the horizontal axis twice indicating that there are two possible rates of return. EX 4-10 Creating a Scatter Chart 18. Save the workbook before moving to the next section.

All Around Vision Care sets a goal for each office to expand its services each year. It establishes a percentage growth rate but uses an adjustment factor for each city to recognize differences in its markets. You have been asked to build a one-variable data table that tests several overall growth percentages to see the effect on the revenues forecast. You also will build a two-variable data table for Boston where you’ll illustrate revenues if both the growth percentage and its adjustment factor are changed.

1. Open the worksheet Revenues

2. Trace dependent cells for cell H4 to see how the overall growth rate affects the 4 year revenues forecast in cell H18. Hint: click “Trace Dependents” in the Formula Tab | Formula Auditing group 3 times. Click “Remove Arrows” after you have completed your evaluation.

3. You would like to evaluate growth rats between 1.00% and 3.00%. Interpolate the data to fill in the cells A26:A32. These percentages represent possible overall growth rates to replace the current value in cell H4. Your goal is to explore various growth rates using a constant amount of increase from 1% to 3%, rather than a constant percentage increase..

4. Set up your one-variable data table by creating a reference to the overall growth rate in cell B24. Make the number bold.

5. Create a data table that shows revenues if the overall growth rate is set to the percentages shown in cells A25:A33. Format the results as currency with 0 decimal places. EX 8-3 Working with Data Tables

6. Use Conditional Formatting to highlight the Overall Growth Rate AND the corresponding Revenue Forecast on your one-variable data table, dependent on what is entered into cell H4. For instance, if you enter 3.00% in cell H4, cells A33 and B33 should be highlighted.

7. Next, create a data table with the result in cells F24:I31 that shows Boston Four-Year Total revenues when the overall growth rate and Boston’s adjusted growth factor are varied. 8. Use Conditional Formatting to highlight the Overall Growth Rate, the Boston Adjustment Factor AND the corresponding Revenue Forecast on your two-variable data 4 | P a g e table, dependent on what is entered into cells H4 and H6. For instance, if you enter 2.25% in cell H4 and 1.75 in H6, cells E29, H23 and H29 should be highlighted.