Sensitivity Analysis - Supertree

1 Sensitivity EXAMPLES ensitivity Analysis is an important step in decision Analysis . It helpsyou gain insight into the nature of decision problems, saving time andsimplifying the Analysis by avoiding undue modeling complexity. Inthis chapter, we will demonstrate the use of Sensitivity by performinga Sensitivity Analysis on a decision about a new product most analyses, only a few critical variables account for most of theuncertainty in the value measure. These variables are prime candi-dates for inclusion in the decision tree. Sensitivity identifies thesecritical Sensitivity program is almost always linked with models writtenin a spreadsheet or programming language. For our example, we usean Excel spreadsheet model called New Product that calculates thefinancial implications related to introducing a new product in anexisting AnalysisSENSITIVITY EXAMPLEThe Excel spreadsheet for the new product introduction model, NewProduct, is called and can be found in the Super95 folder(Windows) or the Supertree folder (Macintosh).

2 The spreadsheet is divided into three sections. The top section con-tains the parameters used in the evaluation, the middle sectioncontains the calculations, and the bottom section contains income andcash flow variables section of the New Product spreadsheet is shown belowin Figure Column A contains descriptive labels for the variables,column B contains the values of each variable used in the calcula-tions, column C contains the range names of the corresponding cell incolumn B. Columns D, E, and F contain the low, base, and high value ofeach variable respectively. Column G contains the index used in theformula in Column B (Value in Use) to choose the right value fromcolumns D, E, and F (Low, Base, and High). Finally, columns I and Jcontain a description of the variable and its remaining structure of the model is straightforward. The calcu-lations depicted in Figure are based on several assumptions:market growth of 2% per year; the new product introduction in 1998;market share growth for three years, flattening out at a peak value,and eventually beginning to decline; sufficient production equipmentinstalled to meet the peak demand; all prices and costs growing withinflation.

3 Figure shows the first few columns of the calculationsand income and cash flow sections of the spreadsheet. Results areshown to 2003, although the full spreadsheet continues for 20 years,to 2015 in column Product ExampleFigure in Use Range NameLowBaseHighUnits/NotesMarket share35% mktshr10%35%50%peak market shareMarket growth rate2% mktgr1%2%4%annual compound growth price$ $ $ per poundStart year1998 inityr199719981999year sales startSENSITIVITY EXAMPLENew Product ExampleFigure (million of pounds)120122125127130132135138 Market Share (%)0%0%12%23%35%35%35%35%Volume (millions of pounds)00153045464748 Revenue ($ million) Cost ($ million) Cost ($ million) machines00124444 New machines00112000 Capital Cost ($ million) capital ($ million) depreciation ($ million) depreciation ($ million) : in this simple example, tax=book=declining balance depreciationWorking capital ($ million) AND CASH FLOW STATEMENT (US $ million) in Working Cash Cash Disc Cash MEASURENPV (US $million)$152 Cell B80 contains the NPV (net present value) for the new Sensitivity Analysis will determine how sensitive this value is tothe uncertainty in the variables listed in the parameters section ofthe spreadsheet.

4 (NPV is a way of reducing a set of financial resultsspread out over years to a single value today.) Sensitivity EXAMPLETo start the Sensitivity program, choose Sensitivity from the WindowsStart menu or double-click the Sensitivity icon on the Macintosh. Themain Sensitivity screen will appear and you can begin using theprogram. The menu bar at the top of the screen controls the is also a toolbar for common menu , Low, and High Values for VariablesThe next step is to assemble the data for the requires three values for each variable: base, low, andhigh. The base value is the median value of the possible range ofvalues for the variable. In other words, for peak market share, thereshould be a 50 percent chance that the actual peak market share willturn out higher and a 50 percent chance it will turn out to be will usually be the value that you already have in , the low value represents a 10 percent chance that theactual peak market share will turn out to be lower (or equal to) and a90 percent chance that it will turn out to be higher.

5 The high valuerepresents a 10 percent chance that the actual peak market sharewill turn out to be higher and a 90 percent chance that it will turn outto be you use these guidelines, all the variations will have the samemeaning (80 percent probability of being within the range), and youcan readily compare the importance of the uncertainty in the to the Spreadsheet ModelThe spreadsheet calculates the impact of variations in individualvariables on the value measure. In the new product introductionexample, the value measure is link Sensitivity to the spreadsheet model, select the Enter orChange Model command from the Structure the name of the spreadsheet program (Excel, in this case)and click OK. Possible responses are Basic, Excel, IFPS, Lotus,Powersim, the ProblemSENSITIVITY the Locate button to locate the spreadsheet (Super95folder, ). A dialog box will appear to enable you toopen your spreadsheet the spreadsheet is not opened at thispoint, but Sensitivity captures the information required tolocate respond to the prompt Result name or cell address you canenter either the cell address B80 or the Range name NPV.

