Decision Making Under Uncertainty

How is quantitative modelling applied to today's risk analysis problems in finance, insurance, oil and gas, manufacturing, and more? Monte Carlo Simulation is the key.  In this blog, we'll discuss the latest tips and techniques to help you judge which risks to take and which to avoid, allowing for the best decision making under uncertainty.

When doing risk analysis simulation modeling, it’s critical to represent the fact that many variables are related. Very rarely are all variables completely independent of one another.  For example, when interest rates are low, housing starts tend to go up. This relationship is represented by a correlation coefficient, a number between -1 and 1. -1 means the two variables are perfectly negatively correlated;  that is, when interest rates go up 1 unit, housing starts go down 1 unit. A coefficient of 1 is exactly the opposite, and 0 means there is no relationship between the variables at all. Typical coefficients are between these extremes. This correlation between input variables can and must be captured in your financial risk analysis models. Even if you are using expert opinion, estimating these relationships is better than ignoring them completely.

@RISK provides an easy way to define correlation coefficients. Through the use of a slider bar, you can dynamically update coefficients between variables as well as scatter plots representing those variables. This helps you visualize and more easily define these relationships.

For a quick video demonstrating this, see:



» View short videos on recently added @RISK risk software features

Presented at the Palisade Risk & Decision Analysis Conference New York City
Chris Albright, author of the book VBA for Modelers, presented a number of examples of how to automate @RISK, RISKOptimizer, and StatTools in Excel using Excel's VBA and Palisade's built-in object-oriented Excel Developer Kit. These examples include production applications, scheduling applications, World Series simulation, and more. All examples include macros written by Dr. Albright, so you'll need to enable macros when you open them.

» Download the examples
» Order Dr. Albright's book "VBA for Modelers"

On Thursday, September 2, 2010, Svetlana A. Sigalova will present a free live webcast entitled. "The Use of the DecisionTools Suite in Biotechnology Project and Portfolio Decision Making "

Given the uncertainty of outcomes in the biotech industry, consideration of variability is an inherent part of the decision process. Often, the mean (average) is not a relevant decision criteria. This is especially true for smaller biotech companies like Vertex – the opportunity costs are extremely high because scarce capital resources would be invested elsewhere, with a higher probability of realistic return. For example, a company may reject a project which is profitable on average (positive Net Present Value) because some of the possible outcomes are unacceptable to the decision maker. Consideration of variability allows a decision maker to bring in their own risk tolerance into the decision. A similar argument applies when estimating a safety margin above a base case (e.g. in cost budgeting).

» Register now (FREE)
» View archived webcasts

Vertex Pharmaceuticals, Inc. is a global biotechnology company based out of Cambridge, MA. The Company's strategy is to commercialize its products both independently and in collaboration with major pharmaceutical companies. Vertex's product pipeline is focused on viral diseases, cystic fibrosis, inflammation, autoimmune diseases, cancer, and pain.

Given the uncertainty of outcomes in the biotech industry, consideration of variability is an inherent part of the decision process. Often, the mean (average) is not a relevant decision criteria. This is especially true for smaller biotech companies like Vertex – the opportunity costs are extremely high because scarce capital resources would be invested elsewhere, with a higher probability of realistic return. For example, a company may reject a project which is profitable on average (positive Net Present Value) because some of the possible outcomes are unacceptable to the decision maker. Consideration of variability allows a decision maker to bring in their own risk tolerance into the decision. A similar argument applies when estimating a safety margin above a base case (e.g. in cost budgeting).

Vertex’s strategy and analytics group within the corporate finance division seeks to provide the senior management with dynamic revenue and profit forecasting methodology that helps to identify types of drugs that should be developed given a finite amount of cash and resources. A traditional financial view allows the user to identify scenarios and potential outcomes, but lacks the ability to show the range of potential values within each and every outcome. Vertex’s team uses the DecisonTools Suite to establish the average outcome, the variability of outcomes and to pressure-test risk and uncertainty of a particular scenario throughout the decision process.

Vertex’s team built a complex financial risk analysis model using @RISK to enhance its portfolio process. Monte Carlo simulation and optimization are used to analyze and optimize project and portfolio decisions, given short and long-term corporate strategy. @RISK is also frequently used throughout the business development process: simulating across multiple sales forecasts provides BD team with a range of potential outcomes, making it easy to pinpoint a particular scenario on a curve, along with its probability and value. TopRank turns the sensitivity analysis into a quick and seamless exercise, answering multiple what-if questions within minutes. Franchise and program leaders can now see a dollar effect of their program being delayed or advanced, adding supplementary indications to the development plan and even addressing the price uncertainties all at the same time. The simple interface of PrecisionTree along with tornado chart outputs makes it easy to explain the effect and importance of a particular assumption / decision to an audience with no finance background.

