Monte Carlo simulations
Monte Carlo simulations are a key decision making tool in statistical risk analysis of models which may contain uncertain values. In Excel using XLSTAT.
The simulation methods available in XLSTAT are Monte Carlo and Latin Hypercubes.
Simulation models allow to obtain information, such as mean or median, on variables that do not have an exact value, but for which we can know, assume or compute a distribution. If some result variables depend of these distributed variables by the way of known or assumed formulae, then the result variables will also have a distribution. XLSTAT allows you to define the distributions, and then obtain through simulations an empirical distribution of the input and output variables as well as the corresponding statistics.
Simulation models are used in many areas such as finance and insurance, medicine, oil and gas prospecting, accounting, or sales prediction.
Four elements are involved in the construction of a simulation model:
- Distributions are associated to random variables. XLSTAT gives a choice of more than 20 distributions to describe the uncertainty on the values that a variable can take. For example, you can choose a triangular distribution if you have a quantity for which you know it can vary between two bounds, but with a value that is more likely (a mode). At each iteration of the computation of the simulation model, a random draw is performed in each distribution that has been defined.
- Scenario variables allow to include in the simulation model a quantity that is fixed in the model, except during the tornado analysis where it can vary between two bounds.
- Result variables correspond to outputs of the model. They depend either directly or indirectly, through one or more Excel formulae, on the random variables to which distributions have been associated and if available on the scenario variables. The goal of computing the simulation model is to obtain the distribution of the result variables.
- Statistics allow to track a given statistic a result variable. For example, we might want to monitor the standard deviation of a result variable.
A correct model should comprise at least one distribution and one result. Models can contain any number of these four elements.
Options for simulation models
A model can be limited to a single Excel sheet or can use a whole Excel folder.
Simulation models can take into account the dependencies between the input variables described by distributions. If you know that two variables are usually related such that the correlation coefficient between them is 0.4, then you want that, when you do simulations, the sampled values for both variables have the same property. This is possible in XLSTAT.
Note : Monte Carlo simulations is only available for Windows OS.
Going further with XLRISK
analyze your data with xlstat