Decision Evaluation

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

Abbott suggests a compromise: streamline your plot by masking (Abbott says "removing") fields that contribute to the robustness of the analysis but involve statistical twists and turns that are distracting to decision makers who may not be fascinated with technique and just want to see how the story turns out. This, he explains, allows you to work from a model both you and the decision makers can believe in.

Your thoughts? 

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

With the New York State primaries coming up September 14 and the general election on November 2, I predict that as soon as summer turns the corner into September, we'll start hearing lots and lots about polls that predict election outcomes.  To find out if there was any early discussion of polls, polling, and outcomes, I returned to my favorite election forecast site from the 2008 presidential elections, FiveThirtyEight: Politics Done Right.

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

No matter how good your model, it's still You Play, You Pay.  And Rowland's disclaimer echoed a comment an influential racing veterinarian made to me: "Never invest in something that eats while you sleep."     

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

"Everything has to run on the principle of profit.  We'll never let creative decisions rule our business decisions.  If it doesn't fit the model, it doesn't get done."  That doesn't mean, he has explained, that if he really likes a project, he and Wilson can't juggle the variables to make the film project fit the model.  They change the parameters to reveal the path to profit.  And profit he has--the estimated assets of Relativity are about $2 billion.  

So Kavanaugh qualifies as a mogul, a math-for-movies mogul.  When the spotlight falls on him, Monte Carlo simulation isn't far out of it.

  

@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

We are pleased to welcome back to my blog consultant and trainer David Roy from Six Sigma Professionals, Inc.

 

 

Oops! Didn’t see that coming! Part 3
 

 

As a continuation from the June blog, we are now covering the “Conceptualize” phase of the ICOV framework of a rigorous new design process as explained in “Services Design for Six Sigma – A Roadmap for Excellence”.

 

This phase is important because it conceives, evaluates and selects good design solutions through robust process methodology which ensures alignment to the customer and the business needs.

 

Many design solutions skip this phase and become typically named as “Launch and Learn”.

 

The Conceptualize phase consists of two stages and associated Tollgates to validate successful completion of the requirements. 

 

The Concept Development stage involves translating Customer requirements into solution free Functional requirements, developing the System Level Conceptual Design, generating Concepts for required functions, Concept selection and translation of the Functional Requirements to Design Parameters.

An example of a Functional Requirement for a Customer Want of “Speedy Service” could be “Speed of Service” and a Design Parameter could be “Waiting Time”

 

Tollgate 3 Exit Criteria:

• Assessment that the Conceptual Development Plan & Cost will satisfy the customer base
• A Decision the design represents an economic opportunity (if appropriate)
• Verification adequate funding will be available to perform Preliminary Design
• Identification of the Tollgate Keeper & the appropriate staff
• An action plan to continue flow-down of the design Functional Requirements

 

The Preliminary Design stage involves creating the design documentation and configuration management, performing design analysis and testing, translating the Design Parameters into Process Variables and formulating the Production strategy.

An example of further mapping the Design Parameter of “Waiting Time” to a Process Variable could be “Number of Phone Lines”

 

Tollgate 4 Exit Criteria:

• Acceptance of the selected Solution/Design
• Agreement the Design is likely to satisfy all Design Requirements
• Agreement to proceed with the next stage of the selected Solution/Design
• An action plan to finish the flow-down of the design Functional Requirements to design parameters and process variables

 

Formal tools which can be used in this phase are QFD, TRIZ/Axiomatic design, Measurement System Analysis (MSA), Failure Mode effect Analysis (FMEA), Design scorecard, Process mapping, Process management, Pugh Concept Selection, Robust Design, Design Scorecards, Design for X and Design reviews.

 

The next and final blog will cover the Optimize and Validate phases.

 

BIO:

 

David Roy is an integral part of the Six Sigma community. He taught GE’s Jack Welch and entire staff Six Sigma, as well as served as Senior Vice President of Textron Six Sigma. He is a Certified GE Master Black Belt, was instrumental in developing GE’s DMADV (DFSS) methodology, and has taught 3 waves of DFSS Black Belts. David holds a BS in Mechanical Engineering from The University of New Hampshire. He is also the co-author “Services Design for Six Sigma – A Roadmap for Excellence”

 

 » Part 1
 » Part 2

If you missed Palisade trainer Rishi Prabhakar's webcast "Customised Solutions Using @RISK and VBA for Excel," you can still view it in our archive.

The hour-long presentation explores the use of VBA for Microsoft Excel to control @RISK functionality, to simplify the process of risk analysis for resource-strapped businesses. Rishi explains the advantages (and limitations) of macro control for modelling and running simulations.

