Base Case and Sensitivity Analysis – “Waterfall and Tornadoes”

Base Case and Sensitivity Analysis – “Waterfall and Tornadoes”

Structure Deterministic Analysis Probabilistic Analysis Appraisal Decision InitialSituation Iteration DeterministicModel DecisionTree Value ofInformation Strategy Table A B C 1 2 3 4 5 InfluenceDiagram DeterministicSensitivity ProbabilityDistributions Decision Quality We create a model to evaluate alternatives and test the importance of uncertainties. 2.05-I • Deterministic & Sensitivity Analysis

We will discuss using spreadsheets for deterministic (base case) analysis and sensitivity analysis. • Base Case Analysis and Waterfall Diagrams • Sensitivity Analysis and Tornado Diagrams • Using Sensitivity Results for Insight 2.05-I • Deterministic & Sensitivity Analysis

¥ 1 2 3 4 A B C Base Case Inputs Base Case Outputs 1 Spreadsheet Model 2 3 4 5 Base case* analysis gives an initial comparison of each alternative. * All variables set at their “base case” values The important question is “Why is #3 best?” 2.05-I • Deterministic & Sensitivity Analysis

30 Aggressive Investment Run Out 20 10 Moderate Investment 0 AnnualCash Flow ($ millions) –10 –20 –30 –40 –50 –60 1 2 3 4 5 6 7 8 9 10 Year Another base case output—the cash flow plot—illustrates cash needs and yields from each alternative under consideration. The plot shows how moderate or aggressive investment extends mature product life, with economic break-even in 3–5 years. 2.05-I • Deterministic & Sensitivity Analysis

Waterfall of Value Sources of Value A B C 1 Base Case Spreadsheet Model 2 3 Tornado Diagram Uncertainty Range 4 5 Sources of Risk Initial evaluation with the model also yields important insights into sources of value and risk. 2.05-I • Deterministic & Sensitivity Analysis

71 “Run Out” Moderate 291 Investment Aggressive 280 Investment Base case evaluation shows the net value of each alternative, in this case dealing with mature products investment alternatives. Net Present Value ($ millions) 0 50 100 150 200 250 300 Why is the value of the aggressive investment less than the moderate investment? 2.05-I • Deterministic & Sensitivity Analysis

0 200 400 600 800 1,000 Product Revenues Halo Revenues Promotion SF Product Revenues Halo Revenues Key Pr. Added Value SF Reduced Value This horizontal “waterfall chart” shows incremental sources of value and cost for each strategy. Net Present Value ($ millions) 71 Run Out Moderate 291 Investment Aggressive 280 Investment Although typically drawn vertically, this orientation simplifies labeling. 2.05-I • Deterministic & Sensitivity Analysis

Value from Future Sale Contributions to Profit New Products Market Growth Net Present Value Fixed Costs Current Contribution Base Case NPV Required Investment The waterfall chart can “break out” the base case value added or lost from each major source. Business context: Purchase an existing business, add value, then sell. 0 2.05-I • Deterministic & Sensitivity Analysis

Waterfall of Value Service Base Case NPV Net Present Value FixedOverhead 0 Licensing Corresponding Influence Diagram NPV Fixed Overhead Licensing Contribution Service Contribution Required Investment Licensing Revenues Installation Costs Commission Costs Consulting Revenues The elements of a waterfall correspond to the “value centers” of the influence diagram. It’s best to “sketch” the waterfall in parallel with developing the influence diagram. 2.05-I • Deterministic & Sensitivity Analysis

The choice of “value centers” depends on the business context and the strategic decisions. Retail chain • Existing stores • New locations Biotech product company • Product #1 • Product #2 • Upcoming product launch • Research pipeline • Global expansion opportunity • North American contribution • Europe • Asia/Pacific • Other regions • Business portfolio • Business unit, group, or sector #1 • Business unit, group, or sector #2 Think of value centers as primary market segments or business centers or components of value, not cost centers. 2.05-I • Deterministic & Sensitivity Analysis

Competitor Exits Increase Fixed Overhead Data Revisions Revised Valuation May 27 Revised Valuation June 15 The waterfall is a good way to reconcile changes in a strategy’s valuation over time. Net Present Value 0 InitialValuation 2.05-I • Deterministic & Sensitivity Analysis

