Overview of The AIE Method Hubbard Decision Research

Overview of The AIE Method Hubbard Decision Research 2 South 410 Canterbury Ct Glen Ellyn, Illinois 60137 www. hubbardresearch. com © Hubbard Decision Research, 2013

Proposed Thursday Agenda Time Topic 9: 30 AM Review of AIE Concepts 10: 15 AM Basic Structure of the AIE Spreadsheets 11: 00 AM Break 11: 15 Information Values and Simulations Noon Lunch 1: 00 PM Define and Outline the Model: Identify major components of costs and benefits of Service Now. 2: 00 PM Basic Financial Metrics: NPV and IRR 2: 30 PM Break 2: 45 PM Student-Run Facilitation 4: 30 PM Adjourn © Hubbard Decision Research, 2013 1

Proposed Friday Agenda Time Topic 9: 30 AM Student-Run Facilitation (continued) 11: 00 AM Break 11: 15 AM Student-Run Facilitation (continued) Noon Lunch 1: 00 PM Modeling Hints and Quality Checks 2: 00 PM How to Answer Questions: Respond to common objections and skepticism 2: 45 AM Break 3: 00 PM Planning the rest of the effort. 4: 30 PM Adjourn © Hubbard Decision Research, 2013 2

Define the Decision and Identify Relevant Variables. Set up the “Business Case” for the decision, using these variables – Model The Current State of Uncertainty – Initially use calibrated estimates and then actual measurements Calibration Training Making the Best Bet Compute the value of additional Information – Determine what to measure and how much effort to spend on measuring it. No Yes Is there significant value to more information? Measure where the information value is high – Reduce uncertainty using any of the methods Optimize Decision – Use the quantified Risk/Return boundary of the Decision makers to determine which decision is preferred. © Hubbard Decision Research, 2013 3

Monte Carlo: How to Model Uncertainty in Decisions Reduction in Costs($MM) Interest or Discount Rate $20 $25 $30 $35 $40 4% 5% 6% 7% 8% Gains in Productivity Increase in Profits ($MM) 10% 15% 20% 15% 30% NPV $30 $40 $50 $60 $70 ? • Simply put, Monte Carlo Models approximate the probability of certain outcomes by running multiple trial runs, called simulations, using random variables. • In the oil industry there is a correlation between the use of quantitative risk analysis methods and financial performance – and the improvement started after using the quantitative methods. (F. Macmillan, 2000) • Data at NASA from over 100 space missions showed that Monte Carlo simulations beat other methods for estimating cost, schedule and risks (I published this in The Failure of Risk Management and OR/MS Today). $1 M $2 M $3 M $4 M $5 M © Hubbard Decision Research, 2013 4

Increasing Value & Cost of Info. Aim for this range EVPI Value or Cost $$$ EVI • EVPI – Expected Value of Perfect Information • ECI – Expected Cost of Information • EVI – Expected Value of Information ECI $0 Low certainty © Hubbard Decision Research, 2013 Perfect Information High certainty 5

The Measurement Inversion In a business case, the economic value of measuring a variable is usually inversely proportional to the measurement attention it typically gets. Lowest Information Value • Initial cost Measured • Long-term costs • Cost saving benefit other than labor productivity • Labor productivity • Revenue enhancement • Technology adoption rate Highest Information Value © Hubbard Decision Research, 2013 • Project completion Least Measured 6

Productive Assumptions About Measurement • • • Its been measured before You have more data than you think You need less data than you think “It’s amazing what you can see when you look” Yogi Berra © Hubbard Decision Research, 2013 7

Don’t Forget: There Are Many Measurement Methods • • Further decomposition Secondary research Small samples/Random surveys Controlled experiments Bayesian methods Forecasting and regression Any hybrids or variations on those above © Hubbard Decision Research, 2013 8

Measurement “Power Tools” You have an Excel spreadsheet tool that will help with a number of these empirical examples: “AIE Analyst Power Tools” Some relevant problems we can address include: • Estimating the mean and the median of a population with small samples (the average time spent per day reading email is between ___ and ____) • Estimating a population proportion (___% to ____% of customers prefer the new feature) • Estimating the size of a population (The number of unreported crimes are ___ to ___) • Comparing two populations (A test group and a control group in an experiment) © Hubbard Decision Research, 2013 9

