Risk Modelling, Risk Analysis, Excel Modelling

My apologies to readers of this blog for such a long gap in the postings … it’s been a hectic time. This short blog follows on from the topic of Excel best practices from the perspective of improving the visual understanding of models.

Following on from the last positing about objectives- and sensitivity-driven models, this posing presents some key areas in ensuring that Excel’s tabular structure is well-respected, which is a key best practice.

Some key best practices in Excel model formulation and implementation are that models are objectives-driven and sensitivity-driven. Further best practices include that Excel’s tabular structure is well-respected. The posting on these two areas is split into two, with the second to follow shortly.

Most commercial Monte Carlo simulation packages (@RISK®, ModelRisk® etc.), offer the ability to correlate variables. In addition, the implementation of relationships of correlation in any model in most cases requires simulation techniques (standard portfolio optimisation being the exception, where mathematical formulae can be used). As a consequence, other possible forms of dependency relationship are often overlooked and correlation used by default without much real consideration. Often, however, other forms of relationship are more appropriate.

A frequent issue that arises in Excel modelling is how to change the input values to a model whilst retaining the integrity of the model’s calculations. Sensitivity analysis is the simplest case of that, and often such analysis and related calculations can be implemented by the use of standard lookup functions, such as CHOOSE, INDEX of OFFSET. A lesser-known but potentially more powerful use is the use of the INDIRECT function.

The multinomial distribution is a generalisation of the binomial distribution; whereas the binomial describes how many of n independent trials occur (and hence also implicitly do not occur) i.e. it divides the trials into two categories, the multinomial allows for more than two categories. It has a number of important applications and uses.

Many people may be familiar with a framework for choosing a distribution to use in a Monte Carlo simulation model (i.e. “data-driven”, “scientific”, or “pragmatic”), that I also discuss in my book. In addition to this framwork – and arguably more fundamental in formulating a model correctl –  is the issue of the tendency to automatically interpret three-point estimates as if they represent a single process, rather than multiple processes.

The RiskTheo functions in @RISK® (version 5 upwards) provide a useful way to calculate the exact values associated with a distribution. These functions are arguably underutilised and not so well known by those who learnt version 4 without learning all the new features of version 5. One powerful use is to combine them with the alternate parameter formulation.

The use of probability distributions in risk modelling (when using Monte Carlo simulation techniques) is often only partially understood. I am not talking here about the selection of the appropriate distribution (continuous, discrete, compound, Poisson, Normal?, etc), but rather the implication of what it means to assume that a model parameter or variable follows a distribution.

A recent piece of work ahs involved studying some failures in risk management and trying to draw conclusions and learn lessons from them. Many major situations, such as the recent financial crisis, the BP Deepwater catastrophe, failures of insurers in the EU have been studied through formal enquiries, each with their own conclusions. My own reading of these studies and conclusions leads me to the following overall “conclusion of conclusions” or lessons learned: