Comprehensive Mining Financial Modeling Course

Hi, and welcome to the Mining Financial Modeling 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. all of which required extensive financial modeling and valuation work. I also developed the company's internal operating model, which was used to value existing assets and to perform pro forma analysis for M&A transactions. By the end of this course, you'll be able to do several things. The first is, you'll be able to read and interpret a technical report or feasibility study, so you can gather the economic information you need to start building your model. Next, you'll input the assumptions from the report into a robust and dynamic model in Excel. From there, you'll use formulas to calculate the net present value of the mining asset. And finally, you'll run sensitivity analysis to output charts and illustrate the range of values for this. investment opportunity. Completing this course is critical if you're looking for a career in several areas of mining. The most common areas for building financial models of mining assets would include investment banking, equity research, corporate development, financial planning and analysis, and treasury. Your final product from this course will be a beautiful model that properly illustrates all the drivers and risks associated with the investment. It also outputs the financial returns, or return on invested capital. It will give managers and investors a clear picture of what this investment opportunity entails. Now, let's get started. Before we start building the financial model in Excel, It's important to review the mining industry and the mining lifecycle. Mining assets can be divided into two main categories, projects and operating mines. Projects can be further broken down into exploration and feasibility stage, as well as planning and construction phase. The purpose of exploration is to find ores that are economically viable to mine. This starts with locating mineral anomalies, or areas that are believed to have high concentration of 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 can begin. During this phase, permitting is applied for, and obtained, Economic and feasibility studies continue and detailed mine plans are refined. A major part of this stage also includes infrastructure development, as mines are often located in remote areas that require roads and electricity to be built specifically for the mine. Once operations are ready to begin, the asset officially becomes an operating mine. During this phase, ore is extracted from the ground, It is processed and refined, and metal is produced, and then sold. This section forms the bulk of the focus of the financial model for an operating mine. Once the ore has been extracted, the mine closure process begins, which can last for several years. This includes cleanup, reclamation, and environmental monitoring. Let's open up the mining financial model file. and get oriented in the model. As you'll see, I've laid out the model with several sections. Assumptions, Mining, Financials, DCF Model, Sensitivity Analysis, and finally Charts and Graphs. I typically like to lay out my model in this fashion, as I find it very easy to stay organized. I'm quickly going to scroll through so you get a sense of the size of the model. and the different components that we're going to build out. Scrolling back up to the top, let's walk through each section together. In the assumptions section, we've got things like conversion rates, metal prices, a production schedule, reserve and resource table, mill capacity, royalties, payability, mine operating costs on a per unit basis, capital costs, reclamation. some balance sheet items, and an asset acquisition cost. Below the assumptions section is the mining tab. In this area, we're going to have a mining schedule and a milling schedule, which will produce our payable metal. From there, we can build the financial section, which up at the top here feeds into revenue. deducts royalties and operating costs, lands us at EBITDA and net income, and by making a few balance sheet item adjustments, we can get to free cash flow. Next in the DCF model, we work our way up to free cash flow. We use a discount factor to calculate the net present value and calculate the internal rate of return, and finally the payback. Below that is our sensitivity analysis, where we can adjust for things like mining cost or gold price, and see the impact on internal rate of return or net present value. And from there we will automatically populate some charts and graphs to display the results. In order to complete the Assumptions section of the financial model, I'm going to be referring to a few documents that were attached in the Downloads section. The 2008 Feasibility Study, the 2014 Reserves Update, and the 2014 Updated Mine Plan. Please have all of these handy when I refer to them in the future. Let's start by opening up the Assumptions section. I'm going to focus in this part on conversion rates, metal prices. The conversion rates you can simply find on the internet, but we want them here for handy reference. The first is to convert grams to troy ounces as the metal that we are going to be producing will be measured in troy ounces. The next is converting pounds to metric tons and the third is converting troy ounces back to grams. As you'll see below the conversions, I have three metal price decks that I've laid out. One is a management case, the next is an analyst consensus or Wall Street case, and the third is a spot price. Below that, I've got an active price deck in the model. This allows us to toggle between the three cases and have them run through the model. Metal price is probably one of the biggest sensitivities that we want to change in a model, so it's great to have a dynamic version. Let's fill in the rest of the years on this table together. We're simply going to take, in the case of management, the prior year, which if I select this area and press CTRL-R, I can fill right the formula. Please refer back to our Fundamentals of Financial Modeling course for all these shortcuts and tips. In the consensus case, same thing, I'm going to link to the previous year, select and then press CTRL-D to fill down, and while holding SHIFT with right arrow, I then press CTRL-R to fill all that right. In the spot case, I only have one year of forecast, as obviously there is only one current spot. price which we are going to carry forward for all years. Now let's create an active price deck using Excel's choose function. To use the choose function I type choose open bracket, select the active case which in this instance is number three, lock it into place with F4 and tell it to choose the third option from the possible list of three. We can see if it's linked up properly by selecting management should be $1500 per ounce, consensus $1350, and spot $1400. I can copy this formula down thanks to anchoring the cells. as you'll see here. And then I can copy this whole section to the right. Now I'm just going to double check that it's linked up correctly again, which it appears to be. And we've now got a very nice little dynamic price deck built into this model. Let's take a minute to review what we've covered in the assumptions section of the financial model. Scrolling up to the top, we've got a table with conversion rates that are handy for converting between grams, troy ounces, pounds, and metric tons, etc. Below that, we've got a dynamic price deck that has three cases, management, consensus, and spot. We can toggle between the three. thanks to a choose function that we've built in down below. The choose function selects a case based on this input here. Currently I've got it running on the spot price. Below that is a production schedule. We took this from an updated mine plan. I copy and pasted these numbers into Excel for you, and these are the detailed amounts of ore that's going to be mined each year for the remaining mine life. Below that is a reserve and resource table. This tells us the total contained ounces of gold and silver that are remaining in the mine. We have our mill capacity expressed in tons per day or tons per year, the royalties that are on this property, blended at 1.4% of revenue, payability assumptions for gold and silver, Updated mine operating costs, which differ quite significantly from the original feasibility study. Capital costs, which again differ significantly from the original feasibility study due to cost inflation. Reclamation cost. Some financial assumptions on the balance sheet, such as property, plant, and equipment. Tax rate. Working capital assumptions. a discount rate which is set to 5% and standard in the gold mining industry, and finally, an assumed acquisition cost of $3 billion. This will allow us to calculate our rates of return. As I group this section back up, we now see that the assumptions part is complete, and we're ready to move on to the mining section of the model. With revenue and royalties in place on the financial section, We can now start calculating the operating costs. We're going to calculate both operating costs and capital costs. I'm going to start by relinking some information from the assumption section. These are the costs per ton for the mine. I'm going to set these equal to, up in the assumption section here, the mining cost. I'm going to use F4 to lock in the column G. by pressing it three times. That type of locking allows me to select it in the area below and press Ctrl D to fill down. At the bottom I can press Alt plus the equal sign to auto sum. Because of the way I've locked these, if I select them and the area to the right, I can press Ctrl R and have them continue to reference column G in the assumptions section. With the unit cost linked down below for ease of reference, I'm now going to multiply them out. I'm going to take the unit operating cost for each section, so mining, processing, transportation, and refining, and G&A, and multiply them by the tons per year. I'm going to be sure to lock row 142 in place of the tonnage. That allows me to fill this down, auto sum with alt equals, and then select this whole area and use control R to fill right. Now let's calculate the payback of this investment in terms of years. In order to do this, we're going to start with the purchase price, which we can link above. Once again, I'm going to relink the undiscounted free cash flow in each year, and once again calculate the total. I can now fill this right. And here I'm going to calculate the cumulative free cash flow. So in the first year, it's just equal to that free cash flow. In the next year, it's equal to previous year plus current year. I'm going to fill this right. Now I need to set a formula to tell me that if the cash flow in that year is greater than zero, on a cumulative basis, we've broken even. If it's still less than zero, we haven't broken even. So I'm going to set a formula equal to if the current cumulative free cash flow is less than zero, then return the value one, otherwise return zero. So in this case, it's adding a year. What I then want to do is set this equal to the previous year plus that same if formula. If the cumulative free cash flow is less than zero, add one. Otherwise, add zero. So as you can see in the next year, logically it's two, and then three, and so on. As I fill it right, You'll notice that it stops at 7. This is the year in which we hit our break-even free cash flow on a cumulative basis. So to calculate the payback in years, I'm simply going to say a max function of this selected area is 7 years. So our payback on this investment of acquiring this business is approximately 7 years. Let's take a minute to recap what we've covered in the DCF model section. We've brought forward EBITDA from the financial section, made some adjustments to it to get to free cash flow. Those adjustments include CapEx, taxes, changes in non-cash working capital, and reclamation expense in the final year. This gets us to unlevered free cash flow. We then build out a discount rate formula. so that we can discount each year's cash flow based on our 5% discount rate. This gets us to a net present value of $3.4 billion. Taking into account an assumed purchase price of $3 billion, we can then calculate an internal rate of return for this transaction. The IRR for this transaction happens to be 8.2%. Next, we calculate a payback on our investment. Assuming the outflow of $3 billion in year 1, the cumulative free cash flow that builds up, and the result is that our cash flow is paid back somewhere around year 7. This is a powerful tool for looking at the rate of return on a mining investment. We can now start to look at sensitivity analysis and play around with key assumptions like gold price. Let's get going on the next section. In this section, we're going to perform sensitivity analysis on the financial model. It's important to point out that there's not one single number for an assumption. We may use a gold price of $1,400 in the model, but we want to run different scenarios. Once the model is set up, it can be sensitized using sensitivity analysis. All the key inputs should be sensitized. This includes things like metal prices, milling rates, capital costs, recovery rates, payability and smelter terms, and unit operating costs. Of these items on the page, metal prices and unit operating costs are probably the most important to sensitize. We're going to build some data tables to perform our sensitivity analysis. We'll start by linking the cell of the data table to the desired output. For example, the net present value, or the internal rate of return. Then we will input a range of assumptions that we want to test. For example, different gold prices, like $1,200, $1,300, or $1,400 per ounce. For Excel to perform these functions, they must be linked to original hardcodes. We'll use the data what-if analysis. and data table functions to get the output we're looking for. Let's get started. The next graph that we're going to create is the cumulative free cash flow over the life of the mine. This illustrates the payback of our initial investment. It can be quite a dramatic chart because this investment requires so much capital up front in the first year with the acquisition. Let's link the acquisition cost up above. Now let's link the cumulative free cash flow, which we've already calculated above. Finally, we'll show the cash flow in each year. I can now copy this to the right. As you'll see in this graph, there's a very large capital investment in the first year, and the cumulative free cash flow breaks even. around 7 years later, and finally we get our excess free cash flow in these latter years. This is a very powerful graph as it illustrates not only the upfront capital investment, but the risk involved in an investment such as this. The third graph that we're going to make shows the gold production produced each year in ounces, as well as the all-in cost to produce that gold each year. Let's link this up above. gold production is at the top of the financial section, and our all-in costs are sort of mid-level of the financial section. Once we have those linked, let's copy this section to the right. And I'll see down below the production profile of this mine each year, as well as the all-in cost to produce that gold each year. As you can see, there's a reasonable amount of variability of production each year, largely due to the difference in grade each year, and the associated difference in all-in cost per year. This is a great way to show the variability of the mine. Now that we've completed the model, let's take a minute to review the final product. We've got a very well organized and laid out model. We've got our assumptions, our mining process, financials, DCF, sensitivity analysis, and graphs, all in separate sections. We can easily open up each section to have a look at what's contained there. We've clearly differentiated between inputs in blue and formulas in black. We've got... neatly labeled headings to differentiate between assumptions like metal prices, production, reserves, etc. Scrolling down further into the mining section, we have nothing but formulas from this point on in the model. We calculate our mining schedule and our milling schedule. This feeds into our financial section, where we calculate revenue, deduct royalties and operating costs. Capital expenses are included here as well. And finally, we arrive at net income. We then model a few balance sheet items so we can calculate free cash flow further down. Let's open up the DCF section. Here we perform a full DCF valuation of this investment. We work our way to unlevered free cash flow. We use a discount factor each year. to then calculate the net present value of the investment opportunity. Taking into account the acquisition cost, which was an assumption up top, we can then calculate an internal rate of return of 8.2% from making this investment. The payback over the life of the mine is approximately 7 years. Next we can get into some sensitivity analysis. We know that the break-even gold price on this investment is $1,288 per ounce. We can see that there are quite a range of internal rates of return based on changes in gold price and mining cost assumptions. The net present value of this transaction, that is the value that's created by making the investment or earning a rate of return over and above the cost of capital, also has quite a big range. It's fairly sensitive to the gold price and the discount rate. Finally, All good financial models need an excellent output to display their results and communicate it effectively. Here I've shown you three very powerful graphs that can be used to display your results. This is a free cash flow graph over the life of the mine. This is a payback or cumulative free cash flow graph. And finally, we've got a production schedule graph that shows gold production and all-in costs. Scrolling back up through the model, you can see that it's very clearly laid out. All the formulas are in black, and only the assumptions section has any inputs that should ever be changed. I can then collapse it all back together, hand it over to someone else, and they can easily jump in and understand how this model works. Now that you've completed the course, you will receive your very own Certificate of Completion with a unique identification number that you can add to LinkedIn. Thanks, and we look forward to seeing you in our next course.

