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.

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

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.

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.

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 ].

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]

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 ].

Dear Martin,

ReplyDeleteThank you for making good on a generous promise

Sam Demuth

Martin, I am sure this will teach me a thing or two about MCS.

ReplyDeleteJulian du Plessis

Thanks guys, I am glad you enjoyed the article and spreadsheet example.

ReplyDeleteThe purpose under this post was to explain the foundations on Monte Carlo and to keep it really practical so that risk practitioners such as yourselves can see the application of the tool.

Have a great day ahead.

Martin, Thank you. As a student who is always looking for deeper understanding of a subject this hits the spot. I hope you don't mind but I have forwarded your link to others in my cohort so they also can benefit.

ReplyDeleteFrank,

ReplyDeleteGlad you enjoyed the article. Any questions, any interesting thoughts or queries on risk, do send them through.

Have a great day ahead.

Martin.

Hi Frank,

ReplyDeleteglad to see that also someone also goes for Monte Carlo in MS Excel.

I also did this using VBA.

If you wanna have a look you can download it here:

http://www.riskcon.at/pdf/Probabilistic_Demoversion_en.exe

Please note, the excel file (with an embedded macro) comes in an exe format.

Therefore, you might need to allow your security software temporarily to download this file and then scan it in order to save the file correctly - thank you.

Frank,

ReplyDeleteWill it be possible to answer a few questions that I can maybe email to you regarding this model and using it to get an aggregate loss distribution for loss events and severity?

Regards

Nico