As the company continues to grow, adding more drugs and collaborations to its development pipeline, we will see in this free live webcast how the DecisionsTools Suite remains one of Vertex’s analytical tools of choice to enhance and guide the decision making process.

» Register now (FREE)
» View archived webcasts

The UK's new coalition government has said that, as part of its 'Green Deal', it will encourage home energy efficiency improvements paid for by savings from energy bills. It seems likely that, in the year that energy regulator Ofgem warned of 20 percent electricity price hikes by 2020, this initiative will include solar panel technology

Currently the UK still lags behind many other countries in Europe and the rest of the world when it comes to harnessing solar power. Not only do we have less hours of sunshine than many regions, but there is a lack of clarity as to the 'payback' time when it comes to users seeing a return on investment.

This is where Palisade customer, the California-based Tioga Energy, makes an interesting case study. Whilst it may seem unfair to compare the UK with the west coast of America when talking about solar-related matters, the sunnier climate does not reduce the need to prove ROI for customers with solar energy agreements.

Tioga Energy provides project financing through its solar Power Purchase Agreements (PPAs), and maintains and operates solar systems on behalf of its customers. Tioga’s offering delivers predictably priced power and enables organisations to to both 'green' their operations and reduce energy costs. To illustrate the benefits of solar, estimating future electricity prices and making comparisons by showing the savings from a new solar system, Tioga enlisted the help of @RISK for risk analysis solutions.

To forecast possible price increases, Tioga Energy inputs California's historical electricity rate data into a quantitative risk analysis model developed using @RISK. This generates a probability distribution for electricity rate rises over the 20-year PPA period, which shows that there is a 25 percent likelihood that price increases will be less than 4.8 percent, and a 25 percent chance that rate rises would be more than 8.7 percent.

The @RISK risk analysis model therefore helps Tioga Energy evaluate the likelihood that a customer will save money for a variety of PPA scenarios (i.e. the rate at which electricity would initially be charged and the amount by which it would then increase each year). It also calculates the magnitude of savings for the different combinations of first year costs and subsequent rises. Consumers are therefore able to better understand the pricing and make an informed decision about whether to sign up for a PPA.

Using historical data and @RISK's risk modelling software capacity, Tioga offers consumers a robust view of the potential benefits of a solar PPA. This enables them to hedge against rising electricity rates, as well as feel confident that they are playing a part in tackling global warming.

» Read the Tioga Energy case study

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

I recently saw an interesting @RISK cashflow model from the portable phone industry. It modeled the uncertainty in the length and decline of overall market demand for a particular technology against five strategies for getting various application products to market as soon as possible. 

Using @RISK’s Simtable function, combined with Excel’s Index function, it was possible to run multiple simulations and see which strategy could take best advantage of the potential market, given the uncertainties in the development process, the possibility of competitors, the market take-up and the margins that might be achieved.

As is often the case in all aspects of life, the simulation revealed that ‘a bird in the hand is better than two in the bush’; it’s very comforting to know that @RISK risk analysis solutions can cut through loads of detail and come back with an answer that echoes received wisdom!

Ian Wallace, ACMA
Palisade Training Team

@RISK risk analysis software using Monte Carlo simulation is used for a wide variety of applications. In this model, we have an example of a general usage to address Operational Risk.

In many circumstances one wishes to calculate the aggregate impact of many possible yes/no type events. For example, it is often important to answer questions such as "What is the loss amount that will not be exceeded in 95% of cases?" @RISK simulation can be used to answer such questions.

» Download the example: EventandOperationalRisks.xls

You can quickly generate a graph of any simulated output relative to a given input using the tornado graphs in @RISK risk analysis software. Just drag a bar from the tornado graph onto a blank space, and a scatter plot of that input relative to the simulated output appears. This is a great communication tool when you’re trying to assess the most important risk variables in your risk analysis model and want to “zoom in” on a particular input to see what’s going on. It can really help understand relationships in your modeling.

See this video for a quick demo:



» View "Getting Started in @RISK" video tutorials

Most people understand that @RISK and Monte Carlo simulation are designed to be an improvement on single-point estimates.  In practice, however, I often see people using @RISK as a forecasting tool to get yet another single-point estimate, such as the 90th percentile, without putting it into the context of the potential range of outcomes.

This is probably the difference between a forecasting and a decision-making.  The former tends to focus on historical or observed trends and developing specific scenarios (e.g. best, most likely, worse) based on expert opinion, while the latter is concerned with confidence ranges and likelihood.

Indeed, it’s not until you add probability, as with @RISK, that you start to measure the quality of your forecasts (i.e. your confidence level) and calculate the margin of error – something that’s crucial in all walks of life!

