Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Management Science, Revised (Book Only)

Contents......Page 10
Title Page......Page 3
Copyright......Page 4
Introduction......Page 23
Characteristics and Benefits of Modeling......Page 25
Mathematical Models......Page 26
Categories of Mathematical Models......Page 28
The Problem-Solving Process......Page 29
Anchoring and Framing Effects......Page 31
Summary......Page 33
The World of Management Science......Page 34
Case......Page 36
Applications of Mathematical Optimization......Page 39
Characteristics of Optimization Problems......Page 40
Constraints......Page 41
Mathematical Programming Techniques......Page 42
Steps in Formulating an LP Model......Page 43
The General Form of an LP Model......Page 45
Solving LP Problems: An Intuitive Approach......Page 46
Solving LP Problems: A Graphical Approach......Page 47
Plotting the Second Constraint......Page 48
Plotting the Third Constraint......Page 49
The Feasible Region......Page 50
Plotting the Objective Function......Page 51
Finding the Optimal Solution Using Level Curves......Page 52
Summary of Graphical Solution to LP Problems......Page 54
Understanding How Things Change......Page 55
Alternate Optimal Solutions......Page 56
Redundant Constraints......Page 57
Unbounded Solutions......Page 59
Infeasibility......Page 60
Questions and Problems......Page 61
Case......Page 66
Spreadsheet Solvers......Page 67
The Steps in Implementing an LP Model in a Spreadsheet......Page 68
A Spreadsheet Model for the Blue Ridge Hot Tubs Problem......Page 70
Representing the Objective Function......Page 71
Representing the Bounds on the Decision Variables......Page 72
How Solver Views the Model......Page 73
Using Solver......Page 75
Defining the Set (or Target) Cell......Page 76
Defining the Constraint Cells......Page 78
Defining the Nonnegativity Conditions......Page 80
Solving the Model......Page 81
Goals and Guidelines for Spreadsheet Design......Page 83
Defining the Decision Variables......Page 85
Implementing the Model......Page 86
Analyzing the Solution......Page 88
An Investment Problem......Page 89
Defining the Objective Function......Page 90
Implementing the Model......Page 91
Solving the Model......Page 93
Defining the Decision Variables......Page 94
Defining the Constraints......Page 95
Implementing the Model......Page 96
Solving the Model......Page 98
Analyzing the Solution......Page 99
A Blending Problem......Page 100
Defining the Constraints......Page 101
Some Observations About Constraints, Reporting, and Scaling......Page 102
Rescaling the Model......Page 103
Implementing the Model......Page 104
Solving the Model......Page 105
Analyzing the Solution......Page 106
Defining the Decision Variables......Page 107
Defining the Constraints......Page 108
Implementing the Model......Page 109
Solving the Model......Page 111
Analyzing the Solution......Page 112
Defining the Decision Variables......Page 113
Defining the Constraints......Page 114
Implementing the Model......Page 116
Analyzing the Solution......Page 118
Modifying The Taco-Viva Problem to Account for Risk (Optional)......Page 120
Implementing the Risk Constraints......Page 122
Solving the Model......Page 123
Data Envelopment Analysis......Page 124
Defining the constraints......Page 125
Implementing the Model......Page 126
Solving the Model......Page 128
Analyzing the Solution......Page 133
Summary......Page 134
The World of Management Science......Page 135
Questions and Problems......Page 136
Cases......Page 152
The Purpose of Sensitivity Analysis......Page 158
An Example Problem......Page 159
The Answer Report......Page 160
Changes in the Objective Function Coefficients......Page 162
A Note About Constancy......Page 164
Changes in the RHS Values......Page 165
A Note About Shadow Prices......Page 166
Other Uses of Shadow Prices......Page 168
The Meaning of the Reduced Costs......Page 169
Analyzing Changes in Constraint Coefficients......Page 171
Simultaneous Changes in Objective Function Coefficients......Page 172
The Limits Report......Page 173
The Sensitivity Assistant Add-in (Optional)......Page 174
Creating Spider Tables and Plots......Page 175
Creating a Solver Table......Page 177
Creating Equality Constraints Using Slack Variables......Page 180
Basic Feasible Solutions......Page 181
References......Page 184
The World of Management Science......Page 185
Questions and Problems......Page 186
Cases......Page 193
Characteristics of Network Flow Problems......Page 199
The Objective Function for Network Flow Problems......Page 201
The Constraints for Network Flow Problems......Page 202
Implementing the Model in a Spreadsheet......Page 203
Analyzing the Solution......Page 204
The Shortest Path Problem......Page 206
The Spreadsheet Model and Solution......Page 208
Network Flow Models and Integer Solutions......Page 210
The Equipment Replacement Problem......Page 211
The Spreadsheet Model and Solution......Page 212
Transportation/Assignment Problems......Page 215
Generalized Network Flow Problems......Page 216
Formulating an LP Model for the Recycling Problem......Page 217
Implementing the Model......Page 218
Analyzing the Solution......Page 220
Generalized Network Flow Problems and Feasibility......Page 221
An Example of a Maximal Flow Problem......Page 223
The Spreadsheet Model and Solution......Page 225
Special Modeling Considerations......Page 227
Minimal Spanning Tree Problems......Page 230
Solving the Example Problem......Page 231
References......Page 232
The World of Management Science......Page 233
Questions and Problems......Page 234
Cases......Page 249
Integrality Conditions......Page 254
Solving the Relaxed Problem......Page 255
Bounds......Page 257
Rounding......Page 258
Stopping Rules......Page 261
Solving ILP Problems Using Solver......Page 262
An Employee Scheduling Problem......Page 265
Defining the Decision Variables......Page 266
A Note About the Constraints......Page 267
Implementing the Model......Page 268
Analyzing the Solution......Page 269
Binary Variables......Page 270
Defining the Decision Variables......Page 271
Implementing the Model......Page 272
Solving the Model......Page 273
Binary Variables and Logical Conditions......Page 275
The Fixed-Charge Problem......Page 276
Defining the Objective Function......Page 277
Determining Values for “Big M”......Page 278
Implementing the Model......Page 279
Solving the Model......Page 281
Analyzing the Solution......Page 282
Quantity Discounts......Page 283
A Contract Award Problem......Page 284
Formulating the Model: The Objective Function and Transportation Constraints......Page 285
Implementing the Transportation Constraints......Page 286
Formulating the Model: The Side Constraints......Page 287
Implementing the Side Constraints......Page 288
Solving the Model......Page 289
The Branch-and-Bound Algorithm (Optional)......Page 290
Branching......Page 291
Bounding Again......Page 294
Summary......Page 296
References......Page 297
Questions and Problems......Page 298
Cases......Page 313
Goal Programming......Page 318
A Goal Programming Example......Page 319
Defining the Goal Constraints......Page 320
Defining the Hard Constraints......Page 321
GP Objective Functions......Page 322
Defining the Objective......Page 323
Implementing the Model......Page 324
Analyzing the Solution......Page 325
Revising the Model......Page 326
Trade-offs: The Nature of GP......Page 327
Multiple Objective Optimization......Page 329
Defining the Decision Variables......Page 331
Implementing the Model......Page 332
Determining Target Values for the Objectives......Page 333
Summarizing the Target Solutions......Page 335
Determining a GP Objective......Page 336
The MINIMAX Objective......Page 338
Implementing the Revised Model......Page 339
Solving the Model......Page 340
Comments on MOLP......Page 342
The World of Management Science......Page 343
Questions and Problems......Page 344
Cases......Page 356
The Nature of NLP Problems......Page 361
Solution Strategies for NLP Problems......Page 363
Local vs. Global Optimal Solutions......Page 364
Economic Order Quantity Models......Page 366
Implementing the Model......Page 369
Solving the Model......Page 370
Comments on the EOQ Model......Page 371
Location Problems......Page 372
Defining the Objective......Page 373
Implementing the Model......Page 374
Solving the Model and Analyzing the Solution......Page 375
Some Comments About the Solution to Location Problems......Page 376
Nonlinear Network Flow Problem......Page 377
Defining the Objective......Page 378
Implementing the Model......Page 379
Project Selection Problems......Page 382
Defining the Objective Function......Page 383
Implementing the Model......Page 384
Solving the Model......Page 386
Implementing the Model......Page 387
Optimizing the Spreadsheet Model......Page 389
The Portfolio Selection Problem......Page 390
Defining the Objective......Page 392
Implementing the Model......Page 393
Analyzing the Solution......Page 395
Handling Conflicting Objectives in Portfolio Problems......Page 396
Sensitivity Analysis......Page 398
Lagrange Multipliers......Page 400
Solver Options for Solving NLPs......Page 401
Evolutionary Algorithms......Page 402
A Spreadsheet Model for the Problem......Page 404
Solving the Model......Page 405
Analyzing the Solution......Page 406
The Traveling Salesperson Problem......Page 407
A Spreadsheet Model for the Problem......Page 408
Analyzing the Solution......Page 409
The World of Management Science......Page 411
Questions and Problems......Page 412
Cases......Page 426
An Example......Page 431
Regression Models......Page 433
Simple Linear Regression Analysis......Page 434
Defining “Best Fit”......Page 435
Solving the Problem Using Solver......Page 436
Solving the Problem Using the Regression Tool......Page 439
Evaluating the Fit......Page 441
The R[sup(2)] Statistic......Page 443
Making Predictions......Page 444
Prediction Intervals for New Values of Y......Page 445
Confidence Intervals for Mean Values of Y......Page 447
Statistical Tests for Population Parameters......Page 448
Assumptions for the Statistical Tests......Page 449
Introduction to Multiple Regression......Page 452
A Multiple Regression Example......Page 453
Models with One Independent Variable......Page 455
Models with Two Independent Variables......Page 456
Inflating R[sup(2)]......Page 458
Multicollinearity......Page 459
The Model with Three Independent Variables......Page 460
Making Predictions......Page 461
Statistical Tests for the Population Parameters......Page 462
Polynomial Regression......Page 463
Expressing Nonlinear Relationships Using Linear Models......Page 464
Summary......Page 468
The World of Management Science......Page 469
Questions and Problems......Page 470
Cases......Page 476
Introduction......Page 481
Group Locations and Centroids......Page 482
Calculating Discriminant Scores......Page 483
The Classification Rule......Page 487
Refining the Cutoff Value......Page 488
Classification Accuracy......Page 489
Classifying New Employees......Page 490
The k-Group DA Problem......Page 491
Multiple Discriminant Analysis......Page 493
Distance Measures......Page 494
MDA Classification......Page 496
References......Page 499
Questions and Problems......Page 500
Cases......Page 503
Introduction......Page 507
Measuring Accuracy......Page 508
Stationary Models......Page 509
Moving Averages......Page 510
Forecasting with the Moving Average Model......Page 512
Weighted Moving Averages......Page 514
Forecasting with the Weighted Moving Average Model......Page 515
Exponential Smoothing......Page 516
Forecasting with the Exponential Smoothing Model......Page 518
Seasonality......Page 520
Stationary Data with Additive Seasonal Effects......Page 522
Forecasting with the Model......Page 524
Stationary Data with Multiplicative Seasonal Effects......Page 526
An Example......Page 529
Double Moving Average......Page 530
Forecasting with the Model......Page 532
Double Exponential Smoothing (Holt’s Method)......Page 533
Forecasting with Holt’s Method......Page 535
Holt-Winter’s Method for Additive Seasonal Effects......Page 536
Forecasting with Holt-Winter’s Additive Method......Page 539
Holt-Winter’s Method for Multiplicative Seasonal Effects......Page 540
Forecasting with Holt-Winter’s Multiplicative Method......Page 543
Modeling Time Series Trends Using Regression......Page 544
Linear Trend Model......Page 545
Forecasting with the Linear Trend Model......Page 547
Quadratic Trend Model......Page 548
Modeling Seasonality with Regression Models......Page 550
Adjusting Trend Predictions with Seasonal Indices......Page 551
Computing Seasonal Indices......Page 552
Forecasting with Seasonal Indices......Page 553
Refining the Seasonal Indices......Page 554
Seasonal Regression Models......Page 556
The Seasonal Model......Page 557
Forecasting with the Seasonal Regression Model......Page 558
Using CB Predictor......Page 560
Summary......Page 566
The World of Management Science......Page 567
Questions and Problems......Page 568
Cases......Page 576
Random Variables and Risk......Page 581
Methods of Risk Analysis......Page 582
Best-Case/Worst-Case Analysis......Page 583
Simulation......Page 584
A Corporate Health Insurance Example......Page 585
Spreadsheet Simulation Using Crystal Ball......Page 587
Random Number Generators......Page 588
Discrete vs. Continuous Random Variables......Page 591
Preparing the Model for Simulation......Page 592
Defining Assumptions for the Number of Covered Employees......Page 594
Defining Assumptions for the Average Monthly Claim per Employee......Page 596
Defining Assumptions for the Average Monthly Claim per Employee......Page 597
Selecting the Output Cells to Track......Page 598
Determining the Sample Size......Page 599
Data Analysis......Page 600
The Distribution of the Output Cell......Page 601
Viewing the Cumulative Distribution of the Output Cells......Page 602
The Uncertainty of Sampling......Page 603
Constructing a Confidence Interval for the True Population Mean......Page 605
Constructing a Confidence Interval for a Population Proportion......Page 606
The Benefits of Simulation......Page 607
Additional Uses of Simulation......Page 608
Implementing the Model......Page 609
Using the Decision Table Tool......Page 611
An Inventory Control Example......Page 617
Implementing the Model......Page 618
Replicating the Model......Page 622
Optimizing the Model......Page 623
Comparing the Original and Optimal Ordering Policies......Page 625
A Project Selection Example......Page 626
A Spreadsheet Model......Page 627
Solving the Problem with OptQuest......Page 629
Considering Other Solutions......Page 631
A Portfolio Optimization Example......Page 633
A Spreadsheet Model......Page 634
Solving the Problem with OptQuest......Page 637
Summary......Page 638
The World of Management Science......Page 639
Questions and Problems......Page 640
Cases......Page 654
The Purpose of Queuing Models......Page 663
Queuing System Configurations......Page 664
Characteristics of Queuing Systems......Page 665
Arrival Rate......Page 666
Service Rate......Page 667
Queuing Models......Page 669
The M/M/s Model......Page 670
An Example......Page 671
Adding a Server......Page 672
Economic Analysis......Page 673
The M/M/s Model with Finite Queue Length......Page 674
Adding a Server......Page 675
The M/M/s Model with Finite Population......Page 676
The Current Situation......Page 677
Adding Servers......Page 679
The M/G/1 Model......Page 680
Adding the Automated Dispensing Device......Page 681
The M/D/1 Model......Page 683
Simulating Queues and the Steady-state Assumption......Page 684
The World of Management Science......Page 685
Questions and Problems......Page 687
Cases......Page 693
An Example......Page 695
Creating the Project Network......Page 696
A Note on Start and Finish Points......Page 698
CPM: An Overview......Page 699
The Forward Pass......Page 700
The Backward Pass......Page 702
Determining the Critical Path......Page 704
A Note on Slack......Page 705
Project Management Using Spreadsheets......Page 706
Gantt Charts......Page 710
An LP Approach to Crashing......Page 713
Determining the Earliest Crash Completion Time......Page 715
Implementing the Model......Page 716
Solving the Model......Page 717
Determining a Least Costly Crash Schedule......Page 718
Crashing as an MOLP......Page 720
PERT: An Overview......Page 721
The Problems with PERT......Page 722
Generating Random Activity Times......Page 724
Running the Simulation......Page 726
Analyzing the Results......Page 728
Microsoft Project......Page 729
The World of Management Science......Page 732
Questions and Problems......Page 733
Cases......Page 742
Good Decisions vs. Good Outcomes......Page 746
An Example......Page 747
The Payoff Matrix......Page 748
The Payoff Values......Page 749
Decision Rules......Page 750
The Maximax Decision Rule......Page 751
The Maximin Decision Rule......Page 752
The Minimax Regret Decision Rule......Page 753
Expected Monetary Value......Page 755
Expected Regret......Page 757
Sensitivity Analysis......Page 758
The Expected Value of Perfect Information......Page 760
Decision Trees......Page 761
Rolling Back a Decision Tree......Page 762
Using TreePlan......Page 764
Adding Branches......Page 765
Adding Event Nodes......Page 766
Determining the Payoffs and EMVs......Page 770
Other Features......Page 771
Multistage Decision Problems......Page 772
A Multistage Decision Tree......Page 773
Developing A Risk Profile......Page 775
Sensitivity Analysis......Page 776
Spider Charts and Tornado Charts......Page 777
Strategy Tables......Page 780
Using Sample Information in Decision Making......Page 782
Conditional Probabilities......Page 783
The Expected Value of Sample Information......Page 784
Computing Conditional Probabilities......Page 785
Bayes’s Theorem......Page 787
Utility Functions......Page 788
Constructing Utility Functions......Page 789
The Exponential Utility Function......Page 792
Incorporating Utilities in TreePlan......Page 793
Multicriteria Decision Making......Page 794
The Multicriteria Scoring Model......Page 795
Pairwise Comparisons......Page 799
Normalizing the Comparisons......Page 801
Consistency......Page 802
Obtaining Scores for the Remaining Criteria......Page 803
Obtaining Criterion Weights......Page 804
Summary......Page 805
References......Page 806
The World of Management Science......Page 807
Questions and Problems......Page 808
Cases......Page 818
Index......Page 823