6 Wesuggest you use the range name because it is easier to readand remains constant even with manipulation of the respond to the prompt Column in which to store results, youcan enter the name of an empty column of the spreadsheet. Forthe New Product spreadsheet, AA is a good program call enables you to choose between twodifferent modes of calling Excel. (The default is Automaticprogram call. The consequences of this choice are discussed inthe Evaluation section of this chapter.)The final line (resulting endpoint expression) is a shorthand expres-sion created by Sensitivity for the information on the screen. E de-notes Excel, the $ signs are separators, and next is the spreadsheetname and location. NPV is the range name (or cell address) for the cellcontaining the value measure, and AA denotes the empty spreadsheetcolumn where results are temporarily you are done entering all the information, the completed dialogbox should look like the one in Figure Click the ProblemFigure EXAMPLEI nputting Variable RangesThe next step is to enter the data.

7 Choose the Input or Change SingleVariable command from the Structure menu. For the firstvariable entered, Sensitivity will request a descriptive name to use tolabel the output; enter New Product Introduction Example. (We havesupplied typical values for entries here and below, but you can useothers if you would like to experiment.)Name the first variable MKTSHR, the range name in the spreadsheetfor the peak market share value. Add a description, the base value,and designate the Sensitivity type VALUE, to allow the high and lowvalues to be directly input (as opposed to being calculated). The com-pleted dialog box is shown in Figure the OK, button and input information for MKTGR, themarket growth rate. Designate the variation type ADDITIVE, meaningthat the high (.02) and low ( ) modifiers will be added to the basevalue (.02), producing a swing from .01 to .04. Figure shows thedisplay:Entering the ProblemFigure EXAMPLEYou can then click OK, to move to the next variable, which isprice.

8 The variation type is MULTIPLICATIVE; the high ( ) and low( ) modifiers will be multiplied by the base value ( ) to and in this list of names under Click on Name to Edit Variable: indicates allvariables that you have entered so far. Double-clicking a name in thislist will recall the data for that that all variables have been entered, click OK and the ShowSensitivity Data screen the ProblemFigure EXAMPLER eviewing the Input DataYou can also select Show Sensitivity from the Structure menuor from the toolbar. The screen will show the first three variables inFigure To create interesting outputs, we have entered variationsfor ten additional variables. You can continue to enter all thevariables listed in Figure or you can open the file all the variables have been entered for that in the last column, Sensitivity shows the status of eachvariable as new or old.

9 New means that you have not yet evaluated orrun the spreadsheet model to determine the Sensitivity to thatvariable. Old means that the model has been evaluated for thevariable indicated. This can save time if you add new variables orchange the input data, because you need only evaluate the new the ProblemFigure EXAMPLEI mporting Variable DataYou can save time when entering the variables by using the ImportVariable Data command from the Structure menu. (This option isvalid for Excel spreadsheets only.) To do so, follow the two steps a range name for the section of your spreadsheetcontaining the input variables. The first line of this spread-sheet must contain columns with the words,Description contents of column, describe the variableName the range name for the Value in Use cellLow the low value for the variableBase the base value for the variableHigh the high value for the variableThe file has a range called RD_inputtable (A22:L38)defined that contains the input Import Variable Data from Sensitivity s Structuremenu.

10 Accept the default name RD_inputtable Locate the spreadsheet. Enter a descriptive name such as New Product IntroductionExample. The Sensitivity Model dialog box appears. Enter NPV as theresult name and AA as the column to store results. Notethat the spreadsheet has already been located. Click OK andthe Show Sensitivity Data screen the ProblemSENSITIVITY EXAMPLEE valuating and AnalyzingSensitivitiesEvaluatingThe Sensitivity data are now ready to be evaluated. Sensitivity estab-lishes a base case by running the model once with all variables set tobase values. Then it runs the model with each variable set first to itshigh value and then to its low you chose the automatic program call when naming the model forevaluation earlier in this section, Sensitivity will do all the work foryou this is recommended. If you chose the manual program call, Sensitivity will provide instructions on how to leave Sensitivity , runExcel, return to Sensitivity , and pick up the first time you run Sensitivity , you may need to specify where theapplication Excel is located.