Analyzing the Distribution • • How are you assessing the resulting histogram from a Monte Carlo simulation? Is this a “good” distribution or a “bad” one? How would you know? ROI = 0% “Expected” ROI Risk of Negative ROI -20% Probability of Positive ROI 0% 20% 40% 60% 80% 100% Return on Investment (ROI) © Hubbard Decision Research, 2013 10

Various Risks & Returns Low Expected Return High Risk -100% 0% 100% 200% Low Risk © Hubbard Decision Research, 2013 11

Quantifying Risk Aversion • • • The simplest element of Harry Markowitz’s Nobel Prize-winning method “Modern Portfolio Theory” is documenting how much risk an investor accepts for a given return. The “Investment Boundary” states how much risk an investor is willing to accept for a given return. For our purposes, we modified Markowitz’s approach a bit. Chance of a negative IRR 50% Region of Unacceptable Investments 40% Acceptable Risk/Return Boundary 30% Investment 20% Region of Acceptable Investments 10% 0% 0% © Hubbard Decision Research, 2013 50% 100% 150% 200% Expected IRR over 5 years 12

Model Building in the AIE Workbook for Risk-Return Analysis Hubbard Decision Research 2 South 410 Canterbury Ct Glen Ellyn, Illinois 60137 www. hubbardresearch. com © Hubbard Decision Research, 2013

Background and Scope • The RRA Workbook is the main tool used in AIE analysis of a risky decision. • The Workbook is an Excel file that contains a set of supporting macros. • The Workbook file acts as a template. – Begin modeling with a blank template. – Save various models with a different file name. © Hubbard Decision Research, 2013 14

RRA Workbook Sheets • The following sheets are present: – Inputs – Random. CF – Mean. CF – Info. CF – Risk Report – Investment Boundary – Hot Key Reference • Some sheets have a bigger role in model design, and others are primarily used during analysis steps. • Other worksheets are typically added to maintain a central location for data sources or other calculation tools created for the organization (such as portfolio classification tools, statistical analysis tools, etc. ). © Hubbard Decision Research, 2013 15

RRA Workbook: User Interaction with Sheets RRA Sheet Name Model Design Inputs Enter Variables, Formulas, and Constants, Setup Model Cash. Flow Enter Formulas None Random. CF Test Cash. Flow entries None Mean. CF None Info. CF None Risk. Report Test Inputs and Cash Flow Entries Examine Results of Simulations Investment Boundary None Express Risk Aversion Quantitatively Hot Key Reference © Hubbard Decision Research, 2013 Decision Analysis Launch Simulation, VIA, Adjust Variable Bounds, Change Constants, Formulas, and Setup Model. As a reference to available hotkeys for keyboard activation of the macro buttons at the top of the Inputs sheet 16

Inputs Sheet – Input Area • Variables, Constants and Formulas that define the model are placed on the rows of the Input Area. • Variables can be entered as a range and assigned a distribution type. © Hubbard Decision Research, 2013 17

Inputs Sheet – Input Area Macro Button Cluster • Create Transpose Reference – For designing a model. Creates a range of cells on a single row of the Cash. Flow sheet that reference a set of rows on the Inputs sheet. • Setup Monte Carlo Model – For designing and tweaking a model. Sets up all the sheets in the model after changes are made to either the Inputs or Cash. Flow sheet. • Clear VIA’s – Clears the results from prior VIA runs from columns L, M, N, and O. • Show Formula Text – Toggle to show the text of formulas in cells. • Run Monte Carlo – Launches the Monte Carlo Wizard. • Compute VIA’s – Calculates the Threshold, Threshold Probability, and EVPI of each variable with an assigned distribution. © Hubbard Decision Research, 2013 18

Inputs Sheet – Input Area • The Risk Free Rate expresses the cost of capital. • Do not delete the rows for these variables, the macros use them to navigate the page. Rows can be added and deleted after row 4 and above the row where the Risk Free Interest Rate is found. • The NPV and IRR Values are calculated on other sheets, and referenced on the Inputs sheet. © Hubbard Decision Research, 2013 19