During that time, I worked on several high-profile mergers, acquisitions, and asset sales. all of which required extensive financial modeling and valuation work. I also developed the company's internal operating model, which was used to value existing assets and to perform pro forma analysis for M&A transactions.

By the end of this course, you'll be able to do several things. The first is, you'll be able to read and interpret a technical report or feasibility study, so you can gather the economic information you need to start building your model. Next, you'll input the assumptions from the report into a robust and dynamic model in Excel. From there, you'll use formulas to calculate the net present value of the mining asset.

And finally, you'll run sensitivity analysis to output charts and illustrate the range of values for this. investment opportunity. Completing this course is critical if you're looking for a career in several areas of mining.

The most common areas for building financial models of mining assets would include investment banking, equity research, corporate development, financial planning and analysis, and treasury. Your final product from this course will be a beautiful model that properly illustrates all the drivers and risks associated with the investment. It also outputs the financial returns, or return on invested capital.

It will give managers and investors a clear picture of what this investment opportunity entails. Now, let's get started. Before we start building the financial model in Excel, It's important to review the mining industry and the mining lifecycle. Mining assets can be divided into two main categories, projects and operating mines.

Projects can be further broken down into exploration and feasibility stage, as well as planning and construction phase. The purpose of exploration is to find ores that are economically viable to mine. This starts with locating mineral anomalies, or areas that are believed to have high concentration of 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 can begin.

