Excel - Monte Carlo simulation

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.

     Download demo file  (320kB - downloaded 4766 times)

Monte Carlo simulation model parts

A Monte Carlo simulation model consists of the following parts:
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).

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.