Value from Future Sale Contributions to Profit 1 Product C 3 Product B 4 2 Overhead Base Case Value Product A 4 5 Required Investment Create the waterfall by working “backwards,” incrementally turning off sources of value. NPV 0 Step 1 Record base case NPV. Step 2 Change future sales value to zero; record NPV. Step 3 Set fixed and overhead costs to zero; record NPV. Step 4 Switch off product contributions one by one; record each NPV. Step 5 With all products switched off, NPV should equal the required investment. 2.05-I • Deterministic & Sensitivity Analysis

a story can tell sometimes. powerful phrases Short One note about style: use short phrases on a “vertical bar” waterfall. Net Present Value 0 You might be tempted to rotate text to add more words. 2.05-I • Deterministic & Sensitivity Analysis

happens be audience Sideways text is not effective, to your lying down! unless Vertical text is OK in books and handouts, but it’s not effective in presentations. 2.05-I • Deterministic & Sensitivity Analysis

The horizontal waterfall format accommodates many bars and lots of text. Net Present Value Look at all arrows & neat stuff you can write on this waterfall chart now that it’s oriented the “other” way. You run the risk of boring your audience of course, but if you write like Charles Dickens, you might consider this form of waterfall. You can also write in the arrow, or use text to help emphasize direction: Left! Right! And your audience doesn’t have to lie down! In here! 2.05-I • Deterministic & Sensitivity Analysis

Uncertainties that Increase Value Uncertainties that Decrease Value The waterfall shows only base case values—it is equally important to understand which factors drive value higher or lower. Value from Future Sale Contributions to Profit Product C NPV Product B Overhead 0 Base Case Value Product A RequiredInvestment 2.05-I • Deterministic & Sensitivity Analysis

What do analysts in companies typically do when they encounter uncertainties? • Ignore them • Run three cases: base case, best case, worst case • How good is “best?” • How bad is “worst?” • Make an “assumption” and proceed. • The “easy way out” • Are these justified? A “tornado” analysis provides the justification you need for leaving variables at their base values versus including their uncertainty explicitly in your analysis. 2.05-I • Deterministic & Sensitivity Analysis

Tornado Many Uncertainties Primary Sources of Risk Sensitivity (“tornado”) analysis identifies the primary sources of risk, which are then analyzed using probabilistic analysis. Structure Deterministic Analysis Probabilistic Analysis Appraisal Decision Initial Situation Iteration We also typically reassess ranges for the top bars in the tornado. 2.05-I • Deterministic & Sensitivity Analysis

Low Case 8% High Case 28% 43 OUTPUT 44 NPV ($ MM) 75 -6 134 Swing 140 Single-variable sensitivity analysis determines the change in model output when one input changes. Spreadsheet Model A B 1 INPUT Base Case 2 Market Share 20% 2.05-I • Deterministic & Sensitivity Analysis

Net Present Value ($ millions) -50 0 50 100 150 200 Price per dose $1.50 $6.00 Market Share 8% 28% Doses per Day 1 3 Prevalence Rate 1.5% 3.0% Compliance 30.0% 70.0% Treatment Rate 30.0% 60.0% Development Cost 140.0 60.0 COGS (%Rev) 30% 15% Uptake curve length 7 3 Annual Price change -2% 1% Base Value = 75 A “tornado diagram” graphically depicts the variables’ contributions to uncertainty in NPV. 2.05-I • Deterministic & Sensitivity Analysis

Base Case NPV = $75 MM The tornado diagram is generated by reevaluating NPV using low and high values for each variable separately. 2.05-I • Deterministic & Sensitivity Analysis

