Decision Analysis Using Microsoft Excel

400 Pages·2006·2.74 MB·English

by  Michael R Middleton

D
ECISION
A
NALYSIS
U
SING
M
ICROSOFT
E
XCEL

S 2006
PRING

Michael R. Middleton
School of Business and Management
University of San Francisco
This page is intentionally mostly blank.

Copyright © 2006 by Michael R. Middleton
1
Detailed Contents

PART 1 MODELS AND SENSITIVITY ANALYSIS ......................11
Chapter 1 Introduction to Decision Modeling.............................................................13
1.1 Models to Aid Decision Making............................................................................13
Components of a Decision Model............................................................................14
1.2 Basic What-If Model..............................................................................................16
Influence Diagram Representation...........................................................................16
Decision Tree Representation..................................................................................18
Consequence Table Representation..........................................................................18
Chapter 2 Sensitivity Analysis Using SensIt................................................................19
2.1 How to Install SensIt..............................................................................................19
2.2 How to Uninstall or Delete SensIt..........................................................................20
2.3 SensIt Overview.....................................................................................................20
2.4 Example Problem...................................................................................................20
2.5 One Input, One Output...........................................................................................21
Cells for Input Variable............................................................................................22
Cells for Output Variable.........................................................................................22
Input Values.............................................................................................................22
2.6 Many Inputs, Many Outputs Tornado....................................................................23
Ranges for Input Variables.......................................................................................24
Cells for Output Variable.........................................................................................25
Ranges for Input Values...........................................................................................25
2.7 Tornado Sorted by Downside Risk........................................................................26
2.8 Tornado Sorted by Upside Potential......................................................................26
2.9 Tornado Showing Major Uncertainties..................................................................27
2.10 Spider...................................................................................................................28
2.11 Tips for Many Inputs, One Output.......................................................................29
2.12 Eagle Airlines Problem........................................................................................31
Chapter 3 Multiattribute Utility...................................................................................33
3.1 Applications of Multi-Attribute Utility..................................................................33
4 Detailed Contents
3.2 MultiAttribute Utility Swing Weights....................................................................34
Attribute Scores........................................................................................................35
Swing Weights.........................................................................................................36
Overall Scores..........................................................................................................37
3.3 Sensitivity Analysis Methods.................................................................................38
Dominance...............................................................................................................39
Monetary Equivalents Assessment...........................................................................39
Additive Utility Function.........................................................................................40
Weight Ratio Assessment.........................................................................................41
Weight Ratio Sensitivity Analysis...........................................................................43
Swing Weight Assessment.......................................................................................44
Swing Weight Sensitivity Analysis..........................................................................46
Direct Weight Assessment and Sensitivity Analysis................................................49
Summary..................................................................................................................51
Sensitivity Analysis Examples References..............................................................51
Screenshots from Excel to Word..............................................................................52
PART 2 MONTE CARLO SIMULATION.......................................53
Chapter 4 Introduction to Monte Carlo Simulation...................................................55
4.1 Introduction............................................................................................................55
Chapter 5 Uncertain Quantities....................................................................................57
5.1 Discrete Uncertain Quantities................................................................................57
5.2 Continuous Uncertain Quantities...........................................................................57
Case A: Uniform Density.........................................................................................57
Case B: Ramp Density.............................................................................................60
Case C: Triangular Density......................................................................................62
Chapter 6 Simulation Without Add-Ins.......................................................................65
6.1 Simulation Using Excel Functions.........................................................................65
Chapter 7 Monte Carlo Simulation Using RiskSim....................................................67
7.1 Using RiskSim Functions.......................................................................................67
7.2 Using RiskSim Functions.......................................................................................68
7.3 Updating Links To RiskSim Functions..................................................................68
7.4 Monte Carlo Simulation.........................................................................................70
7.5 Random Number Seed...........................................................................................71
7.6 One-Output Example..............................................................................................72
7.7 RiskSim Output for One-Output Example.............................................................73
7.8 Customizing RiskSim Charts.................................................................................75
7.9 Random Number Generator Functions...................................................................77
RandBinomial..........................................................................................................77
Detailed Contents 5
RandBiVarNormal...................................................................................................78
RandCumulative.......................................................................................................79
RandDiscrete............................................................................................................80
RandExponential......................................................................................................82
RandInteger..............................................................................................................83
RandNormal.............................................................................................................84
RandSample.............................................................................................................85
RandPoisson.............................................................................................................85
RandTriangular........................................................................................................86
RandUniform............................................................................................................87
7.10 RiskSim Technical Details...................................................................................88
7.11 Modeling Uncertain Relationships.......................................................................90
Base Model, Four Inputs..........................................................................................90
Three Inputs.............................................................................................................91
Two Inputs...............................................................................................................92
Four Inputs with Three Uncertainties.......................................................................93
Intermediate Details.................................................................................................95
Chapter 8 Multiperiod What-If Modeling...................................................................97
8.1 Apartment Building Purchase Problem..................................................................97
Apartment Building Analysis Notes.......................................................................100
8.2 Product Launch Financial Model.........................................................................101
8.3 Machine Simulation Model..................................................................................105
AJS Process 1.........................................................................................................105
AJS Process 2.........................................................................................................106
Chapter 9 Modeling Inventory Decisions...................................................................113
9.1 Newsvendor Problem...........................................................................................113
Stationery Wholesaler Example.............................................................................113
Chapter 10 Modeling Waiting Lines..........................................................................115
10.1 Queue Simulation...............................................................................................115
PART 3 DECISION TREES........................................................121
Chapter 11 Introduction to Decision Trees................................................................123
11.1 Decision Tree Structure......................................................................................123
DriveTek Problem, Part A......................................................................................123
Nodes and Branches...............................................................................................124
11.2 Decision Tree Terminal Values..........................................................................126
DriveTek Problem, Part B......................................................................................126
11.3 Decision Tree Probabilities................................................................................128
DriveTek Problem, Part C......................................................................................128
6 Detailed Contents
Chapter 12 Decision Trees Using TreePlan...............................................................129
12.1 TreePlan Installation..........................................................................................129
Occasional Use.......................................................................................................129
Selective Use..........................................................................................................129
Steady Use..............................................................................................................130
12.2 Building a Decision Tree in TreePlan................................................................130
12.3 Anatomy of a TreePlan Decision Tree...............................................................132
12.4 Step-by-Step TreePlan Tutorial..........................................................................134
DriveTek Problem..................................................................................................134
Nodes and Branches...............................................................................................135
Terminal Values.....................................................................................................136
Building the Tree Diagram.....................................................................................137
Interpreting the Results..........................................................................................145
Formatting the Tree Diagram.................................................................................146
Displaying Model Inputs........................................................................................148
Printing the Tree Diagram......................................................................................150
Alternative Model..................................................................................................151
12.5 Decision Tree Solution.......................................................................................151
Strategy..................................................................................................................151
Payoff Distribution.................................................................................................152
DriveTek Strategies................................................................................................152
Strategy Choice......................................................................................................156
Certainty Equivalent...............................................................................................157
Rollback Method....................................................................................................159
Optimal Strategy....................................................................................................160
12.6 Newox Decision Tree Problem..........................................................................162
12.7 Brandon Decision Tree Problem........................................................................163
Decision Tree Strategies.........................................................................................163
Chapter 13 Sensitivity Analysis for Decision Trees...................................................171
13.1 One-Variable Sensitivity Analysis.....................................................................171
13.2 Two-Variable Sensitivity Analysis.....................................................................173
Setup for Data Table..............................................................................................174
Obtaining Results Using Data Table Command.....................................................174
Embellishments......................................................................................................175
13.3 Multiple-Outcome Sensitivity Analysis.............................................................176
13.4 Robin Pinelli's Sensitivity Analysis...................................................................177
Chapter 14 Value of Information in Decision Trees.................................................181
14.1 Value of Information..........................................................................................181
14.2 Expected Value of Perfect Information..............................................................181
Expected Value of Perfect Information, Reordered Tree.......................................182
Expected Value of Perfect Information, Payoff Table...........................................185
Expected Value of Perfect Information, Expected Improvement...........................186
Detailed Contents 7
Expected Value of Perfect Information, Single-Season Product............................187
14.3 DriveTek Post-Contract-Award Problem...........................................................190
14.4 Sensitivity Analysis vs EVPI.............................................................................194
Chapter 15 Value of Imperfect Information..............................................................195
15.1 Technometrics Problem......................................................................................195
Prior Problem.........................................................................................................195
Imperfect Information............................................................................................196
Probabilities From Relative Frequencies................................................................196
Revision of Probability...........................................................................................200
Chapter 16 Modeling Attitude Toward Risk.............................................................201
16.1 Risk Utility Function..........................................................................................201
16.2 Exponential Risk Utility.....................................................................................204
16.3 Approximate Risk Tolerance..............................................................................207
16.4 Exact Risk Tolerance Using Excel.....................................................................207
16.5 Exact Risk Tolerance Using RiskTol.xla...........................................................211
16.6 Exponential Utility and TreePlan.......................................................................212
16.7 Exponential Utility and RiskSim........................................................................212
16.8 Risk Sensitivity for Machine Problem...............................................................214
16.9 Risk Utility Summary.........................................................................................215
Concepts.................................................................................................................215
Fundamental Property of Utility Function.............................................................216
Using a Utility Function To Find the CE of a Lottery............................................216
Exponential Utility Function..................................................................................216
TreePlan's Simple Form of Exponential Utility.....................................................216
Approximate Assessment of RiskTolerance..........................................................216
Exact Assessment of RiskTolerance......................................................................217
Using Exponential Utility for TreePlan Rollback Values......................................217
Using Exponential Utility for a Payoff Distribution..............................................218
PART 4 DATA ANALYSIS.........................................................219
Chapter 17 Introduction to Data Analysis.................................................................221
17.1 Levels of Measurement......................................................................................221
Categorical Measure...............................................................................................221
Numerical Measure................................................................................................221
17.2 Describing Categorical Data..............................................................................222
17.3 Describing Numerical Data................................................................................222
Frequency Distribution and Histogram..................................................................222
Numerical Summary Measures..............................................................................222
Distribution Shapes................................................................................................223
8 Detailed Contents
Chapter 18 Univariate Numerical Data.....................................................................225
18.1 Analysis Tool: Descriptive Statistics..................................................................225
Formatting the Output Table..................................................................................228
Interpreting Descriptive Statistics..........................................................................229
Another Measure of Skewness...............................................................................231
18.2 Analysis Tool: Histogram..................................................................................233
Histogram Embellishments....................................................................................235
18.3 Better Histograms Using Excel..........................................................................237
Exercises....................................................................................................................238
Chapter 19 Bivariate Numerical Data........................................................................239
19.1 XY (Scatter) Charts............................................................................................240
19.2 Analysis Tool: Correlation.................................................................................242
19.3 Analysis Tool: Covariance.................................................................................244
19.4 Correlations for Several Variables.....................................................................245
Exercises....................................................................................................................247
Chapter 20 One-Sample Inference for the Mean......................................................249
20.1 Normal versus t Distribution..............................................................................249
20.2 Hypothesis Tests................................................................................................249
Left-Tail, Right-Tail, or Two-Tail.........................................................................250
Decision Approach or Reporting Approach...........................................................250
Chapter 21 Simple Linear Regression........................................................................253
21.1 Inserting a Linear Trendline...............................................................................254
Trendline Interpretation..........................................................................................256
Trendline Embellishments......................................................................................257
21.2 Regression Analysis Tool...................................................................................257
Regression Interpretation.......................................................................................261
Regression Charts...................................................................................................262
21.3 Regression Functions.........................................................................................264
Exercises....................................................................................................................267
Chapter 22 Simple Nonlinear Regression..................................................................269
22.1 Polynomial.........................................................................................................271
22.2 Logarithmic........................................................................................................273
22.3 Power.................................................................................................................275
22.4 Exponential........................................................................................................277
Exercises....................................................................................................................282
Chapter 23 Multiple Regression.................................................................................283
23.1 Interpretation of Regression Output...................................................................285
Significance of Coefficients...................................................................................285
Interpretation of the Regression Statistics..............................................................286
Detailed Contents 9
Interpretation of the Analysis of Variance.............................................................286
23.2 Analysis of Residuals.........................................................................................286
23.3 Using TREND to Make Predictions...................................................................288
Interpretation of the Predictions.............................................................................289
Exercises....................................................................................................................290
Chapter 24 Regression Using Categorical Variables................................................293
24.1 Categories as Explanatory Variables..................................................................293
24.2 Interpretation of Regression Using Indicators....................................................296
24.3 Interpretation of Multiple Regression................................................................297
24.4 Categories as the Dependent Variable................................................................298
Interpretation of the Classifications.......................................................................301
Exercises....................................................................................................................302
Chapter 25 Regression Models for Cross-Sectional Data.........................................305
25.1 Cross-Sectional Regression Checklist................................................................305
Plot Y versus each X..............................................................................................305
Examine the correlation matrix..............................................................................305
Calculate the regression model with diagnostics....................................................305
Use the model.........................................................................................................306
Chapter 26 Time Series Data and Forecasts..............................................................307
26.1 Time Series Patterns...........................................................................................307
Chapter 27 Autocorrelation and Autoregression......................................................311
27.1 Linear Time Trend.............................................................................................312
27.2 Durbin-Watson Statistic.....................................................................................313
27.3 Autocorrelation..................................................................................................314
27.4 Autoregression...................................................................................................316
27.5 Autocorrelation Coefficients Function...............................................................320
27.6 AR(2) Model......................................................................................................322
Exercises....................................................................................................................324
Chapter 28 Time Series Smoothing............................................................................325
28.1 Moving Average Using Add Trendline..............................................................327
28.2 Moving Average Data Analysis Tool.................................................................329
28.3 Exponential Smoothing Tool..............................................................................330
Exercises....................................................................................................................333
Chapter 29 Time Series Seasonality...........................................................................335
29.1 Regression Using Indicator Variables................................................................336
29.2 AR(4) Model......................................................................................................342
29.3 Classical Time Series Decomposition................................................................347
Exercises....................................................................................................................354
10 Detailed Contents
Chapter 30 Regression Models for Time Series Data...............................................357
30.1 Time Series Regression Checklist......................................................................357
Plot Y versus time..................................................................................................357
Plot Y versus each X..............................................................................................357
Examine the correlation matrix..............................................................................357
Calculate the regression model with diagnostics....................................................358
Use the model.........................................................................................................358
30.2 Autocorrelation of Residuals..............................................................................359
PART 5 CONSTRAINED OPTIMIZATION..................................361
Chapter 31 Product Mix Optimization......................................................................363
31.1 Linear Programming Concepts...........................................................................363
Formulation............................................................................................................363
Graphical Solution..................................................................................................363
Sensitivity Analysis................................................................................................363
31.2 Basic Product Mix Problem...............................................................................365
31.3 Outdoors Problem..............................................................................................370
Spreadsheet Model.................................................................................................372
Solver Reports........................................................................................................373
Chapter 32 Modeling Marketing Decisions...............................................................375
32.1 Allocating Advertising Expenditures.................................................................375
Chapter 33 Nonlinear Product Mix Optimization....................................................381
33.1 Diminishing Profit Margin.................................................................................381
Chapter 34 Integer-Valued Optimization Models.....................................................383
34.1 Transportation Problem......................................................................................383
34.2 Modified Transportation Problem......................................................................384
34.3 Scheduling Problem...........................................................................................386
Chapter 35 Optimization Models for Finance Decisions..........................................389
35.1 Working Capital Management Problem.............................................................389
35.2 Work Cap Alternate Formulations.....................................................................391
35.3 Stock Portfolio Problem.....................................................................................393
35.4 MoneyCo Problem.............................................................................................395
Appendix Excel for the Macintosh..............................................................................397
The Shortcut Menu.................................................................................................397
Relative and Absolute References..........................................................................397
References.....................................................................................................................399