In my opinion, therefore, @RISK is much more of a decision-making tool than a forecasting tool.  Both involve trying to predict the future but the addition of probability gives decision-makers vital insight to a problem. 

Don’t you just love semantics!

Ian Wallace, ACMA
Palisade Training Team

A key application of @RISK is Six Sigma and quality analysis. This model demonstrates how @RISK can be used for a DOE analysis of a welding project.

Suppose you are analyzing a metallic burst cup manufactured by welding a disk onto a ring. You need to make sure weld strength is within safety limits. The model relates the weld strength to process and design factors, models the variation for each factor, and forecasts the product performance in relation to the engineering specifications. @RISK is used to model the variation in each factor, and simulate different outcomes for weld strength. Output includes After Six Sigma statistics for Cpk-Upper, Cpk-Lower, Cpk, and PPM Defects (or DPM). Standard @RISK statistical analysis functions (like RiskMean) are also used.

» Download the example: SixSigmaDOE.xls

On Thursday, August 19, 2010, David Roy will present a free live webcast entitled. "Assessing Your New Product, Process or Service Introduction Methodology: Is Yours Premier? Does it Enable Six Sigma Performance? "

As companies make changes to or introduce new Products, Processes or Services, we observe a wide spectrum of methodology; from well defined process with trained resources, effective tools and excellent results - to no process, ad hoc application of tools, and frequent cycles of “Launch and Learn.”

Where does your methodology rank?

In this free live webcast, we will provide a framework for assessing the Process, People, Tools and Results for premier attributes in the New Product, Process, Services Introduction Methodology.

» Register now (FREE)
» View archived webcasts

In @RISK (risk analysis software using Monte Carlo simulation), you can easily overlay two graphs directly from any graph window.  Just click on a button to identify the variables to overlay, and you’re done. Alternatively, you can drag thumbnails of different simulated results onto the same graph to create overlays. These overlays in the risk analysis models are useful for comparing different strategies, showing trends over time, and more.

A quick video showing how to do this:



» View short videos on recently added @RISK features

@RISK's use of Monte Carlo simulation allows for powerful features, like RiskSimtable.

The RiskSimtable feature can be used to run multiple simulations to test the sensitivity of the risk analysis model, for example to changes in the parameters of a distribution. This model is of a business with a base case expected revenue of 100 and cost of 80, giving a profit of 20.

The risk model assumes that the revenue and cost distributions are determined from a mean and standard deviation. The RiskSimtable feature is used to test the sensitivity of the distribution of profit to changes in the standard deviation of the revenues. Three values are tested of which the first is our original @RISK model. The number of simulations is therefore set at 3. A RiskSimtable can be set up either by directly typing in the required format, or by inserting it as for other Excel functions via the Insert Function menu option. The model also uses some @RISK statistical analysis functions to report the probability for each simulation that the profit exceeds 50.

» Download the example: BasicBusiness.Simtable.xls

Monte Carlo simulation is a very powerful tool for modeling uncertainty. But perhaps the most critical step in any simulation analysis is the meaningful presentation of results to others. Decision makers won’t act on the results of a simulation if they don’t understand what they are seeing. Graphs are the most powerful way to communicate these important insights.

There are lots of ways to make graphs from the data generated by Monte Carlo simulations. But what is the easiest? Microsoft Excel statistics offers its own graphing engine, but you have to tell it which data to use for what. 

@RISK comes with a powerful graphing engine built-in, and you can create meaningful graphs just by dragging and clicking things with your mouse. In this three-part series, we’ll cover the most common ways to get valuable graphical results in @RISK without ever touching your keyboard.

First off, @RISK automatically generates thumbnail graphs of input variables and output results during a simulation. These are accessible in the @RISK Model window (for inputs) and @RISK Results window (for outputs). You can expand any small thumbnail graph from these windows to full size just by dragging it off the window and onto the spreadsheet. 

Here is a quick video showing how easy it is to do this:



» View short videos on recently added @RISK features

@RISK, Palisade's risk analysis software using Monte Carlo simulation, has many features that help you create powerful models for decision making under uncertainty.

This model demonstrates the use of the alternate, percentile parameter formulation. In this case we assume that we have decided to use a Normal distribution to represent the arrival time of someone at work. The use of traditional parameters would require knowledge of the standard deviation of the arrival time, which may be hard to estimate. The use of the alternative parameter formulation allows data to be estimated by others in a more natural way. In the first case, the traditional parameters are used (mean and standard deviation). In the second case, the mean is still used, and the P90 is used in place of the standard deviation, i.e. the time before which the person arrives in 90% of cases. In the second case, the P10 and the P90 is used in place of the standard deviation i.e. the time before which the person arrives in 10% of cases, and in 90% of cases respectively.

