CHAPTER NINE
Perform Deterministic Analysis and Develop Insights
ERIC R. JOHNSON, GREGORY S. PARNELL, and STEVEN N. TANI
All models are wrong, but some are useful.
—George E P Box
9.1 Introduction
9.2 Planning the Model: Influence Diagrams
9.3 Spreadsheet Software as the Modeling Platform
9.4 Guidelines for Building a Spreadsheet Decision Model
9.4.1 Keep Inputs Separated from Calculations
9.4.2 Parameterize Everything
9.4.3 Use Range Names for Readability
9.4.4 Use Uniform Indexing for Rows and Columns of a Sheet
9.4.5 Manage the Model Configurations
9.5 Organization of a Spreadsheet Decision Model
9.5.1 Value Components
9.5.2 Decisions
9.5.3 Uncertainties
9.5.4 Business Units
9.5.5 Time
9.5.6 Representation of Business Units, Value Components, and Time: P&L Calculation Sheet(s)
9.5.7 Inputs Sheet(s)
9.6 Spreadsheet Model for the RNAS Illustrative Example
9.6.1 Selectors
9.6.2 Inputs and Strategy Table Sheets
9.6.3 Calculations Sheets
9.7 Debugging the Model
9.8 Deterministic Analysis
9.8.1 Sources of Value
9.8.2 Deterministic Sensitivity Analysis
9.8.3 Scenario Analysis
9.9 Deterministic Modeling Using Monetary Multidimensional Value Functions (Approach 1B)
9.10 Deterministic Modeling Using Nonmonetary Multidimensional Value Functions (Approach 1A)
9.10.1 The Additive Value Function
9.10.2 Single-Dimensional Value Functions
9.10.3 Swing Weights
9.10.4 Swing Weight Matrix
9.10.5 Scoring the Alternatives
9.10.6 Deterministic Analysis
9.11 Illustrative Examples
9.11.1 Geneptin
9.11.2 ...