Inputs Sheet – Input Area • The variables in your model are stacked vertically. Each variable such as “Employee Loaded Cost” has its own row. • The left six columns of the “Inputs” worksheet is the primary area for data input for your models. – Variable Name: This is where you enter the names of variables and the titles you might want to add to organize major parts of your spreadsheet. – Lower Bound, Formula & Best Estimate, and Upper Bound – Dist. Type – Notes about sources of the estimate or the formula used to compute the value. © Hubbard Decision Research, 2013 20

Inputs Sheet – Input Area • The rows in the Inputs sheet have three basic purposes. • Each use the columns in the input area differently. • A row may also be blank or contain only a title or heading for a section of the model. 21 Row Input Dist. Type Variable Values 1 -5 Constant Blank Represents the fixed value of a number Formula Blank Contains the formula for the cell (all calculations refer only to other Best estimate values) © Hubbard Decision Research, 2013 Description These are the uncertain variables in the model. The HDR tool uses 5 basic distribution types

Basic Distribution Types What each column contains Dist. Type Distributions* Upper & Lower Bound Best Estimate Normal distribution Represents the "90% confidence interval"; the absolute lower bound of a lognormal is always 0 Represents the absolute (100% certain) upper and lower bounds Test value only, not used in the 1 distribution calculations Lognormal distribution Uniform distribution Split Triangle distribution Represents the absolute (100% certain) upper and lower bounds Test value only, not used in the 2 distribution calculations Test value only, not used in the 3 distribution calculations Represents the median; the 4 point where there is equal chance of the quantity being higher or lower Binary distribution 35% 65% Not applicable; should be empty Represents the % chance of the event occurring 5 *A “ ” means a “hard” stop, an “ ” arrow means unbounded. © Hubbard Decision Research, 2013 22

Inputs Sheet – Analysis Area • Random Scenarios: This is the column that generates random numbers. • Mean: This column computes the mean based on the distribution type. • Scenario w/Information: This shows the “expected” result given perfect information about a particular variable or set of variables while the others are held at their mean. This is used in the information value calculation. • VIA Flag: The macro uses this column to “turn on” individual variables as the one being analyzed for information value. It can also be used manually. By setting it as a “ 1” during a Monte Carlo simulation of the NPV row in the same column, you can compute EOL given perfect information in those variables. © Hubbard Decision Research, 2013 23

Inputs Sheet – Analysis Area • EOL Notes & Calculations: Use this column to place results of selected Monte Carlos for EOL results or for calculations that use them. • Individual EVPI: This is where the EVPI for a single variable is computed by the “Compute VIA’s” button. • Individual Threshold: This is the threshold of that value – the point at which this variable would begin to make a difference in the decision. • Threshold Probability: This is the probability that a threshold in a range is “exceeded” – that is, the chance the value could be different enough to affect the decision. © Hubbard Decision Research, 2013 24

Inputs Sheet – Analysis Area • Opp. Loss: This is the Opportunity Loss for a given scenario (i. e. “the cost of being wrong”) for each scenario run. The EOL measure on the Risk. Report is equal to the average of negative opportunity losses. • Overall EOL: This cell is blank by default. It is used as a placeholder to type in the average opportunity loss for a given simulation run, and is referenced when analyzing VIA results. © Hubbard Decision Research, 2013 25

Cash Flow Sheet • The Cash Flow sheet allows a lot of flexibility. It usually is a convenient way to lay out a prospective Cash Flow statement, but we have used it in other ways. • Entries on the Cash Flow sheet reference cells in the Best Estimate column ONLY (Column C) of the Inputs sheet. If the sheet is used as a prospective statement of Cash Flows, formulas for the total benefits, total costs, and net benefits in each time period allow for calculation of project NPV and IRR. © Hubbard Decision Research, 2013 26

Cash Flow Sheet • Do not delete the rows for these variables, the macros use them to navigate the page. Rows can be added and deleted after row 4 and above the row where Cash Flow Summary is found. © Hubbard Decision Research, 2013 27