During this phase, permitting is applied for, and obtained, Economic and feasibility studies continue and detailed mine plans are refined. A major part of this stage also includes infrastructure development, as mines are often located in remote areas that require roads and electricity to be built specifically for the mine. Once operations are ready to begin, the asset officially becomes an operating mine. During this phase, ore is extracted from the ground, It is processed and refined, and metal is produced, and then sold. This section forms the bulk of the focus of the financial model for an operating mine.

Once the ore has been extracted, the mine closure process begins, which can last for several years. This includes cleanup, reclamation, and environmental monitoring. Let's open up the mining financial model file. and get oriented in the model. As you'll see, I've laid out the model with several sections.

Assumptions, Mining, Financials, DCF Model, Sensitivity Analysis, and finally Charts and Graphs. I typically like to lay out my model in this fashion, as I find it very easy to stay organized. I'm quickly going to scroll through so you get a sense of the size of the model. and the different components that we're going to build out.

Scrolling back up to the top, let's walk through each section together. In the assumptions section, we've got things like conversion rates, metal prices, a production schedule, reserve and resource table, mill capacity, royalties, payability, mine operating costs on a per unit basis, capital costs, reclamation. some balance sheet items, and an asset acquisition cost.

Below the assumptions section is the mining tab. In this area, we're going to have a mining schedule and a milling schedule, which will produce our payable metal. From there, we can build the financial section, which up at the top here feeds into revenue.

