RATTWiki

Regulatory Risk Analysis Tool - Monte Carlo Simulation

Introduction & Motivation

When conducting a Cost Benefit Analysis (CBA) the assessment model is often based on assumptions or parameters that are uncertain. Knowledge of how uncertainties impact outcomes provide important information that deepens our understanding beyond just the most likely outcome. In the case of only one uncertain parameter, it is a relatively simply process to determine how alternative possibilities affect the outcome, however as the number of uncertain/risky parameters increases the range of possible outcomes that may result from a particular combination increases to a magnified level.

When outcomes are not discrete (one possible outcome among a limited set of possibilities) but continuous (one possible outcome among unlimited possibilities with or without a range) the problem becomes even more complex. As most parameters are of a continuous nature a more robust method than traditional sensitivity analysis is needed.

In situations such as this model inputs may be best defined as probability distributions rather than an individual number. When more than one probability distribution is used in repeated simulations to calculate an objective outcome this is referred to as a Monte-Carlo experiment. This works by repeatedly and randomly selecting outcomes from the distributions of the various inputs and applying them to the model. Each time this is done, a possible outcome of the model is created. The more times this process is repeated the more observations from the total set of possible outcomes are calculated, until eventually enough data is collected to piece together a probability-distribution of the model output.

Monte-Carlo Simulation

This process can be seen in the above figure where three model parameters exist, and each parameter is represented by a probability distribution.  By applying the Monte-Carlo method, model outputs can also be represented by a probability distribution.  In the case of a CBA this process would allow us to make statements such as what is the probability that the Net Present Value (NPV) of a regulatory proposal will be above zero (there is a 95% probability that the regulations benefits would exceed its costs), a confidence interval of the NPV (90% of the time the regulations would provide a NPV of between $50 and $90 million), the standard deviation of the NPV, the average NPV, the most likely outcome NPV, and which parameters are likely to have the greatest impact on the NPV.

Because uncertainty and risk in an outcome can be considered as a cost itself, this information adds important insight towards improving program choice and implementation of a regulation. In many cases costs need to be incurred to decrease uncertainties and thus the regulatory option with the central or expected highest net-present value may not always be the preferred option if it is important to limit our exposure to risk. This can be particularly important for outcomes that are discontinuous due to thresholds or irreversible outcomes that can result in low probability but high impact outcomes.

While Monte-Carlo methods have been around for a long time it is only with the increase of computing power that this kind of analysis has become common. Today there are many statistical programs that can perform Monte-Carlo simulations. While these packages are powerful and feature rich, they may not be accessible to all analysts within the Federal Government. They can be expensive and require special knowledge and training. Furthermore, the human resources required to acquire such packages through government acquisition services can become a barrier to the average analyst.  It also becomes difficult to share models created with these software packages with other analysts or departments to confirm reported results or adjust parameters for further analysis as they will need to have the necessary software to do so.

It is for these reasons that I endeavoured to create a simple solution to enable Monte Carlo simulation analysis. The Regulatory Risk Analysis Tool (RRAT) works within Microsoft Excel using visual basic macros. This permits analysts to work within an environment that they are familiar with and does not require the acquisition of any additional software licenses.  It also allows the tool to be incorporated into existing models in excel, increasing their functionality. While it is not as powerful a tool as other commercial software packages, it should fill the needs of most regulatory CBAs, and since it is open-source code, its capacities can be expanded and improved as required by others that may wish to do so.

Steps to Setting up a Simulation

Preliminary Steps: Prepare Excel for RRAT

Before using RRAT there are a few things that need to be checked.  First, the RRAT is a Microsoft Excel application, as it works by using macros, you will have to ensure that your security settings on Excel allows for these to run. The procedures for this differ depending on the version of Excel you are using and the security protocols your department have in place.

RRAT also makes use of certain functions from Excel Add-Ins.  Thus, before running the simulation you should check that the Solver Add-In, the Analysis ToolPak & Analysis ToolPak VBA are active in your Excel program (File - Options - Add-Ins).

RRAT includes two sheets that are referred to by macros.  These sheets are called “Model Inputs” and “Model Outputs” any number of additional sheets can be added or copied into the RRAT but “Model Inputs” must always be the 1^st sheet in the tab list and “Model Outputs” must be the second

Stage 1: Define Distributions for Independent Outcomes with Uncertainty

The first step in setting up the simulation will be to define what variables you are interested in examining the impact of uncertainty on the outcome of the CBA.  Enter the description for each of these variables starting in cell B9 on the Model Inputs sheet.  It is important that a description be entered here as a blank entry in the description column is used as a signal to the program that no more entries exist. The area on the sheet to enter descriptions can be seen in Figure 2 in the area labelled as “A”.

The second step in the setting up the simulation will be to define the probability distributions for the independent variable inputs.  While there are numerous probability distributions used in Monte-Carlo, RRAT is currently limited to five, although this limitation can be overcome by combining distributions.

Two of these are discrete distributions and three are continuous.  The continuous distributions used are the normal, triangle and the flat (continuous uniform).  The discrete distributions are defined (discrete) and fixed (one non-varying constant). The probability distributions need to be selected from a pull-down option from the column under the heading “Distribution Type” (column D) on the Model Inputs sheet (Figure 2 – Area “B”).

Figure 2: Setting Distribution Details

The final stage in defining a distribution is entering in their parameters. These parameters differ depending on the type of distribution selected and are displayed in the table above the columns where they are entered (columns E – F – G).  When a distribution type is selected areas that do not need to be defined are blacked out.  For instance, in Figure 2 the normal distribution has been selected.  As a result, in area “C” where the distribution parameters are entered the first parameter has been blackened out.  From the table above you can see that the mean of the distribution needs to be entered in column F and the variation needs to be entered in column G.

A more detailed description of the probability distributions and the required parameters to define them follows.