Random. CF Sheet • No entries are made to the Random. CF sheet – It is populated automatically by Setup Monte Carlo Model procedure. • After running Setup Monte Carlo Model, the Random. CF sheet looks much like the Cash. Flow sheet, except the values of every variable show up as their random outcomes, using the distributions assigned on the Inputs sheet. Test the model by pressing F 9 for new values. © Hubbard Decision Research, 2013 28

Mean. CF and Info. CF Sheets • Like Random CF - No entries are made to these sheets and it is populated automatically by Setup Monte Carlo Model procedure. • Referenced in the NPV row by the Inputs sheet. • Only used for macros and calculations – modifications here will be overwritten when new models are created. © Hubbard Decision Research, 2013 29

VIA (Value of Information Analysis) • To see the EVPI of each variable in the model run the VIA procedure by pressing the Compute VIA’s button from the Inputs sheet. • The VIA procedure runs, populating the Threshold, Individual EVPI, and Threshold Probability of each variable. © Hubbard Decision Research, 2013 30

VIA Flag Procedure • One may wish to simulate results conditioned on having perfect information for certain variables, this can be done by placing a 1 in the VIA Flag column (column K) for those variables. This will come up if: a) There are no single variables that show an EVPI. b) There may be a high information value for several variables, each of which may decrease if other variables are measured. • Do successive Monte Carlo runs to see the effect of measuring certain variables on EOL. Use this method to further refine results to prioritize variables for measurement. © Hubbard Decision Research, 2013 31

MC Wizard Dialog • When the “Run Monte Carlo” button is clicked, the AIE Monte Carlo Wizard dialog box appears Choose which cell in the model you want to track while the Monte Carlo runs. You can choose from a given list of cells like “NPV” or you can set it to any other cell you like by using the range selector to the right. Choose the number of scenarios to include in the run. Default is 1000. Enter any notes you would like to have be saved with the results of the Monte Carlo simulation. © Hubbard Decision Research, 2013 32

Risk Report Sheet • The Risk Report is automatically updated and displayed after a Monte Carlo runs. • The header box shows key information, including notes that were included for the run. © Hubbard Decision Research, 2013 33

Risk Report Sheet – Adjustable Cells • Cells in the orange cream color can be adjusted • Select Past Results is a dropdown, which allows you to change the simulation result being analyzed. Results are saved in the order ran, starting in column N. After changing this cell, a macro runs to automatically rescale the histogram. • Horizontal Axis Denomination is a dropdown. Use this to select the scale of the horizontal axis of the histogram. • Lower Limit. Type into this field the lowest value to have along the horizontal axis of the histogram. • Increment. Type into this field the increment to use in the horizontal axis of the histogram. © Hubbard Decision Research, 2013 34

Risk Report Sheet – Output Display • Cells in white are for display • Time of Run indicates the date and time of creation of the selected simulation results. • Trial Count indicates the number of simulation trials included in the selected run. • Simulation Variable Cell indicates the cell that contains the variable that was tracked during the simulation, by selections in the Monte Carlo Wizard. • Simulation Variable Name indicates the name of the variable tracked during the simulation, if it was chosen from the list of named variables on the Monte Carlo Wizard. • EOL indicates the average opportunity loss for the selected simulation run. © Hubbard Decision Research, 2013 35

Risk Report Sheet – Output Display • Simulation time (sec) indicates the length of time (in seconds) that the simulation took to run. • Notes. Any notes that were typed in to the Monte Carlo Wizard would show up here. • Average indicates the average value of the variable across each trial. • % Negative Outcomes shows the percentage of outcomes of the variable that are negative. • Min is the minimum value encountered of the variable in the simulation. • Max is the maximum value encountered of the variable in the simulation. • Scaling Denominator shows a multiplier that reduces the values shown in the horizontal axis of the histogram. • NPV and Frequency columns show a frequency breakdown within small ranges of the variable. These are graphed in the histogram. © Hubbard Decision Research, 2013 36