deducts royalties and operating costs, lands us at EBITDA and net income, and by making a few balance sheet item adjustments, we can get to free cash flow. Next in the DCF model, we work our way up to free cash flow. We use a discount factor to calculate the net present value and calculate the internal rate of return, and finally the payback.

Below that is our sensitivity analysis, where we can adjust for things like mining cost or gold price, and see the impact on internal rate of return or net present value. And from there we will automatically populate some charts and graphs to display the results. In order to complete the Assumptions section of the financial model, I'm going to be referring to a few documents that were attached in the Downloads section.

The 2008 Feasibility Study, the 2014 Reserves Update, and the 2014 Updated Mine Plan. Please have all of these handy when I refer to them in the future. Let's start by opening up the Assumptions section. I'm going to focus in this part on conversion rates, metal prices.

The conversion rates you can simply find on the internet, but we want them here for handy reference. The first is to convert grams to troy ounces as the metal that we are going to be producing will be measured in troy ounces. The next is converting pounds to metric tons and the third is converting troy ounces back to grams. As you'll see below the conversions, I have three metal price decks that I've laid out.

One is a management case, the next is an analyst consensus or Wall Street case, and the third is a spot price. Below that, I've got an active price deck in the model. This allows us to toggle between the three cases and have them run through the model.

Metal price is probably one of the biggest sensitivities that we want to change in a model, so it's great to have a dynamic version. Let's fill in the rest of the years on this table together. We're simply going to take, in the case of management, the prior year, which if I select this area and press CTRL-R, I can fill right the formula. Please refer back to our Fundamentals of Financial Modeling course for all these shortcuts and tips. In the consensus case, same thing, I'm going to link to the previous year, select and then press CTRL-D to fill down, and while holding SHIFT with right arrow, I then press CTRL-R to fill all that right.

