Content
The focus of this page will be to learn how to manage uncertainty with quantitative models. We will cover:
Simulation is a method for quantifying the impact of multiple interacting sources of uncertainty on an outcome of interest. Understanding the distribution of the possible outcomes allows for a better understanding of the risk involved in a particular project, as well as the identification of the inputs that are most influential in the project's value. Once these influential inputs are identified, decisions can be made regarding the benefit of controlling the outcome by exploring alternatives.
Decision Analysis is a framework that enables you to make decisions that are consistent with an objective, in the face of uncertainty. We will learn to evaluate alternatives, and try to determine the value of acquiring information. We will cover simple decisions, such as accepting or rejecting a project, as well as complicated ones, such as the quantification of the damage to the value of a project due to conflicts between shareholders and bondholders.
Optimization is a catch-all term that describes methods for finding a solution that maximizes an objective, while respecting potential constraints.
By the end of this page, you should be able to build models to solve real-world business problems. This involves creating a model, performing the correct analysis, validating the model, and drawing conclusions.
- Simulation
- Decision Analysis
- Optimization
Simulation is a method for quantifying the impact of multiple interacting sources of uncertainty on an outcome of interest. Understanding the distribution of the possible outcomes allows for a better understanding of the risk involved in a particular project, as well as the identification of the inputs that are most influential in the project's value. Once these influential inputs are identified, decisions can be made regarding the benefit of controlling the outcome by exploring alternatives.
Decision Analysis is a framework that enables you to make decisions that are consistent with an objective, in the face of uncertainty. We will learn to evaluate alternatives, and try to determine the value of acquiring information. We will cover simple decisions, such as accepting or rejecting a project, as well as complicated ones, such as the quantification of the damage to the value of a project due to conflicts between shareholders and bondholders.
Optimization is a catch-all term that describes methods for finding a solution that maximizes an objective, while respecting potential constraints.
By the end of this page, you should be able to build models to solve real-world business problems. This involves creating a model, performing the correct analysis, validating the model, and drawing conclusions.
The following content will make use of a toolset called "The DecisionTools Suite" which is an integrated set of quantitate data analysis programs that manages uncertainty in decision-making through probabilistic risk analysis using Monte Carlo simulation. It also allow you to create decision trees, optimize resource allocation, identify critical factors, manage uncertainty in project schedules, and more when you install the toolkit into Microsoft Excel.
Note: Once installed, to get access to the tools in Excel you'll simply need to open the "RISK" app, this will give automatically open up excel with the tools available.
- You can request a demo here: https://lumivero.com/products/decision-tools/
Note: Once installed, to get access to the tools in Excel you'll simply need to open the "RISK" app, this will give automatically open up excel with the tools available.
SIMUlation
Modeling Example:
You can use the following attachment to follow along with an example of how to use RISK to perform a simple simulation of savings account growth across different year to year annual growth figures.
You can use the following attachment to follow along with an example of how to use RISK to perform a simple simulation of savings account growth across different year to year annual growth figures.
| 1-1_investing_retirement.xlsx |
|
In this Excel model, we are estimating the value of a savings account after 30 years.
One key uncertainty in this model is the annual rate of return, which can vary from year to year. If we assume the market has an average annual return of 12% with a standard deviation of 25%, we can model this uncertainty using the RISK add-in. To do this, each year’s return rate is defined as a random variable using the following formula:
Next, we specify the model’s output—the value we want the simulation to track. In this case, the output is the final account balance in cell B6. With cell B6 selected, navigate to the RISK tab, click Output, and assign a name to the output (the specific name is not important). Finally, with cell B6 still selected, click Simulate to run the Monte Carlo simulation and generate a distribution of possible outcomes based on the varying annual return rates. |
Here is what the output of the simulation will look like:
Interpretation of Simulation Results
After running 1,000 Monte Carlo simulations, where each simulation randomly assigns an annual return based on a normal distribution with a 12% mean and 25% standard deviation, the following insights emerge:
Notably, the distribution of outcomes is highly right-skewed, indicating that while most outcomes cluster below the mean, a small number of extreme positive outcomes significantly increase the average. This is further supported by the large difference between the mean ($1.34M) and median ($840k) values.
After running 1,000 Monte Carlo simulations, where each simulation randomly assigns an annual return based on a normal distribution with a 12% mean and 25% standard deviation, the following insights emerge:
- The minimum ending balance observed across all simulations was approximately $25,500, representing a very poor sequence of returns over the 30-year period.
- The maximum ending balance observed was approximately $21.1 million, reflecting an exceptionally favorable sequence of returns.
- The average (mean) ending balance across all simulations was approximately $1.34 million.
- The 90% confidence interval for the mean is ± $87,600, meaning that if this simulation were repeated many times, 90% of the calculated mean values would fall within this range around $1.34 million.
Notably, the distribution of outcomes is highly right-skewed, indicating that while most outcomes cluster below the mean, a small number of extreme positive outcomes significantly increase the average. This is further supported by the large difference between the mean ($1.34M) and median ($840k) values.
Modeling Non-Uniform Distributions
While in the previous example we varied our return rate by a known gaussian normal distribution, the RISK tool also allows us to define a parameter's probably distributions to be non-normal, examples of these are:
While in the previous example we varied our return rate by a known gaussian normal distribution, the RISK tool also allows us to define a parameter's probably distributions to be non-normal, examples of these are:
|
|
|
decision analysis
optimization