Simple examples are worked through to show the XDK (@RISK’s automation library) in action, from generic examples to a cost estimation model. This addresses elements of model construction, various simulation settings and finally reporting. The emphasis is on exposing the viewer to the various possibilities the XDK lends to the user rather than an in-depth VBA for Excel coding session.

Rishi Prabhakar holds a BSc in Mathematics from the University of Technology, Sydney Australia. Rishi has experience in the resources, infrastructure and primary industries, telecommunications, scientific research, banking and finance with an emphasis on operational risk.

With technical skills in the areas of modelling, simulation, statistical analysis, cost estimation, time series forecasting, customised solutions utilising VBA for Excel, and extreme value theory, Rishi has provided training and consulting services in risk and decision analysis for Palisade’s Asia Pacific office since 2005.


» Customised Solutions Using @RISK and VBA for Excel
» Webcast archive

The UK finally seems to be heading into summer after an unusually long and cold winter.  However, despite the amount of rain that falls on our 'green and pleasant land' (to the extent of major flooding on occasions), one of the anomalies of the UK weather system is that any prolonged warm period seems to be accompanied by the underlying threat of a hosepipe ban.

This is in stark contrast to many regions around the world that, despite seeing far less precipitation, manage a robust water supply.  Abu Dhabi for example has no rain for several months of the year, and relies almost completely on desalinated seawater for its potable water requirements.  The desalination process is challenging in terms of operation, costs, and environmental impact.  Whilst over-production capacity is expensive, at the other end of the scale it is essential that Abu Dhabi has sufficient water production capacity to support the Abu Dhabi government development plan (Abu Dhabi Plan 2030). 

This plan means that the Abu Dhabi Water & Electricity Company (ADWEC) is required by the Regulation and Supervision Bureau (RSB), the regulatory body of Abu Dhabi, to use a risk-based methodology to assess the water demand and required capacity.  As a result ADWEC uses @RISK risk analysis software to help it to forecast as accurately as possible the demand for water and electricity across the Emirate in order to plan for the optimum expansion as well as the efficient and effective use of water production plants.

@RISK enables ADWEC to model all feasible uncertainties in the variables that determine the quantity of water required over specific timescales, such as per capita water consumption rates and the rate of population growth.  The variables input into the @RISK risk simulation software model are based on the water demand categories such as domestic, agricultural and industrial. Factors with inherent uncertainties that affect the demand forecast outcome and must be modelled include: seasonal variation, distillers' unplanned outages, water losses, population growth rates and demand for housing.

By undertaking risk analysis of the variables involved in assessing demand and supply, ADWEC minimises the potential for water production capacity to be over or under deployed.  As a result of using @RISK to assist with its forecasting, planning and management strategies, ADWEC has been able to consistently meet with almost complete accuracy the Abu Dhabi Emirate water demand forecasts.

A useful lesson...

» Read the full ADWEC case study

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

Modeling Uncertain Number of Events, Each with Uncertain Parameters
@RISK (risk analysis software using Monte Carlo simulation) is widely used in insurance and reinsurance for premium pricing and loss reserves modeling. A 2006 survey identified @RISK as the third most widely-used software by actuaries, after Microsoft Office and in-house actuarial tools.

@RISK's RiskCompound function allows for the sampling of frequency-severity distributions. This is often required in insurance modelling, as well as in some operations management situations. For example, to determine the total insurance claims payout, one must account for the uncertainty in both the total number of claims (frequency) and the dollar amount of each claim made (severity). 

A powerful feature of the function is that the argument that corresponds to the severity may be a reference to a cell containing a formula (rather than just a single distribution function). 

For example, one could use the function in the form RiskCompound(RiskPoisson(5), A10). The Poisson distribution would describe the frequency (occurrence) of events (e.g. an individual sample may determine that three events occur), and cell A10 would contain a formula that is separately evaluated for each of these three events (before returning the sum of these three as the sampled value of RiskCompound). 

A simple example could be A10 = RiskLognorm(10000,1000)/(1.1^RiskWeibull(2,1)), with the Weibull distribution representing the time to settlement of an insurance claim, which is used to discount the basic claim value sampled from the Lognormal distribution of severity. For example, once a claim is filed for a nominal amount, the actual payment may be delayed due to court actions or disputes, which may reduce the cost of the claim to the insurer.

» Download the model: RiskCompoundCellReferencing.xls

@RISK risk modeling software is used for a wide variety of applications in financial risk analysis forecasting, investments, and banking. This model is one example of how @RISK can help in risk analysis decision making.

