Monte Carlo simulation
A Monte Carlo simulation model can be useful to determine the probability of outcomes - which can not easily be predicted due to randomness of input variables - and also to investigate how (strong) outputs correlate with inputs.
Monte Carlo simulation model parts
A Monte Carlo simulation model consists of the following parts:
- the distribution parameters of random inputs
- the calculation model that generates the outcomes of a single run
- the database, a table that contains al values of inputs/outputs of multiple runs (e.g. 10,000)
- an interface between calculation model and database - a single row of formulas that link to each individual input and output variable, and which has the same layout as the database table
- the statistical analysis of inputs/outputs - also to check the calculation model - with metrics such as average/median/min/max, correlations, probability distribution histograms, and regression scatter charts
It also contains short MS Excel VBA code
to run the calculation model multiple times and store inputs/outputs of each run in the database (see MS Excel VBA code at the end of page).
Download MS Excel Monte Carlo simulation model
(320kB - downloaded
Monte Carlo simulation model preview
The example has been kept very small on purpose. The focus is to show what the structure of such a model may look like and how little MS Excel VBA coding is required
, not to show how to create complex models. This example uses uniform distributions. But you can also generate random variables of other distributions (such as a triangular or a normal distribution): see example 14 of the MS Excel function reference file
Short MS Excel VBA code
Of course, you can make the run length (10,000) a user input variable. In that case, do not forget to add code that clears the database first before starting the simulation.