About the Clear Lines Excel implementation

What to read first: How to turn an event frequency into likelihood

Risk specialists Version 1.0 Beta

This layer of articles walks through building your own Excel workbook for Monte Carlo trials, without macros or code of any kind.

The Monte Carlo trials are used to predict a future event count given an event history, and to predict the total size of future events of varying counts and sizes.

You can also download a complete Excel workbook and modify that, if you prefer.

Going the other way, you might want to write your Monte Carlo trial system in code, or find a published tool online. (I haven’t found one that matches the scope of this series.)

These walkthrough explanations assume that you have a moderate proficiency with Excel.

The following mid-level Excel techniques were involved in the Clear Lines workbook to implement the methods in this series. If you don’t know them, you might like to look them up. The links below are to support.office.com.

Hidden rows and hidden columns. Only empty rows and columns are hidden in the Clear Lines workbook. (Hide or show rows or columns)

Named ranges. Named ranges are defined by absolute row and column references (containing $), most often a reference to a whole column (e.g. $C:$C for the whole of column C). (Define and use names in formulas)

Range intersection operator (a space between the names of two intersecting ranges). This Excel technique is not described very often, but it is mentioned on this Microsoft page. (Calculation operators and precedence in Excel > Reference Operators)

Implicit identification of a cell within a range. If you have a formula in cell D4 that includes a name (e.g. ‘Input’) referring to all of column C ($C:$C), Excel takes the name to refer to the single cell C4. If the whole column reference creates ambiguity in the formula, the formula may include the current row as an intersecting range, e.g. ‘4:4 Input’, to refer to the single-cell intersection of the current row and the named column (C4 in this example). The $ in $C:$C means that the reference to column C is absolute rather than relative (to the cell containing the reference to column C).

Functions RAND(), POISSON.DIST(), LOGNORM.DIST(), LOGNORM.INV(), COUNT(), SUM(), COUNTIF(), MATCH(), INDEX(). I have avoided OFFSET(), so please thank me for that.

Solver Add-in, for the determination of LogNormal distribution parameters. (Load the Solver Add-in in Excel, Define and solve a problem by using Solver)

Manual calculation, rather than automatic calculation, when the workbook gets large. The relevant setting is in File > Settings > Advanced. (Change formula recalculation, iteration, or precision > Change when a worksheet or workbook recalculates)

Sheet Protection. Sheet Protection prevents any manual overwrites to cells other than those designated for input and Unlocked. The LogNormal parameters sheet must be Unprotected for Solver to work. The Clear Lines workbook has Protection on for all sheets, without any passwords. (Protect a worksheet)

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
This article

…in Excel.

…in Excel.

…in Excel.

Download the complete Clear Lines Excel Workbook (17 MB)

Main article on repeating risk events and likelihood


Next article for risk specialists

Likelihood of a future event count within a range, from a long-term event frequency

Risk specialists Version 1.0 Beta

Previous article for

How to turn an event frequency into likelihood

Overview of the series

Index to the topic Risk in work unit business planning

Leave a Reply

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