Home

TopRank - Palisade Corporation

1. eeeeeeeeeeeeeeeeneee nennen nennen nenne nennt annees Model Menu eeeeeeeee eere ee eene enne enenatis What lf Analysis Menu eene Report Settings Command esee Swap Functions Command essere Utilities Commands eeseeseeeeeeeeneeeeene eene nnne Help Command eeeeeessesseeee eene nennen ennt nnn nnn Reference TopRank Functions Introduction to TopRank Functions eee TopRank Function Reference eren Listing Of TopRank Functions eren Listing Property Functions eere Listing Output Function eere Using RISK Functions eeeeee sees essen eene 109 115 121 123 125 Appendix A Using TopRank With Other DecisionTools 139 The Decision Tools Suite ccccssseceeessseceeesesceeenseeecenenseseenees 139 Palisade s DecisionTools Case Study 142 Introduction to TOPRAnk ssscsscsssscssessssessesseeessessseesersesensseeesees 144 Using RISK with TopRank esee 148 Introduction to PrecisionTree eeeeeeetetntente tnnt 152 Using RISK with PrecisionTree esseesssssss
2. esee 78 Remove AutoVary Functions Command 78 Add Output Command eere eee ente tn tete tn ntn 79 Model Window Command eee eese 82 Arrange Menu 86 How is the Model Window Table Created ses 89 What lf Analysis Menu eere nnne nnn 91 Analysis Settings Command eee 91 Input Defaults Tab Analysis Settings Command 92 Find Inputs Tab Analysis Settings Command 96 Other Tab Analysis Settings Command ss 99 Run Command cccccsssssssscssssrsscssesescssssssssssssssesscsceessesessssssssssssseesees 103 Tornado Graphs eesse rerne E SEE EEEE RE ES 105 Spider Graphb ccsecececessssssssssssssessssssssesesesesecssssssensssssesssseseeeseesees 106 Sensitivity Graphs essesesseesesscesessoseesesscoresroseesesseosesroscosesseseeerescesesses 107 Report Settings Command serere 109 Reports Tab Report Settings Command 22 4 110 Outputs for Reports Tab Report Settings Command 112 Inputs for Reports Tab Report Settings Command 113 Swap Functions Command esee eene 115 Utilities Commands e eeeeeieiseeesee ense ennt 121 Application Settings Command
3. eese 121 Clear TopRank Data Command eese 122 Unload TopRank Add in Command 122 Help Command ccssscccceeeeeeseeesneeenseeeeeeeeesnaeseneeeeeeeeesseseseenenenees 123 TopRank Reference Guide 63 64 TopRank Help earen naa o E EERE 123 Online Manual e eetiisden tae ote th tuse theo sae th aee tb ane th dene nh ene oo 123 License Activation Command eese esent tn tnanus 123 About Command eres ee eeeeee esses esta snas tasas tns ta tasse tasa atoe 123 Introduction to TopRank Functions eene 125 Vary Function Arguments eee eese estne tn tn tnnt tnn neo 125 Output Function eese esee eene tn teen enntn otto ntn enata soto teta inan 127 Property Functions eese esee eese ettet nent tntn RS 127 Sampling from Vary Functions During an RISK Simulation 128 TopRank Function Reference eese 129 Table of Vary Functions eese entente netten tn snantut 129 Listing Of TopRank Functions eere 131 RiSkAULOVALY M RES 131 Risk V ALY eisses ieoor resessie orao ars MERE Ua Dede SIS Sae Rand 131 RiskVaryMulti s eeeeeeeeeeeeeeeeeeetetetn natant notos toten tn tn tete tnn 132 RiskVaryMultiTable eese eene entente tnn sass sroin oss 132 RiskVaryTable eese es
4. 15 15 Labor Cost per Worker LC a Lock input Assign Input to Category Column Heading d Labor Cost per Worker LC Category Number of Work E Number of Workers Facto 500 15 X gl a OK Cancel The Arrange icon at the bottom of the window can also be used to access the Arrange menu commands Multi Way Copy Input Across Category Function Properties Refresh Edit Function in Excel F2 Delete Del Model Menu Group Inputs By Category Command Group Outputs By Range Name Command Default Categories Commands The Group Inputs By Category command specifies whether or not the table of inputs will be arranged by category When Group Inputs By Category is checked categories entered using a RiskCategory function will always be shown Default categories will also be shown if the Default Categories command Row Heading or Column Heading option is selected The Group Outputs By Range Name command specifies whether or not the table of outputs will be arranged by output range name When Group Outputs By Range Name is checked RiskOutput functions will be grouped by range name if they are part of an output range TopRank Model Inputs Outputs Model Outputs in Open Workbooks Inputs 15 Outputs 6 Name Cel Function Range Profits Per Plant Profits Per Plant Faces LIA A an Profis Per Pant Fac 51 EE Profits Per Plant Fac Edit Refresh Profits Per Plant Fac
5. would be included in a default category named R amp D Costs if the Default Categories command Row Heading command was checked and would be included in a default category named 2010 if the Default Categories command Column Heading command was checked The Assign Input to Category command places an input or set of inputs into a category The Input Categories dialog allows you to create a new category or select a previously created category in which to place the selected inputs gt TopRank Model mI Inputs Outputs Model Inputs in Open Workbooks Inputs 15 Outputs 6 Name E i Input Categories Base Max T Category Labor Cost per Worker LC Category for Selected Inputs Labor Cost per Worker LC Factory 800 15 Labor Cost per Worker LC Factory Labor Cost per Worker LC 900 15 ESOS Labor Cost per Worker LC 1200 15 Labor Cost per Worker LC Factory Training Investment per Worker 800 15 Labor Cost per Worker LC Factory Number of Workers 600 15 Category Number of Workers s Number of Workers Factory 1 500 15 Number of Workers Factory 2 250 15 Number of Workers Factory 3 100 15 Number of Workers Factory 4 A 500 15 Cancel Number of Workers Factory 5 800 15 Category Training Investment per Worke Training Investment per Worker Factor D51 RiskAutoVary 100 15 15 15 100 15 z 6 ok Cancel When an input is assigned to a category by you
6. The values sampled by RISK from Vary and VaryTable functions during a simulation depend on either a distribution argument entered for the function or the default distribution setting used in TopRank For example the TopRank function RiskVary 100 10 10 using a default distribution setting of Uniform and a default range type of percentage is sampled like the RISK distribution RiskUniform 90 110 VaryTable functions from TopRank are sampled as RiskDuniform functions in RISK Introduction to TopRank Functions TopRank Function Reference TopRank functions return values from across a range or from a table during a What If analysis in TopRank When used all functions are preceded by the entry Risk in Excel Table of Vary Functions This table lists the custom Vary functions that are added to Excel by TopRank Vary Function RiskAutoVary base min max type Steps dist RiskVary base min max type Steps dist RiskVaryMulti base min max type Steps dist RiskVaryMultiTable base table RiskVaryTable base table Reference TopRank Functions Returns Returns values between min and max with number of values returned equal to Steps min max range by type and distribution across the min max ran Returns values between min and max with number of values returned equal to Steps min max range by type and distribution across the min max ran Returns values between min and max with number of va
7. Guide to Using TopRank What If Analysis Add In for Microsoft Excel Version 5 7 September 2010 Palisade Corporation 798 Cascadilla St Ithaca NY 14850 607 277 8000 http www palisade com k Copyright Notice Copyright 2008 Palisade Corporation Trademark Acknowledgments TopRank BestFit RISKview and Palisade are registered trademarks of Palisade Corporation RISK is a trademark of Parker Brothers Division of Tonka Corporation and is used under license Microsoft Excel and Windows are registered trademarks of Microsoft Inc Welcome TopRank and What If Analysis Welcome Welcome to TopRank the ultimate What If tool for spreadsheets from Palisade Corporation TopRank greatly enhances the standard What If and data table capabilities found in your spreadsheet In addition you can easily step up to powerful risk analysis with its companion package RISK TopRank helps you find out which spreadsheet value or variable affects your results the most an automated What If or sensitivity analysis You also can have TopRank automatically try any number of values for a variable a data table and tell you the results calculated at each value TopRank also tries all possible combinations of values for a set of variables a Multi Way What If analysis giving you the results calculated for each combination Running a What If or sensitivity analysis is a key component of making any decision based on
8. RISK function Here s how the new model should look EV 22 900 Drill Results Open RiskNormal 22900 50000 70 000 Don t Drill 10 000 During an RISK simulation the RiskLognorm function will return random values for the payoff value of the Results node and PrecisionTree will calculate a new expected value for the tree But what about the decision to Drill or Not Drill If the expected value of the Drill node changes the optimum decision could change iteration to iteration That would imply that we know the outcome of drilling before the decision is made To avoid this situation PrecisionTree has an option Decisions Follow Current Optimal Path to force decisions before running an RISK simulation Every decision node in the tree will be changed to a forced decision node which causes each decision node to select the decision that s optimal when the command is used This avoids changes in a decision due to changing a decision tree s values and probabilities during a risk analysis Using RISK to Analyze Decision Options There may be times when you want to know the outcome of a chance event before making a decision You want to know the value of perfect information Before running a risk analysis you know the expected value of the Drill or Don t Drill decision from the value of the Drill Decision node If you ran a risk analysis on the model without forcing decisions the return value of the Drill Decision node wo
9. 448 Labor Cost ger Worker LC 166 628 196 667 Factory 1 DSS Labar Cost per Worker LC Factory e G48 Labor Cost per Worker LC mam 182 417 Factory n2 E48 Labor Cost per Werkar LC Factary 5 448 Labor Cost par Worker LC Z 422 28 370 437 Factory i D4 Labor Cost per Workar LC Factory 2 48 Labor Cost per Warkar LC F 422 288 170 417 tamaya BM Laer Comper Multi Way What If results include e A list of combinations of inputs ranked by their impact on results and e A grid which details the results generated and input values used in each combination The grid of data detailing the values for each combination can be placed in your spreadsheet for further analysis A spreadsheet Pivot Table for Chapter 3 Overview of TopRank 51 52 example is often effective for viewing the multi dimensional data sets generated by a Multi Way What If analysis Ranking of Multi Way What If results is done based on the amount of change from the output s base value that was caused by the combination of input values used The amount of change is measured as a percentage change This is calculated using the formula New Calculated Output Value Output Base Value Output Base Value Graphical Results from a Multi Way What lf Analysis A tornado graph can be used to quickly describe the results of a Multi Way What If analysis As with a tornado graph created with a one way What If
10. Factory 2 v F57 Profits Per Plant Factory 3 G57 Profits Per Plant Factory 4 v H57 Profits Per Plant Factory 5 D60 Total Profit You can select to report on All Outputs or only Selected Outputs This is useful if you have a model with many outputs but are only interested in analyzing one of them in a specific analysis You can leave all your output functions in your model but select specific ones to analyze here 112 Report Settings Command Inputs for Reports Tab Report Settings Command Specifies significant inputs to be included in reports The Inputs for Reports tab in the Report Settings dialog allows you to specify a cutoff for inputs to be reported on from an analysis By using these settings you can limit your reports to show only critical inputs eliminating those inputs which have minimal affects on results TopRank Report Settings Reports Outputs for Reports Inputs for Reports In Each Report or Graph Limit Inputs Reported to IV Only Top Ranking Maximum of Inputs 10 v Only Those Changing Output gt X Report Only Chg gt 1 Options on the Inputs for Reports tab include e Only Top Ranking specifies that only the entered top number of inputs that impact an output will be included in reports e Only Those Changing Output gt specifies that only those inputs that cause a greater than the entered change in output value will be included in reports Reference TopRank Add In
11. TopRank Add In Commands 89 90 Model Menu What lf Analysis Menu Analysis Settings Command Changes the default settings used in a What If analysis The What If Analysis menu Analysis Settings command affects the nature of a What If analysis and the TopRank functions used in it All settings come with default values that can be changed The What If analysis settings affect the minimum and maximum range TopRank uses in AutoVary functions the default number of one way and Multi Way What If steps the default distribution setting the settings for TopRank s automatic identification of spreadsheet inputs the number of runs to be performed the updating of the worksheet display during What If analysis and others All What If analysis settings are saved when you save a workbook you have used with TopRank Reference TopRank Add In Commands 91 Type 92 Input Defaults Tab Analysis Settings Command Sets the default changes automatically applied to TopRank inputs TopRank Analysis Settings Input Defaults Find Inputs Other Range Type Change from Base v Minimum 15 Maximum 15 v Distribution Triang Steps 5 Options in the Input Defaults tab include Type Minimum and Maximum Distribution and Steps Any change in a Vary function is entered relative to the base value or the first argument of the function This change is entered as a minimum maximum range that defines the values tha
12. arguments to standard Vary and VaryTable functions They are included in the one way What If analysis that runs first in a TopRank analysis prior to any Multi Way What If that is selected All VaryMulti and VaryMultiTable functions are included in a Multi Way What If analysis The number of these functions to be included in each combination tested is determined by the Group Size setting For example you might have four inputs defined using VaryMulti and VaryMultiTable functions price sales volume production costs and investment and you want to see the impacts of every combination of two inputs on your result profit In this case the Group Size is two Your analysis might tell you that price and sales volume varying together are the most significant combination affecting your result profit Setting the Group Size to three might identify the combination of price sales volume and investment as the most important three way combination of inputs on results Chapter 3 Overview of TopRank 47 48 Defining Multi Way What If Inputs Inputs for a Multi Way What If analysis can be defined by e Directly changing the selected Vary and VaryTable functions in your spreadsheet to their equivalent VaryMulti and VaryMultiTable forms or e Highlighting the input Vary and VaryTable functions in the Model Window list right clicking and selecting Multi Way TopRank Model Inputs outputs Model Inputs in Open Workbooks I
13. 156 Appendix B Recommended Readings 160 Appendix C Glossary of Terms 162 Index 166 Table of Contents vii viii Chapter 1 Getting Started Introductions sete cestteeeseeeeeeeeesenesesneeseseeessaessasneeseseeessaesuseneeneneeeeaes 3 Checking Your Package ccscsssssssssssesesssssssssssssessssesessssesesesssseneneeees 3 About This Versioni senrenessansen isase n eines osaa 3 Working with your Operating Environment eese 4 If You Need Help eese entente toten tete tatnen 4 TopRank System Requirements eese 6 Installation Instructions eeeeeeeeeeeeeesee senten 7 General Installation Instructions eee eee 7 The DecisionTools Suite sesser tn tntn esir 7 Setting Up the TopRank Icons or Shortcuts ess 8 Macro Security Warning Message on Startup esses 8 Software Activation cessere nennen nnne 9 On line Tutorial eese nennen nennt 11 Using TopRank eeeeeeeeeenee eren enne nennen nennen nnns 12 Chapter 1 Getting Started Introduction This introduction describes the contents of the TopRank package and shows how to install TopRank and attach it to your copy of Microsoft Excel Checking Your Package Your TopRank package should contain e The TopRank User s Manual this book with e Preface and Getting Started e
14. 3 F51 Labor Cost per Worker LC Factory 3 F48 an M h Tornado D60 Spider D60 Ready L Chapter 3 Overview of TopRank 43 44 The tornado graph brings attention to the variables that have the greatest effects on your output s value those plotted on the top of the graph The tornado graph can summarize the impact of an almost unlimited number of variables in a neat simple graph Spider Graphs A spider graph also compares the results for a single output as generated by multiple input variables For each input variable the percentage of the base case is plotted on the X Axis and the percent change in output value calculated is plotted on the Y Axis The slope of each line depicts the relative change in the output per unit change in the input variable The shape of the curve shows whether a linear or non linear relationship exists between the input and the output e id 2 0 kms Book2 Microsoft Excel x i Home insert Page Layout Formulas Data Review View Add Ins TopRank E jug FE SS Hy Analysis Settings Ai Utilities PX E s E i Report Settings c Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions Model Analysis Tools Al X f B c nee Spider Graph 2 Performed By Test Date Friday February 13 2009 10 36 40 AM Ica D E F G H 1 J K L M N 3 4 ModekFactory xs
15. Commands 113 114 Report Settings Command Swap Functions Command Swaps TopRank and RISK functions in and out of cell formulas With the Swap Functions Command TopRank and RISK functions can be swapped in and out of your workbooks This makes it easy to give models to colleagues who do not have TopRank If your model is changed when TopRank and RISK functions are swapped out TopRank will update the locations and static values of TopRank and RISK functions when they are swapped back in When the Swap Functions icon is clicked you may immediately swap out functions using the current swap settings or change the settings to be used id amp m Jc Home inset fagelseut Formulas Data Review View Addins TopRank ox 4 Buulc Y aco Add AutoVary Add Model T Input Tuncions Output Window gu Functions Modei Anat Tools J K Dow Chemical Company operates five produeton pants eacn inented in different pan afine country Theretee each pant nres a INEren number of workers pays fierent wages and invests different in rain programs nimedto improve predurtivty n a mode desigradto calaute the profi of eechplart Topfarkideries he most ral tactors in rflveninithe otal anhs thee relalve ievgorlange A Whal analysis ofthis model shows Pal Trang Investment for Factory l6 s muster ffectingTotnlryafe From Ine output repari genemted Dy To pRIM we ean see that n 15 inezense inthe TIAN imvestm n Factory SS
16. If Group Size of Steps Multiple Runs of Runs During Analysis Update Display IV Pause on Error Chapter 3 Overview of TopRank 49 Testing All Combinations with a Data Table 50 The number of calculations required to complete a Multi Way analysis grow exponentially with increases in each of these three parameters so user beware When a large number of VaryMulti and VaryMultiTable functions are present in your spreadsheet it is important to keep the Group Size small to rein in the total number of recalculations required There are certain analyses which require all possible combinations of inputs to be tried during a Multi Way What If analysis and all the results from all combinations to be reported This is the equivalent to a data table or What If table calculation in a spreadsheet A traditional spreadsheet data table is a two dimensional matrix filled with results calculated by substituting two lists of values as listed across the top and down the side of the table for two inputs in your spreadsheet The matrix holds results for each two way combination In TopRank a data table calculation can be performed using a Multi Way What If analysis Simply set the Group Size equal to the number of Multi Way inputs Then results are calculated for all possible combinations of input values Unlike the spreadsheet your Multi Way data table calculation in TopRank can have any number of dimen
17. Overview of What If Analysis e Overview of TopRank e TopRank Modeling Techniques e TopRank Command Reference e TopRank Function Reference e Technical Appendices The TopRank CD including e TopRank System Files e TopRank Example Files e TopRank Tutorial The TopRank Licensing Agreement and User Registration Card If your package is not complete please call your dealer or supplier or contact Palisade Corporation directly at 607 277 8000 About This Version This version of TopRank can be installed with Microsoft Excel 2000 or higher Chapter 1 Getting Started Before Calling Working with your Operating Environment This User s Guide assumes that you have a general knowledge of the Windows operating system and Excel In particular e You are familiar with your computer and using the mouse e You are familiar with terms such as icons click double click menu window command and object e You understand basic concepts such as directory structures and file naming If You Need Help Technical support is provided free of charge for all registered users of TopRank with a current maintenance plan or is available on a per incident charge To ensure that you are a registered user of TopRank please register online at www palisade com support register asp If you contact us by telephone please have your serial number and User s Guide ready We can offer better technical support if you are in front of your computer and ready
18. RISK produces a probability distribution as an analysis result The distribution describes which values an output such as profit could have as well as how probable certain outcomes are For example RISK can tell you whether there is a 30 chance that your company will not make a profit next quarter For each output defined TopRank tells you which inputs have the largest effect on the output The results show how much change you can expect in an output when a given input changes by a defined amount For example TopRank can tell you that your company s profits are most sensitive to sales volume and that when the sales volume is 1000 units you will lose 1 million So TopRank told you that to make a profit you ll need to concentrate on keeping sales volumes high The most important difference between the two packages is that RISK studies how the combined uncertainty of all variables affect the output TopRank only tells you how an individual input or a small group of inputs affects the output So while TopRank is faster and easier to use RISK provides a more detailed comprehensive look at the problem We strongly recommend using TopRank first to determine which variables are the most important Then use RISK to runa comprehensive analysis of your problem for the best possible results In summary TopRank tells you what the most important variables are in your model The results of a TopRank What if analysis can be used on their
19. Results Are Understandable and Accessible to All Decision makers are all comfortable with the concept of a What If analysis What s most important and what happens if this factor increases in value are common questions asked during the decision making process Some decision makers however have trouble with the concepts of probabilities simulation and distributions of possible outcomes They re naively afraid of the black box that has executed their simulation and don t trust the results of the more complex technique e For Some Analyses There s Just Not Enough Time for Risk Analysis Many decision makers deal with reams and reams of models some more critical some less They just don t have the time to build a simulation model for all cases A quick What If however gives them needed information in little time for those less important decisions What If Analysis and Risk Analysis Conclusions What If analysis is a powerful technique that has gained great popularity with the advent of the spreadsheet and the personal computer Whether in its one way or Multi Way form What If analysis can provide important guidance for refining a model and implementing a decision It shows you what s important and where to focus important guidance for any decision maker And it also provides a stepping stone to the powerful technique of risk analysis or Monte Carlo simulation Chapter 2 Overview of What If Analysis 29 30 Wh
20. UNIFORM TRI1090 or NORMAL and must be surrounded by quotes 131 RiskVaryMulti RiskVaryMultiTable RiskVaryTable 132 VARYMULTI base minimum maximum range type Steps distribution specifies an input variable for use in both a What if analysis and a Multi Way What if analysis The arguments to this function are identical to those of the Vary function For more information see VARY VARYMULTITABLE base table specifies an input variable for both 1 a What if analysis and 2 a Multi Way What if analysis along with a table of values to substitute for the input The arguments to this function are identical to those of the VARYTABLE function For more information see VARYTABLE VARYTABLE base table specifies an input variable for a What if analysis along with a table of values to substitute for the input The table of values can be directly entered in the function itself or a reference to the location of the table can be entered Any number of values can be included in a referenced table RiskVaryTable 100 70 80 90 110 120 130 specifies a What if input with a base value of 100 Six different values 70 80 90 110 120 and 130 are returned by the VaryTable function during a What if analysis and results are calculated at each of the six values RiskVaryTable 100 A1 A100 specifies a What if input with a base value of 100 Each of the 100 values in the range A1 A100 are returned by the Va
21. addition with the automatic mode important What If results can be gathered by users who have little or no training in analytical techniques At any time AutoVary functions can be removed from your spreadsheet by TopRank restoring it to its original condition Note An AutoVary function is simply a Vary function that was automatically added to your spreadsheet by TopRank Chapter 3 Overview of TopRank 37 Number of Steps for Vary Functions Distribution Across a Vary Function s Range 38 Steps and Distribution The number of values tried for each input and their magnitude is given by the Steps and distribution setting for each Vary function These entries further customize the values used for each input during your What If analyses A differing number of possible values can be returned for each Vary function For each value returned TopRank recalculates the worksheet and stores a new possible result The number of values returned is given by the Steps argument to the Vary function or a default number of steps In the function s simplest form such as Vary 100 10 10 there is no entry in the function for the Steps In this case TopRank uses the default Steps A typical default is four or five steps With four steps TopRank calculates the spreadsheet at the minimum possible value for the Vary function the maximum possible value and two values equally spaced in between If you enter a Steps value in the Vary
22. an licensed copy For more information on licensing your copy of TopRank see Chapter 1 Getting Started in this manual About Command Displays version and copyright information about TopRank The Help menu About command displays the About dialog box listing the version and copyright information for your copy of TopRank Reference TopRank Add In Commands 123 124 Help Commands Reference TopRank Functions Introduction to TopRank Functions TopRank adds a new set of functions to Excel to run What If analyses on spreadsheets These functions are called Vary functions They specify how a spreadsheet input could change during a What If analysis There are several forms of Vary functions e Vary the standard function for specifying how an input value can vary e AutoVary a Vary function that is automatically added by TopRank e VaryTable a Vary function that uses a table to define the possible values for an input e VaryMulti a Vary function that is also used in a Multi Way What If analysis e VaryMultiTable a VaryTable function that is also used ina Multi Way What If analysis As with Excel functions Vary functions contain two elements a function name and argument values which are enclosed in parentheses A typical Vary function is RiskVary 100 10 Vary Function Arguments The number and type of arguments required for a Vary function depend on whether a Vary or VaryTable function is used For example the f
23. d a m m Values in Thousands Ol w E as FA seed QJ Mimi wj dee The simple and change which defines the inputs to a What If analysis can be refined to create the probability distributions needed in arisk analysis TopRank s What If inputs automatically can be directly used in a risk analysis using RISK Chapter 2 Overview of What If Analysis 25 Sensitivities to Probabilities in a Risk Analysis 26 Sensitivity Analysis Using Risk Analysis Sensitivity analysis can be conducted on the data generated by a risk analysis to identify the most critical inputs which affect results This is often accomplished using correlation and or regression analysis of the data sets generated by the simulation This is a robust and highly refined form of sensitivity analysis as it uses as its basis the simulation data where all inputs were changed simultaneously In many cases it may just affirm the results of your initial What If analysis but there are times when there may be a difference in the results Assuming the simulation model is properly specified the sensitivity analysis results from the simulation are the ones you should use fed RISK Output D60 DER Total Profit Regression Coeffidents Training Investment per Worker Factory 5 Labor Cost per Worker LC Factory 5 Training Investment per Worker Factory 4 Labor Cost per Worker LC Factory 4 Labor Cost per Worker LC Factory 1 Training Inve
24. feel that a 20 and 30 change is possible You can also select to not have a value varied as in some cases a spreadsheet value is fixed and could never be changed Running a What If Analysis TopRank Results During its analysis TopRank individually changes values for each Vary function and recalculates your spreadsheet using each new value Each time it recalculates it collects the new value calculated in each result cell This process of changing values and recalculating is repeated for each Vary and VaryTable function The number of recalculations performed depends on the number of Vary functions entered the number of steps i e values across the min max range you want TopRank to try for each function the number of VaryTable functions entered and the values in each table used TopRank ranks all varied values by their impact on each result cell or output you ve selected Impact is defined as the amount of change in the output value that was calculated when the input value was changed If for example the result of your spreadsheet model was 100 prior to changing values and the result was 150 when an input changed there is a 50 change in results caused by changing the input TopRank results can be viewed graphically in a Tornado Spider or Sensitivity graph These graphs summarize your results to easily show the most important inputs for your results Table of Contents Table of Contents Chapter 1 Getting Start
25. own to make better decisions But for the most thorough analysis use TopRank to find the most important variables in your model then use RISK to define uncertainty in those variables and run asimulation TopRank can help you optimize your RISK simulations by defining uncertainty in only the most important variables making your simulation faster and more compact Using RISK with TopRank Appendix A Using TopRank With Other DecisionTools 151 Introduction to PrecisionTree 152 PrecisionTree from Palisade Corporation is a decision analysis add in to Microsoft Excel Now you can do something you ve never been able to do before define a decision tree or influence diagram directly in your spreadsheet PrecisionTree allows you to run a complete decision analysis without leaving the program where your data is your spreadsheet Why You Need Decision Analysis and PrecisionTree You might wonder if the decisions you make are suitable for decision analysis If you are looking for a way to structure your decisions to make them more organized and easier to explain to others you should definitely consider using formal decision analysis When faced with a complex decision decision makers must be able to organize the problem efficiently They have to consider all possible options by analyzing all available information In addition they need to present this information to others in a clear concise format PrecisionTree allows dec
26. spaced in between If you enter a Steps value in the Vary function such as Vary 100 10 10 8 TopRank overrides the default Steps and in this case return eight different values for the Vary function A different default number of steps value is used for a Multi Way What If analysis as opposed to a standard one way What If analysis Typically you want fewer Multi Way steps as compared to one way steps the number of steps must be greater or equal to two This minimizes the number of recalculations or iterations required to complete a Multi Way What If analysis For more information on Multi Way steps see Multi Way options on Other Tab in this section Reference TopRank Add In Commands 95 Scan Precedent Cells to Find Inputs 96 Find Inputs Tab Analysis Settings Command Turns on or off the automatic insertion of AutoVary functions for inputs and sets the parameters for the identification of inputs TopRank Analysis Settings Input Defaults Find Inputs other Scan Precedent Cells to Find Inputs Iv When What If Analysis Starts When Outputs Are Added When Identifying Inputs Indude V Stand Alone Values in Cells Values Embedded in Formulas Values Embedded in Function Arguments Search Ranges The Scan Precedent Cells to Find Inputs options control whether or not TopRank automatically identifies inputs and inserts AutoVary functions When a Scan Precedent Cells to Find Inputs option
27. the input category is defined in a TopRank or GRISK function using the RiskCategory property function For more information on this function see the Listing of Property Functions in the Function Reference in this manual The Copy Input Across Category command takes the variation described for one input in a category and copies that variation across all inputs in the category This is useful when you wish to quickly assign a common variation in value across a category To copy an input across a category 1 Assign the variation in value you wish to copy to a single input in the category 2 Right click on the input you changed in the list and select the Arrange menu Copy Input Across Category command 3 Click OK to confirm the copy and all inputs in the category will be changed to match the copied input Model Menu How is the Model Window Table Created The Model Window table is set up automatically when you select to display the table When the table is displayed your worksheets are scanned or re scanned for TopRank Vary functions and RISK distribution functions As new functions are found they are added to the Inputs list This list summarizes all your input functions their parameters cell addresses and the names Note You can limit the workbooks and worksheets TopRank searches when identifying inputs and outputs by clicking the Search Ranges command in the Analysis Settings dialog Find Inputs tab Reference
28. 0 probability distribution functions available in RISK To define uncertainty in RISK you need to assign a distribution function to every value that you think is uncertain It s up to you the user to determine which inputs are uncertain and which distribution function describes the uncertainty TopRank defines uncertainty in your model using Vary functions Vary functions are simple they define possible values that an input can have without assigning probabilities to those values There are only two basic Vary functions in TopRank Vary and VaryTable TopRank can automatically define variable cells in your model every time you select an output You don t need to know which cells are uncertain or important TopRank identifies those cells for you RISK runs a Monte Carlo or Latin Hypercube simulation For each iteration or step every RISK distribution in the spreadsheet model takes on a new value determined by the probability distribution function To run a thorough analysis RISK needs to run hundreds sometimes thousands of iterations Appendix A Using TopRank With Other DecisionTools 149 Results Summary 150 TopRank runs a single or Multi Way sensitivity analysis During the analysis only one cell or a small number of cells varies at a time according to the values defined in the Vary function With TopRank only a few iterations are needed to study a large number of uncertain cells For each output defined
29. 2 B c D E F G H 1 K L M N o Minimum Maximum Output Input Output Input Rank Input Name Cell Value Change Value Value Change Value 1 TraininglnvestmentperWork E51 515 833 72 86 170 100 833 72 86 230 Factory 2 i Labor Cost rker LC Factory 2 E48 Number of Workers Factory 2 E54 49 583 15 00 212 5 67 083 15 00 287 5 E54 M M Tomado D57 Spider D57 Tornado E57 Spider E57 Tomado F57 Spider IMI m E48 524 583 57 86 1035 92 083 57 86 765 A different ranking is made for each output cell selected since inputs have different effects on each of your output cells A Vary function used for Labor Cost for Factory 1 for example can have a major effect on an output Profit for Factory 1 but no effect on an output Profit for Factory 2 Ranking is done based on the amount of change from the output s base value that was caused by changing the input Vary function to a new value The amount of change is measured as a percentage change This is calculated using the formula New Calculated Output Value Output Base Value Output Base Value Running a What If Analysis in TopRank Graphical Results from a One Way What If Analysis Three different types of graphs can be used to describe What If analysis results These are e Tornado graphs e Spider graphs e Sensitivity graphs Each graph provides an easily accessible method to view different aspec
30. 2 Naming 79 P Palisade Corporation 5 140 PrecisionTree 139 141 151 58 Profit Analyzing 55 R Range Settings 92 References 159 Reports Excel 109 Quick 110 Risk Analysis And TopRank 25 56 61 Sensitivity Analysis 26 Risk Functions Property Functions 127 133 34 RISKLock 133 RISKName 133 134 RISKOutput 80 127 136 RISKResultsGraph 110 S Sensitivity Analysis See What if Analysis Sensitivity Graphs 20 45 107 Show Expanded Toolbar Command 121 Simulation See Risk Analysis Spider Graphs 19 44 106 Step By Input Command 71 Student Version 6 System Requirements 6 T Table of Values Defining 57 Technical Support 4 6 Toolbar Icons Described 67 Toolbars RISK Add in 121 Expanded vs Collapsed 121 TopRank 139 141 143 49 Tornado Graphs 18 23 43 52 105 True EV 117 U Uninstalling RISK 7 V Variables Menu 71 VARY Function 33 35 VARYMULTI Function 48 59 VARYMULTITABLE Function 48 VARYTABLE Function 36 57 W What if Analysis And TopRank 16 Defined 15 35 Multi Way See Multi Way What if Analysis Results 17 42 Running 17 41 103 Why to Use 28 With G RISK Functions 62 What if Analysis Command 91 What if Command 103 167 168 Using RISK with PrecisionTree
31. 5 Output Total Profit 060 6 Base Value 76666 6667 x Spider Graph of Total Profit jer Worker LC D48 er Worker LC E48 yer Worker LC F48 rker LC G48 R 4 Tomado D60 Spider D60 Summary 3 u sey i ia Spider graphs provide more information about the variables than tornado diagrams For example spider graphs show the reasonable limits of change caused by each input variable and the unit impact of these changes on the outcome While tornado graphs may lead the decision maker to think that risk is proportional the slope of spider graphs demonstrates any unproportional changes in outcomes The number of variables used in a spider graph should not exceed seven but a limit of five is recommended to avoid clutter If your What If analysis contains a large number of input variables it is a good Running a What If Analysis in TopRank idea to plot them on a tornado graph first to determine which variables have the greatest impact Then use only these variables on your spider graph Sensitivity Graphs The sensitivity or What If graph is a simple diagram plotting input value used vs output value calculated The value of the selected input variable is plotted on the X Axis and the calculated output value is plotted on the Y Axis r m ee ee Book Microsoft Excel mx gt fs Home Insert Page Layout Formulas Data Review View Add Ins To
32. 51 UK Mail us a letter to Palisade Europe 31 The Green West Drayton Middlesex UB7 7PN United Kingdom If you want to contact Palisade Asia Pacific Email us at support palisade com au Telephone us at 612 9252 5922 AU Fax us at 61 2 9252 2820 AU Mail us a letter to Palisade Asia Pacific Pty Limited Suite 404 Level 4 20 Loftus Street Sydney NSW 2000 Australia Regardless of how you contact us please include the product name exact version and serial number The exact version can be found by selecting the Help About command on the TopRank menu in Excel Chapter 1 Getting Started Student Versions Telephone support is not available with the student version of TopRank If you need help we recommend the following alternatives e Consult with your professor or teaching assistant e Log on to http Aoww palisade com for answers to frequently asked questions e Contact our technical support department via e mail or fax TopRank System Requirements System requirements for TopRank 5 0 for Microsoft Excel for Windows include e Pentium PC or faster with a hard disk e Microsoft Windows 2000 SP4 Windows XP or higher e Microsoft Excel 2000 or higher Introduction Installation Instructions Removing TopRank from Your Computer General Installation Instructions The Setup program copies the TopRank system files into a directory you specify on your hard disk To run the Setup program in
33. 67 55 43 170 119 167 Factory 2 E51 48 42 917 44 02 1035 110 417 Factory 2 Training Im Work 51 167 33 26 255 102 167 33 26 Factory 3 F51 O Labor Cost per Worker LC 558 667 23 48 1380 94 667 23 48 Factory 3 F48 M gt M Tornado D60_ Spider D60 Summary 3 4 Ready Es a e g o Chapter 2 Overview of What If Analysis 17 Tornado Graphs 18 Graphs of What If Results Graphs are also important for both displaying the relative ranking of one input versus another using a tornado graph and a spider graph or displaying the impact of an individual input on results a sensitivity graph A tornado graph compares the effects of all inputs on results For each input listed on the Y Axis the length of the bar drawn indicates the amount of change the input caused on results As the input with largest effects and longest bar is shown at the top and those with less impact below the graph often takes the shape of a tornado edo c Book2 Microsoft Excel EN X Home inset Pagelayout Formulas Data Review View A i3 B ZX H analysis Settings f utilities i 35 Ge t l Report Settings isd Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions Add Ins TopRank e x Model Analysis Tools Al M n B c D E F G H 1 1 K L M N o P da TopRank Tornado Graph 2 Perfo
34. A locked function has a RiskLock property function which is used as an argument to the function in Excel Properties can also be specified or changed by adding property functions directly to the function For more information on property functions see Property Functions in the Reference TopRank Functions section of this chapter 84 Model Menu Multi Way changes a Vary or VaryTable function to its multi way form for use in a multi way what if analysis or removes a multi form TopRank functions can also be selected for inclusion in a Multi Way What If analysis by directly changing the functions in your spreadsheet to their Multi form For example Vary is changed to VaryMulti or VaryTable to VaryMultiTable To change RISK functions to their Multi form the word Multi is added to the end of the function name For example NormalMulti or DiscreteMulti are multi forms of the probability distributions Normal and Discrete Function Properties The Function Properties command allows you to add or change additional options for an entered function in the Properties window For more information on the Properties window see the Add Input command in this chapter Note the Properties window can also be displayed by clicking the dropdown arrow on a cell in the Function column of the Model Window table Edit Function in Excel The Edit Function in Excel command allows you to edit a function in Excel where you can easily add references to the enter
35. Chapter 3 Overview of TopRank 31 32 What If Analysis and Risk Analysis Introduction TopRank is a spreadsheet add in for Microsoft Excel It can be used for What If analysis on any pre existing or new spreadsheet To set up your What If analyses TopRank adds new custom Vary functions to the spreadsheet function set These functions specify how the values in your spreadsheet can be varied in a What If analysis for example 10 and 10 1000 and 500 or according to a table of values you ve entered TopRank can also run a fully automatic What If analysis It uses powerful auditing technology to find all possible values in your spreadsheet which could affect your results It can then change all these possible values automatically and find out which is most significant in determining your results Definition of Terms Before getting into the details of What If analysis with TopRank you should understand some of the special terms used in this chapter e Aninput is a constant value used in a cell or formula in your spreadsheet model that affects your results e An output is a cell on which you want to run a What If analysis that contains the result of spreadsheet calculations e The base case of an input is the number you entered in the spreadsheet when you first designed the model usually the most likely value e The minimum change is the possible downside or negative change you think an input can reasonably have e T
36. E51 34 167 55 43 170 119 167 55 43 Factory 2 E51 LaborCostperWorker LC E48 542 917 44 02 1035 110 417 44 02 Factory 2 E48 Training Investment per Work F51 51 167 33 264 255 5102467 33 26 Factory 3 F51 LaborCostperWorker LC F48 558 667 23 48 1380 94 667 23 48 Factory 3 F48 44 gt H Tornado DG0 Spider D60 Summary 3 A Ready The standard results of a one way What If analysis are a ranking of input variables by their impact on your outputs Input variables are all Vary VaryTable and RISK functions that were changed by TopRank during the What If analysis Outputs are output cells identified by you prior to the analysis If a subset of outputs was selected for reporting using the Report Settings dialog Output for Reports tab only those outputs will be reported on What If Analysis Menu Tornado Graphs A tornado graph compares the effects of all input variables on a given output The X Axis displays the percentage change in output value For each variable listed on the Y Axis a bar is drawn between the extreme values of the output as calculated using the lower and upper input values 3 id c Book Microsoft Excel NEN 5 amp Home Insert Page Layout Formulas Data Review View Add Ins TopRank o x c 339 H Analysis Settings f utilities i mj e i Report Settings amp Help Add AutoVary Add Model R
37. Edit Function in Excel F2 Profits Per Plant Fac Delete Del put S8 57 5 Range lt none gt Total Profit D60 RiskOutput Total Profit E OK Cancel The Default Categories command specifies how TopRank will automatically genetrate category names from input names Default category names are easily created from the default input names gereated by TopRank The section of this manual How Are Default Names Created describes how default names are generated for an input using a Row Heading and a Column heading in your spreadsheet The Row Heading portion of a default name is shown to the left of the 7 separator in the default name and the Column heading portion to the right of the separator The Default Categories options are as follows e Row Heading specifies that names which use a common Row Heading will be grouped together in a category e Column Heading specifies that names which use a common Column Heading will be grouped together in a category e None turns off any automatic generation of default categories Default Categories can also be created from input names entered using a RiskName function as long as a separator is included to separate text to use as row or column headings in the name For example the input Risk Vary 100 10 10 RiskName R amp D Costs 2010 Reference TopRank Add In Commands 87 Assign Input to Category Command Copy Input Across Category Command 88
38. If analysis output A ranking of how inputs affect results can be generated for an output cell Such a ranking is created by collecting the values calculated for the output cell each iteration of a What If analysis Output cells are also evaluated in a Multi Way What If analysis A ranking of how combinations of inputs affect results is generated for an output cell To add an output for a single cell select the cell in your spreadsheet and click the Add Output icon the one with the single red arrow TopRank Add Edit Output Cell D60 Nome foam o y Ol e Remove ok canei When an output is added you are given the opportunity to name it or use the default name TopRank has identified Optionally you can click the Reference entry button to select cell s in your spreadsheet with the name If you select a reference for a name the name from the referenced cell s will be shown in italics in the Add Edit Output dialog The name if not the TopRank default name is added as an argument to the RiskOutput function used to identify the output cell At any time a name may be changed by e Editing the name argument to the RiskOutput function in Excel e Changing the name in the Model Window list e Re displaying the Add Edit Output dialog with the cell selected in Excel and changing the name Reference TopRank Add In Commands 79 RiskOutput Functions How To Add a What If Analysis Ouiput Range 80 Whe
39. Inputs in Open Workbooks Name Category Labor Cost per Worker LC Labor Cost per Worker LC Factory 1 RiskAuto Vary gage Labor Cost per Worker LC Factory 2 RiskAutoVary l Arrange 15 Labor Cost per Worker LC Factory 3 RiskAutoVary Edit 15 Labor Cost per Worker LC Factory 4 RiskAutoVary amp Lock Input 15 Labor Cost per Worker LC Factory 5 RiskAutoVary Category Number of Workers Number of Workers Factory 1 RiskAutoVary Multi Way 15 p Function Properties Edit Function in Excel F2 E Delete Del OK Cancel Commands available on the right click menu in the Model Window include 15 v e Arrange The Arrange menu commands allow you to arrange inputs by categories and copy the variation in value for on input across all inputs in a category For outputs the Arrange commands allow you to arrange outputs by output range The Arrange icon at the bottom of the window can also be used to access the Arrange menu commands For more information on Arrange commands see The Arrange Menu in this section e Lock Input The Lock Input command specifies whether or not the Vary function or RISK distribution will be stepped across during a what if analysis If Lock is selected the Vary function or distribution will return its expected value during analysis This allows you to remove a Vary function or distribution from a what if analysis without deleting it from your model
40. List of Values eese ttt 57 Multi Way What If Analysis eere 59 Multi Way Analysis Results eee eese eeeeeetetntntntnn 60 Modeling with RISK Functions eeessessessss 61 For Users of TopRank Industrial eerte 61 What If Analysis with RISK Functions eere 62 Chapter 4 TopRank Modeling Techniques 53 54 Running a Multi Way What If Analysis in TopRank Modeling Critical Factors in Profitability Example Model FACTORY XLS One of the most frequently asked questions when studying a production process is What factors should I concentrate in order to maximize my profit The traditional way of answering this question is to go into a spreadsheet identify all possible factors that can influence results and vary each input factor manually to determine which variation of input brings most dramatic change to the output after recalculation With TopRank the identification and variation of input factors take place automatically Once you ve identified your output cell TopRank both identifies the inputs which could affect your results and varies them all automatically To see this in action with Excel with TopRank first load the example worksheet FACTORY XLS Then 1 Select cell D60 and identify this cell as an output by pressing the Add Output icon the icon with a single arrow 2 Pre
41. Palisade RISK45 Risk xla contains macros by P 5 Details Palisade Corporation Macros may contain viruses It is always safe to disable macros but if the macros are legitimate you might lose some Functionality The security level is set to High Therefore you cannot enable macros From sources that you do not trust Disable Macros Enable Macros More Info Installation Instructions Software Activation Frequently Asked Questions Activation is a one time license verification process that is required in order for your TopRank software to run as a fully licensed product An activation code is on your printed emailed invoice and may resemble a dash separated sequence like 19a0 c7c1 15ef 1be0 4d7f cd If you enter your Activation code during installation then your software is activated the first time the software is run and no further user action is required If you wish to activate your software after installation select the TopRank Help menu License Activation command and enter your activation code in the displayed Palisade License Activation dialog box Palisade License Activation Product RISK Industrial For Excel Purchase Serial Number Status Trial 17 days remaining View EULA Activate Software Activation ID 1930 c7c1 15ef 1be0 4d7f cd Aetra Manual via Email Deactivate Software Fulfillment ID NjA 1 What if my software is not activated If you do not enter an activation c
42. SK distribution was C10 RiskNormal 990 100 RiskStatic 1000 After swap out the formula would be C10 1000 If the value of C10 was changed while functions were swapped out to C10 2000 TopRank would swap the following function back in updating the static value C10 RiskNormal 990 100 RiskStatic 2000 If theSwap In option Only Where Formulas or Static Values Were Modified was selected TopRank would report this change prior to swapping in e Only Where Formulas Were Modified Only changes to be made that include a changed formula are reported with this option For example if the original TopRank and RISK distribution was in a formula C10 1 12 RiskNormal 990 100 RiskStatic 1000 After swap out the formula would be C10 1 12 1000 If the formula for C10 was changed when functions were swapped out to C10 1000 TopRank would swap the following formula and function back in C10 RiskNormal 990 100 RiskStatic 1000 Reference TopRank Add In Commands 119 Previewing Changes Prior to Swapping TopRank and RISK Functions In Swapping in Functions When a Workbook is Opened 120 If the options Only Where Formulas or Static Values Were Modified or Only Where Formulas Were Modified were selected TopRank would report this change prior to swapping in e None No changes to be made to the model are reported and TopRank automatically swaps in its recommended change TopRank creates a rep
43. TopRank How Many Multi Way Calculations Are Made Multi Way What If analyses are executed by TopRank automatically once all one way What Ifs are completed When executing a Multi Way What If TopRank first identifies all VaryMulti and VaryMultiTable functions in your spreadsheet Then using the Group Size you ve selected TopRank tries all combinations of input values possible As with a one way What If TopRank recalculates your spreadsheet for each combination and collects the new output values generated Once the Multi Way What If is completed TopRank ranks all calculated combinations according to their impact on each output you ve selected The number of iterations or recalculations required during a Multi Way What If depends on e The number of VaryMulti and VaryMultiTable functions in your spreadsheet e The Steps entered in VaryMulti functions or number of values in the table for each VaryMultiTable function If no Steps is entered in a VaryMulti function the default Multi Way What If of Steps entered in the Other tab of the Analysis Settings dialog is used e The Group Size selected In the Professional version Group Size is limited to 2 or 3 and there is no Group Size limit in the Industrial version The selections for Group Size and default of Steps used in a Multi Way analysis are made in the TopRank Analysis Settings Dialog TopRank Analysis Settings Input Defaults Find Inputs Other Multi Way What
44. Vary function depend on the minimum maximum range you entered for the function the number of steps and the distribution selected During What If calculations a new value is returned for each step of each Vary function For each new value a new result is calculated and collected from each selected output cell Once all steps have been calculated TopRank then returns the Vary function to its base value and begins changing the next entered Vary function Once all Vary function have been changed all are returned to their original base value and the ranking of results is performed Chapter 3 Overview of TopRank 41 What If Analysis Results for Two Outputs 42 Results from a What If Analysis The standard results of a one way What If analysis are a ranking of input variables by their impact on your outputs Input variables are all Vary and VaryTable function that were changed by TopRank during the What If analysis Outputs are output cells identified by you prior to the analysis Gi Book2 Minimum Maximum Output Input Output Input Rank _Input Name 1 Labor Cost Cell Value Change Value Value _Change Value D48 176 667 51 434 920 556 667 51 43 680 Vork D51 5159 167 36 439 85 74 167 36439 115 Factory 1 D51 Number of Workers Factory 1 DS4 134 167 15 009 575 99 167 15 00 425 p54 4 M Tornado D57 Spider D57 Tomado E57 Spider E57 Tomado F57 Spider Ml Gi Bool
45. Windows 2000 or higher 1 Insert the TopRank CD ROM in your CD ROM drive 2 Click the Start button click Settings and then click Control Panel 3 Double click the Add Remove Programs icon 4 On the Install Uninstall tab click the Install button 5 Follow the Setup instructions on the screen If you encounter problems while installing TopRank verify that there is adequate space on the drive to which you re trying to install After you ve freed up adequate space try rerunning the installation If you wish to remove TopRank from your computer use the Control Panel s Add Remove Programs utility and select the entry for TopRank The DecisionTools Suite TopRank for Excel is a member of the DecisionTools Suite a set of products for risk and decision analysis described in Appendix A Using TopRank With Other DecisionTools The default installation procedure of TopRank puts TopRank in a subdirectory of a main Program Files Palisade directory This is quite similar to how Excel is often installed into a subdirectory of a Microsoft Office directory One subdirectory of the Program Files Palisade directory will be the TopRank directory by default called TOPRANKS This directory contains the program files plus example models and other files necessary for TopRank to run Another subdirectory of Program Files Palisade is the SYSTEM directory which contains files which are needed by every program in the DecisionTools Suite inc
46. __pektccutmrerse The Add Output Range dialog allows you to name your range as a whole plus set the names for individual cells in the range Optionally you can click the Reference entry button to select cell s in your spreadsheet with the name A range of names from Excel can be applied to all individual cells in the output range by e Selecting a block of names in the Add Output Range dialog and clicking the Reference entry button Each output cell is matched the name from a cell in the selected range Model Menu 3 Click OK and the selected range of cells will be added as outputs and RiskOutput functions will be entered 4 To view the outputs in the Model Window list click the Model Window icon the one with both a blue and red arrow or select the TopRank Model menu Model Window command Use the Insert or Remove buttons to remove individual cells from an output range Removing an To remove an output simply select the output cell in your spreadsheet Output and click the Add Output Icon Click the Remove or Delete Output Range buttons to delete the output Alternatively you can use the Delete command on the right click menu in the Model Window to delete outputs Reference TopRank Add In Commands 81 Model Window 82 Model Window Command Displays the Inputs and Outputs table listing output cells Vary functions and RISK distribution functions in your workbook s The Model Window lists all selected output cel
47. a perfect companion to PrecisionTree RISK allows you to 1 quantify the uncertainty that exists in the values and probabilities which define your decision trees and 2 more accurately describe chance events as a continuous range of possible outcomes Using this information RISK performs a Monte Carlo simulation on your decision tree analyzing every possible outcome and graphically illustrating the risks you face Using RISK to Quantify Uncertainty With RISK all uncertain values and probabilities for branches in your decision trees and supporting spreadsheet models can be defined with distribution functions When a branch from a decision or chance node has an uncertain value for example this value can be described by an RISK distribution function During a normal decision analysis the expected value of the distribution function will be used as the value for the branch The expected value for a path in the tree will be calculated using this value However when a simulation is run using RISK a sample will be drawn from each distribution function during each iteration of the simulation The value of the decision tree and its nodes will then be recalculated using the new set of samples and the results recorded by RISK A range of possible values will then be displayed for the decision tree Instead of seeing a risk profile with a discrete set of possible outcomes and probabilities a continuous distribution of possible outcomes is
48. a spreadsheet This analysis identifies which variables affect your results the most This shows you those factors you should be most concerned with as you 1 gather more data and refine your model and 2 manage and implement the situation described by the model TopRank is a spreadsheet add in for Microsoft Excel It can be used with any pre existing or new spreadsheet To set up your What If analyses TopRank adds new custom Vary functions to the spreadsheet function set These functions specify how the values in your spreadsheet can be varied in a What If analysis for example 10 and 10 1000 and 500 or according to a table of values you ve entered TopRank can also run a fully automatic What If analysis It uses powerful auditing technology to find all possible values in your spreadsheet which could affect your results It can then change all these possible values automatically and find out which is most significant in determining your results TopRank Applications Multi Way What If Analysis TopRank applications are the same as spreadsheet applications If you can build your model in a spreadsheet you can use TopRank to analyze it Businesses use TopRank to identify the critical factors price up front investment amount sales volume or overhead that most affect the success of their new product Engineers use TopRank to show them the individual product components whose quality most affects final product production r
49. actory 1 Reference TopRank Add In Commands 107 108 What If Analysis Menu Report Settings Command Specifies the type of reports and graphs that will be generated in Excel for each output the location of those reports and the outputs and inputs that will be reported on The Report Settings command allows you to specify the location and type of reports that will be generated by TopRank In addition you can select to limit reporting to only specific outputs in your workbooks and set criteria for selecting which inputs to report on Reference TopRank Add In Commands 109 Reports Tab Report Settings Command Specifies the type of reports and graphs to be generated by TopRank and their the location The Reports tab in the Report Settings dialog allows you to specify the location and type of reports that will be generated by TopRank TopRank Report Settings Reports Outputs for Reports Inputs for Reports For Each Output Report V Tornado Graph IV Spider Graph Sensitivity Graphs One for Each Input vs the Output Detail for All Inputs For Entire Run Report Detail By Input All Input Values vs All Outputs Options I On Graphs Show Output Values as Change from Base A variety of different pre built reports are available directly in Excel at the end of a What If analysis The For Each Output Report options include e Tornado graphs graphically display the the key inputs af
50. al results of your spreadsheet when TopRank is not in use As with standard spreadsheet functions Vary functions can be used by themselves in a cell or as part of a formula Multiple Vary functions can be present in a cell and Vary functions can be arguments to other functions Chapter 3 Overview of TopRank 35 VaryTable Functions Identifying Inputs in Formulas 36 If there is a table of possible values you want to try for an input the VaryTable function is used With VaryTable you simply enter a base value and a reference to the location in the spreadsheet that contains your table of values Alternatively you can also enter the table of values directly in the function itself Typical VaryTable functions are e RiskVaryTable 100 G1 G50 where 50 values located in the range G1 G50 are successively returned by the VaryTable function during the What If analysis and new output values calculated for each e RiskVaryTable 50 40 42 5 48 52 58 where five different values 40 through 58 are successively returned by the VaryTable function during the What If analysis and new output values calculated for each Letting TopRank Define What If Variables Vary and VaryTable functions can be entered directly in your spreadsheet by you just as are any standard spreadsheet functions TopRank however also includes a powerful automatic mode where Vary functions are automatically inserted in your spreadsheet for all input variab
51. ample the TopRank function RiskVary 100 10 10 using a default distribution setting of Uniform and a default range type of percentage is sampled like the RISK distribution RiskUniform 90 110 VaryTable functions from TopRank are sampled as RiskDuniform functions in RISK Using RISK with TopRank The Similarities The Differences Inputs Calculations The Differences Between TopRank and RISK TopRank and RISK share many common features so it s easy to think that they perform the same functions In fact the two programs perform different but complementary tasks Don t ask yourself Which should I use RISK or TopRank ask yourself Shouldn t I use both Both RISK and TopRank are add ins for analysis of models designed in spreadsheets By using special spreadsheet formulas both programs explore how uncertainty affects your model and thus the decisions you make And a common user interface guarantees a smooth transition between the two products one learning curve instead of two There are three main areas where RISK and TopRank differ e Inputs how uncertainty is defined in your model e Calculations what happens during an analysis e Results what types of answers the analyses provide RISK defines uncertainty in your model using probability distribution functions These functions define all the possible values an input can have with a corresponding probability of that value occurring There are over 3
52. analysis each bar in the Tornado represents the swing in results caused by changes in input values For a Multi Way tornado each bar represents the minimum and maximum changes caused by a combination of a set of inputs e a ge 8ooi3 Microsoft Excel S nome men Page layout Formutes Data Renew View Adain TopRank ox P i re E ga sans settings Su aA utimie u i Report Settings 5d e ay Help Add AutoVwy Add Medel Run What Swap Input Functions gt Output Window Analysis Functions Moses Anat too Al T f Um Output DRE Multi Way KE URN retry morum IaM 4 ModekFactery 5 Oak a a DO Tornado Chart for Molti Fus Alit of Total Profit CEES Tomado Mutt HS7 Tornado Multi D60 Detab Muti OS Detai Mots E57 JE Resay ERES The number of inputs used in each combination is given by the Group Size setting If you have specified that your Group Size equals the number of Multi Way inputs no Tornado graph is available Running a Multi Way What If Analysis in TopRank Chapter 4 TopRank Modeling Techniques Modeling Critical Factors in Profitability 55 Adjusting the Model with the Lock Command 55 Further Analysis of the Model using RISK 2 56 Using the VaryTable Function eren 57 Defining a Table of Values eese 57 Defining a
53. and Outputs command View or change TopRank settings for identifying and changing inputs and running multi way analyses Command equivalent Analysis menu Analysis Settings command Display reporting options Command equivalent Analysis menu Report Settings command Run a what if analysis Command equivalent Analysis menu Run command Swap Functions in and out Command equivalent Swap Functions command Display TopRank Utilities Command equivalent Utilities commands Display Help options Command equivalent Help commands Reference TopRank Icons Reference TopRank Add In Commands Introduction This section of the TopRank Reference Guide details the available TopRank commands as they appear on the TopRank add in menu in Excel Commands are discussed as they appear on the menu TopRank icons can be used to perform many of the available commands The Reference TopRank Icons section of this chapter gives the command equivalents for each TopRank icon Several TopRank commands are also available in a pop up floating menu that is displayed when the right mouse button is clicked on a cell in Excel Reference TopRank Add In Commands 69 70 Introduction Model Menu Add Input Command Add or change TopRank and RISK functions in the selected cell formula The Model menu Add Input command displays the formula for each cell you select while the Add Input window is displayed The Add Input window makes it easy to mod
54. ange additional options for an entered function Input Properties D48 Change from Base Triang Steps s Category Multi Way Loc Options in the Properties window include Type of Range selects the type of change described by your entered Min Max range Typically you will use a percentage change Change from Base but optionally you can use an absolute change Change from Base enter actual Min Max values for the range Actual Min and Max or use a table of values Table of Values Excel Range or Table of Values actuals with a VaryTable function RISK Distribution enters a probability distribution supported by RISK Distribution is a probability distribution type which shows the likelihood of a value in an input s minimum maximum range occurring Steps are the number of values across an input s minimum maximum range to be used in a What If analysis Category assigns the input to a category to be used in grouping related inputs together For more information on Categories see Using Input Categories in the Model Window command listing Multi Way changes a Vary or VaryTable function to its multi way form for use in a multi way what if analysis Lock locks or unlocks an input Locking keeps an input from being changed in an analysis Reference TopRank Add In Commands 73 For more information on the possible settings for these arguments to Vary and VaryTable functions see the sectio
55. ar in Excel 2003 and earlier and or in TopRank dialog boxes Icon Function Performed and Command Equivalent x Adds a TopRank input Command equivalent Model menu Add Input command p Adds or removes AutoVary functions Command equivalent Model menu Add or Remove AutoVary Functions commands da Adds a TopRank output 2 E Displays a table of TopRank inputs and outputs Ha View or change TopRank settings for identifying and changing inputs and running multi way analyses Command equivalent What If Analysis menu Analysis Settings command E Display reporting options Command equivalent Report Settings command E Run a what if analysis Qe Swap Functions in and out 4 Display TopRank Utilities 2 Display Help options Command equivalent Help commands TopRank Reference Guide 68 The following icons are shown on the TopRank ribbon in Excel 2007 Icon AutoVary Functions Model Window Analysis Settings t Report Settings Y Run What If Analysis i Swap Functions 4f Utilities Help Function Performed and Command Equivalent Adds a TopRank input Command equivalent Model menu Add Input command Adds or removes AutoVary functions Command equivalent Model menu Add or Remove Auto Vary Functions commands Adds a TopRank output Command equivalent Model menu Add Output command Displays a table of TopRank inputs and outputs Command equivalent Model menu Table of Input
56. are identified as the most critical to the model TopRank Industrial also includes 35 probability distribution functions found in RISK These functions can be used along with Vary functions to describe variation in spreadsheet values TopRank functions are entered wherever you want to try different values in a What If analysis The functions can be added to any number of cells in a spreadsheet and can include arguments which are cell references and expressions providing extreme flexibility in defining variation in value in spreadsheet models In addition to adding Vary functions yourself TopRank can automatically enter Vary functions for you Use this powerful feature to quickly analyze your spreadsheets without manually identifying values to vary and typing in functions When automatically entering Vary functions TopRank traces back through your spreadsheet and finds all possible values which could affect the result cell you identify As it finds a possible value it substitutes in an AutoVary function with the default variation parameters such as 10 and 10 you ve selected With a set of AutoVary functions inserted TopRank can then run its What If analysis and rank the values which could affect your results by their importance With TopRank you can step through your Vary and AutoVary functions and change the variation each function specifies Asa default you can use a 10 and 10 variation but for a certain value you may
57. at If Analysis and Risk Analysis Chapter 3 Overview of TopRank Introduction 4 sees eene nnne nennen nnn 33 Definition of Terms eese eese esent E E is 33 What is What If Analysis e eeeeeeeeeeeeeeener enne 35 Defining a What If Input eese ettet tenente tente ntn 35 Letting TopRank Define What If Variables e 36 Steps and Distribution eee eese stnt netten tn osso 38 Adding Inputs by Cell eese eere eee tete tntntntn tnn 39 Looking at All Inputs and Outputs in Open Workbooks 40 Running a What If Analysis in TopRank eese 41 Results from a What If Analysis eese ttes 42 Graphical Results from a One Way What If Analysis 43 Tornado Graphs eee s nonai ia raK eA EEIN tutu SES 43 Spider Graph csssscececsesessesssssssessssssssssesesesesesesssesssnsnseseseesseeseeesenes 44 Sensitivity Graphs ccccssssssesscesessssssesssesesesesssssessssstsssessseseeseeesees 45 What is Multi Way What If Analysis eese 47 Defining Multi Way What If Inputs eese 48 Running a Multi Way What If Analysis in TopRank 49 Results from a Multi Way What If Analysis eese 50 Graphical Results from a Multi Way What If Analysis 52
58. ates A loan officer can have TopRank quickly run his model at all possible interest rate loan principle amount and down payment combinations and review results for each possible combination Whether your application is in business science engineering accounting or other field TopRank can work for you to identify the critical variables which affect your results Modeling Features As an add in to Microsoft Excel TopRank links directly to Excel to add What If analysis capabilities The TopRank system provides all the necessary tools for conducting a What If analysis on any spreadsheet model And TopRank works in a style you are familiar with Excel style menus and functions What If analysis and Data Tables are functions that can be performed directly in your spreadsheet but only in a manual unstructured format Simply changing a cell value in your spreadsheet and calculating a new result is a basic What If analysis And a Data Table which gives a result for each combination of two values can also be built in your spreadsheet TopRank however performs these tasks automatically and analyzes their results for you It instantly performs What Ifs on all possible values in your spreadsheet which could affect your result instead of requiring you to individually change values and recalculate It then tells you what spreadsheet value is most significant in determining your result TopRank also runs data table combinations automatically wi
59. available using TopRank s companion product RISK is often the next analysis performed on a spreadsheet after a What If analysis What If analyses initially identify what s important in your model You can then focus on these important components to better estimate what their values could be Usually however there are several or more of these important uncertain components and in reality they could all vary at the same time To analyze an uncertain model such as this you need risk analysis or Monte Carlo simulation Risk analysis varies all uncertain inputs simultaneously just as they do in real life and builds a range and distribution of the possible results that could occur With risk analysis inputs are described with a probability distribution such as normal lognormal beta or binomial This is a much more detailed description of the uncertainty present in an input s value than a simple or percentage variation A probability distribution shows both the range of values possible for an input and the likelihood of occurrence of any value in the range Simulation combines these input distributions to generate both a range of possible results from your model and the likelihood of any result occurring WI enisk Output D60 Total Profit Bn Minimum 153122 2595 Maxmum 311429 7744 Mean 76684 3524 StdDev 70542 5589 Values 1000 bi 5 3 5 gt 2 o 2 o o 2 o 2 o a 2 S n s n n Ss n S n S n D
60. ay What If options include e Group Size The number of inputs to be included in each combination tested in a Multi Way analysis is determined by the Multi Way Group Size setting By default all VaryMulti and VaryMultiTable functions in a spreadsheet are included in a Multi Way What If analysis For example you might have four inputs defined using VaryMulti and VaryMultiTable functions price sales volume production costs and investment and you want to see the impacts of every combination of two inputs on your result profit In this case the Group Size is two Your analysis might tell you that price and sales volume varying together are the most significant combination affecting your result profit Setting the Group Size to three might identify the combination of price sales volume and investment as the most important three way combination of inputs on results A Group Size of Auto forces the Group Size to equal the number of all VaryMulti and VaryMultiTable functions in a spreadsheet Use this setting if you want to force TopRank to calculate every possible combination of input values Note Group Size is a default entry that is set at the start of a Multi Way What If analysis If you want Multi Way What If results with a different Group Size than was calculated in your current results simply change the default Group Size and re run the analysis e of Steps The of Steps entry specifies the number of steps TopRank
61. both decision trees and influence diagrams Influence diagrams are excellent for showing the relationship between events and the general structure of a decision clearly and concisely while decision trees outline the chronological and numerical details of the decision In PrecisionTree all decision model values and probabilities are entered directly in spreadsheet cells just like other Excel models PrecisionTree can also link values in a decision model directly to locations you specify in a spreadsheet model The results of that model are then used as the payoff for each path through the decision tree All calculations of payoff happen in real time that is as you edit your tree all payoff and node values are automatically recalculated PrecisionTree s decision analyses give you straightforward reports including statistical reports risk profiles and policy suggestions PrecisionTree Pro only Also decision analysis can produce more Appendix A Using TopRank With Other DecisionTools amp 153 Sensitivity Analysis Reducing a Tree Utility Assessment Advanced Analysis Capabilities 154 qualitative results by helping you understand tradeoffs conflicts of interest and important objectives All analysis results are reported directly in Excel for easy customization printing and saving There s no need to learn a whole new set of formatting commands since all PrecisionTree reports can be modified like any othe
62. by each input variable and the unit impact of these changes on the outcome While tornado graphs may lead the decision maker to think that risk is proportional the slope of spider graphs demonstrate any unproportional changes in outcomes The number of variables used in a spider graph should not exceed seven but a limit of five is recommended to avoid clutter If your What If analysis contains a large number of input variables it is a good idea to plot them on a tornado graph first to determine which variables have the greatest impact Then use only these variables on your spider graph What If Analysis Menu Sensitivity Graphs The sensitivity or What If graph is a simple diagram plotting input value used vs output value calculated The value of the selected input variable is plotted on the X Axis and the calculated output value is plotted on the Y Axis The base case is always noted r 3 es o gt cg Books Microsoft Excel NC X Home Inset Page layout Formulas Data Review View Addns TopRank 7 x RCRA Ye roomed eT SE o 5i Report settings id Help Add AutoVary Add Model Run What f Swap Input Functions Output Window Analysis Functions Model l Analysis Tools Sensitivity Graph Total Profit vs Labor Cost per Worker LC Factory 1 160000 140000 120000 100000 80000 60000 g gt 40000 20000 Value of Labor Cost per Worker LC F
63. calculates across the minimum maximum range of Vary and distribution functions during a Multi way What If analysis A different default number of steps value is used for a Multi Way What If analysis as opposed to a standard one way What If analysis Typically you want fewer Multi Way steps as compared to one way steps the number of steps must be greater or equal to two This minimizes the number of recalculations or iterations required to complete a Multi Way What If analysis 100 What If Analysis Menu Multiple Runs Multiple runs can be executed for any What If analysis in TopRank When the of Runs setting is greater than one TopRank sequentially executes the desired number of What If analyses one after another Multiple runs are used to run several What If analyses on the same model using different assumptions for each analysis Compare the results from each run to see the effect of the changing assumptions on the What If results Assumptions can be changed by run by using the SIMTABLE function Each run a new value is used for every SIMTABLE function in the model The SIMTABLE function takes a list of values as arguments such as RiskSimtable 100 200 300 400 Each run the SIMTABLE function returns the argument whose position in the list is the run number In the above SIMTABLE function the value 100 is returned the first run 200 the second and so on The of Runs defined should be less than or equal to the number of argume
64. cant impact on the model A probability distribution where any value between the minimum and maximum is possible has finite probability See discrete distribution The set of points each of which equals the integral of a probability distribution starting at the minimum value and ending at the associated value of the random variable A dependent variable is one that depends in some way on the values of other variables in the model under consideration In one term the value of an uncertain dependent variable can be calculated from an equation as a function of other uncertain model variables Alternatively the dependent variable can be drawn from a distribution based on the random number which is correlated with a random number used to draw a sample of an independent variable Indicates that there is no uncertainty associated with a given value or variable A sensitivity analysis where the variable is a payoff related to an event or events See Event Sensitivity Analysis Probabilistic Sensitivity Analysis Number of inputs being varied together in a Multi Way What If analysis Dimensions in Multi Way What If analysis is equivalent to the Multi Way Group Size A probability distribution where only a finite number of discrete values are possible between the minimum and maximum See continuous distribution Using RISK with PrecisionTree Input Most Likely Value Mean Multi Way Group Size Multi Way Sensitivity Analysi
65. cted based on the branch probabilities entered This method does not require that distribution functions be present in the model however if they are used a new sample is generated each iteration and used in path value calculations The output from the simulation is the cell containing the value of the model such as the value of the root node of the tree At the end of the run an output distribution reflecting the possible range of out values for the model and their relative likelihood of occurrence is generated Using Probability Distributions in Nodes Let s take a look at a chance node in an oil drilling decision tree EV 22 900 Dry 80 000 43 Drill Wet 40 000 34 Open Soaking 190 000 23 Don t Drill 10 000 The results of drilling are divided into three discrete outcomes Dry Wet and Soaking But in reality the amount of oil found should be described with a continuous distribution Suppose the amount of money made from drilling follows a lognormal distribution with a mean of 22900 and a standard deviation of 50000 or the RISK distribution RiskLognorm 22900 50000 Appendix A Using TopRank With Other DecisionTools amp 157 Drilling Decision Witha Probability Distribution Decision Forcing During Simulation Value of Perfect Information 158 To use this function in the oil drilling model change the chance node to have only one branch and the value of the branch is defined by the
66. del Window table These names are created by scanning the spreadsheet around the cell where the input or output is located To identify names TopRank moves from the input or output cell across the row of the spreadsheet to the left and up the column towards the top It moves across these ranges of the spreadsheet until it finds 1 a label cell or a cell without a formula in it or 2 a recognizable sequence of values such as years It then takes these row and column headings and combines them to create a possible name for the input or output Many times in standard spreadsheets with row labels down the left and column labels across the top this process results in accurate names However in some spreadsheets automatic naming creates nonsensical labels In these cases you must edit the names displayed in the Model Window table in order to enter more meaningful names Any names you define are entered as a RiskName property function which is used as an argument to the function in Excel Properties can also be specified or changed by adding property functions directly to the function For more information on property functions see Property Functions in the Reference TopRank Functions section of this chapter Reference TopRank Add In Commands 83 Model Window The right click menu in the Model Window can be used to access Right Click addional options for the selected items in the table Menu TopRank Model Inputs outputs Model
67. e TopRank Functions 137 138 Using RISK Functions Appendix A Using TopRank With Other DecisionTools Palisade s DecisionTools Suite is a complete set of decision analysis solutions for Microsoft Windows With the introduction of DecisionTools Palisade brings you a decision making suite whose components combine to take full advantage of the power of your spreadsheet software The DecisionTools Suite The DecisionTools Suite focuses on providing advanced tools for any decision from risk analysis to sensitivity analysis to distribution fitting Software packaged with the DecisionTools Suite includes e QGRISK risk analysis using Monte Carlo simulation e TopRank sensitivity analysis e PrecisionTree decision analysis with decision trees and influence diagrams e NeuralTools neural networks in Excel e Evolver genetic optimization in Excel e StatTools statistics in Excel While all the tools listed above can be purchased and used separately they become more powerful when used together Analyze historical and fit data for use in an RISK model or use TopRank to determine which variables to define in your RISK model This chapter explains many of the ways the components of the DecisionTools suite interact and how they will make your decision making easier and more effective Note Palisade also offers a version of RISK for Microsoft Project RISK for Project allows you to run risk anal
68. e enclosed in quotes Names may also be included by referencing cells with labels in them Position must be a positive integer gt 1 Note All outputs added with TopRank will also be recognized when running a simulation with RISK Listing Output Function Using RISK Functions RISK s probability distribution functions maybe used in TopRank Industrial to describe input variables When an RISK distribution function is used in TopRank Industrial values across the percentiles of the entered probability distribution are used in a What If analysis Using RISK distributions you can 1 Describe variation in value that cannot be precisely described by a Vary function and 2 Conduct a What If analysis on a model that was previously used in an RISK simulation For more information on RISK distribution functions see your RISK User s Guide Using RISK Functions in a Multi Way What lf Analysis Most RISK functions can be included in a TopRank Multi Way What If analysis by including the word Multi at the end of the function name but before the first parenthesis that surrounds the function arguments For example RiskNormalMulti 100 10 would identify a normal distribution to include in a Multi Way What If analysis Inputs can also be identified in the Model Window table using the Multi Way button The following RISK functions cannot be defined as Multi Way inputs e CURRENTITER e CURRENTSIM e SIMTABLE Referenc
69. ecision Analysis Introductory Lectures on Choices Under Uncertainty Reading MA Addison Wesley 1968 Winston Wayne Operations Research Applications and Algorithms 3rd Ed Belmont CA Duxbury Press 1994 Albright S C et al Managerial Statistics Pacific Grove CA Duxbury Thomson Learning 2000 Technical References to Sensitivity Analysis Eschenbach T G 1992 Spiderplots versus Tornado Diagrams for Sensitivity Analysis Interfaces 22 40 46 French S 1992 Mathematical Programming Approaches to Sensitivity Calculations in Decision Analysis Journal of the Operational Research Society 43 813 819 160 Using RISK with PrecisionTree Appendix B Recommended Readings 161 Appendix C Glossary of Terms RISK Base Case BestFit Critical Inputs Critical Combinations Continuous Distribution Cumulative Distribution Dependent Variable Deterministic Deterministic Sensitivity Analysis Dimensions Discrete Distribution 162 Pronounced at risk risk analysis add in for Excel from Palisade Corporation The state of a decision model before a sensitivity analysis is run when all variables are set to their most likely value Distribution fitting add in for Excel from Palisade Corporation Input factors in a spreadsheet model that can produce significant impact on the output when varied Combinations of inputs in a Multi Way What If analysis that produce signifi
70. ed Introduction E E enne Installation Instructions sees Software Activation seen On line Tutorial ceres Using TopRank eene Chapter 2 Overview of What If Analysis Introduction eese nnn Running a What If Analysis Multi Way What If Analysis What lf Analysis and Risk Analysis Chapter 3 Overview of TopRank Introduction esee nnn What is What If Analysis sees Running a What If Analysis in TopRank What is Multi Way What If Analysis Running a Multi Way What If Analysis in TopRank 13 15 17 21 25 31 TOTO 35 USE 47 vi Chapter 4 TopRank Modeling Techniques Modeling Critical Factors in Profitability Using the VaryTable Function eere Multi Way What If Analysis eese Modeling with RISK Functions sees TopRank Reference Guide Introduction 22 adeo ese tede pa dd aT area gates Reference TopRank lcons eene Reference TopRank Add In Commands Introduction
71. ed by a rise in a second such as crop yield As with the standard one way What If analysis spreadsheet users often conduct Multi Way What If analyses manually Each of several inputs can be changed to their maximum possible value at the same time calculating an optimistic scenario for results Conversely the inputs then can be changed to their minimum possible values creating a pessimistic scenario Calculating more than a small number of Multi Way combinations manually with a spreadsheet becomes very cumbersome TopRank automates Multi Way What If analyses trying all possible combinations of inputs you select tracking results calculated at each combination and ranking them according to their impact on results A Multi Way What If analysis can include 1 a varying number of total inputs and 2 a varying number inputs used in each combination For example you might have four inputs price sales volume production costs and investment and you want to see the impacts of every combination of two inputs on your result profit Your analysis might tell you that price and sales volume varying together are the most significant combination affecting your result profit Chapter 2 Overview of What If Analysis 21 Multi Way What If Analysis Results As with one way What If analyses Multi Way What If analysis results can be displayed in tables and graphs A table lists the combinations of inputs that were tested and the correspond
72. ed formula Delete Delete removes any Vary function or RISK distribution entry from the Model Window table Although entries are removed from the Model Window with Delete they are not actually removed from the cell formulas in your workbook until the OK button is clicked dismissing the Model Window Reference TopRank Add In Commands 85 86 Arrange Menu The Model Window Table may be arranged by category or output range name using the commands on the Arrange Menu For inputs typically a category defines a group of related inputs such as Development Costs or inputs in the year 2010 TopRank can automatically assign Default Categories to inputs based on their names or you can assign inputs to categories using the Category Assign command When Categories are used a common variation in value may be assigned to all inputs in a category by using the Copy Input Across Category command The contents of the Arrange menu change depending on whether you are viewing the Inputs tab or the Outputs tab The only option for the Outputs tab is to Arrange Outputs by Range Name TopRank Model DER Inputs Outputs Model Inputs in Open Workbooks Inputs 15 Outputs 1 Name Cel Function Min Base Max x Category Labor Cost per Worker LC Labor Cost per Worker LC a s Labor Cost per Worker LC v Group Inputs By Category nn 15 Labor Cost per Worker LC 44 Edit Default Categories ld V Row Heading 15
73. ee R netten toten teinte tete tn tn n 132 Listing Property Functions eene 133 RAS LOCK suan E heveuessidigacesteveioesitutase ievensesdebeaevtevense 133 IMEEM E 133 JHEIRG IT UR 134 Listing Output Function eere 135 june an 136 Using RISK Functions eeeeeeeeeeeeee essen eren nnne 137 Using RISK Functions in a Multi Way What If Analysis 137 Introduction All TopRank operations are performed using menus and commands You can execute these commands with the menu bar the command icons or by right clicking on the appropriate window This chapter contains descriptions of each command as well as a section describing the toolbar icons The commands in this chapter are presented in the order in which they appear on the menus TopRank Menu Commands Menu commands are discussed as they appear on the TopRank menu in Excel 2003 and earlier TopRank icons can be used to perform many of the available commands The TopRank Toolbar Icons section of this chapter gives the command equivalents for each TopRank icon TopRank Reference Guide 65 66 Introduction Reference TopRank Icons TopRank icons are used to quickly and easily perform tasks necessary to set up and run What If analyses TopRank icons appear as a custom toolbar in Excel 2003 or earlier and on a ribbon in Excel 2007 The following icons are shown on the TopRank toolb
74. effects of an individual input on results can be plotted with a standard line graph The value of the selected input is plotted on the X Axis and the value of results is plotted on the Y Axis This simple X Y plot clearly illustrates how results change in conjunction with changes in the underlying input The graph also shows if changes are constant or linear or if results start to change more dramatically with increases in input value 2 3 4 Modet Factory 5 Output Total Profit D60 6 Base Value 75565 6667 7 8 9 Sensitivity Graph Total Profit vs Labor Cost per Worker LC Factory 1 Value of Labor Cost per Worker LC Factory 1 Running a What If Analysis Multi Way What If Analysis Changing More Than One Input Critical Combinations There are many cases when you may want to see the impacts of combinations of changes in two or more inputs on results This is called a Multi Way What If analysis Multi Way What If analyses vary inputs at the same time and calculate the effect of each combination of input values on results Multi Way What If analyses deal with the problem that inputs in real life do not vary one at a time as is assumed by the standard one way What If At the same time one input can differ positively from what was expected while a second can differ negatively Inputs can also vary jointly as a rise in one such as rainfall often is accompani
75. efore using RISK to save time and effort Chapter 2 Overview of What If Analysis 27 28 Why What lf First Risk analysis is a more robust and comprehensive analytical technique when compared with What If analysis as it varies all inputs simultaneously It also accounts more explicitly for interrelationships among inputs in the results it generates And with its own form of sensitivity analysis it identifies the most important inputs which affect your results But even with all this why is What If analysis so popular and why should it still be used in conjunction with risk analysis e It s Easy There s no doubt about it a What If analysis is easy From the simple manual What If performed by most every spreadsheet user to the automated extensive What If processing of TopRank a What If analysis can be performed quickly and easily Describing your inputs in terms of a possible and change is easier and more understandable for many when compared with the probability distributions required by risk analysis e It Saves Time for a Subsequent Risk Analysis Quickly identifying the most critical inputs with What If analysis saves time when developing a risk analysis model You can then concentrate your efforts in defining probability distributions to those most important inputs Describing the range and shape of a probability distribution can be time consuming and it s easier if you can focus on a more limited set of inputs e Its
76. ell D29 that contains the VaryTable function i e RiskVaryTable most likely value location of the table in Excel In our example the function reads RiskVaryTable 480 B43 B54 3 During a What If analysis TopRank recalculates the spreadsheet using each of the values in the range B43 B54 a total of 12 values and 12 recalculations Defining a List of Values If your values are not listed in a table in the spreadsheet TopRank allows a list of possible values in the function itself To change the VaryTable function in D29 from a reference to a list of values e Type RiskVaryTable 480 200 400 550 100 800 900 650 350 570 225 450 570 Chapter 4 TopRank Modeling Techniques 57 58 If a VaryTable function is involved in a sensitivity analysis TopRank shows the effect of every value in the table on the output In this example TopRank s Detail Result window shows the effect that every value in the Shipping Cost table has on the Total Profit A Shipping Cost of 100 for car Model A increases the Total Profit by 30 while a Shipping Cost of 900 decreases the Total Profit by 32 Using the VaryTable Function Multi Way What If Analysis Example Model MULTIWA Y XLS Individual inputs that do not seem critical in a model may become important once they are varied collectively with other factors Therefore an analyst is often interested in knowing how combinations of factors affect the output instead of just
77. eps With four steps TopRank calculates the spreadsheet at the minimum possible value for the Vary function the maximum possible value and two values spaced in between If you enter a Steps value in the Vary function such as RiskVary 100 10 10 8 TopRank overrides the default Steps and in this case return eight different values for the Vary function Reference TopRank Add In Commands 77 78 Add AutoVary Functions Command Defines AutoVary functions for inputs which could affect the selected output The Model menu Add AutoVary Functions Command adds AutoVary functions for outputs in your worksheets First input values which affect outputs are identified by TopRank using the criteria specified using the What If Analysis menu Analysis Settings command Then AutoVary functions are substituted for identified input values For more information on the criteria for identifying inputs see the What If Analysis menu Analysis Settings command Remove AutoVary Functions Command Removes all AutoVary functions from open workbooks The Model menu Remove AutoVary Functions Command removes all AutoVary functions from open workbooks Model Menu Adding a Single Output Cell Naming an Output Add Output Command Adds a cell or range of cells as a What If analysis output or output range Selecting the Model menu Add Output command or clicking the Add Output icon adds the currently selected range of worksheet cells as a What
78. ere the 5 and 95 percentile are Reference TopRank Add In Commands 93 Converting Min Max Ranges to Distribution Parameters Steps 94 returned instead Additional steps are spread equally on a percentile basis across the min max range If you select five steps for example TopRank returns values for the min and max plus the 25 50 and 75 percentiles a total of five values returned 5 recalculations and 5 new results generated For Tri1090 and Normal distribution types TopRank converts your Vary function s minimum and maximum values as calculated from the base value minimum argument maximum argument and range type into parameters for the selected distribution type A Tri1090 distribution has a minimum most likely and maximum value argument plus a percentile location for the minimum and maximum values For the Tri1090 distribution type TopRank uses the 10th and 90th percentiles as the location for the minimum and maximum values For example the TopRank function RiskVary 100 10 10 Tri1090 is the equivalent to the RISK function RiskTrigen 90 100 110 10 90 where 90 is the 10th percentile value 110 is the 90th percentile value and 100 is the most likely value A Normal distribution has a mean and standard deviation as arguments A Vary function is converted to a Normal distribution by setting your Vary function s minimum and maximum values to equal the 5th and 95th percentile in a Normal distribution wi
79. erent part ofthe country Therefore each anthresa amp terent number of workers pays different wages and invests Gflerenty intrainng programs simedto veorove producti In a model designedto cniniate me profits of each piant TopRank identifies tne mo criseni factors in infuerangine total revenue and lank mer relaie portance Arata anahvas of is eode shove that Traning nvestmenttocFaciorr 86 i Most crBicalvarebte fecting TotWProft From the outout report generatedby TopRankcwe can see that a 15 inc m Factory Bu warincreate ne Total Prost by TOS Eve neut varabies Cad be vanedby mare ot less Dian Tis yeu choose Th ecandanainrg most important factors are Labor Costsin Faery and Training Investments in Fact 4 urther Anata ofthe Model Using RISK Keohane VUE iva soln eo angaras noas Sarakina Sube caeelul speci wan probata delia ropRandfor amore detaled study ofa uneto Day surruntingy ur made Production Capacity PD related to of workers A1 Etticieney Factor EF 42 mlated to quality of workers in mgion 43 and money invested in training program 45 Units Produced UP sof aa Clicking the Swap Option icon next to the Help icon in the Swap TopRank Functions dialog displays the Swap Options dialog Swap Options are available for e Swap Out when TopRank and RISK functions are removed e Swap In when TopRank and RISK functions are returned to your workbook Swap Functions Command Swap O
80. f analysis It uses powerful auditing technology to find all possible values in your spreadsheet which could affect your results It can then change all these possible values automatically and find out which is most significant in determining your results M4000 Introduction to TopRank8 TopRank Applications Why TopRank Multi Way What if Analysis TopRank applications are the same as spreadsheet applications If you can build your model in a spreadsheet you can use TopRank to analyze it Businesses use TopRank to identify the critical factors price up front investment amount sales volume or overhead that most affect the success of their new product Engineers use TopRank to show them the individual product components whose quality most affects final product production rates A loan officer can have TopRank quickly run a model at any possible interest rate loan principle amount and down payment combinations and review results for each possible scenario Whether your application is in business science engineering accounting or another field TopRank can work for you to identify the critical variables which affect your results Modeling Features As an add in to Microsoft Excel TopRank links directly to your spreadsheet to add What if analysis capabilities The TopRank system provides all the necessary tools for conducting a What if analysis on any spreadsheet model And TopRank works in a style you are familiar wi
81. f the change on your results asingle What If calculation Usually a couple more What Ifs are tried manually maybe a best case and a worst case and the results are noted and reported More ambitious spreadsheet users often run a set of What Ifs each on different input values manually tracking their effects on results They then compare the impacts on results from each change and identify the most critical variables in their model TopRank brings an automated and rigorous approach to What If analysis on spreadsheets Instead of requiring you to manually change a set of input values TopRank automatically changes any or all inputs in you spreadsheet tracks all results calculated and ranks them according to their impacts TopRank also graphs your results for easy presentation TopRank greatly speeds What If modeling and provides a thorough analysis of a greater number of input values Instead of being limited to a manual testing of five or ten What If calculations TopRank can quickly process hundreds or thousands of What If calculations Introduction Running a What If Analysis A What If analysis in an iterative process One by one each spreadsheet variable in TopRank called an input is changed to a new possible value and the spreadsheet is recalculated A new result is generated and recorded the input is reset to its original value and the next input is changed At the end of the analysis a set of data is created conta
82. fecting an output A Summary table of key inputs is created with the graph e Spider graphs also graphically display the key inputs affecting an output The slope of each line depicts the relative change in the output per unit change in the input variable e Sensitivity Graphs display the impact of an individual input on an output e Detail for All Inputs report contains information on how each input change made in the analysis affected an output 110 Report Settings Command The For Entire Run Report option is e Detail By Input Report is a single report that details the impact of individual inputs on all outputs in a model Use this to quickly compare how individual inputs affect different outputs Output values may be shown in graphs in terms of the actual output value calculated or as a percent change from the output s base value Select On Graphs Show Output Values as Change From Base to view percent change in graphs Reference TopRank Add In Commands 111 Outputs for Reports Tab Report Settings Command Selects the outputs to be reported on by TopRank The Outputs for Reports tab in the Report Settings dialog allows you to specify a subset of outputs to be reported on from an analysis TopRank Report Settings Reports Outputs for Reports Inputs for Reports Select Outputs to Report On All Outputs Selected Outputs Factory xls v 57 Profits Per Plant Factory 1 V E57 Profits Per Plant
83. function such as Vary 100 10 10 8 TopRank overrides the default Steps and in this case returns eight different values for the Vary function If a table of values is entered using the VaryTable function such as VaryTable 100 G1 G100 TopRank calculates spreadsheet results for each of the values entered in the table whether ten one hundred or thousands of values The actual values returned by TopRank for each step for each Vary function depend on the Distribution argument to the Vary function or the default distribution setting The distribution selected describes how values are distributed across the minimum maximum range of the function For example if the Uniform distribution is selected such as Vary 100 10 10 Uniform any value in the minimum maximum range described by the Vary function is equally likely to occur Four distribution types are available with TopRank They are e Uniform rok e Triangular N e Tri1090 A e Normal What is What If Analysis Percentile Values TopRank steps across the min max range defined by the Vary function by using a distribution s percentiles Any probability distribution can be divided into 100 segments of equal probability At the 20th segment or percentile there is a 20 chance of a lower value and a 80 chance of a higher value occurring TopRank always returns the 0 percentile minimum and the 100 percentile maximum a minimum of two steps for each Vary f
84. functions may be added to inputs affecting an output at any time after an output is selected by clicking the Model menu Add AutoVary Functions command By default TopRank will search for inputs and outputs in all worksheets in all open workbooks There may be times however that you wish to limit TopRank to only find inputs and outputs in specific workbooks worksheets or ranges The Search Ranges command allows you to do this Search Ranges Select Workbooks Sheets and Ranges to Search Factory xls t Sheeti D 38 H 61 only Range to Search D 38 H 61 x amp Note This is an advanced feature which is only needed with very large models where search time is an issue Only values in the selected locations will be identified as inputs when AutoVary functions are added by TopRank ok Cancel By unchecking workbooks or worksheets in the displayed list you can keep them from being searched for inputs In addition you can select a specific range on a worksheet that will be searched by clicking on the name of the sheet in the upper part of the Search Ranges dialog and specifying the desired range of cells in the Range to Search field If you add new worksheets or workbooks to an analysis by default they will be searched by TopRank until you deselect them TopRank will use entered search ranges when it is automatically identifying inputs in precedent cells When generating the list of inputs and outputs in your
85. g where program defaults can be set A wide variety of TopRank settings can be set at default values that will be used each time TopRank runs These include defaults for scanning precedents for inputs default input min max ranges reporting options and others TopRank Application Settings General Show Welcome Screen Find Inputs Scan Precedent Cells for Inputs When Analysis Starts Scan Stand Alone Values in Cells True Scan Values Embedded in Formulas False Scan Values Embedded in Fct Args False Input Defaults Type of Min Max Range E Change from Base Minimum 10 Maximum 10 Distribution Uniform Steps 5 Update Display True Pause on Error False For Each Output Report Tornado Graph True Spider Graph True Sensitivity Graph False Detail For All Inputs False Graph Outputs as Perc from Base False Place Reports In New Workbook Reuse Same New Workbook False Swap Functions Preview Changes Modified Formulas Static Values Where No RiskStatic Use Expected Value Se ae Reset OK Cancel Reference TopRank Add In Commands 121 Clear TopRank Data Command Clears the Selected TopRank Data from Open Workbooks The Clear TopRank Data Command clears the selected TopRank data from open workbooks Clear TopRank Data Data to Clear Settings Spreadsheet Functions The following data may be cleared e Settings This clears any TopRank settings and def
86. generated by RISK You can see the chance of any result occurring In decision trees chance events must be described in terms of discrete outcomes a chance node with a finite number of outcome branches But in real life many uncertain events are continuous meaning that any value between a minimum and maximum can occur Using RISK with PrecisionTree makes modeling continuous events easier using distribution functions Also RISK functions can make your decision tree smaller and easier to understand Using RISK with PrecisionTree Drilling Decision for Open Test Results Methods of Recalculation During a Simulation Two options are available for recalculation of a decision model during a simulation performed with RISK The first option Expected Values of the Model causes RISK to first sample all distribution functions in the model and supporting spreadsheets each iteration then recalculates the model using the new values to generate a new expected value Typically the output from the simulation is the cell containing the expected value of the model At the end of the run an output distribution reflecting the possible range of expected values for the model and their relative likelihood of occurrence is generated The second option Values of One Sampled Path Through the Model causes RISK to randomly sample a path through the model each iteration of a simulation The branch to follow from each chance node is randomly sele
87. he maximum change is the possible upside or positive change you think an input can reasonably have e Steps are the number of values across an input s minimum maximum range to be used in a What If analysis e Distribution is a probability distribution type which shows the likelihood of a value in an input s minimum maximum range occurring e g a normal distribution e What If Table is a table of values to be substituted for an input in a What If analysis Chapter 3 Overview of TopRank 33 34 Vary and VaryTable are functions used by TopRank to describe the base case minimum change maximum change steps distribution and What If table for an input Multi Way What If analysis varies two or more inputs at the same time and reports results for all combinations of inputs VaryMulti and VaryMultiTable are functions used by TopRank to identify inputs which are to be included in a Multi Way What If analysis Introduction What is What If Analysis One Way What If Analysis Vary Functions TopRank can conduct one way and Multi Way What If analyses on any spreadsheet model TopRank performs these analyses by 1 adding a new set of functions to the spreadsheet function set and 2 providing capabilities to conduct What If analyses on spreadsheets and calculate and graph the results of these analyses One Way What If analysis studies the effect of changes in individual input variables on the output values of a spreadsheet Each input
88. ified input Vary functions in your workbook s This table is displayed when Model Window icon the icon with a red and blue arrow is clicked TopRank Model Inputs outputs Model Inputs in Open Workbooks Inputs 15 Outputs 1 Cell Function S548 RiskVary 800 15 15 15 Labor Cost per Worker LC Factory 2 E48 RiskAutoVary 900 15 15 15 Labor Cost per Worker LC Factory 3 F48 RiskAutoVary 1200 15 15 15 Labor Cost per Worker LC Factory 4 G48 RiskAutoVary 800 15 15 15 Labor Cost per Worker LC Factory 5 H48 RiskAutoVary 600 15 15 15 Category Number of Workers 1 E 1 j Number of Workers Factory 1 D54 RiskAutoVary 500 15 15 z 15 Number of Workers Factory 2 E54 RiskAutoVary 250 15 15 15 Number of Workers Factory 3 F54 RiskAutoVary 100 15 15 15 Number of Workers Factory 4 G54 RiskAutoVary 500 15 15 15 Number of Workers Factory 5 H54 RiskAutoVary 800 15 15 1 15 Category Training Investment per Worker Training Investment per Worker Factor D51 RiskAutoVary 100 15 15 15 Training Investment per Worker Factor E51 RiskAutoVary 200 15 15 15 Training Investment per Worker Factor F51 RiskAutoVary 300 15 15 15 Training Investment per Worker Factor G51 RiskAutoVary 270 15 15 15 Training Investment per Worker Factor H51 RiskAutoVary 300 15 15 15 The possible variation in value described by a Vary fu
89. ify the parameters of Vary functions without typing the functions directly in your spreadsheet Use the Add Input command to view or define the variability in your input values VA TopRank Add Input D48 Name Labor Cost per Worker LC Labor Cost per Worker LC Factory t 1 labor Cost per Worker LC Factory 1 1 E Cell Riskvary 800 15 15 Formula Min Base Max 800 15 Remove Input OK Cancel As you click on different cells while the Add Input window is open the window changes to show the formula and functions in the new cell Values and functions in a displayed formula are selected by clicking on them turning them red Once a value is selected you can change it to a Vary function You can also edit or remove existing Vary or RISK functions Do this by either e Typing directly in the formula or e Clicking Add Input to replace a value with a Vary function built from the entries in the Min Base Max table displayed below the formula e Using the Properties fx icon to change parameters for a displayed Vary function As you leave cells formulas in Excel are updated with any changes you have made in the Add Input window Click Close to dismiss the Add Input window Reference TopRank Add In Commands 71 Add Input Window Options 72 The options available in the Add Input window include Name The Name entry displays the name of the current input i e the value shown in red i
90. ined Excel names used by TopRank e Spreadsheet Functions This removes all TopRank and RISK functions from open workbooks replacing them with their Static value or if a Static value is not found the Swap Out value as specified in the Swap Options dialog However this is not a Function Swap as TopRank will not place swap information in your workbook to be used when swapping functions back in and thus all model information will be gone Selecting all options allow you to remove all TopRank information from open workbooks Unload TopRank Add in Command Unloads the TopRank add in from Excel The Unload TopRank Add in Command unloads TopRank closing all TopRank windows 122 Utilities Commands Help Commands TopRank Help Opens on line help file for TopRank The Help menu TopRank Help command opens the main help file for TopRank All of TopRank s features and commands are described in this file Online Manual Opens online manual for TopRank The Help menu Online Manual command opens this manual in PDF format You must have Adobe Acrobat reader installed to view the online manual License Activation Command Displays licensing information for TopRank and allows the licensing of trial versions The Help menu License Activation command displays the License Activation dialog box listing the version and licensing information for your copy of TopRank Using this dialog box you can also convert a trial version of TopRank into
91. ing Investment in Plant 5 increases the total revenue by 110 The second and third important factors are Labor Cost in Plant 5 and Training Investment in Plant 4 When this model is simulated in RISK probability distributions should be applied to these top factors to describe their range of possible values and the probabilities of different values occurring Modeling Critical Factors in Profitability Using the VaryTable Function Example Model VARYTABLE XLS Often in the real world inputs in a model only vary among several possible values For example the size of men s shoes can only take on full and half size values between five and fifteen that is unless you re Shaquille O Neal with size 18 s In this case the built in AutoVary function is not appropriate because the value it returns can be any value across a range For example an AutoVary function could return a value of 7 65 for the shoe size To address this aspect of modeling TopRank provides a VaryTable function to enable you to specify a list or table of values among which the input varies To learn more about the VaryTable function first open the worksheet VARYTABLE XLS Then watch how the VaryTable function can be used with a table or a list of values Defining a Table of Values To define a table of values in TopRank 1 Find the locations of cells that contain the table of values In our example VARYTABLE the locations are cells B43 B54 2 Move to the c
92. ing result that was calculated r ido c Book2 Microsoft Excel x X Home Inset Pagelayout Formulas Data Review View Add Ins TopRank 7 X FA i Ed i 3 Ha Analysis Settings Y fF Utilities i Report Settings id Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions Model Analysis I Tools M18 Q fl a Wie c D t F eG H 1 J K L NEMEE x 1 TopRank Output Detail Multi Way Analysis 2 Performed By Test 3 Date Friday February 13 2009 10 41 42 AM 4 ModekFactory xls 5 Output Total Profit 060 6 _Base Value 76666 6667 7 8 What f Analysis Multi Way Analysis Output Detail 9 Inputs and Outputs 10 Output Variation Input Variation Investment per Training Investment per Work Factory 43 Number of Workers 11 Total Profit D60 Work Factory 82 E51 F51 Factory 83 F54 12 Value Change X Value Change X Value Change Value Change 3 13 58 667 p 14 144 667 15 26 667 16 126 667 17 113 492 0056 41 667 005 005 19 111 667 008 15 00 L 1 20 47 492 21 54 842 22 59 667 23 93 667 24 90 842 25 26 27 OOO S a M 4 gt Tomado D60 22 Multi Way What If Analysis Multi Way Tornado Graph in TopRank The combinations that have the most impact on results can be displayed with a tornado graph just as are one way What If analysis results When a tornado graph is used with Mul
93. ining various possible input values and the results associated with each Results from What If Analyses The results of a What If analysis include tables ranking inputs by their effects on results and graphs which summarize those rankings A typical ranking as produced by TopRank shows the most important inputs at the top of the list with the maximum and minimum output value caused by each input shown C3 PEENE B Book2 Microsoft Excel 5 y fs Home Insert Page Layout Formulas Data Review View Add Ins TopRank 9 x E d i 3 H Analysis Settings 25 fF Utilities b A Report Settings d amp Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions Analysis Tools Model Al v f B c D E F 31 What f Analysis Summary for Output Total Profit 32 Top 10 Inputs Ranked By Change in Actual Value Minimum Maximum Output Input Output Input Rank Input Name Value Change Value Value Change _Value Training Investment per Work 57 333 109 57 255 160 667 109 57 345 Factory 5 H51 LaborCostperWorker LC H48 4 667 93 91 690 148 667 93 91 510 Factory 5 H48 Training Investment per Work G51 15 917 79 24 229 5 137 417 7924 310 5 Factory 4 G51 LaborCostperWorker LC D48 16 667 78 26 920 136 667 Factory 1 D48 LaborCostperWorker LC G48 16 667 78 26 920 136 667 Factory 4 G48 34 167 55 43 85 119 167 341
94. ion for the examples is C PROGRAM FILES PALISADE TOPRANK5 EXAMPLES Click the Add AutoVary Functions icon the second one on the toolbar Click the Model Window icon on the TopRank Toolbar the one on the Toolbar with the red and blue arrow The Model Window list listing the Vary functions in the FACTORY worksheet along with your output cells is displayed Click the Run What if Analysis icon the one with the red tornado graph You ve just started a what if analysis for the FACTORYworksheet The what if analysis is underway When it is complete your what if analysis results will be displayed Using TopRank Chapter 2 Overview of What lf Analysis Introduction 0 ceec sete ee eanas tiaan iaaa a 15 What is What lf assensati 15 Better Decisions With What If Analysis eene 15 What If Analysis and the Computer seen 16 Running a What If Analysis cerent 17 Results from What If Analyses eese eere tntntnnns 17 Graphs of What If Results eese 18 Multi Way What If Analysis eren 21 Multi Way What If Analysis Results essen 22 What lf Analysis and Risk Analysis eese 25 Sensitivity Analysis Using Risk Analysis sss 26 Sensitivity Analyses in RISK vs TopRank ss 27 Why What If First c
95. ionTools amp 145 TopRank Functions How are TopRank Functions Entered Automated What if s 146 TopRank defines variations in spreadsheet values using functions To do this TopRank has added a set of new functions to the Excel function set each of which specifies a type of variation for your values These functions include e Vary and AutoVary functions which during a What if analysis change a spreadsheet value across a and range you define e VaryTable functions which during a What if analysis substitute each of a table of values for a spreadsheet value TopRank uses functions to change spreadsheet values during a What if analysis and keeps track of the results calculated for each value change These results are then ranked by the amount of change from the original expected results Then functions which caused the greatest change are identified as the most critical to the model TopRank Pro also includes over 30 probability distribution functions found in RISK These functions can be used along with Vary functions to describe variation in spreadsheet values TopRank functions are entered wherever you want to try different values in a What if analysis The functions can be added to any number of cells in a spreadsheet and can include arguments which are cell references and expressions providing extreme flexibility in defining variation in value in spreadsheet models In addition to adding Vary functio
96. is Run Command Starts a What If analysis The What If Analysis menu Run command starts a What If analysis using the current settings When the Run command is selected first TopRank will insert AutoVary functions if necessary in open workbooks It then displays a Status dialog showing the settings for the run it is about to execute TopRank Run What If Analysis TopRank is ready to run a what if analysis with the following options Analysis Setup Total Output Cells to be Analyzed 1 Auto Identify Inputs for these Outputs Yes Change Applied to these Inputs 1596 15 Total Inputs to be Varied 15 Total Recalculations Needed 75 of Multi Way Recalculations in Total 0 Reports Selected Number of Outputs to be Reported On 1 For Each Output Report Top 10 Inputs w gt 1 Impact Reports Summary of Key Inputs Graphs Tornado Spider of Report Worksheets to be Created 3 Max of Graphs to be Created 2 The Status dialog shows the number of recalculations TopRank will perform during the analysis along with the reports it will generate By clicking the Analysis Settings or Reports Settings buttons you can change any settings prior to executing the analysis Clicking the Run button in the Status dialog starts the What If analysis During an analysis a status dialog box displays the progress of the calculation Click the Cancel command on the status dialog box to halt the calculation at any time If Update Disp
97. is changed individually while holding all others at their base case value In TopRank a one way What If analysis is conducted on all input variables identified using Vary functions A Vary function is a custom function added to the Excel spreadsheet function set by TopRank Defining a What If Input Inputs to be changed in a one way What If analysis can be identified individually by the user or automatically by TopRank At a minimum each variable is defined by three values its base value the one originally present in the spreadsheet its possible downside negative change and its possible upside positive change Negative and positive change is typically entered as a percentage such as 1076 or 20 As an option you can also enter an actual change such as 1000 or 950 or an actual minimum and maximum such as 100 or 200 In TopRank base minimum change and maximum change for an input are entered in Vary functions such as e RiskVary 100 10 10 indicating a base case of 100 and a possible 10 and 10 change e RiskVary 50 45 10 indicating an expected value of 50 and a possible 45 and 10 change During a normal spreadsheet calculations Vary functions return their base case or expected value For the functions shown above the values returned are 100 and 50 These are also the values entered in the cell before the Vary function was added By returning their expected value Vary functions do not affect the norm
98. is selected TopRank goes through the following procedure 1 TopRank traces through spreadsheet cells that are precedents to an output cell A precedent is a cell whose formula s calculation directly or indirectly affects the value in the output cell 2 Whena precedent cell is found TopRank uses the criteria specified in the When Identifying Inputs Include settings to identify constants in the cell s formula These are constants which could vary thus affecting the output s value 3 For identified constants TopRank inserts an AutoVary function that uses the current constant value as the base value and the default minimum maximum change What If Analysis Menu When Identifying Inputs Include TopRank will insert AutoVary functions either When What If Analysis Starts or When Outputs are Added When What If Analysis Starts specifies that TopRank will insert AutoVary functions at the start of the analysis and then remove them once the run is complete This allows you to perform a what if analysis without directly adding inputs to your spreadsheet When Outputs are Added specifies that TopRank will insert AutoVary functions when you add a new output AutoVary functions added by TopRank when this setting is used will not be automatically removed once the run is complete All AutoVary functions however may be removed at any time using the Remove AutoVary functions command The When Identifying Inputs Include settings control
99. ision makers to do all this and more But what exactly does decision analysis allow you to do As the decision maker you can clarity options and rewards describe uncertainty quantitatively weigh multiple objectives simultaneously and define risk preferences All in an Excel spreadsheet Introduction to PrecisionTree PrecisionTree and Microsoft Excel PrecisionTree Nodes Model Types Values in Models Decision Analysis Modeling Features As an add in to Microsoft Excel PrecisionTree links directly to Excel to add Decision Analysis capabilities The PrecisionTree system provides all the necessary tools for setting up and analyzing decision trees and influence diagrams And PrecisionTree works in a style you are familiar with Excel style menus and toolbars With PrecisionTree there s no limit to the size tree you can define Design a tree which spans multiple worksheets in an Excel workbook PrecisionTree reduces the tree to an easy to understand report right in your current workbook PrecisionTree allows you to define as well as influence diagrams and decision tree nodes in Excel spreadsheets Node types offered by PrecisionTree include e Chance nodes e Decision nodes e End nodes e Logic nodes e Reference nodes Values and probabilities for nodes are placed directly in spreadsheet cells allowing you to easily enter and edit the definition of your decision models PrecisionTree creates
100. knowing each individual factor s effect on the output TopRank provides this type of information by performing a Multi Way What If analysis In a Multi Way What If analysis the user selects a group of inputs to be included in the analysis and specifies the number of inputs in each combination The number of inputs in each combination is the Group Size TopRank then calculates the spreadsheet using all possible combinations in value of the selected inputs All calculated combinations are then ranked according to their relative importance in determining the output value To examine a Multi Way What If analysis first open the worksheet MULTIWAY XLS Then 1 Bring up the Model Window list by pressing the Model Window icon 2 Highlight all inputs that are to be considered in the Multi Way analysis right click and select the Multi Way command The selected inputs are changed from and to VaryMulti functions 3 Click the Analysis Settings icon switch to the Other tab and set the Group Size to 2 4 Click the What If command to run your Multi Way analysis A Word of Caution The number of calculations required to complete a Multi Way analysis can grow exponentially with increases in the Group Size Multi Way steps and number of VaryMulti and VaryMultiTable functions To see an example of a dramatic increase in number of calculations required change the Group Size in MULTIWAY XLS from 2 to 9 Chapter 4 TopRank Modeling Technique
101. las In each identified formula it finds input What is What If Analysis AutoVary Functions values based on identification criteria you specify For example you can have TopRank find any possible input value anywhere in a cell s formula or functions or cause it to restrict its search to only values which are by themselves in a cell s formula This keeps the list of identified inputs more manageable when desired After TopRank has identified inputs you of course can remove them from your inputs list or lock them so they won t be changed in an analysis You can do this if a value was identified by TopRank as a possible input but given your knowledge of the model it could never be changed it s a fixed value and changing it is nonsensical When TopRank defines inputs for you it automatically inserts an AutoVary function for each constant it finds that could affect your output These AutoVary functions use a default variation that you select such as 20 and 20 For example TopRank can enter e AutoVary 50 10 10 indicating an expected value of 50 and a possible 10 and 10 change This is the equivalent to the function Vary 50 10 10 the Auto just identifies it as a function automatically entered by TopRank By using this automatic mode you can quickly analyze a spreadsheet prior to making more detailed Vary function entries In as little as two mouse clicks you ve generated valuable What If results In
102. lay is selected you can watch your spreadsheet change every iteration But this will slow down the What If analysis Press the Num Lock key to toggle the Update Display setting When the analysis is complete an Excel workbook displays the reports selected with the Report Settings command Reference TopRank Add In Commands 103 TopRank Summary Results 104 j e 20g Book Microsoft Excel M Home Inset Pagelayout Formulas Data Review View Add Ins TopRank e 7 x y i E ZS H Analysis Settings amp F Utilities Ai iH SS a Report Settings c Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions Model Analysis Tools Al Qu f B c D E F G H 1 J K li M N o P Minimum Maximum Output Input Output Input Rank Input Name Cell Value _Change Value Value Change S Value TraininginvestmentperWork H51 57 8333 109 57 amp 255 160 667 109 57 amp 345 Factory 85 H51 LaborCostperWorker LC H48 4 667 8319 690 148 667 93 91 Factory 5 H48 Training Investment per Work G51 515 9317 79 24 229 5 137 417 79 24 Factory 4 651 LaborCostperWorker LC D48 16 667 78 26x 9205136667 78 26 Factory 1 D48 LaborCostperWorker LC G48 16 667 78 26 920 136 667 78 26 Factory 4 648 Training Investment per Work D51 34 167 55 43 85 119 167 55 43 Factory 1 D51 Training Investment perWork
103. les which could affect each output you select As an option TopRank can automatically search your spreadsheet for all values in cells and formulas which could affect your output s value These values are numeric constants such as 1000 10 5 or 99 entered in the spreadsheet cells and formulas that combine to calculate an output TopRank identifies inputs either when you start an analysis or when you add an output Inputs can be found in the formulas and values which are entered into spreadsheet cells Through the use of cell references formulas use the results of calculations in other cells with subsequent formulas referencing those intermediate cells Ultimately this chain of formulas leads to a result calculated in an output cell Within this tree of formulas any value either alone in a cell such as 100 or embedded in a formula such as the 1 1 value in the formula 1 1 A2 A3 isa possible input variable which could affect the output This is because only values not cell references can be changed in a TopRank What If analysis i e you can t change cell references without changing the structure of the model When TopRank automatically identifies inputs for you it finds all possible values in cells and formulas which could be changed to affect the output selected To do this TopRank searches through the formulas in the spreadsheet tracing back from the output cell through the dependent cells and formu
104. lower to upper bound See Sensitivity Analysis One Way Sensitivity Analysis A cell which you want to run a What If analysis on that contains the result of spreadsheet calculations A sensitivity analysis where the variable is the probability of a chance Occurrence or occurrences See Deterministic Sensitivity Analysis Event Sensitivity Analysis A measure of how likely a value or event is to occur A probability distribution or probability density distribution is the proper statistical term for a frequency distribution constructed from an infinitely large set of values where the class size is infinitesimally small See Frequency Distribution The range is the absolute difference between the maximum and minimum values in a set of values The range is the simplest measure of dispersion or risk of a distribution Appendix C Glossary of Terms 163 Risk Risk Analysis Risk Averse RISKview Sensitivity Analysis Steps Spider Graph Standard Deviation TopRank TopRank Industrial Tornado Graph Uncertainty Value Sensitivity Analysis 164 Uncertainty or variability in the outcome of some event or decision In many cases the range of possible outcomes can include some that are perceived of as undesirable along with others that are perceived as desirable The range of outcomes is often associated with levels of probability of occurrence Risk Analysis is a general term used to describe any method used to st
105. ls and all identified input functions in your workbook s This table is displayed when the Model menu Model Window command is selected or the Model Window icon is clicked TopRank Model DAR Inputs outputs Model Inputs in Open Workbooks Inputs 15 Outputs 1 Name Cell Function Min Base Max a SS Labor Cost per Worker LC Labor Cost per Worker LC Factory D48_RiskAutoVary 800 15 15 15 l C ANNI Labor Cost per Worker LC Factory 2 E48 ITEM 15 rj 15 900 15 Labor Cost per Worker LC Factory 3 F48 RiskAutoVary 1200 15 15 15 1200 15 Labor Cost per Worker LC Factory 4 G48 RiskAutoVary 800 15 15 15 800 15 Labor Cost per Worker LC Factory 5 H48 RiskAutoVary 600 15 15 15 600 15 Category Number of Workers Number of Workers Factory 1 D54 RiskAutoVary 500 15 15 15 500 15 X For each input or output variable the list shows e Name or the name of the cell as determined by TopRank or entered by you e Cell and Worksheet if necessary showing the location of the input e Function The function cell for an input shows the actual Vary function or RISK distribution as it is used in the cell formula in Excel e Min Base and Max values for each input For vary functions these columns allow editing of arguments of the function Some of the entries in the list may be editied as follows e Name The name of an input or output can be changed by clicking i
106. luding common help files and program libraries Chapter 1 Getting Started Setting Up the TopRank Icons or Shortcuts The TopRank setup program automatically creates a TopRank command in the Programs menu of the Taskbar However if problems are encountered during Setup or if you wish to do this manually another time follow these directions 1 Click the Start button and then point to Settings 2 Click Taskbar and then click the Start Menu Programs tab 3 Click Add and then click Browse 4 Locate the file TOPRANK EXE and double click it 5 Click Next and then double click the menu on which you want the program to appear 6 Type the name TopRank and then click Finish Macro Security Warning Message on Startup Microsoft Office provides several security settings under Tools Macro Security to keep unwanted or malicious macros from being run in Office applications A warning message appears each time you attempt to load a file with macros unless you use the lowest security setting To keep this message from appearing every time you run a Palisade add in Palisade digitally signs their add in files Thus once you have specified Palisade Corporation as a trusted source you can open any Palisade add in without warning messages To do this e Click Always trust macros from this source when a Security Warning dialog such as the one below is displayed when starting TopRank Security Warning PR C Program Files
107. lues returned equal to Steps min max range by type and distribution across the min max range also used ina Multi Way What if Returns values from table also used in a Multi Way What if Returns values from table 129 Table of Property Functions This table lists the custom Property functions that can be added to TopRank Vary functions Property Function Specifies RiskLock Blocks the varying of the input in which the Lock function is included RiskName input name Name for the input in which the Name function is included RiskCategory category Category name for the category of inputs in name which the input will be placed Table of Output Function This table lists the Output function that can used in TopRank Output Function Specifies RiskOutput name output What If analysis output cell with name range name position in range output range name to which the output belongs and the position in range Note all arguments to this function are optional TopRank Function Reference Listing of TopRank Functions RiskAutoVar RiskVary Reference TopRank Functions AUTOVARY base minimum maximum range type Steps distribution specifies an input variable in a What if analysis that was automatically entered by TopRank The arguments to this function are identical to those of the Vary function For more information see VARY VARY base minimum maximum range type Steps di
108. mean and standard deviation as arguments A Vary function is converted to a Normal distribution by setting your Vary function s minimum and maximum values to equal the 5th and 95th percentile in a Normal distribution with the mean located half way in between the two values Using this information TopRank generates a Normal distribution with the appropriate mean and standard deviation to include these 5th mean and 95th percentile values Note The default distribution setting is used when a distribution type has not been explicitly entered as a Vary function argument Model Menu Steps The Steps entry specifies the number of steps TopRank calculates across the minimum maximum range of Vary functions During a What If analysis a value is returned and a new result calculated for each step for each Vary function During a What If analysis a differing number of possible values can be returned for each Vary function For each value returned TopRank recalculates the worksheet and stores a new possible result for each output The number of values returned is given by the Steps argument to the Vary function or a default Steps entered in the Analysis Settings dialog In the function s simplest form such as RiskVary 100 10 10 there is no entry in the function for the Steps In this case TopRank uses the default Steps as defined using the What If Analysis menu Analysis Settings command A typical default is three or four st
109. ments of equal probability At the 20th segment or percentile there is a 20 chance of a lower value and a 80 chance of a higher value occurring TopRank always returns the 0 percentile minimum and the 100 percentile maximum a minimum of two steps for each Vary function except for unbounded distributions such as Normal where 5 and 95 percentiles are returned instead Additional steps are spread equally on a percentile basis across the min max range If you select five steps for example TopRank returns values for the min and max plus the 2576 5076 and 75 percentiles a total of five values returned 5 recalculations and 5 new results generated For Tri1090 and Normal distribution types TopRank converts your Vary function s minimum and maximum values as calculated from the base value minimum argument maximum argument and range type into parameters for the selected distribution type A Tri1090 distribution has a minimum most likely and maximum value argument plus a percentile location for the minimum and maximum values For the Tri1090 distribution type TopRank uses the 10th and 90th percentiles as the location for the minimum and maximum values For example the TopRank function RiskVary 100 10 10 Tri1090 is the equivalent to the RISK function RiskTrigen 90 100 110 10 90 where 90 is the 10th percentile value 110 is the 90th percentile value and 100 is the most likely value A Normal distribution has a
110. model TopRank will still include any explicitly entered TopRank or RISK functions present in workbooks or worksheets not selected for searching These functions will also be used in an analysis What If Analysis Menu Multi Way What If Analysis Other Tab Analysis Settings Command Defines other settings affecting a What If analysis TopRank Analysis Settings Input Defaults Find Inputs Other Multi Way What If Group Size of Steps Multiple Runs of Runs During Analysis Update Display V Pause on Error Multi Way What If analyses vary inputs at the same time and calculate the effect of each combination of input values on results When you have Multi forms of functions in your worksheets VaryMulti VaryMultiTable or RISK functions in their Multi form such as NormalMulti or DiscreteMulti an analysis run using the What If Analysis Run command will include a multi way what if analysis When executing a Multi Way What If TopRank first identifies all VaryMulti and VaryMultiTable functions in your spreadsheet Then using the Group Size entered TopRank tries all combinations of input values possible As with a one way What If TopRank recalculates your spreadsheet for each combination and collects the new output values generated Once the Multi Way What If is completed TopRank ranks all calculated combinations according to their impact on each output Reference TopRank Add In Commands 99 Multi W
111. mportant in your model You can then focus on these important components and better estimate what their values could be Usually however there are several or more of these important uncertain components and in reality they could all vary at the same time To analyze an uncertain model such as this you need risk analysis or Monte Carlo simulation Risk analysis varies all uncertain inputs simultaneously just as they do in real life and builds a range and distribution of the possible results that could occur With risk analysis inputs are described with a probability distribution such as normal lognormal beta or binomial This is a much more detailed description of the uncertainty present in an input s value than a simple or percentage variation A probability distribution shows both the range of values possible for an input and the likelihood of occurrence of any value in the range Simulation combines these input distributions to generate both a range of possible results from your model and the likelihood of any result occurring The simple and change defined by a Vary function in a What if analysis can be used directly in risk analysis RISK actually samples your Vary functions directly in a risk analysis The values sampled by RISK from Vary and VaryTable functions during a simulation depend on either distribution argument entered for the function or the default distribution setting used in TopRank For ex
112. mulas Data Review View Addins TopRank 7x i Wagon He oan t SS 2 Report settings c Help Add AutoVan Add Model Run Whatif Swap Input Functions Output Window Analysis Functions t Model I Analysis Jl Tools Al MO fe Y Im ants Lc Lp RN RT CR TRI RITTER n a r TopRank Spider Graph 2 Performed By Test 3 Date Friday February 13 2009 10 36 40 AM 4 ModeLFactory xis 5 Output Total Profit D60 Value 76666 6667 Spider Graph of Total Profit Labor Cost per Worker LC D48 Labor Cost per Worker LC E48 Labor Cost per Worker LC F48 Labor Cost per Worker LC G48 Labor Cost per Worker LC H48 Output 6 Change Training Investment per W D52 Training Investment per W E51 Training Investment per W F51 Training Investment per W G51 Training Investment per W H51 Chapter 2 Overview of What If Analysis 19 Sensitivity Graphs 20 r 3 id 2 c 9 Book Microsoft Excel ex i Home Inset Pagelayout Formulas Data Review View Addins TopRank x i3 EH E H analysis Settings Y amp fF Utites 21 Report Settings c Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions 20 Modell Analysis OJO Tos Al Hc fe iy a B C D E F G H 1 1 K m M N E TopRank Sensitivity Graph 1 By Test Date Friday February 13 2009 10 38 39 AM xs The
113. n More About Vary and VaryTable Functions below Entering Tables In the Properties window when Type of Range is set to Table of of Values with Values Excel Range the Reference entry button selects an Excel VaryTable cell range with the values you wish to use in the VaryTable function Each of the values in the selected range will be used for the input in a What If analysis Input Properties F51 Type of Range Iype Table of Values Excel range X Options Table Range 349 352 El x cna When Type of Range is set to Table of Values actuals the values entered in the displayed grid are used in the VaryTable function Each of the values will be used for the input in a What If analysis Input Properties F51 Type of Range Type Tabie of Values actual E zl E core 74 Model Menu Range Type of Range Distribution More About Vary and VaryTable Functions Vary and VaryTable functions allow you to specify the possible variation in value of your inputs in many ways For a Vary function the Range and Type of Range arguments specify the nature of the range you are specifying while the Distribution argument specifies how values are distributed across the Min max range The Steps argument selects the number of values TopRank will test across the Min max range during a What If analysis These are described in detail here The Range setting defines the minimum change possible downside o
114. n a cell is added as an output a RiskOutput function is placed in the cell The function allows the easy copying pasting and moving of output cells RiskOutput functions may also be entered in formulas the same way you would type in any standard Excel function bypassing the Add Output command RiskOutput functions optionally allow you to name your outputs and add individual output cells to output ranges A typical RiskOutput function might be RiskOutput Profit NPV 1 H1 H10 where the cell prior to its selection as a output simply contained the formula NPV 1 H1 H10 The added RiskOutput function selects the cell as an output and gives the output the name Profit For more information on RiskOutput functions see the section Reference TopRank Functions Output ranges are groups of related cells such as Profit by Year To add a new output range with multiple cells 1 Highlight the range of cells in your spreadsheet that you wish to add as an output range If multiple cells are included in the range highlight all the cells by dragging the mouse 2 Click the Add Output icon the one with the single red arrow TopRank Add Edit Output Range Cells D57 H57 Range Name Profits Per Plant 5l Output Cells in Range Cell Index Name D57 1 Profits Per Plant Factory 1 E57 Profits Per Plant Factory 2 F57 Profits Per Plant Factory 3 G57 Profits Per Plant Factory 4 H57 Profits Per Plant Factory 5 e
115. n the Name cell of a row in the table and entering the desired name Optionally you can click the Reference entry button to select cell s in your spreadsheet with the name If you select a reference for a name the name from the referenced cell s will be shown in italics in the Model Window table e Function If desired you may edit the function directly in this cell or press F2 to edit the function directly in Excel Model Menu How Are Default Names Created e Min Base and Max These cells allow you to quickly edit the parameters of a Vary function Select the desired Min Max range using the drop down lists or type a Min or Max value in Optionally you can click the Reference entry button to select cell s in your spreadsheet with the min or max value TopRank Model Inputs outputs l Model Inputs in Open Workbooks Name Function Category Labor Cost per Worker LC Labor Cost per Worker LC Factory 1 RiskAutoVary 800 15 15 Labor Cost per Worker LC Factory 2 Labor Cost per Worker LC Factory 3 RiskAutoVary 1200 15 15 ES 15 Labor Cost per Worker LC Factory 4 RiskAutoVary 800 15 15 15 Labor Cost per Worker LC Factory 5 RiskAutoVary 600 15 15 on 15 Category Number of Workers Number of Workers Factory 1 i RiskAutoVary 500 15 15 50 15 z a When an Input is added TopRank automatically tries to create a name for the input or output it displays in the Mo
116. n the cell formula box You may type in a new name or optionally you can click the Reference entry button to select cell s in your spreadsheet with the name Cell Formula The Cell Formula box displays the formula with any edits you have made for the selected cell Clicking on a value or TopRank or RISK function in the formula selects it by turning it red Once selected you can add a new TopRank function to the formula replacing the value or edit or remove an existing function If you select an existing TopRank function in the formula the Entry table at the bottom updates to show the arguments to that function Min Base Max These cells in the table at the bottom of the window allow you to quickly edit the parameters of a Vary function Select the desired Min Base Max range using the drop down lists or type a value in Click the Reference entry button in the dropdown list to reference an Excel cell with a min or max value Add Input and Remove Input If you are replacing an existing fixed value in a formula with a Vary function click the Add Input button This adds the new function to the formula Click Remove Input to take an existing function out of a formula and replace it with its expected value If you are simply editing the range of an existing function the function is immediately updated as you make changes Model Menu Properties The Properties window displayed by clicking th fx icon allows Window you to add or ch
117. native can vary with changes in a specific quantity when all other variables remain at their base values See Risk Measuring the effects of model inputs on the decision policy by varying any value in the model and examining the effects on the optimal policy and expected value Using RISK with PrecisionTree Variable Variance Vary Function VaryTable Function VaryMulti Function What If Analysis What If Value Table A basic model component that can take on more than one value If the value that actually occurs is not known with certainty the variable is considered uncertain Usually a variable is found in a cell or named range in your model A measure of how widely dispersed the values are in a distribution and thus is an indication of the risk of the distribution It is calculated as the average of the squared deviations about the mean The variance gives disproportionate weight to outliers values that are far away from the mean Functions used by TopRank to describe the base case minimum change of inputs maximum change of inputs steps and distribution Functions used by TopRank for entering a table of values to be used in a What If analysis Functions used by TopRank to identify inputs which are to be included in a Multi Way What If analysis Any method used to study and understand the risk inherent to a situation of interest Methods can be quantitative and or qualitative in nature Synonym Sensitivit
118. nction can also be changed in the Model Window just as in the Add Input window Simply type in the new Min or Max value you want or use the drop downs to select a value from the list What is What If Analysis What If Calculations Running a What If Analysis in TopRank Clicking the Run What If icon starts a what if analysis When starting TopRank first locates all Vary functions in your spreadsheet If you have selected to add AutoVary functions by automatically identifying inputs TopRank will add them Then it displays a status screen describing the analysis you are about to run TopRank Run What If Analysis TopRank is ready to run a what if analysis with the following options Analysis Setup Total Output Cells to be Analyzed 1 Auto Identify Inputs for these Outputs Yes Change Applied to these Inputs 1596 15 Total Inputs to be Varied 15 Total Recalculations Needed 75 of Multi Way Recalculations in Total 0 Reports Selected Number of Outputs to be Reported On 1 For Each Output Report Top 10 Inputs w gt 1 Impact Reports Summary of Key Inputs Graphs Tornado Spider of Report Worksheets to be Created 3 Max amp of Graphs to be Created 2 Following the Status dialog TopRank runs the analysis During the analysis one by one TopRank changes the values returned by each Vary function recalculates the spreadsheet and collects and stores the new output value The values returned by each
119. nctions optionally allow you to name your outputs and add individual output cells to output ranges A typical RiskOutput function might be RiskOutput Profit NPV 1 H1 H10 where the cell prior to its selection as a simulation output simply contained the formula NPV 1 H1 H10 The added RiskOutput function selects the cell as a simulation output and gives the output the name Profit Note All outputs added with TopRank will also be recognized when running a simulation with RISK Property Functions Optional arguments to TopRank functions can be entered using Property functions These optional arguments are used to name an input and its category for reporting and graphing and keep an input from being varied in an analysis These arguments are not required but can be added as needed Optional arguments specified using property functions are embedded inside of a TopRank function Property functions are entered just as are standard Excel functions and can include cell references and mathematical expressions as arguments For example the following function names the entered Vary function RiskVary 100 10 10 RiskName My Input Reference TopRank Functions 127 128 Sampling from Vary Functions During an RISK Simulation The simple and change defined by a Vary function in a What If analysis can be used directly in an RISK simulation RISK actually samples your Vary functions directly in a simulation
120. nd additional arguments can be added as necessary e In Excel VaryTable functions with tables entered directly in the function require the list of values to be entered as an array i e VaryTable 100 80 90 110 120 130 e Arrays in Excel are denoted by either enclosing the values of the array in brackets or using a reference to a contiguous range of cells such as A1 C1 Some additional guidelines e VaryTable functions return error values if an insufficient number of arguments are entered and ignore extra arguments if too many are entered e Vary functions return error values if arguments are of the wrong type number array or text Please refer to the function descriptions later in this chapter for a list of arguments and their descriptions for each TopRank function Introduction to TopRank Functions Important Note In Excel you can not list cell references or names in arrays as you list constants For example you can not use A1 B1 C1 to represent the array containing the values in cells A1 B1 and C1 Instead you must use the cell range reference A1 C1 or enter the values of those cells directly in the arrays as constants for example 10 20 30 Output Function Output cells are defined using RiskOutput functions These functions allow the easy copying pasting and moving of output cells RiskOutput functions are automatically added when the standard TopRank Add Output icon is pressed RiskOutput fu
121. ng is to transfer your copy of TopRank from your office PC to your laptop To rehost a license from Machine1 to Machine2 make sure both machines have the software installed and are connected to the Internet during the deactivation activation rehosting 1 On Machinel click deactivate Automatic via Internet in the License Activation dialog Wait for the success message 2 On Machine2 click activate Automatic via Internet Wait for the success message If the machines do not have Internet access then you may follow the similar instructions above for rehosting by the automated email process 6 I have Internet Access but I am still unable to Activate Deactivate automatically Your firewall must be set to allow TCP access to the licensing server For single user non network installations ths is http service palisade com 8888 TCP port 8888 on http service palisade com On line Tutorial In the on line tutorial TopRank experts guide you through sample models in movie format This tutorial is a multi media presentation on the main features of TopRank The tutorial can be run by selecting the TopRank Help Menu Getting Started Tutorial command Chapter 1 Getting Started 11 Using TopRank 12 To use TopRank in a normal Excel session 1 2 Click the TopRank icon in the Windows Start Programs Palisade DecisionTools group Use the Excel Open command to open the example spreadsheet FACTORY XLS The default locat
122. nputs 15 Outputs 6 Name Category Labor Cost per Worker LC Labor Cost per Worker LC aa Edt Category Number of Works l Lock Input y 2 Number of Workers Factor 715 15 15 Number of Workers Factor JZ Function Properties 15 15 15 Number of Workers Factor Edit Function in Excel 15 15 15 Number of Workers Factor 715 15 15 Number of Workers Factor Delete 715 15 15 E Category Training Investment per Worker Training Investment per Worker Factor D51 RiskAutoVary 100 15 15 1 15 Training Investment per Worker Factor E51 RiskAutoVary 200 15 15 15 Training Investment per Worker Factor F51 RiskAutoVary 300 15 15 15 Training Investment per Worker Factor G51 RiskAutoVary 270 15 15 15 Training Investment per Worker Factor RiskAutoVary 300 15 15 15 EI oK Cancel VaryMulti and VaryMultiTable functions take the same arguments as do Vary and VaryTable functions The Multi option just instructs TopRank to run a second analysis following the one way What If analysis that tries all Multi Way combinations using Multi functions in your model VaryMulti and VaryMultiTable functions are also evaluated as standard Vary and VaryTable functions during the one way What If analysis This provides both one way and Multi Way What If results on the same inputs in a single run What is Multi Way What If Analysis Running a Multi Way What If Analysis in
123. ns yourself TopRank can automatically enter Vary functions for you Use this powerful feature to quickly analyze your spreadsheets without manually identifying values to vary and typing in functions When automatically entering Vary functions TopRank traces back through your spreadsheet and finds all possible values which could affect the result cell you identify As it finds a possible value it substitutes in an AutoVary function with the default variation parameters such as 10 and 10 you ve selected With a set of AutoVary functions inserted TopRank can then run its What if analysis and rank the values which could affect your results by their importance With TopRank you can step through your Vary and AutoVary functions and change the variation each function specifies As a default you can use a 1076 and 10 variation but for a certain value you may feel that a 20 and 30 change is possible You can also select to not have a value varied as in some cases a spreadsheet value is fixed and could never be changed Introduction to TopRank amp Running a What if Analysis TopRank Results During its analysis TopRank individually changes values for each Vary function and recalculates your spreadsheet using each new value Each time it recalculates it collects the new value calculated in each result cell This process of changing value and recalculating is repeated for each Vary and VaryTable function The number of
124. nt for entering a single element output range where TopRank creates the name of the output for you RiskOutput used with a single argument such as RiskOutput output cell name specifies for a single element output range where the name is entered by you When a multiple element output range is identified the form RiskOutput output cell name output range name position in range is used however the output cell name entry can be omitted if you wish to have TopRank automatically generate a name for each output cell in the range RiskOutput functions are automatically generated for you when you select outputs using the TopRank Add Output icon However like any other TopRank function RiskOutput may be typed directly in the cell which you wish to reference as an output A RiskOutput function is entered by adding it to the cell formula which is already present in the cell that is to be an analysis output For example a cell containing the formula zNPV 1 G1 G10 would become RiskOutput NPV 1 G1 G10 when the cell is selected as an output RiskOutput Profit 1999 Annual Profit 1 NPV 1 G1 G10 identifies the cell where the RiskOutput function is located as an output and gives it the name Profit 1999 and makes it the first cell in a multiple cell output range named Annual Profit If names are entered directly in the RiskOutput function the entered output cell name and output range name must b
125. nts entered into all SIMTABLE functions If the number of runs is greater than the number of arguments entered into a SIMTABLE function the SIMTABLE function returns an error value during a run whose number is greater than the number of arguments Refer to the RISK Function Reference in the RISK User s Guide for more information on using the Simtable function Reference TopRank Add In Commands 101 During Analysis 102 The Update Display option toggles on and off the updating of the worksheet displayed during a What If analysis For each iteration of a What If analysis a new value is returned for a Vary or VaryTable function and the spreadsheet is recalculated Update display shows the results of each recalculation on the screen box checked or suppresses the display no check Selecting the Update Display option causes What If analyses to run slower The Update Display setting can be changed while a What If analysis is running by pressing the Num Lock key The Pause on Error setting pauses a What If analysis if an error value is generated in any of the selected outputs during a calculation When the analysis pauses the output range containing the error value is highlighted in the worksheet Scroll about the worksheet to examine the combination of input values which leads to the error condition Then continue the simulation if you wish What If Analysis Menu Run What If Analysis Status Dialog Running a What If Analys
126. ode during installation or you are installing a trial version your software will run as a trial version with time and or number of uses limitations and must be activated with an activation code in order to run as a fully licensed product 2 How long can I use the product before I have to activate it Software that is not activated may be run for fifteen days All of the product s features are present but the License Activation dialog will appear each time the program is launched to remind you to activate and to indicate the time remaining If the 15 day trial period expires the software will require activation in order to run Chapter 1 Getting Started 9 3 How do I check my activation status The License Activation dialog box is viewed through the TopRank Help menu License Activation command Activated software shows a status of Activated and trial version software shows a status of Not Activated If the software is not activated the remaining time that the software is allowed to run is displayed 4 How do I activate my software If you do not have an activation code you may obtain one by clicking the Puchase button in the License Activation dialog An online purchase will be immediately given an activation code and an optional link to download the installer should reinstallation become necessary To purchase by phone call the local Palisade office given in the Contacting Palisade section of this chapter Activation may be done
127. ools 141 Palisade s DecisionTools Case Study Run TopRank First Then RISK Next Assess Probabilities Add Distribution Fitting Simulate with RISK Decide with PrecisionTree 142 The Excelsior Electronics company currently makes desktop computers They are working on a laptop computer the Excelsior 5000 and want to know whether or not the company will profit from this venture They built a spreadsheet model which spans the next two years each column representing one month The model takes into account production costs marketing shipping price per unit units sold etc The bottom line for each month is Profit Excelsior expects some initial setbacks on this venture but as long as they are not too great and profits are up towards the end of two years they will go ahead with the E5000 TopRank is used on the model to find the critical variables The Profit cells are selected as outputs and an automatic What if analysis is run The results quickly show there are five variables out of many more that have the most impact on profits price per unit marketing costs build time price of memory and price of CPU chips Excelsior decided to concentrate on these variables Distribution functions are needed to replace the five variables in the spreadsheet model Normal distributions are used for price per unit and build time based on internal decisions and information from Excelsior s manufacturing division Resea
128. orrected Expected Value will use the discrete value in the distribution closest to the true expected value as the swap value e True Expected Value This setting causes the same values to be swapped as the option Corrected Expected Value except in the case of discrete distribution types such as DISCRETE POISSON and similar distributions For these distributions the true expected value will be used as the swap value even if the expected value could not occur for the entered distribution i e it is not one of the discrete points in the distribution e Mode or a distribution s mode value e Percentile or the entered percentile value for each distribution Reference TopRank Add In Commands 117 Swap In Options 118 TopRank Function Swap Options Swap Out Swap In When Removing an RISK Function from a Formula its Static Value will be Inserted When Available Replacing the Function Where RiskStatic Is Not Defined Use Percentile to Use as Swap Value X When Removing a TopRank Function from a Formula its Base Value will be Inserted the first argument in the Vary function Replacing the Function Swap In Options control how TopRank will report changes that it will make to your spreadsheet prior to inserting distribution functions back into formulas Spreadsheet formulas and values can be changed when TopRank and RISK functions are swapped out When swapping in TopRank will identify where it should
129. ort which you can use to preview the changes that will be made to a workbook when swapping functions in The report includes the Original Before Swap the Original After Swap the Current and the Recommended formulas to be swapped back in TopRank Model Changes Found Changes Were Made To This Model While RISK Functions Were Swapped Out Specify How RISK Should Update Formulas Use Formula Original Before Swap Original After Swap Current Recommended Sheeti F 48 Original Before Swap Original After Swap Current Recommended RiskAutoVary 800 15 15 800 900 RiskAutoVary 900 15 15 8717171 RiskAutoVary 1100 15 15 1100 1000 RiskAutoVary 1000 15 15 B Swap In RISK Functions Cancel If desired you can edit the Recommended formula to be swapped back in or alternatively select one of the other displayed formulas to be used when swapping back in By selecting the Edit icon s Create Report to Excel command at the bottom of the window you can choose to create a report in Excel of the changes made to the model S717 If TopRank is running it will automatically offer to swap in functions when a swapped out workbook is opened However this will not happen if the swapped out workbook is opened while TopRank s toolbar is disabled because functions are swapped out Swap Functions Command Utilities Commands Application Settings Command Displays the Application Settings dialo
130. over the Internet or via email e Activation if you have Internet Access In the Palisade License Activation dialog box type or paste the activation code and press Automatic via Internet A success message should appear after a few seconds and the License Activation dialog box will reflect the software s activated status e Activation if you do not have Internet Access Automated activation by email requires a few steps 1 Click Manual via Email to display the request xml file which you may save to disk or copy to the Windows clipboard It is recommended you note the location on your computer of the request xml file 2 Copy or attach the XML file to an email and send it to activation palisade com You should receive an automatic response to the return address in your email shortly 3 Save the response xml attachment in the response email to your hard drive 4 Clickon the Process button that is now in the Palisade License Activation dialog box and navigate to the response xml file Select the file and click OK A success message should appear and the License Activation dialog will reflect the software s activated status Software Activation 5 How do I transfer my software license to another machine Transfer of a license or rehosting may be peformed through the Palisade License Activation dialog box as a two step procedure deactivation on the first machine and activation on the second machine A typical use of rehosti
131. pRank o x PER eo Sr acs E sg A Report Settings id Help Add AutoVary Add Model RunWhatif Swap Input Functions Output Window Analysis Functions Model l _Analysis L Tools Al MO f ly IE B cjo E F G H 1 T mmm M N a TopRank Sensitivity Graph 2 Performed By Test Date Friday February 13 2009 10 38 39 AM Mode Factory xis Output Total Profit 060 2 3 4 5 6 Base Value 76666 6667 7 8 9 Sensitivity Graph Total Profit vs Labor Cost per Worker LC Factory 1 160000 140000 120000 100000 80000 Value ofTotal Profit 60000 40000 20000 Value of Labor Cost per Worker LC Factory 1 Chapter 3 Overview of TopRank 45 46 Running a What If Analysis in TopRank What is Multi Way What If Analysis TopRank Functions for Multi Way What If Analysis Group Size A Multi Way What If analysis studies the impact of combinations of several inputs in a spreadsheet on results In a Multi Way What If analysis combinations of inputs are varied at the same time and the results calculated by each combination is tracked Combinations are then ranked by their impact on each selected output Multi Way What If analysis is accomplished in TopRank using the Multi form of the Vary and VaryTable functions VaryMulti and VaryMultiTable functions identify those inputs to be included in a Multi Way What If analysis These functions are identical in form and
132. r negative change you think an input can reasonably have and maximum change possible upside or positive change you think an input can reasonably have Any change entered is relative to the base value or the first argument to the Vary function The minimum maximum range defines the values that TopRank returns for the Vary function during a What If analysis This range can be defined as any of three Type of Range options e Change from Base or a change and change from the displayed base value e Change from Base or value and value from the displayed base value e Actual Min and Max or an actual minimum value and maximum value to be used in defining the range The actual values returned by TopRank for each step of a Vary function depend on the Distribution setting The distribution selected describes how values are distributed across the minimum maximum range of the function For example if the Uniform distribution is selected any value in the minimum maximum range described by the Vary function is equally likely to occur Four distribution types are available with TopRank They are e Uniform rk e Triangular VW e Tri1090 A e Normal Reference TopRank Add In Commands 75 Converting Min max Ranges to Distribution Parameters 76 TopRank steps across the min max range defined by the Vary function by using the selected distribution type s percentiles Any probability distribution can be divided into 100 seg
133. r Excel worksheet or chart Have you ever wondered which variables matter most in your decision If so you need PrecisionTree s sensitivity analysis options Perform both one and two way sensitivity analyses and generate Tornado Graphs spider graphs strategy region graphs PrecisionTree Pro only and more For those who need more sophisticated sensitivity analyses PrecisionTree links directly to TopRank Palisade Corporation s sensitivity analysis add in Because decision trees can expand as more possible decision options are added PrecisionTree offers a set of features designed to help you reduce trees to a more manageable size All nodes can be collapsed hiding all paths which follow the node from view A single subtree can be referenced from multiple nodes in other trees saving the repeated re entry of the same Sometimes you need help in creating a utility function that is used to factor your attitude towards risk into the calculations in your decision models PrecisionTree contains features which help you identify your attitude towards risk and create your own utility functions PrecisionTree offers many advanced analysis options including e Utility functions e Use of multiple worksheets to define trees e Logic nodes Introduction to PrecisionTree Appendix A Using TopRank With Other DecisionTools 155 Using RISK with PrecisionTree Chance Events as a Continuous Range of Possible Outcomes 156 RISK is
134. r minimum and maximum depend on the Type of Range specified When TopRank inserts AutoVary functions when identifying inputs the minimum and maximum you specify is inserted into each newly created AutoVary function This range of course can be edited at any time by directly modifying the AutoVary function in the spreadsheet or by using the Add Input command Entered Minimum and Maximum values will also be the defaults displayed when the Add Input dialog is used The actual values returned by TopRank for each step of a Vary function depend on the Distribution setting The distribution selected describes how values are distributed across the minimum maximum range of the function For example if the Uniform distribution is selected any value in the minimum maximum range described by the Vary function is equally likely to occur Four distribution types are available with TopRank They are e Triang e Uniform e Normal e Tri1090 TopRank steps across the min max range defined by the Vary function by using the selected distribution type s percentiles Any probability distribution can be divided into 100 segments of equal probability At the 20th segment or percentile there is a 20 chance of a lower value and a 80 chance of a higher value occurring TopRank always returns the 0 percentile minimum and the 100 percentile maximum a minimum of two steps for each Vary function except for unbounded distributions such as Normal wh
135. rch is done to get weekly price quotes for memory and CPU s over the past two years This data is fed into RISK s distribution fitting and distributions are fitted to the data Confidence level information confirms that the distributions are good fits and the resulting RISK distribution functions are pasted into the model Once all the RISK functions are in place the Profit cells are selected as outputs and a simulation is run Overall the results look promising Although there will be losses initially there is an 8576 chance they will make an acceptable profit and a 2576 chance the venture will generate more revenue than they had initially assumed The Excelsior 5000 project has been given the go ahead Excelsior Electronics had assumed they would sell and distribute the Excelsior 5000 themselves However they could use various catalogs and computer warehouses to distribute their product A decision tree model is built using PrecisionTree taking into account unit prices sales volume and other critical factors for direct sales versus catalog sales A Decision Analysis is run and PrecisionTree suggests using catalogs and warehouses Excelsior Electronics puts that plan into full motion Palisade s DecisionTools Case Study Appendix A Using TopRank With Other DecisionTools 143 Introduction to TopRank TopRank is the ultimate What if tool for spreadsheets from Palisade Corporation TopRank greatly enhances the standard Wha
136. re insert TopRank and RISK functions and if desired show all the changes it is going to make to your formulas You can check these changes to make sure TopRank and RISK functions are returned as you wish In most cases Swap In is automatic as TopRank captures all changes to static values that were made when functions were swapped out It also automatically handles moved formulas and inserted rows and columns However if formulas where TopRank and RISK functions were previously located were deleted when functions were swapped out TopRank will notify you of the problem formulas prior to swapping functions back in TopRank Function Swap Options Swap Out i When Restoring TopRank Functions Static or Expected Values will be Replaced with their Original TopRank Function Prior to Restoring TopRank Functions Preview Changes C Al C Only Where Formulas or Static Values Were Modified Only Where Formulas Were Modified C None Swap Functions Command Swap In options for Prior to Restoring TopRank and RISK Functions Preview Changes include e All With this option all changes to be made to the model are reported even if a formula and swapped out value were not changed when TopRank and RISK functions were swapped out e Only Where Formulas or Static Values Were Modified With this option only changes to be made that include a changed static value or formula are reported For example if the original TopRank and RI
137. recalculations performed depends on the number of Vary functions entered the number of steps i e values across the min max range you want TopRank to try for each function the number of VaryTable functions entered and the values in each table used TopRank ranks all varied values by their impact on each result cell or output you ve selected Impact is defined as the amount of change in the output value that was calculated when the input value was changed If for example the result of your spreadsheet model was 100 prior to changing values and the result was 150 when an input changed there is a 50 change in results caused by changing the input TopRank results can be view graphically in a Tornado Spider or Sensitivity graph These graphs summarize your results to easily show the most important inputs for your results Appendix A Using TopRank With Other DecisionTools amp 147 Using RISK with TopRank Using What if Definitions ina Risk Analysis 148 What if analysis is often the first analysis performed on a spreadsheet Its results lead to a further refinement of the model additional analyses and ultimately a final decision based on the best model possible Risk analysis a powerful analytical technique available using TopRank s companion product RISK is often the next analysis performed on a spreadsheet after a What if analysis Moving from What if to Simulation A What if analysis initially identifies what s i
138. recognizes RISK distribution functions and incorporates them in What If analyses This ability provides more flexibility and accuracy in modeling the possible variation in input value in your What If analysis As compared with TopRank s standard Vary functions an RISK distribution can model a greater variety of ranges and probabilities In addition if data on an input is already collected in the form of a probability distribution entering it as a distribution in TopRank is the easiest way to go To examine how RISK distribution functions can be used in a TopRank What If analysis first open the worksheet titled RISK XLS Then review some of the distribution functions used in this model e Select cell C42 The formula in this cell reads RiskDiscrete 6000000000 8000000000 0 7 0 3 The Discrete distribution can take either of two values 6 000 000 000 or 8 000 000 000 There is a 70 chance of 6 000 000 000 and a 30 chance of 8 000 000 000 During a What If analysis each of these values is used Chapter 4 TopRank Modeling Techniques 61 62 What If Analysis with RISK Functions As with Vary functions TopRank steps across the min max range defined by a distribution function by using a distribution s percentiles Any probability distribution can be divided into 100 segments of equal probability At the 20th segment or percentile there is a 20 chance of a lower value and a 80 chance of a higher value occurring TopRank al
139. rmed By Test 3 Date Friday February 13 2009 10 36 39 AM 4 ModekFactory xis 5 Output Total Profit D60 6 Base Value 76666 6667 7 8 Tornado Graph of Total Profit Impact by Input Training Investment per Work Factory 5 H51 Labor Cost per Worker LC Factory 5 H48 Training Investment per Work Factory 4 G52 Labor Cost per Worker LC Factory 1 D48 Labor Cost per Worker LC Factory 4 G48 Training Investment per Work Factory 1 D51 Training Investment per Work Factory 82 E51 Labor Cost per Worker LC Factory 2 E48 Training Investment per Work Factory 3 F51 Labor Cost per Worker LC Factory 3 F48 M Tornado D60 Spider D60 Summary J 4 i Ready e The tornado graph brings attention to the inputs that require further attention those plotted on the top of the graph The tornado summarizes the impact of an almost unlimited number of inputs in a neat simple graph Running a What If Analysis Spider Graphs A spider graph also compares the effects of multiple inputs on results For each input the percentage change in its value from the base case is plotted on the X Axis and the percentage change in results is plotted on the Y Axis As inputs have different impacts on results the graph often resembles a spider r e aa eas Book Microsoft Excel CONT Home Inset Pagelayout For
140. rs and how changes in them affects the results of your decision What If analysis is a critical component of the decision modeling process Typically first the spreadsheet model is built Then What If analysis identifies the critical components of the model Additional data is then gathered on these critical variables and the model is refined Ultimately a decision is made using a robust model in which all important issues are identified What If analysis also aids in planning operations and contingencies Once a decision has been made to go based on your model your What If analysis has already identified what affects outcomes the most and what s insignificant This helps you plan by showing where you should prepare the most to guard against changes and where you don t need to worry if changes occur Chapter 2 Overview of What If Analysis 15 Automated What Ifs with TopRank 16 Perhaps for example your What If analysis identified labor costs as the critical factor affecting the profitability of a new assembly plant Because of your What If analysis results you ll spend more time insuring that there s a solid long term labor contract in place What If Analysis and the Computer The spreadsheet was revolutionary in bringing the benefits of What If modeling to a broad spectrum of users A simple What If analysis can be accomplished with any spreadsheet model by changing a value and recalculating Instantly you see the effect o
141. ryTable function during a What if analysis and results are calculated at each of the returned values Table values directly entered into the VaryTable function must be entered as arrays with notation Listing of TopRank Functions Listing Property Functions The following functions are used to add optional arguments to TopRank functions The arguments added by these functions are not required but can be added as needed Optional arguments are specified using property functions that are embedded inside of a TopRank function RiskLock Description RiskLock keeps an input from being varied in an analysis Locking an input causes it to return its base value during a what if analysis or in a simulation in RISK RiskVary 100 10 10 RiskLock stops the input from being varied in an analysis RiskName Description RiskName Input Name names the input in which the function is used as an argument This name will appear in both the TopRank Model Window list and in any reports and graphs which include results for the input RiskVary 100 10 10 RiskName Price gives the name Price to the input described by the base value of 100 and a variation of 10 and 10 RiskVary 100 10 10 RiskName A10 gives the name contained in the cell A10 to the input described by the Vary function RiskVary 100 10 10 The name specified must be entered in quotes Any valid cell references can be used to define a name Reference TopRank F
142. s Multi Way Tornado Diagram One Way Sensitivity Analysis One Way Sensitivity Graph Output Probabilistic Sensitivity Analysis Probability Probability Distribution Range An input is a constant value used in a cell or formula in your spreadsheet model that affects your results The most likely value or mode is the value that occurs most often in a set of values In a histogram and a result distribution it is the center value in the class or bar with highest probability The mean of a set of values is the sum of all the values in the set divided by the total number of values in the set Synonym expected value Number of inputs being varied together in a Multi Way What If analysis An analysis of the effect of varying multiple variables on outcome of a model Results are typically displayed in a Multi Way Tornado Diagram See Multi Way Tornado Diagram A Multi Way Tornado Diagram shows the impacts of combinations of varying inputs on the model in a bar format The Multi Way Tornado Diagram is usually used to display the results of Multi Way Sensitivity Analysis One Way Sensitivity Analysis studies the effect of changes in individual input variables on the output values of a spreadsheet Each input is changed individually while holding all others at their base case value See Sensitivity Analysis A graph comparing a variable against the expected value of a model as the value of the variable ranges from its
143. s 59 60 Multi Way Analysis Results TopRank identifies the combinations of factors that affect the output most It also provides a detailed Multi Way analysis report of how much each factor in the group varies to achieve a particular change in the output value In this example the effectiveness of Drug B on Category 4 cells combined with the effectiveness of Drug A on Category 4 cells is the most significant combination of inputs in determining overall effectiveness after biosynthesis Multi Way What If Analysis Modeling with RISK Functions Example Model RISK XLS RISK the risk analysis companion to TopRank defines uncertainty in your spreadsheet models by entering probability distributions in cells and formulas These functions describe the range of possible values for an input and the likelihood of values in that range occurring Distributions can be continuous where any value in the range of the distribution is possible or discrete where only specific values can be returned An example of a continuous distribution function is RiskNormal 100 25 representing a normal distribution with mean of 100 and standard deviation 25 An example of a discrete distribution function is RiskDiscrete 0 1 0 5 0 5 which models a two point distribution with 5076 chance of the value 0 occurring and 5076 chance of the value 1 occurring For Users of TopRank Industrial The industrial version of TopRank TopRank Industrial
144. sions i e inputs and each input can have different number of possible values i e steps Results from a Multi Way What If Analysis The standard results of a Multi Way What If analysis are a ranking of combinations of inputs by their impact on results Inputs are all VaryMulti and VaryMultiTable functions that were changed by TopRank during the Multi Way What If analysis Outputs are all output cells identified by you prior to the analysis The number of inputs used in each combination is given by the Group Size setting you selected prior to the analysis Running a Multi Way What If Analysis in TopRank Multi Way What If Results c m al 1 Outgsit Vanation Input Varation Mier an Factoy er Worte Famer Worker ILC Faber Worer c Pater Wortar LC Papar Warner Lc Value Change Value Change s Value Change lW Valve Change lH Value Change M Value crores Da 86 667 4 15 00 1300 T 800 556 667 15008 1200 656 667 ooon 3020 556 667 O 008 1380 556 667 0 008 1200 155 667 56 667 556 667 s176667 51 15176 667 276 667 G88eR assesses Rank 1 1 4 5 6 a 5 10 n s y SRERSRERERRERS ESSRSESESEEES PSSBERERERSERRE Labor Cost par Worker LC 472 178 764 208 667 Factory mi D48 Labor Cast per Worker LC Factary e M48 Lavor Cost ger Worker LC 472 17 206 667 Factory Pi G48 Labor Coat per Worker LC Factory 5
145. ss the What If icon to begin the sensitivity analysis 3 Once you ve reviewed your initial What If results try a new analysis under different assumptions 4 Click the Analysis Settings icon and change the range that inputs could vary In this example inputs can vary from 15 to 15 5 Press the What If icon to begin the new What If analysis Adjusting the Model with the Lock Command Suppose you discover that one input in your model can t be changed i e Labor Cost in Plant 1 and you want TopRank to exclude the input in its sensitivity analysis You can do this telling TopRank to lock the value of this input 1 Bring up the Model Window list by pressing the Model Window icon 2 M Highlight the input you wish to lock right click and select the Lock Input command 3 Press the What If icon to begin the new What If analysis The locked inputs are not varied in the analysis Chapter 4 TopRank Modeling Techniques 55 56 Further Analysis of the Model using RISK TopRank serves as a first step in an analytical process by determining which variables in the model are more important You can decide which variables should be carefully specified with probability distributions prior to running an RISK simulation In this example TopRank identifies Training Investment in Plant 5 as the most critical factor in improving the total revenue The detailed report from the Result window also shows that a 15 increase in the Train
146. sssssssscscssssssssssssssssssssssesesesesssssssnsnsesesssseseserenes 28 eid mM A 29 Chapter 2 Overview of What If Analysis 13 14 Using TopRank Introduction What If Analysis and Planning Have you ever wondered which factors matter most in your decision If so you need What If or sensitivity analysis which measures the impact on results of changing an uncertain variable across its possible values Change an assumption you ve made in your model and see how it affects results This is a simple What If analysis and it is a familiar activity to many What is What If What If analysis can be used for almost any type of model and is often done with a spreadsheet on a PC A business person for example might use a spreadsheet to combine the uncertain variables sales price sales volume production costs and investment amount to calculate the results they re interested in profits By individually varying each uncertain variable they can see how changes affect profits And by ranking the impacts of each variable on profits the What If analysis shows which is most important Better Decisions With What If Analysis By examining the impact of reasonable changes in base case assumptions What If analysis determines which variables have little impact on outcomes and which are significant This perspective on what s important can help structure the decision under consideration You can concentrate your attention on those critical facto
147. stment per Worker Factory 2 Training Investment per Worker Factory 1 Labor Cost per Worker LC Factory 2 Coefficient Value w E al Aj ela Y Ahi JI gt EE A What If analysis can also be performed on the probabilities used in a risk analysis In this form of What If analysis probabilities or input distribution parameters are changed by simulation One simulation is executed with one set of probabilities then a second is executed on the same model with different probabilities and so on The results of each simulation are then compared to identify the impact of changing probabilities RISK has the capability to run multiple simulations one after another and change values by simulation It then automatically compares results from each of the multiple simulations TopRank also can run multiple What If analyses one after another and change input variations by run You can then compare the results from each What If analysis and see how changes in assumptions affect your results What If Analysis and Risk Analysis TopRank RISK Sensitivity Analyses in RISK vs TopRank For a simple What If calculation TopRank determines how a single input affects the output by only changing the value of the input The values of all other inputs in the model remain constant This method is called single variable sensitivity analysis The advantages of a TopRank sensitivity analysis is that it is quick and easy TopRank can automa
148. stribution specifies an input variable in a What if analysis with a range defined by minimum and maximum If desired arguments for range type Steps and distribution can be entered If not entered the default range type steps and distribution are used The base value is the value returned by the function when a What if analysis is not underway Typically this is the value the was used in the spreadsheet prior to entering the Vary function RiskVary 100 10 10 0 8 TRIANG specifies a What if input with a base value of 100 a 10 and 10 range a percentage range type 8 steps and a triangular distribution across the min max range RiskVary 100 A1 B1 specifies a What if input with a range minimum defined by the value in A1 and range maximum defined by the value in B1 The default range type Steps and distribution are used Maximum must be greater than Base Base must be greater than Minimum Range Type 0 indicates a percentage change from base case is defined by minimum and maximum i e 20 and 20 Percentage is entered as an absolute percentage value such as 20 instead of 2 Range Type 1 indicates a actual change is defined by minimum and maximum i e 150 and 150 Range Type 2 indicates that the minimum entered is the actual minimum value in the range and the maximum entered is the actual minimum value in the range i e 90 and 110 Steps must be a positive integer Distrib must be TRIANG
149. t TopRank returns for the Vary function during a What If analysis This range can be defined as any of three Types e Change From Base or a change and change from the displayed base value e Change From Base or value and value from the displayed base value e Actual Min and Max or an actual minimum value and maximum value to be used in defining the range Each AutoVary function entered by TopRank has the same default minimum change and maximum change and the same default range type Because of this it is recommended that a default Change From Base range type be used so that ranges can be more meaningfully applied to the various inputs TopRank identifies in your spreadsheet What If Analysis Menu Minimum and Maximum Distribution Note A Range Type can be also selected in a Vary function by using the Range Type argument This is the third argument for the function For more information on specifying the Range Type in a Vary function see the Vary function in the TopRank Function Reference of this manual Minimum and Maximum specify the default changes that will be applied by TopRank to inputs it automatically identifies in your spreadsheet AutoVary function are substituted for these inputs You can enter a value select a value from the drop down list or click the Reference button to select a cell in your spreadsheet that contains a minimum or maximum value you wish to use Note the type of value entered fo
150. t if and data table capabilities found in your spreadsheet In addition you can easily step up to powerful risk analysis with its companion package RISK TopRank and What if Analysis TopRank helps you find out which spreadsheet value s or variable s affects your results the most an automated What if or sensitivity analysis You also can have TopRank automatically try any number of values for a variable a data table and tell you the results calculated at each value TopRank also tries all possible combinations of values for a set of variables a Multi Way What if analysis giving you the results calculated for each combination Running a What if or sensitivity analysis is a key component of making any decision based on a spreadsheet This analysis identifies which variables affect your results the most It shows you those factors you should be most concerned with as you 1 gather more data and refine your model and 2 manage and implement the situation described by the model TopRank is a spreadsheet add in for Microsoft Excel It can be used with any pre existing or new spreadsheet To set up your What if analyses TopRank adds new custom Vary functions to the spreadsheet function set These functions specify how the values in your spreadsheet can be varied in a What if analysis for example 10 and 10 1000 and 500 or according to a table of values you ve entered TopRank can also run a fully automatic What i
151. te the whole process of finding and defining inputs and it only needs to run a few iterations to get meaningful results And TopRank gives you answers in an easy to understand format The spider graph tells exactly how much a given change in an input affects the output The disadvantage of a TopRank sensitivity analysis is that is does not take the variability of other inputs into account The Multi Way sensitivity analysis can compensate for this weakness to an extent but does not always find cross correlation between input variables In an RISK simulation the value of each input changes simultaneously Data is collected for both the inputs and the output and the sensitivity is calculated using a rank order correlation coefficient or via linear regression The disadvantages of an RISK sensitivity analysis is that it requires a large number of iterations and you must use uncertainty distributions in your model And while the results tell you which variables have the greatest effect they do not tell you how much a given change in an input affects the output The advantage of an RISK sensitivity analysis is that it does not require an extra set of calculations if you are going to run an RISK simulation anyway And any cross correlation between inputs is always reported in the results We recommend using TopRank and RISK in most instances TopRank is quick and easy and present results in an easy to interpret form Use TopRank b
152. th Excel style menus and functions What if analysis and Data Tables are functions that can be performed directly in your spreadsheet but only in a manual unstructured format Simply changing a cell value in your spreadsheet and calculating a new result is a basic What if analysis A Data Table which gives a result for each combination of two values can also be built in your spreadsheet TopRank however performs these tasks automatically and analyzes their results for you It instantly performs What ifs on all possible values in your spreadsheet which could affect your result instead of requiring you to individually change values and recalculate It then tells you what spreadsheet value is most significant in determining your result TopRank also runs data table combinations automatically without requiring you to set up tables in your spreadsheet Combine more than two variables in its Multi Way What if analysis you can generate combinations of any number of variables and rank your combinations by their affect on your results You can perform these sophisticated and automated analyses quickly as TopRank keeps track of all the values and combinations it tries and their results separate from your spreadsheet By taking an automated approach TopRank gives you What if and Multi Way What if results almost instantly Even the least experienced modeler can get powerful analysis results Appendix A Using TopRank With Other Decis
153. th the mean located half way in between the two values Using this information TopRank generates a Normal distribution with the appropriate mean and standard deviation to include these 5th mean and 95th percentile values Note The default distribution setting is only used when a distribution type has not been explicitly entered as a Vary function argument The Steps entry specifies the number of steps TopRank calculates across the minimum maximum range of Vary functions During a What If analysis a value is returned and a new result calculated for each step for each Vary function The Steps entry is applied only when a Steps argument which must be greater or equal to two is not entered in a Vary function itself During a What If analysis a differing number of possible values can be returned for each Vary function For each value returned TopRank recalculates the worksheet and stores a new possible result for each output The number of values returned is given by the Steps argument to the Vary function or a default Steps entered in the Settings dialog In the function s simplest form such as Vary 100 10 10 there is no entry in the function for the Steps In this case TopRank uses the What If Analysis Menu default Steps A typical default is be three or four steps With four steps TopRank calculates the spreadsheet at the minimum possible value for the Vary function the maximum possible value and two values
154. the type of constants that TopRank selects as inputs in the precedent cells and formulas for an output cell For each selected constant TopRank inserts an AutoVary function By changing the criteria TopRank uses you can change the number of inputs TopRank automatically identifies During TopRank s identification of inputs constants can be found as Stand Alone Values in Cells where a constant is the only item in the formula in a precedent cell such as 100 or 100 In this case TopRank changes the cell s formula to RiskAutoVary 100 10 10 Values Embedded in Formulas where a constant is part of a mathematical expression in a precedent cell s formula such as 1 22 A10 where the identified constant is the value 1 22 In this case TopRank changes the cell s formula to RiskAutoVary 1 22 10 10 A10 Function Arguments where a constant is an argument to a spreadsheet function that is present in the precedent cell s formula such as NPV 0 1 C31 L31 where the identified constant is the value 0 1 In this case TopRank changes the cell s formula to NPV RiskAutoVary 0 1 10 10 C31 L31 Any combination of the above identification criteria can be used when TopRank automatically identifies inputs for a new output cell Reference TopRank Add In Commands 97 Search Ranges 98 Notes 1 RISK distribution functions such as RiskNormal 100 10 are always identified when TopRank identifies inputs 2 AutoVary
155. thout requiring you to set up tables in your spreadsheet Combine more than two variables in its Multi Way What If analysis you can generate combinations of any number of variables and rank your combinations by their affect on your results You can perform these sophisticated and automated analyses quickly as TopRank keeps track of all the values and combinations it tries and their results separate from your spreadsheet By taking an automated approach TopRank gives you What If and Multi Way What If results almost instantly Even the least experienced modeler can get powerful analysis results TopRank Functions How are TopRank Functions Entered Automated What lfs Welcome TopRank defines variations in spreadsheet values using functions To do this TopRank has added a set of new functions to the Excel function set each of which specifies a type of variation for your values These functions include e Vary and AutoVary functions which during a What If analysis change a spreadsheet value across a and range you define e VaryTable functions which during a What If analysis substitute each of a table of values for a spreadsheet value TopRank uses functions to change spreadsheet values during a What If analysis and keeps track of the results calculated for each value change These results are then ranked by the amount of change from the original expected results Then functions which caused the greatest change
156. ti Way results each bar represents the changes in results caused by a combination of two or more inputs g be gt Br Book2 Microsoft Excel _ x X Home Insert Pagelayout Formulas Data Review View Add Ins TopRank 7 X A i i p B 33 H Analysis Settings Y G 4f Utilities acs v i Report Settings e 9 Help Add AutoVary Add Model Run What If Swap Input Functions Output Window Analysis Functions Model je Analysis Tool 2 Q Y B c D E F J K L G H TopRank Output Summary Multi Way Analysis Tornado Chart for Multi Way Analysis of Total Profit Multi Way Impactby Input Training Investment per Work Factory 82 E51 Training Investment per Work Factory 3 F51 Training Investment per Work Factory 82 E51 Number of Workers Factory 3 F54 Training Investment per Work Factory 83 F51 Number of Workers Factory 3 F54 Value of Total Profit Chapter 2 Overview of What If Analysis 23 24 Multi Way What If Analysis What If Analysis and Risk Analysis Moving from What If to Simulation Using What If Definitions in a Risk Analysis What If analysis is often the first analysis performed on a spreadsheet Its results then lead to a further refinement of the model additional analyses and ultimately a final decision based on the best model possible Risk analysis a powerful analytical technique
157. to work Before contacting technical support please review the following checklist e Have you referred to the on line help e Have you checked this User s Guide and reviewed the on line multimedia tutorial e Have you read the README file It contains current information on TopRank that may not be included in the manual e Can you duplicate the problem consistently Can you duplicate the problem on a different computer or with a different model e Have you looked at our site on the World Wide Web It can be found at http www palisade com Our Web site also contains the latest FAQ a searchable database of tech support questions and answers and TopRank patches in our Technical Support section We recommend visiting our Web site regularly for all the latest information on TopRank and other Palisade software Introduction Contacting Palisade Palisade Corporation welcomes your questions comments or suggestions regarding TopRank Contact our technical support staff using any of the following methods Email us at support palisade com Telephone us at 607 277 8000 any weekday from 9 00 AM to 5 00 PM EST Follow the prompt to reach Technical Support Fax us at 607 277 8001 Mail us a letter to Technical Support Palisade Corporation 798 Cascadilla St Ithaca NY 14850 USA If you want to contact Palisade Europe Email us at support palisade europe com Telephone us at 44 1895 425050 UK Fax us at 44 1895 4250
158. ts of What If analysis results Tornado Graphs A tornado graph compares the effects of all input variables on a given output The X Axis is drawn in the units of change in output value For each variable listed on the Y Axis a bar is drawn between the extreme values of the output as calculated using the lower and upper input values The variable with the greatest range or longest bar the difference between the maximum and minimum value is plotted on the top of the graph and the variables proceed down the Y Axis with decreasing range ef Book2 Microsoft Excel mE LE Home Insert Page Layout Formulas Data Review View Add Ins TopRank 7 x AQ 13 3 H ZB Analysis Settings u Utilities Ha ss z A 4 Report Settings Help Add Ed Add Model Run What If Swap Input Functions Output Window Analysis Functions Model Analysis Tools f Y IM D E F G H 1 J K L M N o P da TopRank camo Graph co 4 e un i co f Tornado Graph of Total Profit Impact by Input Training Investment per Work Factory 5 HS1 Labor Cost per Worker LC Factory 5 H48 Training Investment per Work Factory 84 851 Labor Cost per Worker LC Factory 1 D48 Labor Cost per Worker LC Factory 4 G48 Training Investment per Work Factory 1 D51 Training Investment per Work Factory 82 E51 Labor Cost per Worker LC Factory 2 E48 Training Investment per Work Factory
159. udy and understand the risk inherent to a situation of interest Methods can be quantitative and or qualitative in nature An attitude toward risky situations where a decision maker is less likely to chose a situation with a higher payoff if it includes a proportionately higher risk There are situations where individuals may display the opposite behavior they are risk takers Distribution viewing companion for RISK and TopRank from Palisade Corporation A determination of which variables matter most in a decision are most critical by examining the impact of reasonable changes in base case assumptions Sensitivity analysis is useful for finding variable that have little impact on the final decision so that they can be treated deterministically The number of steps in a What If or Multi Way What If analysis determines how many varying values are going to be tried for every identified input in a model A graph showing the reasonable limits of change for each independent variable and the unit impact of these changes on the expected value of a model The square root of the variance See Variance What If analysis add in to Excel by Palisade Corporation The software discussed by this User s Guide The Industrial version of TopRank featuring unlimited Multi Way Group Size and support for RISK distribution functions in What If analyses Created after a one way sensitivity analysis a Tornado graph shows how much the value of an alter
160. uld reflect the expected value of the decision if you could perfectly predict the future The difference between the two values is the highest price you should pay perhaps by running more tests to find out more information before making the decision Using RISK with PrecisionTree Selecting RISK Outputs Running a risk analysis on a decision tree can produce many types of results depending on the cells in your model you select as outputs True expected value the value of perfect information and path probabilities can be determined Select the value of a start node of a tree or the beginning of any sub tree to generate a risk profile from an RISK simulation Since RISK distributions generate a wider range of random variables the resulting graph will be smoother and more complete than the traditional discrete risk profile Start Node Appendix A Using TopRank With Other DecisionTools 159 Appendix B Recommended Readings The TopRank manual has given you a start on understanding the concepts of decision analysis and What If analysis If you re interested in finding out more about the decision analysis techniques and the theory behind them here are some book and articles which examine various areas in the sensitivity analysis field Introduction to Sensitivity Analysis Clemen R T and Reilly T Making Hard Decisions 2nd Ed with DecisionTools Pacific Grove CA Duxbury Thomson Learning 2000 Raiffa Howard D
161. un What If Swap Input Functions Output Window Analysis Functions Model Analysis Tools I li Al ME f EN zm a i x B c D E F G H 1 J K L M N o P d TopRank Tornado Graph Performed By Test 2 3 Date Friday February 13 2009 10 36 39 AM 4 ModekFactory xis 5 Output Total Profit 060 6 Base Value 76666 6667 7 8 Tornado Graph of Total Profit Impact by Input Training Investment per Work Factory 5 H51 Labor Cost per Worker LC Factory 5 H48 Training Investment per Work Factory 4 651 Labor Cost per Worker LC Factory 048 Labor Cost per Worker LC Factory 4 G48 Training Investment per Work Factory 1 D51 Training Investment per Work Factory 82 E51 Labor Cost per Worker LC Factory 2 E48 Training Investment per Work Factory 3 F51 Labor Cost per Worker LC Factory 3 F48 Value of Total Profit al era M 4 Tornado _D60_ Spider D60 Summary J L Ready A The variable with the greatest range or longest bar the difference between the maximum and minimum value is plotted on the top of the graph and the variables proceed down the Y Axis with decreasing range The variable that has most influence on the output has the longest bar on the graph Reference TopRank Add In Commands 105 106 Spider Graphs A spider graph also compares the results for a single output as generated by multiple input
162. unction RiskVaryTable base value table ref takes a fixed number of arguments that are specified each time you use the function For others such as Vary you specify the number of arguments you desire based on your situation A Vary function can have three arguments or more as needed Reference TopRank Functions 125 Entering Arguments in Vary Functions 126 Like Excel functions Vary functions can have arguments which are references to cells or expressions For example RiskVary 100 B2 1 5 B3 In this case the value for the input minimum is specified by the value taken from cell B2 multiplied it by 1 5 and the value for input maximum is taken from cell B3 Vary functions can be used in cell formulas just as are Excel functions For example a cell formula could read B2 100 RiskVary 10 20 20 1 5 Risk VaryTable 40 A1 41000 All standard Excel editing commands are available to you when entering Vary functions However TopRank must be loaded for the Vary functions to be recognized by Excel If TopRank is not attached Excel returns VALUE for the function when the worksheet is recalculated The guidelines for entering Excel functions presented in the relevant users manual are also applicable to entering TopRank Vary functions However some additional guidelines specific to TopRank functions are e Vary functions can have between three and six and arguments A minimum of three arguments are required a
163. unction except for unbounded distributions such as the Normal where TopRank returns the 5 and 95 percentile instead Additional steps are spread equally on a percentile basis across the min max range If you ask for five steps for example TopRank returns values for the minimum and maximum plus the 25 50 and 75 percentiles a total of five values returned 5 recalculations and 5 new results generated Adding Inputs by Cell TopRank makes it easy to look at values in your spreadsheet and add change update or remove Vary and VaryTable functions associated with your inputs ee 7 d Won me 116 667 76 667 LEEREN Om When the Add Input window is displayed you can move to different cells in your worksheet and view their formulas By clicking on values and functions in the formula displayed in the Add Input window turning them red you can add edit or remove Vary functions by either e Typing directly in the formula or e Using the Min Base Max table below the formula to change parameters for a Vary function Chapter 3 Overview of TopRank 39 40 When adding inputs TopRank generates and inserts in the spreadsheet formula the necessary Vary function to go with the parameters you ve selected for an input You can also easily remove those Vary functions you don t want Looking at All Inputs and Outputs in Open Workbooks The Model window lists all selected output cells and all ident
164. unctions 133 Description RiskCategory Category Name names the category in which the input will be placed This name will appear in the category grouping in the TopRank Model Window list RiskVary 100 10 10 RiskCategory Research Costs places the input described by the Vary function RiskVary 100 10 10 in a category named Research Costs along with other Research Cost inputs RiskVary 100 10 10 RiskCategory A10 places the input described by the Vary function RiskVary 100 10 10 in a category named with the contents of the cell A10 The category name specified must be entered in quotes Any valid cell references can be used to define a category name 134 Listing Property Functions Listing Output Function Output cells are defined using RiskOutput functions These functions allow the easy copying pasting and moving of output cells RiskOutput functions are automatically added when the standard TopRank Add Output icon is pressed RiskOutput functions optionally allow you to name your outputs and add individual output cells to output ranges Reference TopRank Functions 135 RiskOutput 136 Description The function RiskOutput is used to identify output cells you have selected in your spreadsheet This function takes up to three arguments as shown here wow RiskOutput output cell name output range name element in range These arguments are optional as a simple RiskOutput is sufficie
165. ut Options Swapping RISK Functions When swapping out the primary value used for replacing a TopRank function is the base value that is given in the first argument of a Vary function For an RISK function the value used for replacing the function is its static value Typically this is the value in a formula in your model that was replaced by an RISK function It is stored in an RISK distribution in the RiskStatic property function TopRank Function Swap Options Swap Out Swap In When Removing an RISK Function from a Formula its Static Value will be Inserted When Available Replacing the Function Where RiskStatic Is Not Defined Use Corrected Expected Values v When Removing a TopRank Function from a Formula its Base Value will be Inserted the first argument in the Vary function Replacing the Function The Function Swap command swaps RISK distribution functions out along with TopRank s Vary functions Options for swapping RISK functions out in TopRank are the same as available in RISK For RISK functions if a Static value is not defined i e no RiskStatic function is present a set of different values are available for replacing the RISK functions value These are selected in the Where RiskStatic is Not Defined Use options and include e Corrected Expected Value or a distribution s expected or mean value except for discrete distributions For discrete distributions the setting C
166. variables For each input variable the percentage of the base case is plotted on the X Axis and the percent change in output value calculated is plotted on the Y Axis The slope of each line depicts the relative change in the output per unit change in the input variable The shape of the curve shows whether a linear or non linear relationship exists between the input and the output fa Ho e e Book2 Microsoft Excel mi oy amp Home Insert Page Layout Formulas Data Review View Add Ins TopRank o3 x His i3 m ZZ H Analysis Settings 2 fF Utilities P ER zs S 2 Report Settings ad amp Help Add AutoVay Add Model Run What f Swap Input Functions Output Window Analysis Functions Model Analysis Tools Al f C D E F G H 1 J K L M N 2 By Test 3 Date Friday February 13 2009 10 36 40 AM 4 Modet Factory xls 5 Output Total Profit D60 6 Base Value 75565 6667 7 8 Spider Graph of Total Profit Labor Cost per Worker LC D48 Labor Cost per Worker LC E48 Labor Cost per Worker LC F48 Labor Cost per Worker LC G48 Labor Cost per Worker LC H48 Output Change Training Investment per W D51 Input Change N aP Tomado D60 Spider_D60_ Summary 3 4 L Ready laou z Spider graphs provide more information about the variables than tornado diagrams For example spider graphs show the reasonable limits of change caused
167. ways returns the 076 percentile minimum and the 10076 percentile maximum a minimum of two steps for each distribution function except for unbounded distributions such as Normal where 5 and 95 percentiles are returned instead Additional steps are spread equally on a percentile basis across the min max range If you specify five steps for example TopRank returns values for the min and max plus the 2576 5076 and 75 percentiles a total of five values returned 5 recalculations and 5 new results generated To run a What If analysis on the RISK worksheet 1 Click the What If icon Five values are returned for each distribution function in the worksheet and results are calculated for each returned value 2 For the RISK worksheet Year 1 Fixed Costs and Year 1 Sales are the most critical factor in determining Total Revenues from the new car production project Modeling with RISK Functions TopRank Reference Guide Introduction 4 esses eene nnn nnn nnnm nnn 65 TopRank Menu Commands eese eee tentetnnnnnn 65 Reference TopRank ICONS eren 67 Introduction 4 esee eese eere nennen nnns 69 Model Menu 4 eres iniiai askai ainni ariani naira aa 71 Add Input Command eese tenente entente ntn 71 More About Vary and VaryTable Functions eee 75 Add AutoVary Functions Command
168. wilincrease the Tetai Proft by 110 me input variables cosia be varied by more ar less than 15 if yeu enon p Jost important factors are Labor Costs in iprocess by wies Should be Carefully specfied wit aama ia amn sora Ota map aut Toptani and RIS Aretora epo Production Capacity PD related to amp of workers Efficiency Factor EF related fo quality of workers in region land money invested in traimng program Units Produced UP IUPSPD EF 48 Labor Cost per Worker LC 200 300 51 reining m per Worker 100 200 300 270 300 400 LER she clc Ready poaa Reference TopRank Add In Commands 115 TopRank After Function Swap Swap Options 116 When functions are swapped out the TopRank toolbar is disabled and if you enter a TopRank function it will not be recognized The Swap options dialog allows you to specify how TopRank will operate when functions are swapped in and out If your workbook is changed when TopRank functions are swapped out TopRank can report to you how it will re insert TopRank functions into your changed model In most cases TopRank will be able to automatically handle changes to a workbook when functions are swapped out Factoryxits Compatibility Mode Microsoft Excel fomus Dats Renew View Addini TopRank o Y Au of uane v Gr Hep a Functions low Chemical Company operates tive production piares eschiocatedina diff
169. y Analysis A table of values to be substituted for an input in a What If analysis See VaryTable Appendix C Glossary of Terms 165 Index 166 Steps Setting 38 RISK Differences with TopRank 27 Functions 61 137 Using with TopRank 25 56 61 A About Command 123 Add AutoVary Functions Command 78 Add Output Command 79 Add In RISK 69 Authorization 123 Authorization Command 123 AUTOVARY Function 37 78 B Bibliography 159 D Data Tables 50 DecisionTools Suite 7 139 Distribution Setting 38 Don Vary This Input Command Button 71 E Example Models RISK TOP 61 FACTORY TOP 55 MULTIWAY TOP 59 VARYTABL TOP s 57 Execute Menu 91 F Fix Vary Input Command 55 G Glossary 161 Group Size Setting 47 H Help Menu 109 Icons Desktop 8 Input ID Settings 96 99 110 112 113 Inputs Defining 35 36 78 96 99 110 112 113 125 Defining Multi Way 48 Listing 82 Locking 133 Naming 133 134 Stepping Through 39 40 Installation Instructions 7 8 L List Inputs By Output Command 82 List of Values Defining 57 Menus Help Menu Model Window 123 Multi Way What if Analysis Defined 21 47 Results 22 50 60 Running 49 59 Using RISK with PrecisionTree N Not Auto Command Button 71 O One Way What if Analysis See What if Analysis Outputs Defining 79 Listing 8
170. yses on project schedules created in Microsoft Project the leading software package for project management Contact Palisade for more information on this exciting implementation of RISK Appendix A Using TopRank With Other DecisionTools 139 Purchasing Information All of the software mentioned here including the DecisionTools Suite can be purchased directly from Palisade Corporation To place an order or receive more information please contact the technical sales department at Palisade Corporation using one of the following methods e Telephone 800 432 7475 U S only or 607 277 8000 Mon Fri from 8 30 AM to 5 00 PM EST e Fax 607 277 8001 e E mail sales palisade com e Visit us on the Web at http www palisade com e Or mail a letter to Technical Sales Palisade Corporation 798 Cascadilla St Ithaca NY 14850 USA To contact Palisade Europe e E mail sales palisade europe com e Telephone 44 1895 425050 UK e Lax 44 1895 425051 UK e Or mail a letter to Palisade Europe 31 The Green West Drayton Middlesex UB7 7PN United Kingdom If you want to contact Palisade Asia Pacific e Email us at sales palisade com au e Telephone us at 61 2 9252 5922 AU e Fax us at 61 2 9252 2820 AU e Mail us a letter to Palisade Asia Pacific Pty Limited Suite 404 Level 4 20 Loftus Street Sydney NSW 2000 Australia 140 The DecisionTools Suite Appendix A Using TopRank With Other DecisionT

TopRank - Palisade Corporation

Contents

Download Pdf Manuals

Related Search

Related Contents