Risk Report Sheet – Output Storage • The output of Monte Carlo runs are stored starting in column N. • Each time the Monte Carlo procedure runs, the new results go into column N, and the prior results are moved one column right. • The Clear History button removes results in columns O and beyond. If clicked, it will shade the area to be deleted, and ask for confirmation. One result (the one in column N) will always be preserved after the history is cleared. • If a new model is created, then old results should be deleted after the first new result. • If a lot of historical results are saved, then Excel performance will be degraded. © Hubbard Decision Research, 2013 37

Investment Boundary Sheet • The plotted point comes from the values in the “Risk Report” page. • The thinner investment boundaries represent three benchmark investment sizes. • The thick investment boundary is interpolated from the other three. • The “compute CME” calculates a “Certain Monetary Equivalent” based on the relative position of the investment to its boundary. • Investments sorted by the “CME Ratio” is a very good estimator of much more elaborate methods in MPT. © Hubbard Decision Research, 2013 38

Hot Key Reference Sheet • Shows keyboard shortcuts that have been created to allow users to quickly launch the various macros found in the workbook. © Hubbard Decision Research, 2013 39

Defining the Decision • Is it one “Yes/No” decision, multiple yes/no decisions, or a continuum of choices? • How big is the decision? • What part of the decision can the analysis really influence? • Who are the decision makers? • How soon must the decision be made? • What are the components (costs, benefits, risks, etc. )? © Hubbard Decision Research, 2013 40

Basic Finance and Model Check Hubbard Decision Research 2 South 410 Canterbury Ct Glen Ellyn, Illinois 60137 www. hubbardresearch. com © Hubbard Decision Research, 2013

Present Value • The Present Value is what a cash flow is worth in terms of present day dollars – Example: If you are to receive a payment of $100 in 5 years, how much could you get today if you took out a loan at 7% annual interest that could be paid off when you receive the $100 in 5 years? • assume an interest only loan with no closing fees and a single payment in 5 years $ ? $100 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 • A question with the same answer (but different sign) is, how much should I invest today in an account that pays 7% annual interest so that in 5 years, the investment will be worth $100? $100 $ ? Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 © Hubbard Decision Research, 2013 42

Present Value The Present Value is calculated by discounting by the interest rate 7% 7% 7% $100 Year 1 Year 2 Year 3 Year 4 Year 5 A formula for the Present Value is where r = interest rate t = time periods (sign is negative if discounting to earlier periods) © Hubbard Decision Research, 2013 43

NPV – Net Present Value • Net Present Value (NPV) of a project is the present value of benefits, less the present value of costs. • For example, a 10 -year project that costs $2000 up front, plus $50 every year, and pays $600 every year, at a 5% rate of interest. $600 $600 $600 $2000 $50 $50 $50 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 • Find the net benefits in each year by subtracting costs from benefits • To the present value of net benefits, apply discounting • To compute NPV, sum the present value of all cash flows Year Annual Interest Rate Benefits Costs Net Benefits Present Value of Net Benefits NPV © Hubbard Decision Research, 2013 0 1 2 3 4 5 6 7 8 9 10 5. 0% -$2, 000 $2, 247 $600 -$50 $524 $600 -$50 $550 $499 $600 -$50 $550 $475 $600 -$50 $550 $452 $600 -$50 $550 $431 $600 -$50 $550 $410 $600 -$50 $550 $391 $600 -$50 $550 $372 $600 -$50 $550 $355 $600 -$50 $550 $338 44

NPV – Net Present Value Equation and Excel Formula • The net present value of costs and benefits can be summarized in the equation • The Excel formula for NPV is NPV. Here is an example with different numbers. • The first argument is the interest rate, the next argument is a range of cells containing the time-series of cash flows. © Hubbard Decision Research, 2013 45

IRR – Internal Rate of Return • IRR is equal to the interest rate that sets NPV equal to zero. • Returning to the equation, for NPV • if all Cash Flows and their timing is known, it is possible to set an interest rate, denoted by r in the equation, such that NPV = 0, this is the IRR. • The higher, the better © Hubbard Decision Research, 2013 46

Computing IRR in Excel • In Excel, IRR is computed using the IRR Formula. • The single argument for this formula is for a range of cells containing net cash flows from a contiguous time-series. © Hubbard Decision Research, 2013 47