Range of Output The output variable may be highly sensitive to small changes in an input. Range of Output Range of Input { Uncertain inputs can have a large effect on the output for two reasons. The input may be highly uncertain. Range of Input 2.05-I • Deterministic & Sensitivity Analysis

Three of the variables are “coupled” or “dependent.” They tend to vary together. Price/Dosing Scenario “tid”* “bid”* “Once a day” 3 30% $1.50 2 55% $3.50 1 70% $6.00 Number of Doses Compliance Price per Dose • We should determine the sensitivity of NPV to joint changes in these variables. • Note: These scenarios are obtained by assessing a combination of events that would lead to a low (10th percentile) and a high (90th percentile) outlook. *tid: ter in die – three times a day bid: bis in die – twice a day 2.05-I • Deterministic & Sensitivity Analysis

Variable Values Results Variable Base Case Low High “Low” “High” Swing Doses per Day Compliance Price per Dose 2 55% $ 3.50 3 30% $ 1.50 1 70% $ 6.00 - 13 87 100 Base Case NPV = $75 MM Joint sensitivity analysis determines the sensitivity of NPV to simultaneous changes in coupled* variables * Coupled, or dependent, uncertainties are likely to vary together—either positively or negatively correlated. 2.05-I • Deterministic & Sensitivity Analysis

Net Present Value ($ millions) -50 0 50 100 150 200 Market Share 8% 28% Prevalence Rate 1.5% 3.0% Dose/Pricing Scenario TID BID Sensitive Treatment Rate 30.0% 60.0% Insensitive Development Cost 140.0 60.0 COGS (%Rev) 30% 15% Uptake curve length 7 3 Annual Price change -2% 1% Base Value = 75 The updated tornado diagram focuses our attention on the most significant sources of risk. We suggest re-assessing the uncertainty in the most sensitive uncertain variables and including them in probabilistic analysis. 2.05-I • Deterministic & Sensitivity Analysis

Uncertainty Individual Swing Contribution to Total Uncertainty* Cumulative Contribution Variance (swing)2 Market Share 140 18225 38% 38% Prevalence Rate 101 10201 20% 58% Dose/Pricing Scen 100 10000 19% 77% Treatment Rate 79 6241 12% 89% Dev Costs 48 2304 4% 93% COGS 37 1369 3% 96% Uptake Curve 35 1225 2% 98% Price Change 31 961 2% 100% TOTAL 50526 Uncertainties with the greatest “swing” are crucial to capturing the uncertainty in overall value. * The uncertainty in the model output (e.g., NPV) is proportional to the sum of the squares of individual input variable swings, if the variables are independent. 2.05-I • Deterministic & Sensitivity Analysis

Are these really the most sensitive, or are the ranges too wide? Too symmetric? Are experts biased? Cost: High Low Low cost, high NPV! “Floating” bar— are the values right? Are these really low/zero swings, or is there a mistake? The tornado diagram helps identify potential problems with the assessments and the model. Net Present Value Does the base case match the waterfall (and spreadsheet) base case? 2.05-I • Deterministic & Sensitivity Analysis

Waterfall and tornado analyses provide tools to “mine” for NPV insights. • • What are the primary sources of value? • What drives the uncertainty in net present value? • What uncertainties have the greatest effect on decisions? • Which variables are dependent? • Which risk factors might we be able to control? • Is any strategy clearly most attractive?- How can we revise the strategy to capture more value?- How can we help focus the attention of company management? • Where should the analysis be refined? 2.05-I • Deterministic & Sensitivity Analysis

Appendix 2.05-I • Deterministic & Sensitivity Analysis

Uncertainties Market Share Price/dosing scenario Prevalence There is another form of sensitivity—decision sensitivity; it identifies uncertainties that affect decision-making. Example: A Drug Development investment decision Invest Don’t Invest $ 0 How important is each uncertainty—enough to change our decision? 2.05-I • Deterministic & Sensitivity Analysis

Best Decision, Given: Low Input High Input The tornado identifies uncertainties that affect our choice between investing or not; this is called “decision sensitivity.” Net Present Value 50 100 150 0 Variable Don’t Invest 8% 28% Market Share Invest Invest Prevalence Rate 1.5% 3.0% Don’t Invest Dose/Pricing Scan TID BID Invest Invest 30.0% 60.0% Treatment Rate Invest Invest Development Cost 140 60 Invest Invest 30% COGS (%Rev) 15% Base Value: 75 We could change our decision based on the outcome Market Share and Dose/Pricing Scenario alone. These two are “decision sensitive.” 2.05-I • Deterministic & Sensitivity Analysis

4 Clear Values and Trade-offs 3 Meaningful, Reliable Information 2 Creative, Doable Alternatives 5 Logically Correct Reasoning DecisionQuality 0% 100% 1 Appropriate Frame 6 Commitment to Action Judge the quality of available information before plunging into evaluation. Meaningful, Reliable Information: 0% “Blissful ignorance” • Don’t know how much we know • Ignoring uncertainty • Ignoring “intangibles” 50% “Informed about uncertainty” • Know information gaps • Know what’s important • Quantified uncertainty • Interdependence not explored 100% “Knowledgeable and ready” • Information correct and explicit • Important gaps filled • Know limits of knowledge 2.05-I • Deterministic & Sensitivity Analysis

Base Case and Sensitivity Analysis – “Waterfall and Tornadoes”