» Download the example: AltPars.ArrivalTime.xls

One of the side-effects of the recession appeared to be a reduction in the demand for electricity as businesses and consumers alike looked to make savings on their outgoings. However, economic recovery seems to render this trend as temporary, meaning that the global need to tackle energy-consumption is as pressing as ever.

BC Hydro, Canada's third largest electrical utility provides an interesting case study in how to  ascertain the most effective ways to tackle the gap between supply and demand of electricity in British Columbia. Trends such as an expanding population growth, and the increase in energy usage per customer, have led to a rise in the demand for electricity across the region. By legislation, BC Hydro must aim to meet these energy needs through implementing cost-effective energy conservation approaches before it can turn to increasing the supply. 

The company has set itself one of the most aggressive targets in North America, with a plan to meet almost 75 percent of its incremental load through Demand Side Management (DSM) over the next 20 years. DSM projects include compact fluorescent light promotions; subsidies for energy efficient appliances; variable speed motor promotions (for home furnaces); and promotional activity aimed at motivating customers to use less energy.

BC Hydro uses @RISK risk analysis software to measure the uncertainty around its energy conservation efforts, both at the project stage and at a higher portfolio level. Around 60 projects were analysed on a case-by-case basis, and a probability distribution around the forecast outcome was developed. @RISK helps BC Hydro to capture the level of uncertainty of the estimated savings for each individual DSM venture.

In recognition that projects do not operate in isolation, BC Hydro also uses @RISK to explore the interrelationships between key uncertainties: the participation and savings per participant, and the participation across projects. The analysis showed that if a 'conservation culture' was developed in the province, it would result in an increase in energy savings across all programmes. However, it also illustrated that, if this culture failed to materialise, the performance of all programmes will be dragged down.

Exploring uncertainty using @RISK allowed BC Hydro to find the best balance between the uncertainties of supply side resources and those of relying heavily on energy conservation. Employing Decision Making Under Uncertainty helps BC Hydro to meet both financial risk analysis and environmental risk analysis goals. As a result, it expects to meet the majority of its incremental load growth through conservation measures.

» Read the BC Hydro case study

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

This financial risk analysis example demonstrates the use of the Corrmat function to correlate multiple @RISK distributions. The distributions are correlated using a matrix of coefficients that specify the relationship between each pair of functions. The coefficients must be between -1 and +1, with a value of +1 indicating a perfect correlation, 0 indicating no correlation, and -1 indicating a perfect negative correlation. In this example three variables, the current US interest rate, the Pound/$ exchange rate, and the Euro/$ exchange rate, are correlated using a 3x3 matrix. Correlation matrix inconsistency occurs when a matrix that is mathematically impossible to realize is entered. If this happens, @RISK will try to adjust your matrix. However, you may have some correlation coefficients that you do not wish to be adjusted, while others are more or less free to be changed. This information may be entered using an adjustment weight matrix. 

» Download the example:
   CorrmatWithAdjustmentWeightMatrix.xls
» See more Financial Risk Analysis models here
 » Case Study: FiduciaryVest uses @RISK's correlation
    matrices in asset allocation modeling

Another great way to collaborate with others is through the @RISK Library. This is a SQL-based database that lets you store customized @RISK functions with the specific parameters that you need.  Then others can pull the same @RISK function for their risk analysis models, directly from the standard Define Distribution window. This way you can be sure everyone is using consistent parameters in the statistical analysis.

You can also store simulation results in the @RISK Library. This is a great tool for auditing simulations to see exactly what happened, what changed, and what affected the outcome. Furthermore, these simulation results can be used as inputs for other simulations.

A quick video on the @RISK Library:



» View short videos on recently added @RISK features

@RISK, risk analysis software using Monte Carlo simulation, has many powerful features that help you create powerful models for decision making under uncertainty.

For example, you can use the RiskTheo function in @RISK to determine the parameters of a discrete distribution based on a continuous one. In this example, the RiskTheo functions of @RISK work out the P10, P50, and P90 percentiles of a continuous distribution (in this case the LogNormal), and the Mean and Standard Deviation of a RiskDiscrete distribution which has these X-values and some assumed probabilities (or weights). It then uses Excel's Solver to work out the probabilities required so that the discrete distribution based on these percentiles and probabilities would have the same mean and standard deviation as the continuous distribution.

» Download the example: CtsToDiscrete.xls
» See "Uses of the RiskTheo functions in
   @RISK to match distributions"
 » See "DMAIC Failure Rate using RISKTheo" for a
    Six Sigma application of the RISKTheo function