Instructions and Guidance
You have been called into act as a consultant at a mining company. The previous consultant has not returned from a weekend skiing trip and you have been asked to pick up and finish their work. The task is to help ‘run some numbers' for the acquisition department. You will report directly to the manager of the acquisition team as this is a high-profile analysis that he needs to present to the board shortly.
After hunting around in the project folder, all you can find is an Excel spreadsheet and some notes that looks like some next steps the consultant was going to perform on the analysis. Perform the steps, gain an understanding of the outcomes and write the report for the director. Note that the report is going to be presented to the board, so its tone and quality should match its audience. The report needs to be professionally presented with the outcome of the analysis attached (Excel spreadsheet).
See the following pages for an explanation of the spreadsheet and how it has been put together. You need to examine the formulas in the cells and then determine what each one is doing. Remember that outcomes for different values may be determined by a combination of previous outcomes and a independent new simulation.
Note that it is important to understand how the charts are generated and that they use a count of the number of value outcomes that occur within a range (buckets) that is determined on the fly by looking at the minimum and maximum values and breaking range into 21 equal buckets. It is recommended that generate your own basic Monte Carlo scenario and charts from scratch to ensure that you understand how they work before attempting to complete the provided spreadsheet.
Step Instructions and Questions
"General Notes:
Be careful which field you are overwriting as some cells are links to other cells on different tabs - This is not a protected worksheet.
Don't forget to calculate the worksheet after you have finished making changes - F9.
To really understand how the Excel workbook is constructed, it often helps to recreate it. However, this is the one to submit."
1 Using the Dashboard tab, change the value in cell B3 to determine the minimum total revenue needed to ensure that in 90% of the scenarios the mine is kept open
2 Using the Dashboard tab, change the value in cell E3 to determine the MAXIMUM extraction cost as a % of Income needed to ensure that 90% of the scenarios the mine is kept open
3 Expand the number of simulations from 100 to 10,000 - ensure that you update all of the formulas correctly
4 Using the Dashboard tab, change the value in cell B3 to determine the minimum total revenue needed to ensure that in 90% of the scenarios the mine is kept open
5 Using the Dashboard tab, change the value in cell E3 to determine the MAXIMUM extraction cost as a % of Income needed to ensure that 90% of the scenarios the mine is kept open
6 Why is there a difference between the results from 100 and 10,000 simulations
7 Outline the reasons for and against doing a MC simulation with 100 or 10,000 rows reflecting on your experience of adding 9,990 rows
8 "Perform a back of the envelope calculation to determine the minimum and maximum values of the total revenue for year one.
- Show your working as a new tab
- Check your results against the MC tab cell N8 & N9"
9 What is the most likely Total Revenue that could be expected from the mine
10 What is the expected Total Revenue range in the best 10% of outcomes
11 What is the expected Total Revenue range in the worse 10% of outcomes
12 What is the expected Total Revenue range in the most likely quartile
"What is most likely Total Revenue that could be expected from the mine if:
Note: Assume all other distributions remain unchanged"
13 "A.
Examine how the 'Extraction Cost' is calculated in the 2nd year. See how it is determined by the interactions of the 2nd years 'Extraction Cost Modifier on PY' which modifies the 1st years 'Extraction Cost %' against the 2nd years 'Gross Income'. This interaction is driven because the extraction costs are understood to be fairly fixed once the mine is up and running, but do change slightly due to inflation driving costs up and efficiencies and other cost savings pushing them down.
The estimate for the 'Extraction Cost Modifier on PY ' in the 2nd year were optimistic. Thus we are not going to be able to achieve a best case scenario of reducing our extraction costs by 5% or worse case only increasing them by 3.75% on the previous year.
Use the figures from our latest report, based on our other mining operations, which indicates that the new figures are more likely to be in the following range:
Min: -3.50%
Most Likely: 1.75%
Max: 5.25%
After modifying them, explain in layman's terms what the figures mean and the impact to the overall position of the change and if it is a significant one.
(PY = Previous Year)"
13 "B.
Outline if there is a significant impact to the final outcome?
Expand on why or why not."
13 "C.
What happens to the viability of the mine if the value of the starting gold price was incorrect by 30% either way?
This requires two answers."
13 "D.
Outline what the impact is and if it is a significant impact to the final outcome?"
13 "E.
Compare the impact for the change to extraction cost and the gold price.
Explain the reasons and implications."
14 "The fixed cost of running the mine is missing from the scenario.
As time is short just add that in for the second year. We will come back later and add it if the board want more detail.
- The range of fixed costs are that it is most likely to cost $80,000 per year with error of 40%
- Remember to reset your gold price - $1,500"
15 "What does the extra cost do to your values for the scenarios outlined in question 1 & 2?
Provide explanations for your conclusions"
16 "Year one total revenue does not carry forward into the second year. The amount of revenue is not accounted for at the beginning of year two.
I need you to calculate the total income at the end of year two, by including the amount from year one.
Once you have done that, determine what factors influence to top 10% and bottom 10% performing mines.
Note the model does not yet account for the cost of borrowings. We will add this in at a later date once we understand the lending model that the board would like us to use.
Hint: Use filters to start to look at the ranges of values for those mines and then start to understand their impacts"
17 What is one other interesting novel insight you think the board might like to know about?
18 Write the above into a report for the director to present to the board as a discussion paper.
Attachment:- Instructions.zip