Why you shouldn’t use the triangular distribution

The triangular distribution is popular in risk analysis because it seems to naturally embody the idea of ‘three point estimation’, where subjective judgement is used to estimate a minimum, a ‘best guess’ and a maximum value of a variable such as the cost of an item or the time taken to accomplish a task. It looks mathematically simpler than many of the standard distributions and could be regarded as the simplest probability density function that embodies a random variable with a given minimum, mode and maximum. Because of this, you may be tempted to think of it as the distribution that involves fewest assumptions and that it is therefore the one to use when you don’t know what the real distribution is. Its Wikipedia article says ‘… the triangle distribution has been called a “lack of knowledge” distribution’. Although the article gives no reference for this assertion, and I’ve never seen it explicitly stated anywhere else, it sounds plausible and I do think it represents the main reason people use the triangular distribution.

The problem with this idea is that it isn’t true. Read more

VBA Monte Carlo risk analysis spreadsheet with correlation using the Iman-Conover method. Part 5

Welcome to the fifth and final part of my series of blog posts about how to build a VBA application in Microsoft Excel that performs Monte Carlo simulation. It follows on from my post on 11 February, which assembled into a working prototype the parts developed in earlier posts. (If you haven’t read any of the other posts in this series, it would probably be best to read those first, starting with Post 1.) Read more

VBA Monte Carlo risk analysis spreadsheet with correlation using the Iman-Conover method. Part 3a

Introduction

Welcome to the third of five (sort of) posts about Monte Carlo risk analysis using Excel VBA. Actually, what was going to have been today’s post has turned out to be much longer than my target of around 3,000 words, so I have decided to split it in two, with the second half appearing tomorrow.

Yesterday we looked at how to generate arrays of random numbers using a combination of inverse transform and ‘latin hypercube’ sampling. Today we get to the heart of the problem and consider how to induce correlation onto our random sample using the Iman-Conover method. This is based on the idea of rank-order correlation. It involves the following steps: Read more

VBA Monte Carlo risk analysis spreadsheet with correlation using the Iman-Conover method. Part 2

Welcome back! This is number two in a series of five blog posts that describe how to construct a Monte Carlo risk analysis application in Excel VBA. It follows on from yesterday’s, where I gave an overview of the problem and how I propose to tackle it. Today’s post describes how to generate random numbers according to arbitrarily chosen probability distributions using the inverse transform method. Read more

VBA Monte Carlo risk analysis spreadsheet with correlation using the Iman-Conover method. Part 1

As this is my first blog post, I thought I’d try something slightly more ambitious than just spouting opinions. Unfortunately it’s turned into something of a treatise. If I’d known how big it was going to be I probably wouldn’t have started but, as Magnus Magnusson would have said; “I’ve started so I’ll finish”! I will feel it has been worthwhile if someone somewhere makes use of it. Read more