Models of the prices of assets (stocks, property, commodities) very often assume a random walk over time, in which the periodic price changes are random, and in the simplest models are independent of each other. The future price level of the asset may result in some contract or payoff becoming valuable, such as in the case of financial market options. In these cases, the value of the contract (contingent payment or option) is calculated as the average discounted value of the future payoff. In the special case of European options on a traded underlying asset, the value calculated from the simulation may be compared with mathematical formulas that analytically provide the valuation, such as the Black-Scholes equation. In many more complex cases, the pertinent analytic formulas may be unknown or very complex to derive, and one may wish to rely on simulation techniques. This particular model compares the average simulated payoff for European Call and Put options with the Black-Scholes valuation.

» Download the example: AssetPrices.Options.BS.Multi.xls

@RISK risk modeling software is used for a wide variety of applications in financial risk analysis forecasting, investments, and banking. Below is an application of a discounted cash flow analysis.

Discounted cash flow (DCF) calculations are a frequent example of the use of @RISK. In the example model, the sources of risk are the revenue growth rate and the variable costs as a percentage of sales. After taking into account the assumed investment, and applying a discount factor, the DCF is derived. Following the simulation, the average (mean) of the DCF is known as the net present value (NPV).

In this example, the results show that the average DCF is positive (about 40), whereas the probability of a negative DCF is about 15%. The decision as to whether to proceed or not with this project will therefore depend on the risk perspective or tolerance of the decision-maker.

This example has also been extended to calculate the distribution of bonus payments on the assumption that a bonus is paid whenever the net DCF is larger than a fixed amount (such as 50). It also uses some of the @RISK Statistics functions RiskMean, RiskTarget, and RiskTargetD to work out the average net DCF, the probability that the net DCF is negative and the probability that a bonus is paid.

» Example model: CashFlow.xls

Guest blogger David Roy Six Sigma Professionals, Inc., and taught Jack Welch and his entire staff their Six Sigma Green Belt training. Dave also has a quick survey for your input on structuring DFSS training. brings us the second installment of his four-part blog. Dave comes to us from SSPI,

 

--Steve Hunt

 
Oops! Didn’t see that coming! Part 2

We’d like to ask for your guidance by completing a short marketing survey to help SSPI structure our training in a way that is most useful to our community. This 8 question survey should take less than 5 minutes, and is anonymous. Your opinions are greatly appreciated.

As a continuation from the May blog, we are now covering the “Identify” phase of the ICOV framework of a rigorous new design process.

This phase is important because it establishes the framework for the concept, establishes the level of rigor required for the project management process, estimates the development cost, collects the Customer and Business requirements and the criteria for success.

 

The level of project management needs to be flexible and scalable depending on the Level of Effort (cost) and the Level of Innovation (risk) of the new concept.

 

Surely a project that will take a month to develop and has been done elsewhere requires less rigor that a concept that will take 3 years to develop and represents a brand new invention which has never been done before.

 

The I phase consists of two Tollgates during which an objective steering committee will decide whether to refine the work in the current phase, proceed or cancel the project. 

 

Tollgate 1 Exit Criteria are:

o     Decision To Collect The Voice Of The Customer To Define Customer Needs, Wants And Delights

o     Verification adequate funding is available to define Customer Needs

o     Identification of the Tollgate Keepers1 leader & the appropriate staff

 

Tollgate 2 Exit Criteria is successful demonstration of:

o     Assessment of market opportunity

o     Command a reasonable price or be affordable

o     Commitment to development of the Conceptual Designs

o     Verification adequate funding is available to develop the Conceptual Design

o     Identification of the Gate Keepers leader (gate approver) & the appropriate staff

o     Continue flow down of CTSs to Functional Requirements

 

Formal tools which can be used in this phase are Market/Customer research tools, Product Roadmaps, Process Roadmaps, Technology Roadmaps, Multigenerational plans, Quality Functional Deployment (House of Quality).

 

Market/Customer research tools may include Customer Relationship Management (CRM) Data, Surveys, Focus Groups, Conjoint Analysis and Kano Model Analysis.

 

The next blog will cover the Conceptualize phase

 

 

 

BIO:

 

David Roy is an integral part of the Six Sigma community. He taught GE’s Jack Welch and entire staff Six Sigma, as well as served as Senior Vice President of Textron Six Sigma. He is a Certified GE Master Black Belt, was instrumental in developing GE’s DMADV (DFSS) methodology, and has taught 3 waves of DFSS Black Belts. Dave’s experience includes Product and Transactional so his examples are of interest to all. David holds an BS in Mechanical Engineering from The University of New Hampshire. He is also the co-author “Services Design for Six Sigma – A Roadmap for Excellence”

» Part 1