23.5 Monte Carlo Simulation

Up to this point, we have tested what the profit would be at different levels of the exchange rate and differing gold grades. For our simple Monte Carlo example, we will set up our table such that we calculate a normal distribution around that exchange rate and then test that over a thousand different values or trials.

Our table in the workbook has three columns. In the first column of the table, we will have an equation as seen in cell F6 that will generate a value for the exchange rate based on the mean of .92 and the standard deviation of .02. The equation is =NORMINV(RAND(),.92,0.02). Please refer back to previous sections for directions on selecting and filling a thousand different cells. For the workbook here, the step has already been done for us. The second column is titled Ex Rate. The third is titled Profit. The idea behind this is that the Ex Rate column will calculate a value according to the equation in the first column. The value seen is a product of that normal inverse equation. This will then be substituted into the inputs on the left. As you can see in cells G5 and H5, they directly reference cells in the inputs to the left with formulas that say =C10 and =C19. I will try to explain this in simpler terms. When we make this a data table, we will make $C$10 our Column Input Cell. This will substitute the value in the F column into cell C10. This will recalculate the profit. That exchange rate and profit are then carried over to the table in columns G and H and locked. That’s right—the values in columns G and H are locked. Column F is not locked, and since it is filled with an equation that recalculates anytime anything is calculated in Excel, it will change from its original exchange rate that is preserved in column G.

Once we have done that a thousand different times, our simulation will end. Upon completion, we will calculate the average profit across all of those rows with a formula, specifically AVERAGE. In other words, we will compute our profit on average across a thousand runs according to our exchange rate assumptions (mean of 0.92 and standard deviation of 0.02). Again, the thousand cells have already been filled out in the workbook with that normal inverse formula. If we hit F9 or Calculate Now, we can see that those numbers jump around in each of the cells as they recalculate the random numbers.

With the conclusion of the explanations, we can now run the simulation. First, we will select everything in our table. To select it all, activate the cell in the top left corner, which should be an empty cell. Hold down Ctrl+Shift and press right arrow + right arrow (which will get everything to the right) and then down arrow + down arrow (which will get everything below). Then, we will come up here to Data in the ribbon at the top. Then, we will click What-If Analysis and pick Data Table from the drop-down menu. The Data Table Parameter selection will then pop up in the middle of the screen. What is our input cell? At the top and across the row, we just have formulas, so those will not vary, which means this is a one-way table (by column). As the values change going down the column, we want to substitute those for the exchange rate in our inputs and references section to the left in cell $C$9, so we will put that into the Column Input Cell. We will go ahead and say OK. If the values are unchanged, we will go to Calculate Now (or F9), and it will populate. It looks like after a thousand runs, our average profit based on a normal distribution of the exchange rate is approximately $5.5 million with these specific random numbers. As we continue to recalculate, our average adjusts with some variation on that original approximation.

As you can see, we can do a simple Monte Carlo calculation or simulation with a one-way table and by varying one parameter. Beyond this, it would be more versatile and useful if we could vary lots of different parameters, such as the number of tons that we mine, the gold grade, the gold price, the exchange rate, etc.

We will continue adding complexity to our example in the next tab titled “Monte Carlo (Advanced).” The first thing to note is that the formulas on the left have been adjusted to reflect random numbers based on means and standard deviations. For example, in ore tonnes, we are using a NORMINV function rather than a hard-coded number. The same goes for the rest of them. This means that each time we update or recalculate, these numbers will change, too. The values along the top of our table are linked directly (refer) to those formulas on the left exactly how they did in the “Monte Carlo (Simple)” tab. We will simply capture the values that are calculated on each refresh and put them in the row here. Remember, the random numbers recalculate with each refresh, which means that each new instance (row calculated) of a trial (of the 1,000 that will run in this table) will initiate a refresh. Due to the constraints associated with data tables, we still need to include a first column to initiate that refresh. This is because the values along the top of the table are formulas, so we will not have a Row Input Cell, which means we need a Column Input Cell. The Column Input Cell would be substituted in some out-of-the-way place that will not affect anything else, so it does not really matter what we put in that first column. So, we are just going to put the run number. The first row is 1, the second is 2, and so on.

In review, Monte Carlo simulations at this level take advantage of random numbers and normal distributions. The data table needs either a Row Input Cell, a Column Input Cell, or both. In this advanced example, our inputs are randomized with each iteration or trial run. When the simulation runs and moves down the table, those inputs are refreshed with different random numbers and locked into the table. This will be a one-way table since the values across the top row are formulas that we want to copy down. This means that our Column Input Cell needs to exist. The question, then, becomes a matter of where we put that number. The answer is that it does not really matter. It does not have to go into our calculation here, so we can just put it somewhere else in the spreadsheet. As the simulation runs, it will record those values in the columns from exchange rate through gold grade that are adjusted each iteration due to the NORMINV function we used in the input area. It will also take the profit, which is a function of the inputs, and include that in the table on the far-right column. The run number changes the out-of-the-way cell we selected, which really is essential to recalculate or refresh the input cells. After this is complete, we can analyze the profits for patterns.

Let us run it. Select the whole table. You can do this by selecting the blank cell directly above the run numbers and directly to the left of the input columns (the top left cell of the table). Then hit Ctrl + Shift + right arrow + right arrow and Ctrl + Shift + down arrow + down arrow. The whole table should now be selected. Go to the Data tab at the top in the ribbon, click What-If Analysis, and then select Data Table. Now, we ask ourselves, “What varies as we go across the row?” Nothing varies, which means that we are going to make a one-way table because we are not substituting these as we go across the row. Then we ask ourselves, “What changes as we go down the column?” The run number changes. In this case, it does not matter which cell changes, so we can just pick $A$1, which will be overridden each iteration. We hit OK, and if nothing changes, we have to go to Formulas again and hit Calculate Now or F9.

After that is complete, we have just run a thousand different simulations of varying inputs. We can then devise a formula for the average of all the calculated profits with AVERAGE. It looks like after a thousand runs, varying our inputs according to the means and standard deviations we understand them to have, we have an average of $5.5459 million in profits. This $5.5459 million is specific to this iteration of random numbers, but it is a good estimate as we figure that each time will be similar to that $5.5 million. We can also notice that if we look at individual runs, sometimes there is a loss and sometimes there is a gain. We can also increase the number of rows that we do. Instead of 1,000, we can extend to 10,000, 100,000, or even a million and more. Overall, we now have the ability to run some pretty complex models here in Excel. This combines what we have learned about random numbers and one- and two-way tables. They are pretty powerful.

We can now plot our profits and look at the distribution of those profits. This is done by highlighting that last column of profits, inserting a PivotTable, and placing it just to the right of the table. In the PivotTable fields, we can place M$ (profits) in rows and values (count of). Next, we can group it by fives from -20 to 35, which makes it so that every value is covered in the range. We can then name it and look at the risk and benefit. Better information can help us make better decisions, but we have to remember that the model is only as good as the assumptions that we code in, including the means and standard deviations for our inputs.