Monte Carlo Example

A recent discussion on the G31000 forum debates the importance of Monte Carlo but it resulted in me promising a model to the group, a straight forward and fundamental model on Monte Carlo?

I have talked about Monte Carlo before on this blog and in more than one place. However, the effort today is to develop a Monte Carlo simulation in Excel that works for operational risk losses, rather than in market risk or credit risk where the technique is so often found. The objective is to build a fully working Microsoft Excel model which isn't just statistical theory but actually exemplifies the Monte Carlo simulation process in practice.

This article explains such a model and there is a link at the end of this posting that will allow you to download the spreadsheet, if you so desire.
  

Monte Carlo Basics

The model here has been designed to solve a common but straight forward operational risk problem which is; "To estimate the potential loss from errors in a manufacturing process". Risk analysts will generally track slips or errors in their operations, so that they can attempt to understand the expected and unexpected outcome from randomness or uncertainty.

The questions we are trying to solve here include: 
[1] What is a typical accepted unknown level of uncertainty for us? 
[2] What should we be reserving for in this typical loss?
[3] How bad could a potential loss really become during an operational slip?

Monte Carlo will help us quantify all of these uncertain values and many more areas of risk.

There are many ways in which this analysis could have been built up and Monte Carlo is ideal for this work. Rather than show deterministic formulas, Monte Carlo will evidence the outcome of uncertainty across a simulation of potential trials. In effect, it describes uncertainty in a way that is more real than simple point estimates overtime that are often very erroneous.

This foundation spreadsheet has five key parts to its operation which have been labeled below:

[1] A business variables section 
[2] The random trial simulation table
[3] A histogram plot calculation table
[4] A statistical summary table
[5] A probability distribution function of potential loss

Monte Carlo Main Sheet [Click to enlarge]

From a user perspective, the model is very easy to operate.

All that needs to be supplied in the model is a handful of variables which have been highlighted in the orange section of the spreadsheet.  When these figures are entered into the sheet, "Monte Carlo randomness" will automatically act out and an outcome or result is represented as both a statistical table and a probability loss distribution graph.

Statistical Formulas [Click to enlarge]

Once the uniform simulation is run, a table of statistical measures is generated which informs the risk analyst about the type of randomness they are witnessing.  A 95% cut off has been supplied and acts as a Value at Risk measure for the analyst but other statistical information about the simulation is also demonstrated in the model.

Loss Distribution [Click to enlarge]

A Histogram Plot Table is used to drive out a probability distribution graph and it will also show the risk analyst; the likelihood of landing on a specific area of uncertainty and the range of expected loss counts.

The final caveat I would like to add at this point is: This is Monte Carlo 101 and there is a lot more work that could be done to explain randomness or uncertainty in our business operations.

The spreadsheet can be downloaded from this [ LINK ].