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. [Read more…] about Excel Best Practices and Influence Diagrams
Archives for 2011
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. [Read more…] about Some Best Practices in Excel Modelling – Respecting Excel’s Tabular Structure
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. [Read more…] about Best Practices in Excel Modelling – Objectives Driven Models
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. [Read more…] about Correlation versus Dependency in Monte Carlo Simulation Models
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. [Read more…] about Flexible Model Input Ranges using INDIRECT and ADDRESS
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. [Read more…] about The Multinomial Distribution
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. [Read more…] about Choosing the Right Distribution in a Monte Carlo Simulation Model
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. [Read more…] about Using the RiskTheo Functions in @RISK®
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. [Read more…] about Interpreting the Role of Distributions in Risk and Simulation Models
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: [Read more…] about Lessons from Risk Management Failures
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. [Read more…] about Excel Short-Cuts