Mining Financial Modeling
Part 1 (video course)
1 Course introduction
Hi! – and welcome to the Mining Financial Modeling and Valuation Course with the Corporate Finance Institute.
My name is Tim Vipond and I’ll be your instructor for this course.
From 2012 to 2015, I worked in the Corporate Development group at Goldcorp, one of the largest and most valuable mining companies in the world.
During that time I worked on several high profile mergers, acquisitions and asset sales, which required extensive financial modelling and valuation work. I also developed the company’s internal operating model, used to value all existing assets, as well as perform pro forma analysis for M&A transactions.
By the end of this course you will be able to do several things:
- The first is, read and interpret a technical report or feasibility [D1] to gather the economic information you need for your model.
- The second is, input the assumptions on that report into a robust and dynamic financial model in Excel.
Third, calculate the net present value of that mining asset in Excel.
And finally, run a sensitivity analysis on the value of that asset and output charts to illustrate the range of values.
Completing this course will give you the training of a baseline professional and it’ll help prepare you for your career in several areas within the mining industry including:
- Mining Investment Banking
- Mining Equity Research
- Working in house at a Mining Company in Corporate Development
- Working in house at a Mining company in the FP&A team
- Or the treasury management team
Your final product for this course will be a beautiful model that properly identifies all the drivers and risks, and also outputs the financial returns of your investment.
And gives managers and investors a clear [D2] picture of what this investment entails.
Now, let’s get started!
2 Mining life cycle – types of assets
Before we start building the financial model in Excel. It’s important to review the Mining industry and the Mining life cycle.
Mining assets, can more or less be divided into two categories:
- and operating mines
Projects can then further be broken down in;
- Exploration and Feasibility stage,
- as well as planning and construction stage
The purpose of exploration is to find ores that are economically viable to mine.
This starts with locating mineral anomalies, areas that are believed to have high concentration of this elements, and then discovering and sampling confirms or denies that there is a find there.
This can be further proven through drilling programs and resource definition.
Once a potential mine is proven to be viable. The planning and construction phase begins.
During this phase, permitting is applied for and obtained, economic studies continue, and detailed mine plans are refined.
A mayor part of this stage, also include infrastructure building, as mines are often located in remote areas that require road and electricity.
Once operation is ready to begin, the asset officially becomes an operating mine.
During this phase, ore is extracted, processed refined and metal is produced.
This section forms the bulk of the focus of the financial model for an operating mine.
Once all the ore has been extracted the mine closure process begins, which can last for several years.
This includes, clean-up, reclamation, and environmental monitoring.
3 Technical reports
As mentioned in the previous chapter, mining assets are divided into two main categories: projects and operating mines.
Let’s now review the types of technical reports that categorized those two different stages.
Working from left to right we start with at least 10 years out with a scoping study, and work our way towards production of an operating mine.
After scoping study is completed, a preliminary economic assessment (or PEA), is typically performed.
This can be anywhere from 5 to 10 years out from production.
Following that, is a pre-feasibility study (or PFS).
At this stage it has a reasonable chance of economic viability, but is further enhanced by a feasibility study (or FS), which is the final stage before entering production.
Once a mine is in production, it will continue to produce technical reports updates that will provide investors, with an update in both the mine plan, the size of the resource and details around economic viability.[D3]
4 Reserves and resources
The mining company’s main assets are its reserves and resources.
This are the ores that contain economic material that is viable to mine.
It’s important to be able to read a reserve and resource statement and understand what information to pull out for the financial model.
Ultimately, what is contained in this table, produce the annual cash flow that build up the financial model. [D4]
So how do you read this table?
If you look from right to left you are moving in Increasing Geologic Certainty. That is to say, geologist are more and more comfortable with the amount that metal is contained in the ground.
And then moving from bottom to top, you are increasing the economic viability. That is to say, that those are the top or more economically attractive to extract, than those at the bottom.
So, an inferred resource is the least geologic certain and the least economically viable to mine.
While a proven reserved is the most economically viable and the most geologically certain portion to mine.
As we are building a financial model, is important to think about which part of the table we are building information from.
You should probably risk-adjust the different components of this table, to reflect the risk associated with it.
Typically an inferred resource would be excluded from an economic model. As it contains such a high amount of risk on it that is hard to apply any probability to it.
The summation of the entire table is referred to as the company’s total resources.
5 Key terms and definitions
Let’s review the key mining terms that are required to pull economic information from a technical report.
Ore is rock that contains metal that is economic to mine and it’s measured in metric tonnes.
The grade is the amount of metal contained in the ore and is measured in either:
- Grams per tonne, which indicates precious metals
- or percentage, which indicates the base metals
One gram per tonne (g/t) is the same as one part per million (ppm).
The recovery is the percentage of the metal that is recoverable form the ore after the extraction process, measured as a percentage.[D5]
Production is the amount of metal that is actually produced per year measured in either:
- Ounces in the case of precious metals, or
- Pounds in the case of base metals
Payability is a term that refers to smelter and refinery terms in terms of how much they will pay the metal that it’s produced.[D6]
Cash costs represent the mine site operating costs and include things like:
- and consumables
These are typically measured in cost per tonne of material that it’s moved.
All-in sustaining costs go one step further than mines at operating costs to include things like
- corporate G&A
- sustaining capital to maintain the mine
- capitalize exploration to continue to explore for reserves and resources
- It does not include interest or taxes
6 Key financial concepts
Let’s review the key financial concepts that are required to build a financial model, and that are unique to the mining industry.
Revenue is a function of:
- Ore multiplied by grade,
- multiplied by the recovery rate,
- then multiplied by the payability rate
- and finally multiplied by the metal price.
Royalties are typically deducted from revenue, and properties often put royalties in place that are payable over the life of those mining assets.
An example would be a 2% net smelter return.
We’ll talk about that in more detail later.
Operating costs in the mining industry are typically started as a per tonne basis.
For example, $2.50 per metric tonne of material that it’s moved to mine.
Capital costs include things like the initial capital to build the mine, as well as sustaining capital for ongoing equipment purchases.
Reclamation costs take place at the end of the mining life and are accrued for accounting purposes.
In the cash flow model you don’t have to worry about the accrual of those costs.
Depreciation is typically done in a percentage of production bases, over the entire life of the mine.
Taxes can often be complicated with mining companies operating in several countries.
There are also mining specific taxes and royalty agreements that have to be taken into account.
Changes in net working capital would of course have to be factored in to a cash flow model.
They are typically not too material, as inventory turns are very high, AP and AR terms are often net out.
Module 2: Valuation Metrics
1 Net Present Value (NPV)
Let’s review the key valuation metrics that are unique to the mining industry.
Mining projects are essentially one giant NPV.
The model can be forecasted out to the end of the mine life and discounted back today.
This is because the engineering reports and technical reports, have a very detailed mine plan.
This is different than other industries that have a terminal value, and perhaps a 5 year forecast period.
In mining, there is no terminal value, or if there is, is actually a negative number as reclamation and closure costs are experienced.
Mining projects are essentially a perfect DCF candidate as they allow us to forecast out for the whole life, and discount back to today.
The earlier the stage of the asset is, the harder it is to do, as there is more uncertainty involved.
2 P/NAV ratio
Price to net asset value ratio or P/NAV ratio is the most common metric used in the mining industry.
It’s an equity value metric and essentially you take the NPV of the mining assets that you are trying to value,
- add any minority interest for any other equity investments,
- plus any cash equivalents on the balance sheet,
- and then deduct any debt or NPV of corporate overhead.
This is expressed as P/NAV.
NAV is a sum of the parts that approach the valuation, in that every single mining asset is individually valued and added together.
Corporate adjustments are made at the end, such as corporate overhead or debt.
Here is an example of an NPV analysis on a company that has 3 mines.
As you can see, then Net present value is calculated for each mine (the Sauder mine, the Keevil mine and the Chan mine).
We [D8] show the discount rates used for each one, as well as the long term goal price assumption for each one.
We then add the cash and equivalents and we get the total asset value of this business.
Next we deduct the NPV or the corporate overhead and G&A, deduct the debt, and we are left with the equity value, or Net asset value.
By comparing the market cap of the company, to the net asset value of the company that is calculated in our financial model, we get the P/NAV ratio.
Here is an interesting question about P/NAV Ratios:
- Why would a company trade at a premium to its net asset value?
- I.e. why would you pay more than it’s worth?
This is sort of the opposite of a Warren Buffett style investment.
And it’s quite interesting in the phenomenon that applies only to gold companies. Quite frequently gold companies trade at up to 1.5 times the Net Asset Value.
There a few reasons for this, one reason is:
Because not all of the reserves and resources that may be discovered over the life of the mining are included in the model.
As you recall in the resource table we looked up previously, some of those inferred resources may be excluded and more ounces might be discovered later in the life of the mine.
This would be part of the reason to pay more than the Net Asset Value of the business.
The second reason to pay more than the Net Asset Value of the business may be because of your gold price assumption.
In the model you may be using a certain price, for the long term, say 1,300 dollars an ounce. However, you may believe that in the long run it’s more upside than this.
So in a sense there optionality build in, and again, you will be willing to pay more.
A third reason is the nature of the gold industry.
Gold pays off in bad times like an insurance product, and typically people are willing to pay more for some security that pays of in bad times, than a security that pays off in good times.
3 P/CF ratio
The price to cash flow ratio, or “P-cash flow”, as it’s also commonly used in the mining industry, but it’s only useful to producing mines as it takes the current cash flow in that year, relative to the price in the security.
It’s also an equity value metric like P/NAV.
It starts by taking the adjusted cash flow of the business in that year, and compares that to equity price of the security or stock price.
It’s important to note, that net operating cash flows after interest, and therefore, an equity metric, it’s also after taxes, but it does not include capital expenditures and does not include any changes in working capital.
It is therefore not a true cash flow metric, but it is important to be aware that this is commonly used in the mine space for producing companies.
4 EV/Resource ratio
Another commonly used metric in the mining space is the EV to resource ratio.
This is a ratio that total resources contained in the ground, and it’s an enterprise value metric even as before interest and available to all stakeholders in a business.
It values everything in the ground.
It’s just a physical metric, based on analysis.
And it’s typically used for very early stage projects, where not a lot of detail or information is available.
It’s a fairly crude valuation metric and it doesn’t take into account capital cost to build the mine, nor operating cost to extract the mine, and does not discount back the present value.
And overall it’s not a good indicator of economic value, it is however an important metric to be aware of, and it is useful for very early stage projects.
5 TAC (Total Acquisition Cost)
Another commonly used metric in the mining industry for early stage projects is Total Acquisition Cost or TAC.
This is an enterprise value metric as well and it builds on the previous chapter on EV/Resource metrics.
You essentially build up form that, a quick and dirty approach to a P/CF model, without actually building all of the detail.
So, you start with the cost to acquire the asset, which is to say to buy the company as it trades, which is the total EV/Resource ratio.
Plus add on a cost to build the mine,
Plus add on the average cost of gold mining per ounce with the all-and-sustaining cost and you will get the total acquisition cost, which is expressed on a per ounce basis.
Let’s walk through an example together.
Suppose that a publicly traded stock, had a market capitalization of $100 million dollars, and it had $1 million ounces.
I could therefore, acquire the asset for $100 dollars per ounce.
I know that the cost of build the mine divided by the number of ounces would be $200 dollars per ounce.
I also know that the average all-and-sustaining cost to operate this mine is about $900 dollars per ounce. Based on some studies.
All this, combines for a $1,200 per ounce TAC.
As you can see, this is a very crude metric that is used for early stage projects and it’s something that you typically want to be at a discount of the spot gold price.
Say for example gold was at $1,800 dollars right now, then a $1,200 dollar TAC might not be a bad approach.
But because of the lack of detail, this certainly is not a high level of certainty.
Module 3: Financial Model – Assumptions Section
Now that we have reviewed the mining industry, and discussed key valuation metrics, it’s time to start building our financial model.
We are going to start with the assumption section.
It’s very important to keep all of the assumptions in one place, this makes for a much cleaner and easier to follow model.
It makes it easier for other users that are using our model to understand.
And it also simplifies the structure and reduces the chance of errors.
Another benefit is that for sensitivity analysis, it is important to have everything in one spot, as the tab has to be the same for the inputs and outputs when performing sensitivity analysis.
It’s important to know when building this model and lay down assumptions that typically in the mining industry, all figures are expressed in real dollar terms (i.e. without effected inflation).
Let’s review the key components of the assumptions section.
The mayor inputs that we are going to put in this module include:
- Metal price,
- resource tonnes,
- resources grade, which includes the containing metal,
- a milling rate,
- a recovery rate,
- terms of payability,
- operating costs and,
- capital costs, just to name a few
Let’s get started!
Let’s open up the Mining financial model file and take a few minutes to get oriented with the model.
As you’ll see on the cover tab, we’ve got a nice overview of what’s going to be model here.
You can typically put some title information, a cover page I find it’s helpful.
It’s also handy to have some notes, so throughout this model all inputs would be put in blue font, all formulas on the same tab will be black, and any formulas that lead to other tabs or any sheets would be green.
If you use ctrl + page down to switch to the next tab, you will see here the asset model itself.
I typically like to organize my models in the same fashion, all in one spreadsheet starting with assumptions, then moving to the mining process, then to the financial statements, followed by the valuation and finally ending with the sensitivity analysis.
As you see this makes it a very clean structure, and if you use grouping it’s easy to categorize them as follows.
Let’s start by opening up the assumptions section.
This is where we are going to be inputting things like conversion rations, metal prices, a mining production schedule, the resource table, we also have things like royalties, payability, mine operating costs and capital costs.
The next section is the mining tab, in this section we are going to be multiplying all of the above assumptions to go through mining, milling and finally production.
From there we can open up the financial section, where we start with revenue, work our way down to operating costs, EBITDA, all-in-sustaining costs, net income, etc.
Following that, we can open up the valuation section, where you are going to see here a work up from EBITDA to free cash flow, as well as IRR and payback analysis.
Finally we can open up the sensitivity analysis section where we are going to use data tables which will give us our range of outputs.
At the top here I can click the 1 and 2 to quickly group all and ungroup all sections of the module, this can be quite handy.
And if I page down, finally we are going to have some nice charts summary output that will be automatically populated by completing the model.
3 Conversion rates and metal prices
I order to complete the assumptions section of this module, I am going to be referring to three documents:
- The 2008 feasibility study
- The 2014 reserves update
- And the 2014 updated mine plan
Please file all three of this files, so you can easily refer to them.
Let’s start at the top of the assumption section and work our way down.
Please open up that section, at the top you’ll see a table type of conversions.
This is where we keep handy some conversions that will be used quite frequently throughout the module, one is grams to Troy ounces, Pounds to metric tonnes and then troy ounces to grams.
Below that we’ve got metal prices, this is probably the biggest assumption in any mining model.
What is the price of the metal that we assume is going to be realized over the life of the mine?
I typically like to have a few cases build in using a choose function in it. I’ll walk through the details a bit later, but typically you’ll see a management price deck,
A wall street consensus price deck
And perhaps a third case which is just taking this current spot price and running it out into the future.
This model is set up so that we can toggle here, between management, consensus and spot prices, which the flow though through the model.
You will notice here that I inputted this three years of information in blue, because they are hardcoded assumptions.
The same thing with 2016th
And in the case of the spot price, there is just one current spot price and then everything else is linked to that.
So, at 2017th you can go ahead and we are going to build this formula right.
By selecting this cells, and pressing Ctrl +R.
Let’s do the same with the second case.
And we can do the third with the spot price
We’ve now quickly filled in all the metal prices assumptions.
For management we are taking three years of forecast, same wall street consensus and just one year for the spot price.
Below that, I’ve built in a choose function, which is now going to pick either management, consensus or spot, and pull that price down to the active price deck that is used in the model.
This is the case for gold and for silver.
Please copy that formula over to the right.
Let’s tested out and make sure is linked up properly.
In the management case I should have 1,500 in 2014 and 1,800 in 2016.
And then select consensus, and it flips to 1,350 and 1,450, that’s correct.
And then spot, 1,200 all the way across.
Ok great! So we know this is lay down properly.
4 Mine plan and resource table
Now that the metal prices are in place, let’s fill in the production schedule.
The production schedule is where we put in ore milled every single year according to the mine plan.
Below that, we have the grade of gold contained in the ore, which is expressed in grams per tonne.
And below that we have the recovery rate, the amount of metal that will be recovered in the mining and milling process.
Below that I’ve calculated for us here, the percentage of mine life that we mining in the current year which will be used for depreciation and amortization functions in this model.
In order to fill in the production schedule, please take a look at the 2014th updated mine plan file.
As you noticed in the measured and indicated section that the information is contained in page 4.
If you go to page 4, you are going to see here, for each year the ore mine, waste mined, ore milled, the grade, and the recovery.
This is the information that I’ve inputted into the models for you. If you were building this from scratch, you will go in yourself and pull this information into Excel and typed it in (.
Below that we’ve got the reserve and resource statement. Again, I filled this in for your, but for reference let’s go to the reserve and resource update file.
Take a look at the table here, as you’ll see, all the information here has been inputted in the Excel file for you.
It’s very important to make sure you are looking at the most updated copy of the reserve and resource statement and mine plan. Then build in your model.
Once the tonnes and the grade are inputted, we can then calculate the contain.
As you see, the formula for contained ounces is:
Tonnes of ore, multiplied by the gold grade, multiplied by the conversion from grams to troy ounces.
This is done from proven and probable reserves.
As you recall from earlier in the course, reserves and resources are categorized according to geologic certainty and economic certainty, proven and probable are the most geologically and economically viable to find.
You will also notice in the reserve and resource statement, as well as technical report is that, silver is not a mayor part of this mine, so it’s not expressed with a lot of detail.
It’s simply expressed as a ratio of ounces of silver contained, relative to ounces of gold contained.
So all I’ve done is take that ratio which is contained in the technical report, typed it in here, and multiply it out, to get the contained ounces of silver.
I’ve also pulled for us the Strip ratio at 2 times.
5 Mill capacity, royalties and payability
In this next section let’s look at no capacity, royalties and payability.
All three of this are taken from the technical report and they are indicated on the left where you can find them.
For a quick reference, let’s hop over to the feasibility study, and look at page 247.
Here you’ll see the Plant Design criteria. In reading through this section, you’ll find that the normal daily throughput of the mill, is designed to be 55, 000 tonnes per day.
This is the main assumption that I carry through for us here. While mill capacity is typically expressed as tonnes per day, a financial model is built on years.
So let’s do a simple calculation to gather here and multiply that out.
In the next section here, we’ve got royalties, you’ll see on page 428 of the technical report there is a description of the two royalties that are owned to the property.
For the purposes of this course, I am not going to get into details on those, but I would ask you to please read further if you are interested in it.
In doing this courses, I’ve assumed that we are going to do 1.4% as the life of mine average across the entire life of the mine.
Next is payability. In the technical report on page 438 you will find the payability assumptions there, let’s go to page 430.
And as you see for gold 99.935, silver 99.0
And that is what I put in the model for us here.
6 Operating costs and capital costs
In this section of the assumptions part of the financial model. Let’s look at mine operating cost, as well as capital costs.
These are contained in the technical report, and again I’ve indicated on the side here where to find them.
Let’s click and refer back to the technical report, so we can review the costs that are inputted in the model here.
If you look at the feasibility study you’ll see that mining processing, transportation and G&A costs are outlined. They total 9.43 cents for tonne milled.
If you flip back to the model, you’ll see that I’ve actually inputted different numbers here.
There reason being is that the date of the technical report is 2008, and the numbers that were initially putted in the feasibility study turned out to be quite different than what the mine is expected to experience during more current operations.
This is a very important point in the mining industry where technical reports and feasibility studies, quickly become out of date and we have to make a more updated assumption about the cost of the operating mine.
Let’s just assumed for the purposes of this model, that we have good reasons to believe that this are good operating costs.
I’ve completely made these up for the purposes of the model, but they do spit out a result that looks to be quite realistic relative to the gold price.
This of course would be an important part for you if you are an analyst of working internally at a mining company.
So, let’s sum this up, with alt + = (Alt=) the quick way to do a sum.
And we are going to have a model that it’s producing gold at 17.93 cents per tonne milled.
Below that we’ve got corporate G&A and exploration costs.
As you’ll noticed here I’ve actually made a note about G&A.
If this is an asset level model, we are not going to model corporate G&A. We have model up here as you recall, G&A, but this is at a mine operating level that is actually people that is actually sitting at the mine in that office that is located at that level.
We are not going to model in the corporate G&A at this model that will be done later on a consolidation tab.
Next we are going to fill in the capital costs.
Again we are going to pull those from the technical report on page 433.
Let’s flip over to the feasibility study and page 433.
Now here you see a nice schedule were we’ve got total sustaining capital estimated over the life of the mine.
If we flip back to this model, I’ve actually taken a more recent update from the financial reports of the company.
And what I’ve done is multiply the total development capital costs by the tonnes and the amount that is milled each year.
In this effect I’ve actually spread it out over the remaining life of the mine.
The same is true for the sustaining capital.
I’ve taken a total amount estimated by the company. And multiplied it out by the amount of production of that year, relative to the tonne of production over the life of the mine.
This is a very effective way to amortize the capital costs across the mine.
It is not the most detail or accurate way, but it is a fantastic way to quickly model out in this case.
Finally, we’ve got total reclamation costs, at 45 million.
This is taken from the technical report at page 435.
This will flow through at the end of the model, as the cash flow will be experienced at the end when the mine is ___ down.
7 Working capital and discount rate
In this last part of the assumptions tab, let’s fill in a few key items that we need for the balance sheet.
Which is the most recent PP&E balance, taken from the most recent financial statements.
The tax rate, which is not cover in extreme detail in this course, so we will pick a flat tax rate of 30% .
- In addition we are going to need to put in assumptions about working capital days.
- What is the average accounts receivable days,
- The average accounts payable days
- And how many days does it take on average for an inventory to turn.
These will of course calculate our changes in net working capital.
Finally we need a discount rate, as you can see, I’ve put 5% here, which is very common in the mining industry. 5% is convention and it’s different for many other industries for WAC is calculated, in this case is expressed in real terms, so as a reminder, that does not include inflation, and therefore, it might be say 7% if you are thinking about nominal terms with inflation.
It’s also important to point out that gold product has a very low cost of capital, because of the insurance life nature, the investment as I described earlier in the course.
8 Wrap up
Let’s quickly review what it’s covered in the assumptions section of this model.
Going back to the top, we’ve got all of our conversion rates that we need for the rest of this model in place here.
We’ve got three different price decks:
- And spot
And we use a choose function here to select the active case.
I am going to leave it at management for now.
We have a production and mill schedule here, which is taken form the technical report thus I indicated page 4 of the updated mine plan.
The reserve and resource statement is pulled from the most recent version.
We’ve got mill capacity information,
- And our updated mine operating costs, Which as I stressed are very important to be up to date and may differ from the original technical report, as you will see if you reference page 402 of the feasibility study.
Corportate G&A is not model at the asset level.
Our capital costs is spread out equally based on mine production over the ramining life of the mine.
Reclamation costs will come in at the end.
We have an opening balance to be used for PP&E on the balance sheet.
We are going to model a flat tax rate,
We have our changes in working capital assumptions
And a 5% industry standard discount rate
Let’s get started with the mining tab.
Module 4: Financial Model – Mining Section
Now that we have all our key assumptions in place, let’s get ready to build the mining section of the financial model.
This is where we get the full production schedule for the mine, all the way from ore to revenue.
It’s often a very detail and complicated section of the model.
You often encounter multiple of ore types and stockpiling of ore.
There are multiple products that are produce, include dore which is pure metal, more concentrate, which is a concentrated form but not quite in metal.
And finally, penalty items need to be factored in.
There are two main approaches to do a mining schedule.
First is the mineral inventory approach. We simply take the reserve and resource statement and complete them over time a t a constant rate and grade.
Second is using a detail schedule, for specific volume and grade each year. This is the case for us in this morel, although it does happen to be very consistent over time.
The first step in the mining section is to calculate the ore.
We’ve got ore which is economic to mine, and waste, which is material that must be moved to get at the ore.
Combined they made the total material moved.
Step 2 is grade. As we know grade is expressed in grams per tonne, and it’s how we calculate contained metal.
Contained metal is the ore multiplied by the grade, this tells us the number of ounces produced each year.
Next is the processing section.
This is where we have a milled schedule, where the ore is dropped up by the trucks, and is put through a plant, where is milled and refined into a more concentrated product.
It’s calculated in terms of tonnes per day, and then tonnes per year.
From the mill, we have a recovery date, this tells us how much metal is extracted from the milling process, it is also expressed in ounces per year.
Step 7, we have payability, this is smelter and refiner terms come in, as discussed earlier in the course.
And finally we arrived at payable metal. Which is expressed in ounces per year.
Payable metal is the final step of the mining section, once multiplied by metal price, it allows us to arrive at revenue.
2 Mining schedule
We the assumptions in place, lets now start filling in the mining tab.
If we expand that section, you will see there is two parts.
One is mineral inventory, and the other is milling schedule.
Let’s start by filling in mining schedule and mineral inventory.
We open up the assumptions tab as well, to have access to the information that we need up here.
I am going to scroll down to the opening balance of mineral inventory.
This is total tonnes of ore contain. I am going to link that up to the assumptions, where I have here my total tonnes of ore.
Once I have an opening balance, I am going to subtract the amount that was mined in that particular year.
And what I am going to do is I am going to use the minimum function (MIN), so that I take the minimum of either the amount that is remaining in the balance, or the amount of ore that is to be mined in that year.
The closing balance is equal to the opening balance, minus the amount mined.
The next year’s opening balance is equal to the previous year’s closing balance.
This is called the corkscrew calculation, because it’s way across year over year.
The amount mined can be carried across with Ctrl + R.
Same with the closing balance for this next year.
We now have the formulas properly in place.
Just select this entire section, using our right arrow we then select across and press ctrl+R.
We know the formulas are working correctly, because at the end of 2028 there is no ore left at the mineral inventory.
The next section to complete is the Strip ratio.
As you recall, is up in the assumptions tab, Strip ratio is used 2.0x.
Let’s used F4 to lock that in place, the Waste, is equals to Strip ratio times the amount of ore that is mined.
The total material moved is equal to the waste, plus the ore.
This is now set up as a function that we can select and fill right with ctrl+R.
Since there is no cost to adding extra lines and space in this model, I often like to pull things forward.
Let’s bring the ore mined back down to this level here, so we can easily access it.
We are also going to pull from the assumption section the gold grade and the silver grade.
If we scroll up we have the gold grade here, which is unique in each year.
And we have the silver grade, which is assumed to be constant throughout.
Right here, let’s use F4 to lock that in the case.
Once I have these in place, I can select all three, and ctrl + R.
Now we can calculate contained metal.
Contained metal, is simply going to be Ore x the grade x the conversion.
So let’s take the ore, and then we use F4 to lock in row 98. So that I can copy it down later, I am going to multiply it by the gold grade, then I am going to multiply it by the conversion to go from grams to troy ounces which I will I will lock with F4.
And now the formula it should copy down, because of the locking that I used, and I can select both of those, and fill right.
3 Milling schedule
With the mining schedule in place, we can now start to build the no fee schedule.
Link the no fee up to the ore mined line above.
When that is in place you can select the area and press ctrl + r to fill right.
As you will notice in this cell, I made a comment that the stockpile function could be built if we had more time to go into detail.
But essentially the mill feed, could be limited by its capacity, and therefore, if more ore was mined within the mining process we’d have built a dynamic stockpile.
Let’s link the recovery now, up to the assumption section, the gold.
And for silver.
Select that, and fill right.
With that in place we can now produce melt production, we
4 Wrap up
Module 5: Financial Model – Financial Statement Section
Alright, let’s get started on the financial section of the financial model.
This section contains a combination of accrual, that is accounting based metrics, and cash flow metrics.
We’ll be flipping back and forth between the two.
This entire section should only be formulas.
Even at tax rates and discount rates we shouldn’t move back into the assumptions.
A well working model should require no adjustments on this section.
It should be easy to follow and understand.
And no more than one or maybe two calculations per cell.
There is no need for complicated formulas in cells, whenever formulas get long, break them out into multiple steps.
We are going to start by linking revenue as a function of metal price and metal production.
Royalties, these are a percentage of revenue that are deducted to get to net revenue.
Operating costs, we’ll take the unit cost per tonne, and multiplied them out by the tonnes of ore that are find in the mine.
A depreciation schedule, this would be based on a percentage of production from the life of the mine.
A tax schedule, certain tax regimes can be quite complicated, in this model we’ve taken a simplified approach with a constant tax rate.
And finally step 6
A working capital schedule, this would be build up as a function of accounts receivable, inventory and accounts payable.
Let’s get started
2 Drivers of revenue
3 Revenue and operating costs
4 AISC (All In Sustaining Cost) to EBITDA
5 Depreciation, taxes and net income
6 Changes in working capital
7 Wrap up
Module 6: Financial Model – DCF Valuation
Now that we’ve build the financial section of the model let’s go on to build the DCF analysis.
In this section, we are going to calculate the tax schedule, which can be complicated form many mining companies. However, we will take a simplified approach in this model.
Next we are going to the working capital schedule.
It’s typically not material in mining as inventory is not really build up and the market for metal is highly liquid.
Next we are going to build a free cash flow schedule, we’ll start with net income, and work our way to unlevered free cash flow.
Finally we are going to calculate the discount rate, and then discount the cash flows in each year.
This is the corner stone of an NPV or DCF analysis.
In the mining industry, there is a lot of debate over discount rates.
Tradition in the gold industry is to use a 5% real rate that is without including inflation.
WACC over the long term does tend to be about 5 or 6% for gold mining businesses on a real basis.
Country risk premium should be considered and assets in high risk companies like in Argentina, Guatemala, Dominican Republic, etc. Should probably be discounted at a higher rate, than those assets that are located in Canada, United States and Australia.
Let’s get started