Monte Carlo Simulation Excel Model

Description
The Monte Carlo method is taking a pile of estimates for your inputs and then seeing what the results come out like.

Nearly all of the inputs into models are just estimates, however there is an amount of uncertainty around the estimates. If we draw values from every one of the distributions many times we can work out the probable outcomes.

The important bit is that you should run the model multiple times with new draws for the variables. 'Multiple' in this case means up to millions of times.

To make things the least complicated possible, the model demonstrating these techniques is really straightforward. From a normal distribution, 2 numbers are drawn and these are our inputs. The output is their product. The rand () function is used to return a random number as the probability and it is fed into the inverse normal probability function so that a value can be generated.

Pressing the F9 button to generate a recalculation for the workbook (giving new random numbers) never seems to give a feel for how the numbers are generating output and interacting. The other option is to write VBA code so that we can draw lots of values and then overview the whole set of outcomes.

*Originally shared on theexcelninja.wordpress.com*