In the spot case, I only have one year of forecast, as obviously there is only one current spot. price which we are going to carry forward for all years. Now let's create an active price deck using Excel's choose function. To use the choose function I type choose open bracket, select the active case which in this instance is number three, lock it into place with F4 and tell it to choose the third option from the possible list of three.

We can see if it's linked up properly by selecting management should be $1500 per ounce, consensus $1350, and spot $1400. I can copy this formula down thanks to anchoring the cells. as you'll see here.

And then I can copy this whole section to the right. Now I'm just going to double check that it's linked up correctly again, which it appears to be. And we've now got a very nice little dynamic price deck built into this model.

Let's take a minute to review what we've covered in the assumptions section of the financial model. Scrolling up to the top, we've got a table with conversion rates that are handy for converting between grams, troy ounces, pounds, and metric tons, etc. Below that, we've got a dynamic price deck that has three cases, management, consensus, and spot.

We can toggle between the three. thanks to a choose function that we've built in down below. The choose function selects a case based on this input here.

Currently I've got it running on the spot price. Below that is a production schedule. We took this from an updated mine plan. I copy and pasted these numbers into Excel for you, and these are the detailed amounts of ore that's going to be mined each year for the remaining mine life. Below that is a reserve and resource table.

This tells us the total contained ounces of gold and silver that are remaining in the mine. We have our mill capacity expressed in tons per day or tons per year, the royalties that are on this property, blended at 1.4% of revenue, payability assumptions for gold and silver, Updated mine operating costs, which differ quite significantly from the original feasibility study. Capital costs, which again differ significantly from the original feasibility study due to cost inflation. Reclamation cost. Some financial assumptions on the balance sheet, such as property, plant, and equipment.

