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