A Monte Carlo simulation model is useful to determine the probability of outcomes - if it can not easily be calculated due to randomness of input variables - and to investigate how (strong) outputs correlate with inputs.
Generating random input can be done with regular Excel formulas: see example 14 of function reference file (for Uniform, Triangular, Normal, Negative Exponential, Erlang-K, Weibull distributions; others can be created by VBA).
This page focuses on how to structure a Monte Carlo simulation model.
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 all 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. See also the comments and warning at the end of the page.
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 as found in demo file
Shorter alternative
Removing
Application.ScreenUpdating = False
would make it incredibly slow (due to continuous "screen flickering"), so these two lines have been kept in.
Variable run length
You can make run length (10,000 in the above) a user input variable in a cell named "nruns", and adjust code to:
For iRun = 1 To Range("nruns").value
.
Then also add code that clears the database before starting the simulation:
Range("database").value=""
.
Display progress
You can display progress by adding line
application.statusbar=iRun
inside the For Next loop.
Clear statusbar at the end of the run by
application.statusbar=""
Warning
If your model calculates extremely slow - for whatever reason - then it may happen that VBA already continues writing results to database before calculations have actually finished, resulting in a wrong database!
To make VBA wait until all calculations have finished, you can use the following code:
Application.Calculation = xlCalculationAutomatic
Application.Calculate
Do Until (Application.CalculationState = xlDone)
DoEvents
Loop
VBA is very slow compared to other programming languages, even if VBA code is lean and fully optimized. For example, Javascript excutes 100-1000 times faster for CPU-bound tasks. This speed difference was one of the reasons Stelling Consulting started developing web apps to replace Excel VBA tools, many years ago.