Tax rate. Working capital assumptions. a discount rate which is set to 5% and standard in the gold mining industry, and finally, an assumed acquisition cost of $3 billion. This will allow us to calculate our rates of return. As I group this section back up, we now see that the assumptions part is complete, and we're ready to move on to the mining section of the model.

With revenue and royalties in place on the financial section, We can now start calculating the operating costs. We're going to calculate both operating costs and capital costs. I'm going to start by relinking some information from the assumption section.

These are the costs per ton for the mine. I'm going to set these equal to, up in the assumption section here, the mining cost. I'm going to use F4 to lock in the column G.

by pressing it three times. That type of locking allows me to select it in the area below and press Ctrl D to fill down. At the bottom I can press Alt plus the equal sign to auto sum.

Because of the way I've locked these, if I select them and the area to the right, I can press Ctrl R and have them continue to reference column G in the assumptions section. With the unit cost linked down below for ease of reference, I'm now going to multiply them out. I'm going to take the unit operating cost for each section, so mining, processing, transportation, and refining, and G&A, and multiply them by the tons per year. I'm going to be sure to lock row 142 in place of the tonnage.

That allows me to fill this down, auto sum with alt equals, and then select this whole area and use control R to fill right. Now let's calculate the payback of this investment in terms of years. In order to do this, we're going to start with the purchase price, which we can link above. Once again, I'm going to relink the undiscounted free cash flow in each year, and once again calculate the total. I can now fill this right.

And here I'm going to calculate the cumulative free cash flow. So in the first year, it's just equal to that free cash flow. In the next year, it's equal to previous year plus current year. I'm going to fill this right. Now I need to set a formula to tell me that if the cash flow in that year is greater than zero, on a cumulative basis, we've broken even.

If it's still less than zero, we haven't broken even. So I'm going to set a formula equal to if the current cumulative free cash flow is less than zero, then return the value one, otherwise return zero. So in this case, it's adding a year. What I then want to do is set this equal to the previous year plus that same if formula.

If the cumulative free cash flow is less than zero, add one. Otherwise, add zero. So as you can see in the next year, logically it's two, and then three, and so on.

As I fill it right, You'll notice that it stops at 7. This is the year in which we hit our break-even free cash flow on a cumulative basis. So to calculate the payback in years, I'm simply going to say a max function of this selected area is 7 years. So our payback on this investment of acquiring this business is approximately 7 years. Let's take a minute to recap what we've covered in the DCF model section. We've brought forward EBITDA from the financial section, made some adjustments to it to get to free cash flow.

Those adjustments include CapEx, taxes, changes in non-cash working capital, and reclamation expense in the final year. This gets us to unlevered free cash flow. We then build out a discount rate formula.

so that we can discount each year's cash flow based on our 5% discount rate. This gets us to a net present value of $3.4 billion. Taking into account an assumed purchase price of $3 billion, we can then calculate an internal rate of return for this transaction. The IRR for this transaction happens to be 8.2%.

Next, we calculate a payback on our investment. Assuming the outflow of $3 billion in year 1, the cumulative free cash flow that builds up, and the result is that our cash flow is paid back somewhere around year 7. This is a powerful tool for looking at the rate of return on a mining investment. We can now start to look at sensitivity analysis and play around with key assumptions like gold price.

Let's get going on the next section. In this section, we're going to perform sensitivity analysis on the financial model. It's important to point out that there's not one single number for an assumption.

We may use a gold price of $1,400 in the model, but we want to run different scenarios. Once the model is set up, it can be sensitized using sensitivity analysis. All the key inputs should be sensitized.

