If you can't see the site index on the left click here

A Probability Density Function can be thought of as a description of the relative probabilities of all the possible outcomes of a chance event.

It can be a discrete function, describing the relative probabilities of a finite number of outcomes (like tossing a coin - 50% heads, 50% tails) or it can be a continuous function, where the value of the outcome will lie within a range, but could take any value within that range (such as the normal distribution). For a continuous function, the probability of a particular exact value of the outcome occurring is effectvely zero (because you are taking an infinitely thin slice from a finite range).

Whether the function is continuous or discrete, for a PDF the sum of probabilities for all the outcomes adds up to one (unity) - meaning that all the possible outcomes have been accounted for.

The key to good risk analysis is to build a good model of the system or scenario, then to work out how it would behave in a variety of circumstances. You can then see what are the influences (variables) that are most important (because they have the most significant effect on the likely outcome). To carry out Monte Carlo analysis, where lots of different situations are tested, we need a way to generate different, but representative, values for these variables each time we recalculate the model. This is where we can use PDFs.

Say I want to model my bank account, and want to work out the likely balance at the end of the week.

Well, I know that there is a 75% (0.75) chance that the Hi-Fi shop in town will finally have in stock that super gizmotronic sound squedgelizer that I have been waiting for when I visit on Friday. So I can model the outcome by putting in a function that deducts the price of the squedgelizer from my account 75% of the time (and doesn't for the rest of the time).

But if I can't buy the squedgelizer, I think I will treat my cat to a night at the movies, so that's another deduction I can make, though that will depend on whether there is a Lassie movie showing (30% 0.3 chance, another discrete function - cat hates Lassie), and whether I can get to the movies on time.

This time I have to model the time it takes me to drive home (to pick up the cat) and then to the cinema. For this I can use the sum of two normal distributions, since I know that it takes me an average (mean) 20 mins to get home from work, with a standard deviation of 5 mins and a further 10 mins on average (mean) to get to the cinema, with a standard deviation of 3 mins. The movie starts at 6:30, I leave work at 5:45 and it takes me exactly 9 minutes (every time, go figure!) to find the cat.

Lost the plot yet? This begins to illustrate why it makes sense to build models and use a tool like Quadrant to do Monte Carlo analysis. Sometimes the situation gets so complicated that you just can't tell what the likely spread of outcomes might be. The only way to find out in advance is to build an accurate model, feed it with inputs that follow as closely as you can the probabilities of the real-life inputs, then crank the handle (lots of times) to see what pops out - and how often.

Where PDFs come in is in modelling those real-world inputs.

Moviecat : An example that uses discrete PDFs constructed from IF functions, together with Excel's NORMINV function to provide continuous PDFs for our Saturday morning bank balance model.

moviecat.xls

Many of the PDFs and other inputs that you might need for building models are already available in Excel. Where these functions require a random number between 0 and 1 as an input to specify where to sample the distribution, simply use the RAND() function.

The following function types have examples in the worksheet file below.

Available already in Excel:

Normal, LogNormal, Beta, Gamma, ChiSquared, F, Uniform, Discrete Uniform

Available in Quadrant ( because they aren''t in Excel ):

Triangular, 3 Point Beta

Other functions will be added to later releases of Quadrant as needed - so please e-mail me if you need specific functions.

All e-mail feedback to : quadrantrisk@btconnect.com