About the Clear Lines Excel implementation — Clear Lines on Audit and Risk

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.