This includes things like metal prices, milling rates, capital costs, recovery rates, payability and smelter terms, and unit operating costs. Of these items on the page, metal prices and unit operating costs are probably the most important to sensitize. We're going to build some data tables to perform our sensitivity analysis. We'll start by linking the cell of the data table to the desired output.

For example, the net present value, or the internal rate of return. Then we will input a range of assumptions that we want to test. For example, different gold prices, like $1,200, $1,300, or $1,400 per ounce.

For Excel to perform these functions, they must be linked to original hardcodes. We'll use the data what-if analysis. and data table functions to get the output we're looking for. Let's get started. The next graph that we're going to create is the cumulative free cash flow over the life of the mine.

This illustrates the payback of our initial investment. It can be quite a dramatic chart because this investment requires so much capital up front in the first year with the acquisition. Let's link the acquisition cost up above.

Now let's link the cumulative free cash flow, which we've already calculated above. Finally, we'll show the cash flow in each year. I can now copy this to the right.

As you'll see in this graph, there's a very large capital investment in the first year, and the cumulative free cash flow breaks even. around 7 years later, and finally we get our excess free cash flow in these latter years. This is a very powerful graph as it illustrates not only the upfront capital investment, but the risk involved in an investment such as this. The third graph that we're going to make shows the gold production produced each year in ounces, as well as the all-in cost to produce that gold each year.

Let's link this up above. gold production is at the top of the financial section, and our all-in costs are sort of mid-level of the financial section. Once we have those linked, let's copy this section to the right. And I'll see down below the production profile of this mine each year, as well as the all-in cost to produce that gold each year. As you can see, there's a reasonable amount of variability of production each year, largely due to the difference in grade each year, and the associated difference in all-in cost per year.

This is a great way to show the variability of the mine. Now that we've completed the model, let's take a minute to review the final product. We've got a very well organized and laid out model.

We've got our assumptions, our mining process, financials, DCF, sensitivity analysis, and graphs, all in separate sections. We can easily open up each section to have a look at what's contained there. We've clearly differentiated between inputs in blue and formulas in black. We've got... neatly labeled headings to differentiate between assumptions like metal prices, production, reserves, etc.

Scrolling down further into the mining section, we have nothing but formulas from this point on in the model. We calculate our mining schedule and our milling schedule. This feeds into our financial section, where we calculate revenue, deduct royalties and operating costs. Capital expenses are included here as well. And finally, we arrive at net income.

We then model a few balance sheet items so we can calculate free cash flow further down. Let's open up the DCF section. Here we perform a full DCF valuation of this investment. We work our way to unlevered free cash flow. We use a discount factor each year.

to then calculate the net present value of the investment opportunity. Taking into account the acquisition cost, which was an assumption up top, we can then calculate an internal rate of return of 8.2% from making this investment. The payback over the life of the mine is approximately 7 years.

Next we can get into some sensitivity analysis. We know that the break-even gold price on this investment is $1,288 per ounce. We can see that there are quite a range of internal rates of return based on changes in gold price and mining cost assumptions.

The net present value of this transaction, that is the value that's created by making the investment or earning a rate of return over and above the cost of capital, also has quite a big range. It's fairly sensitive to the gold price and the discount rate. Finally, All good financial models need an excellent output to display their results and communicate it effectively.

Here I've shown you three very powerful graphs that can be used to display your results. This is a free cash flow graph over the life of the mine. This is a payback or cumulative free cash flow graph.

And finally, we've got a production schedule graph that shows gold production and all-in costs. Scrolling back up through the model, you can see that it's very clearly laid out. All the formulas are in black, and only the assumptions section has any inputs that should ever be changed. I can then collapse it all back together, hand it over to someone else, and they can easily jump in and understand how this model works.

Now that you've completed the course, you will receive your very own Certificate of Completion with a unique identification number that you can add to LinkedIn. Thanks, and we look forward to seeing you in our next course.

Transcript for:Comprehensive Mining Financial Modeling Course

Transcript for:
Comprehensive Mining Financial Modeling Course