Today we are going to talk about a more advanced topic in model evaluation.

Three approaches to Excel VBA Monte Carlo simulations

What is it, how can I get started using it? Well just follow after the jump to find out. To put it simply the Monte Carlo method is taking a bunch of guesses for your inputs and seeing what all of your results look like.

We know that almost every input to our models are just point estimates. But there is a degree of uncertainty around these estimates, by drawing values from each of the distributions multiple times we can determine likely outcomes.

For a more extensive discussion of this method check out the Wikipedia article HERE. The important part is that we have to run our model lots of times with new draws for the variables.

When I say lots of times I mean thousands, perhaps hundreds of thousands or even millions of times. In order to keep things simple the model I will be demonstrating these techniques on will be supper simple. Two numbers drawn from a normal distribution are our inputs and the output is their product. We use the rand function to return a random number as our probability and feed it into the inverse normal probability function to generate a value.

What we can do is write some VBA code to draw lots of values and then look at the entire outcome set. To speed up this code we are going to turn off automatic calculation of the workbook and then manually calculate when we want a new value. This is important because the rand function is volatile it will draw a new value each time we touch the workbook and we could lose output information and unnecessarily slow everything.

However, we must save the users current calculation state because that is just the nice thing to do. Of course turn off screen updating if you want to finish an estimation in less than a day.

We declare our variables. One to count what iteration we are on and another to hold the total number of iterations we are supposed to run. Using a simple for loop we run through the code however many times we should.

Monte Carlo Simulation Formula in Excel - Tutorial and Download - Excel TV

We start by forcing the application to recalculate, thus drawing new numbers and then setting the next cell in Sheet2 equal to our output value. Now you may be wondering why print out our numbers in a sheet rather than holding them in an array.

But another advantage is that once we run the model we can do whatever we want to the data using built in Excel functions rather than having to decide beforehand what analysis we want to do and programing it in the VBA. This code runs a standard progress bar I use for long processes.

Something to note is that I use an if statement to make sure that this progress bar macro is only run times once per percent of progress.

This is enough to show where the calculation is without slowing down the macro a lot. Be nice and set the calculation method back to what it was at the start and let the screen update again.

Monte Carlo Simulation in Excel | The Excel Ninja

Run the model for 10, draws take a sip of coffee and we will look at the results. Sheet2 contains the results from each and every draw. You should have an average around 2 and a StDev around 2.

Also try out Histogram and bar charts to plot your output like this. That should give you a feel for how to build a basic Monte Carlo simulation in Excel. Try modifying it with different distributions or multiple outputs. You can easily apply different statistical methods to the output and save it for latter analysis.

Tell me how you like to analyze uncertainty FbGary. You are commenting using your WordPress. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email. The Excel Ninja Not just ideas, best practices Search Main menu Skip to primary content.

What is Monte Carlo To put it simply the Monte Carlo method is taking a bunch of guesses for your inputs and seeing what all of your results look like. Our Model In order to keep things simple the model I will be demonstrating these techniques on will be supper simple. Option Explicit Sub run 'Start up and save original calcualtion state Dim StartState As Single Application. Clean up 'clean up what we did on startup Application.

Running The Model Here is the file that I have built to demo this method. What Next That should give you a feel for how to build a basic Monte Carlo simulation in Excel. Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in: Email required Address never made public.