Risk Modelling, Risk Analysis, Excel Modelling

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:

I’ll be attending Palisade’s user conference at the end of March in Amsterdam. This year, I’ll only be giving one talk (instead of the customary 3 or 4), so – for a change – will have a bit of a chance to relax and to listen to the other presenters. My own talk will be about modelling best practices in both Excel and @RISK.

When one has data or graphs in Excel it can be very tempting to just copy these in to Word or PowerPoint by using the ‘Ctrl C and Ctrl V’ options. However, the robust interaction between these applications is more complex than this and may have a number of unintended consequences.

I am not a great believer in the extensive use of short-cuts; at least for most of my own modelling work, most of the time is not spent copying endless rows or columns of formulae, but rather in thinking through a solution to a problem, which is then implemented in Excel. Nevertheless, knowledge of those short-cuts that match closely with the type of activities that a particular modeller frequently undertakes can save valuable time. Attached to this message is an Excel file that lists the approximately 45 short-cuts that I tend to use fairly frequently.