67
edits
Changes
→Regulatory Risk Analysis Tool - Monte Carlo Simulation
[[File:Copilot 20250707 152121.png|left|thumb]]
[[File:Copilot 20250707 152121.png|left|thumb]]
= 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 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.
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.
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.
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<sup>st</sup> sheet in the tab list and “Model Outputs” must be the second