Time Value of Money – Future value of $1 in 2014 Various annual interest rates are charted for the Future Value of $1 in 2014 $1. 00 1% $0. 90 2% $0. 80 3% Value of $1. 00 in 2014 $0. 70 4% $0. 60 5% $0. 50 $0. 40 10% $0. 30 $0. 20 15% $0. 10 20% $0. 00 2015 2025 2035 2045 2055 2065 2075 2085 Year © Hubbard Decision Research, 2013 48

Modeling Hints • Try to start simple and let the VIA tell you where to add more detail if necessary • Constantly redirect the estimators to provide ranges (they tend to get caught up in telling stories that may not have any effect on the range) – put a time limit on “fine tuning” ranges and just err on the side of uncertainty (wider ranges) • Don’t underestimate the task of getting the client to define and scope the investment and the options to be considered – this is often the most labor intensive part • A good model gives more than a binary “good/bad” result for the investment – you can use it to play “what if” and define a better investment • People aren’t very good (even if calibrated) at assessing the uncertainty of catastrophic events or extremely unlikely things (<5% probabilities) – historical data turns out to be a better basis for risk assessment © Hubbard Decision Research, 2013 49

More Modeling Hints • Consider both internal and external events • Look at a longer historical period for examples of disasters • Use “Premortems” • Look up the Form 10 -k of competitors and similar companies • Include all parts of the organization which would have input into the model • Peer reviews © Hubbard Decision Research, 2013 50

Erroneous Exclusions • Certain quantities are traditionally left out that should be included in a complete probabilistic model –Cancellation or other “catastrophic events” –Adoption rate and other human factors –Productivity Realization Rate –Changes in staff size/volumes/budget –Monetized values –Cost of infrastructure impact –Cost of training & long term maintenance © Hubbard Decision Research, 2013 51

Other CBA Error Checks • Spreadsheet errors: studies show 30% to 90% error rates in spreadsheets - about 1 erroneous cell per 100 (The Institute of Chartered Accountants in England & Whales) • Check for “Double Counting” costs or benefits • Use “Auditing” tool in spreadsheet • Use the Monte Carlo to look for potential errors (sometimes errors produce bizarre results in Monte Carlos that would not otherwise be visible in a traditional spreadsheet) • Be familiar with basic financial calculations • Check the known financial procedures and assumptions for the firm © Hubbard Decision Research, 2013 52

Basic Quality Audit Steps • Check that a MC and VIA runs without stopping and without errors. • Line by line audit of the Inputs page. – For each value, check correct columns and dist type. Confirm that lower bounds are less than best estimates which are less than upper bounds. – Confirm that ranges don’t produce irrational values (e. g. normals and lognormals could produce values outside their bounds) – Check for “orphans” (cells that are not used) – Use “Trace Precedents” and “Trace Dependents” cell to check for “orphans” – Confirm columns B or D or E do not contain extraneous formulas. All formulas must be in the column C. • Line by line audit of the Cash Flow page. – Confirm that formulas refer only to column C on the Inputs page or to other cells on the Cash flow page. – Note that references to column C should be anchored on C if it is to be copied to the right in other columns (as in a cash flow where years are columns). – Confirm that the calculation of the formula is correct. Complex formula should be decomposed and each component should be validated. – Confirm that formulas do not contain constants that should be modeled as variables. © Hubbard Decision Research, 2013 53

Problems Even w/ the “Quants” • In a survey I conducted of 34 Monte Carlo models in 17 companies, I found the following: – – • • • While most used a significant number of subjective inputs(70%), none ever used calibration While use of historical data was somewhat common (52%), very few conducted additional empirical measurements for a risk analysis (4%) Even when they used Monte Carlo simulations on problems they specialized in, they would revert to less sophisticated methods when dealing with problems outside of their specialty Still, Monte Carlo simulations do show a measurable improvement One researcher in the oil industry (Fiona Macmillan) found a correlation between the use of quantitative risk analysis methods and financial performance – and the improvement in performance started when they started using the quantitative methods. © Hubbard Decision Research, 2013 Copyright HDR 2008 dwhubbard@hubbardresearc 54 54