Management Science: The Art of Modeling with Spreadsheets , Third Edition

COVER......Page 1
TITLE PAGE......Page 3
COPYRIGHT......Page 4
BRIEF CONTENTS......Page 6
TABLE OF CONTENTS......Page 7
PREFACE......Page 11
ABOUT THE AUTHORS......Page 15
1.1 Models And Modeling......Page 17
1.1.2 Models in Business......Page 18
1.1.3 Models in Business Education......Page 19
1.2 The Role of Spreadsheets......Page 20
1.2.1 Risks of Spreadsheet Use......Page 21
1.2.2 Challenges for Spreadsheet Users......Page 22
1.3 The Real World and the Model World......Page 24
1.4 Lessons From Expert and Novice Modelers......Page 26
1.4.1 Expert Modelers......Page 27
1.4.2 Novice Modelers......Page 28
1.5 Organization of the Book......Page 30
Suggested Readings......Page 31
2.1 Introduction......Page 32
2.2 The Problem-Solving Process......Page 33
2.2.1 Some Key Terms......Page 34
2.2.2 The Six-Stage Problem-Solving Process......Page 35
2.2.3 Mental Models and Formal Models......Page 41
2.3 Influence Charts......Page 42
2.3.1 A First Example......Page 43
2.3.2 An Income Statement as an Influence Chart......Page 45
2.3.4 Two Additional Examples......Page 46
2.4 Craft Skills for Modeling......Page 50
2.4.1 Simplify the Problem......Page 52
2.4.2 Break the Problem into Modules......Page 53
2.4.3 Build a Prototype and Refine It......Page 54
2.4.4 Sketch Graphs of Key Relationships......Page 57
2.4.5 Identify Parameters and Perform Sensitivity Analysis......Page 59
2.4.6 Separate the Creation of Ideas from Their Evaluation......Page 61
2.4.8 Focus on Model Structure, not on Data Collection......Page 62
2.5 Summary......Page 65
Exercises......Page 66
3.2 Excel Prerequisites......Page 68
3.3 The Excel Window......Page 69
3.4 Configuring Excel......Page 71
3.6 Navigation......Page 73
3.8 Entering Text and Data......Page 75
3.9 Editing Cells......Page 76
3.10 Formatting......Page 77
3.11 Basic Formulas......Page 78
3.12 Basic Functions......Page 79
3.13 Charting......Page 83
3.14 Printing......Page 86
3.16 Summary......Page 87
Suggested Readings......Page 88
4.2 Keyboard Shortcuts......Page 89
4.3 Controls......Page 90
4.4 Cell Comments......Page 93
4.5 Naming Cells and Ranges......Page 94
4.6 Advanced Formulas and Functions......Page 96
4.6.2 Mixed Addresses......Page 97
4.6.3 Nesting Calculations......Page 98
4.6.4 Parameterization......Page 99
4.6.5 Advanced Functions......Page 100
4.7.1 Recording a Macro......Page 104
4.7.2 Editing a Macro......Page 106
4.7.3 Creating a User-Defined Function......Page 109
Suggested Readings......Page 110
5.1 Introduction......Page 111
5.2.1 Sketch the Spreadsheet......Page 113
5.2.2 Organize the Spreadsheet into Modules......Page 114
5.2.3 Start Small......Page 115
5.2.5 Design for Use......Page 116
5.2.7 Design for Communication......Page 117
5.2.8 Document Important Data and Formulas......Page 118
5.3 Designing a Workbook......Page 119
5.3.1 Use Separate Worksheets to Group Similar Kinds of Information......Page 120
5.3.2 Design Workbooks for Ease of Navigation and Use......Page 121
5.3.3 Design a Workbook as a Decision-Support System......Page 123
5.4.4 Copy and Paste Formulas Carefully......Page 125
5.4.7 Use Range Names to Make Formulas Easy to Read......Page 126
5.5 Testing a Workbook......Page 127
5.5.2 Check That Formulas Are Correct......Page 128
5.6 Summary......Page 132
Exercises......Page 133
6.1 Introduction......Page 135
6.3 What-If Analysis......Page 136
6.3.1 Benchmarking......Page 138
6.3.2 Scenarios......Page 139
6.3.3 Parametric Sensitivity......Page 141
6.3.4 Tornado Charts......Page 145
6.4 Breakeven Analysis......Page 146
6.6 Simulation and Risk Analysis......Page 148
Exercises......Page 150
7.1 Introduction......Page 153
7.2 Finding Facts from Databases......Page 154
7.2.1 Searching and Editing......Page 156
7.2.2 Sorting......Page 157
7.2.3 Filtering......Page 159
7.2.4 Tabulating......Page 161
7.3 Analyzing Sample Data......Page 164
7.4 Estimating Parameters: Point Estimates......Page 166
7.5 Estimating Parameters: Interval Estimates......Page 167
7.5.1 Interval Estimates for the Mean......Page 168
7.5.3 Sample-Size Determination......Page 171
Exercises......Page 172
8.1 Introduction......Page 175
8.2.1 Base-case Analysis......Page 176
8.2.2 Sensitivity Analysis......Page 177
8.3 Exploring Data: Scatter Plots and Correlation......Page 179
8.4 Simple Linear Regression......Page 181
8.5 Goodness-of-Fit......Page 182
8.6 Simple Regression in the BPI Example......Page 186
8.7 Simple Nonlinear Regression......Page 189
8.8 Multiple Linear Regression......Page 190
8.9 Multiple Regression in the BPI Example......Page 192
8.10 Regression Assumptions......Page 194
8.11.1 Trendline......Page 195
8.11.2 LINEST......Page 197
8.12 Summary......Page 198
Exercises......Page 199
9.2 Forecasting with Time-Series Models......Page 202
9.2.1 The Moving-Average Model......Page 203
9.2.2 Measures of Forecast Accuracy......Page 206
9.3 The Exponential Smoothing Model......Page 207
9.4 Exponential Smoothing with a Trend......Page 211
9.5 Exponential Smoothing with Trend and Cyclical Factors......Page 215
Exercises......Page 218
10.1 Introduction......Page 222
10.2.1 Optimizing Q1......Page 223
10.2.2 Optimization Over All Four Quarters......Page 226
10.2.3 Incorporating the Budget Constraint......Page 227
10.3.1 Formulation......Page 229
10.3.2 Layout......Page 231
10.4 Model Classification and the Nonlinear Solver......Page 232
10.5.1 Facility Location......Page 234
10.5.2 Revenue Maximization......Page 236
10.5.3 Curve Fitting......Page 238
10.5.4 Economic Order Quantity......Page 241
10.6 Sensitivity Analysis for Nonlinear Programs......Page 243
10.7* The Portfolio Optimization Model......Page 248
Suggested Readings......Page 251
Exercises......Page 252
11.1.1 Linearity......Page 256
11.1.2 Simplex Algorithm......Page 257
11.2 Allocation Models......Page 258
11.2.2 Spreadsheet Model......Page 259
11.2.3 Optimization......Page 261
11.3.1 Formulation......Page 263
11.3.2 Spreadsheet Model......Page 264
11.3.3 Optimization......Page 265
11.4.1 Blending Constraints......Page 266
11.4.2 Formulation......Page 268
11.4.3 Spreadsheet Model......Page 269
11.4.4 Optimization......Page 270
11.5.1 Sensitivity to Objective Function Coefficients......Page 271
11.5.2 Sensitivity to Constraint Constants......Page 273
11.6.1 Identifying Patterns......Page 276
11.6.2 Further Examples......Page 278
11.6.3 Review......Page 283
11.7* Data Envelopment Analysis......Page 284
11.8 Summary......Page 288
Exercises......Page 289
Appendix 11.1 The Solver Sensitivity Report......Page 294
12.2 The Transportation Model......Page 297
12.2.2 Model Formulation......Page 298
12.2.3 Spreadsheet Model......Page 299
12.2.4 Optimization......Page 300
12.2.5 Modifications to the Model......Page 301
12.2.6 Sensitivity Analysis......Page 302
12.3 Assignment Model......Page 306
12.3.1 Model Formulation......Page 307
12.3.4 Sensitivity Analysis......Page 308
12.4 The Transshipment Model......Page 309
12.4.1 Formulation......Page 310
12.4.2 Spreadsheet Model......Page 311
12.4.3 Optimization......Page 312
12.5 A Standard Form for Network Models......Page 313
12.6.1 Yields as Reductions in Flow......Page 316
12.6.2 Yields as Expansions in Flow......Page 317
12.6.3 Patterns in General Network Models......Page 320
12.7* Network Models for Process Technologies......Page 321
12.7.1 Formulation......Page 322
12.7.3 Optimization......Page 324
Exercises......Page 325
13.1 Introduction......Page 330
13.2 Integer Variables and the Integer Solver......Page 331
13.3.1 The Capital Budgeting Problem......Page 333
13.3.2 The Set Covering Problem......Page 336
13.4.1 Relationships Among Projects......Page 338
13.4.2 Linking Constraints and Fixed Costs......Page 340
13.4.3 Threshold Levels and Quantity Discounts......Page 343
13.5* The Facility Location Model......Page 345
13.5.1 The Capacitated Problem......Page 346
13.5.2 The Uncapacitated Problem......Page 348
13.5.3 The Assortment Model......Page 350
13.6 Summary......Page 351
Exercises......Page 352
14.2 Features of the Evolutionary Solver......Page 356
14.3 An Illustrative Example: Nonlinear Regression......Page 357
14.4 The Advertising Budget Problem (Revisited)......Page 364
14.5 The Capital Budgeting Problem (Revisited)......Page 366
14.6 The Fixed Cost Problem (Revisited)......Page 368
14.7 The Machine-Sequencing Problem......Page 369
14.8 The Traveling Salesperson Problem......Page 372
14.9 Group Assignment......Page 374
Exercises......Page 377
15.1 Introduction......Page 381
15.2.1 Benchmark Criteria......Page 382
15.2.2 Incorporating Probabilities......Page 384
15.3 Using Trees to Model Decisions......Page 385
15.3.1 Decision Trees......Page 386
15.3.2 Decision Trees for a Series of Decisions......Page 389
15.3.3 Principles for Building and Analyzing Decision Trees......Page 392
15.3.4 The Cost of Uncertainty......Page 393
15.4 Using Decision Tree Software......Page 394
15.4.1 Solving a Simple Example with Decision Tree......Page 395
15.4.2 Sensitivity Analysis with Decision Tree......Page 398
15.5* Maximizing Expected Utility with Decision Tree......Page 399
15.6 Summary......Page 403
Exercises......Page 404
16.1 Introduction......Page 408
16.2 A Simple Illustration......Page 409
16.3 The Simulation Process......Page 411
16.3.1 Base-Case Model......Page 412
16.3.2 Sensitivity Analysis......Page 414
16.3.3 Specifying Probability Distributions......Page 415
16.3.4 Specifying Outputs......Page 416
16.3.6 Analyzing Simulation Outputs......Page 417
16.4 Corporate Valuation Using Simulation......Page 421
16.4.1 Base-Case Model......Page 422
16.4.2 Sensitivity Analysis......Page 424
16.4.3 Selecting Probability Distributions......Page 425
16.4.4 Simulation Analysis......Page 426
16.4.5 Simulation Sensitivity......Page 428
16.5.1 The Logic of Options......Page 431
16.5.2 Modeling Stock Prices......Page 432
16.5.3 Pricing an Option......Page 435
16.5.4 Sensitivity to Volatility......Page 436
16.6 Selecting Uncertain Parameters......Page 437
16.7.1 Empirical Data and Judgmental Data......Page 440
16.7.2 Six Essential Distributions......Page 441
16.7.3 Fitting Distributions to Data......Page 446
16.8.1 Illustrations of Simulation Error......Page 448
16.8.2 Precision versus Accuracy......Page 450
16.8.4 Precision Using the MSE......Page 451
16.8.5 Simulation Error in a Decision Context......Page 452
16.9.1 Simulation Results......Page 453
16.9.2 Displaying Results on the Spreadsheet......Page 454
16.10*When to Simulate and When Not to Simulate......Page 456
16.11 Summary......Page 458
Exercises......Page 459
17.2 Optimization with One or Two Decision Variables......Page 464
17.2.1 Base-case Model......Page 465
17.2.2 Grid Search......Page 467
17.2.3 Optimizing using Simulation Sensitivity......Page 469
17.2.4 Optimizing Using Solver......Page 471
17.3.1 Optimization of the Base-Case Model......Page 472
17.3.2 A Portfolio Optimization Problem......Page 474
17.4 Chance Constraints......Page 477
17.5 Two-Stage Problems with Recourse......Page 483
Exercises......Page 488
Retirement Planning......Page 494
Icebergs for Kuwait......Page 495
The Racquetball Racket......Page 496
The XYZ Company......Page 497
Reid’s Raisin Company......Page 498
Flexible Insurance Coverage......Page 499
Cox Cable and Wire Company......Page 500
The BMW Company......Page 502
The ERP Decision......Page 503
National Leasing, Inc.......Page 506
Mega Pharma and Micro Pharma......Page 508
Probability Distributions......Page 510
Examples of Discrete Distributions......Page 513
Examples of Continuous Distributions......Page 514
Expected Values......Page 515
Tail Probabilities......Page 517
Variability......Page 518
Sampling Theory......Page 519
INDEX......Page 521