by David Vose

Quantitative risk analysis involves a wide range of skills and tasks that risk modelers need to have mastered before embarking on an important risk analysis of their own. Among the most over-looked and underestimated of these skills is knowledge of how to manipulate variables within Monte Carlo simulation models which is the standard modeling technique for risk analysis.

Add, subtract, multiply, divide – we have learned how to use them with numbers at school by the age of ten at the latest, and we take it for granted that we have mastered them. It is hard to imagine a risk analysis model in any field that does not include some of these four operations. Yet these basic operations very often do not work in the same way when those numbers are uncertain. Very worryingly, nearly every person I encounter who is involved in risk modeling is to some degree unaware or unclear about the correct ways of manipulating uncertain variables in a model, perhaps because we don’t give a second thought to calculations using + – * / .

Most risk analyses are performed using Excel with a Monte Carlo add-in like our software, ModelRisk. There is a widely held belief that one can take a standard spreadsheet model (e.g. a cashflow model with EBITDA or NPV calculation) and simply replace any value within that model that is uncertain with a function that generates random samples from some distribution to reflect its uncertainty. People mistakenly think that the rest of the model’s logic can be left unchanged.

And it really does matter. Incorrect manipulation of uncertain variables in a model will almost always produce simulation results with something close to the correct average value, which people use as a ‘reality check’, but completely wrong spread around that average value. The net effect is that decisionmakers are presented with a wildly inaccurate estimate of the uncertainty (risk) of the outcomes of different decision choices. Some may realize that the model results are unrealistic and dismiss them, while others won’t and will make very misguided decisions.

I invite you to read these examples very carefully if you are intend to write Monte Carlo risk analysis models, and share it with other colleagues who build such models.