Likelihood of a future event size total within a range: in Excel

Find the LogNormal parameters Include sizes in trials Find the proportion of trials that will push your buttons

What to read first: Likelihood of a future event size total within a range

Risk specialists Version 1.0 Beta

Build an Excel workbook for finding the likelihood of a future event size total within a range, using Monte Carlo trials.

Events are assumed to occur at random with an inferred long-term frequency, and with random sizes following a LogNormal distribution.

This page adds to an Excel workbook along the lines described in earlier articles. You can also download a completed Excel workbook.

Download the complete Clear Lines Excel Workbook (17 MB)

Find the size distribution parameters

You must first have a LogNormal distribution for event sizes, using LogNormal distribution parameters matching your chosen 50% and 95% percentile sizes, using a method like that recommended in Pareek (2012), Figure 1. There is a separate Clear Lines Excel workbook to do this step. Building your own is quick and easy. However, these methods require that you install the Solver Add-In to your Excel installation.

A similar method can be used for distributions other than the LogNormal.

How to load the Solver Add-in in Excel and define and solve a problem by using Solver

Download a working LogNormal Parameters Excel Workbook (14 KB)

Include sizes in trials

Set up event count trials as in the preceding sections.

As at 11 December 2018, community consultation on LinkedIn had raised some questions about the Monte Carlo method as implemented for predicting future counts from history. Those questions may also apply to the method here used for generating event sizes. This page will be updated as questions are resolved.

Pareek (2012) assumed a known long-term event frequency, so that trials were not necessary to generate a distribution of event counts. Pareek suggested trials only for the variation of event sizes.

Add a further stage to each trial (i.e. 101 extra columns in the trials sheet), leaving two columns spare. This creates a new grid in which a size is selected for each of the single events predicted in the trial. There may be zero events in some trials.

Call the columns of the new grid Trials_EventSizes.

The number of events appears across the top, in the same row as used for the pure count trials, so the row name is again Trials_PotentialCounts.

The event size selection is made within each cell of the added grid by

=LOGNORM.INV(RAND(),LogNormalMean,LogNormalStandardDeviation)

where LogNormalMean and LogNormalStandardDeviation are the results of the iterative search for LogNormal parameters. In the Clear Lines workbook, these cells are named ControlPanel_LogNormalMean and ControlPanel_LogNormalStdDev.

Excel cuts out some messy table lookups by providing the inverse LogNormal directly as a function, LOGNORM.INV(). The inverse calculates the event size from a given cumulative probability, in this case the random selector value RAND().

The Clear Lines formula in each cell is actually:

=IF(Trials_TrialEventCount>=Trials_PotentialCounts, LOGNORM.INV(RAND(),ControlPanel_LogNormalMean,ControlPanel_LogNormalStdDev), “”)

This formula returns a size value where an event occurs, and a blank if the required number of events have already been generated for that trial.

Calculate the total size of all events predicted within each trial. That’s an extra column in the Trials sheet, conveniently placed to the left of the new grid of event sizes (column Name Trials_TrialTotalOfEventSizes).

Find the proportion of trials that will push your buttons

Classify each trial total as inside or outside the range of interest. The Clear Lines does that in the column Trials_TrialTotalInTargetRange. Calculate the proportion of trials with a total inside the range of interest. In the Clear Lines workbook, see ControlPanel_TotalProportionInTargetRange.

Map of the series

Likelihood of a future event…

Theory

How to turn an event frequency into likelihood

count within a range, from a long-term event frequency

count within a range, from a history of events

size total within a range

Excel

About the Excel implementation

…in Excel.

…in Excel.

…in Excel.
This article

Download the complete Clear Lines Excel Workbook (17 MB)

Download a working LogNormal Parameters Excel Workbook (14 KB)

Main article on repeating risk events and likelihood

References

Pareek, Mukul (2012) Using Scenario Analysis for Managing Technology Risk. ISACA Journal, Volume 6 of 2012.


Next article for risk specialists

Some results from Monte Carlo trials of total event sizes

Comments on the table of results

Risk specialists Version 1.0 Beta

Parent articles

Likelihood of a future event size total within a range

Conceive a distribution for event sizes Find the LogNormal parameters A table for your LogNormal distribution Include sizes in trials Find the proportion of trials that will push your buttons Conclusions you may draw

Risk specialists Version 1.0 Beta

Index to the topic Risk in work unit business planning

Leave a Reply

Your email address will not be published. Required fields are marked *