MANAGEMENT
DECISION MAKING
•
Spreadsheet modeling,
analysis, and application
George E. Monahan
' University of Illinois at Urbana-Champaign
CAMBRIDGE
U N I V E R S I T Y P R E S SContents
List of tables List of figures Preface xix xxv xxxiii1 The science of managerial decision making
1.1 1.2 1.3 1.4 1.5 1.6 1.7 Introduction
1.1.1 Part I: decision making using deterministic models
1.1.2 Part II: decision making under uncertainty Models
1.2.1 Stages of model development Spreadsheet models
A lease/buy problem 1.4.1 Problem statement 1.4.2 Influence diagrams
1.4.3 A spreadsheet model of the lease/buy problem 1.4.4 Sensitivity analysis
What's next? Problems
Solution to the hands-on exercise
2 5 6 8 9 11 11 11 13 19 23 23 24
Part I Decision making using deterministic models
272 Introduction to linear programming models
2.1 Formulating a decision model
2.1.1 General structure of decision models
29 30 31
2.1.2 Micro Works, Inc.: a product mix problem 32
2.1.3 Model formulation 33 2.1.4 Linear programs 40 2.2 Building a linear programming model in an EXCEL
worksheet 41 2.2.1 Benefits of LP Standard Form 45
2.3 Computing an optimal solution using Solver 46
2.3.1 Installing Solver 46 2.3.2 Using Solver 46 2.4 Integer versus non-integer solutions 54
2.5 Cash-for-Trash Recycling blending problem 56
2.5.1 Formulating the CTR model 57 2.5.2 Pictures and simple numerical examples:
some problem formulation aids 59
2.6 The CTR spreadsheet model 60 2.7 Computing an optimal solution to the CTR problem 62
2.8 The StdFormTemplate.xls workbook 67
2.9 Trouble-shooting 68 2.9.1 Nonlinearities 68 2.9.2 No solution found 69 2.9.3 Unbounded solution 69 2.9.4 An unexpected "optimal" solution 70
2.10 The BGT Candy Company problem 70
2.10.1 The optimal solution 74
2.11 Summary 77 2.12 Problems 77 2.13 Solutions to some hands-on exercises 91
3 Developing model formulation skills 93
3.1 An overview of problem types 94 3.1.1 Static and dynamic models 96 3.2 A portfolio selection problem (how?) 97
3.2.1 Solving the portfolio selection problem 97 3.3 A cutting stock problem (how, what?) 100
3.3.1 Solving the cutting-stock problem 100 3.4 Network flow problems (what, where?) 102
3.4.1 Integer solutions 106 3.5 Captain Wise's packing problem (what, where?) 106
ix Contents
3.5.1 Solving Captain Wise's problem 107 3.6 A staff scheduling problem (what, when?) 111
3.6.1 Solving the staffing problem 112 3.7 Capital budgeting (when, what?) 113
3.7.1 Maximizing terminal net worth 114 3.7.2 Maximizing the sum of discounted cash flows 119
3.7.3 Optimality of consuming versus saving 122 3.8 A "transportation" problem (what, where?) 124
3.8.1 The LP formulation 124 3.8.2 Spreadsheet models 125 3.9 Production-inventory problems (what, when?) 127
3.9.1 Precision Products'production planning problem 128 3.9.2 A spreadsheet model of the Precision Products
problem 129 3.10 A multiple supplier problem (where, how?) 131
3.10.1 Solving Farm Implement's supply problem 132 3.11 Disentangling the bankruptcies in the al-Manakh
stock market crash 135
3.12 Problems 139 3.13 Solution to the hands-on exercise 168
4 More advanced linear decision problems 169
4.1 High Margins, Inc. 170 4.1.1 A production planning problem with
backlogging 174 4.2 Solving a max-min problem as a linear program 176
4.2.1 Problem statement 176 4.2.2 An LP model 177 4.2.3 The min{ } function 178 4.2.4 Finessing the problem 178 4.2.5 The solution to the gambler's problem 179
4.3 A dynamic transportation problem with inventory 180
4.3.1 Problem statement 180 4.3.2 The single-site purchasing problem 182
4.3.3 Two sites: a transshipment problem 185 4.3.4 The general problem: two sites and five
processing plants 188
x Contents
1
• • * • • •••' • • I
1^
5 Output analysis I: small changes 195
5.1 Introduction to sensitivity analysis 195
5.1.1 Methods of analysis 197 5.2 The Lotsa Pasta problem 198 5.3 Graphical representation of linear programs 200
5.3.1 Graphing the feasible region 201 5.3.2 Production plans that satisfy more than one
constraint simultaneously 203 5.3.3 Finding an optimal solution 204 5.3.4 Optimality of "corner" solutions 207
5.4 Optimal plans 207 5.5 Small changes in the objective function 208
5.5.1 Changing more than one objective function
coefficient 215 5.6 Changing right-hand-side values 216
5.6.1 A proposal you might be able to refuse 216
5.6.2 Shadow prices 219 5.6.3 Small changes in several right-hand-side values 223
5.7 Reduced cost 226 5.7.1 Activity 229 5.7.2 Pricing out the activities 230
5.7.3 Reduced cost as a shadow price 231
5.8 Adding an activity 232 5.9 Multiple optimal solutions 233
5.10 Interpreting Solver output: The BGT Candy Company 234
5.11 Comparing sensitivity reports 240 5.12 Identifying exploitable decisions 244
5.13 Problems 245 5.14 Solutions to some hands-on exercises 270
Supplement: Duality: the story behind shadow prices 273
5.15 The primal problem 273 5.16 An offer you can't refuse 274 5.17 The pricing problem 275
5.17.1 Products as bundles of resources 275
5.17.2 Optimal resource prices 277
5.18 Primal-dual pairs 278 5.18.1 The dual of the dual 278
xi Contents
5.20 Dual variables and shadow prices 283
5.21 Some concluding remarks 284 5.22 A challenging capstone problem 285
5.22.1 The solution 286
5.23 Problems 288
6 Output analysis II: large changes 291
6.1 The optimal value function for RHS values 291 6.1.1 Revisiting the Lotsa Pasta problem 291 6.1.2 Measuring the benefit of labor hours 292 6.1.3 Generating an optimal value function 294 6.1.4 Properties of the optimal value function 295 6.1.5 Using Solver to generate an optimal value
function 297 6.1.6 General properties of optimal value functions 298
6.1.7 Utilizing the optimal value function 298 6.2 Optimal value functions for objective function
coefficients 300 6.2.1 The slope of the iso-profit line 301
6.2.2 Generating an optimal value function 301 6.2.3 Properties of the objective function coefficient
value functions 305 6.3 Belles and Baubles Trinket Company 306
6.3.1 Determining the trinket demand function 308 6.3.2 Impact of the marginal production cost on the
optimal selling price 309
6.4 Problems 310 6.5 Solutions to a hands-on exercise 312
7 Integer linear programs 313
7.1 Fixed charge problems 313 7.2 Using Solver to solve integer linear programs 315
7.2.1 Farm Implement revisited 316 7.3 Using binary-valued decision variables 318
7.3.1 Mutually exclusive decision variables 318
XII Contents
7.3.3 Either/or constraints 320 7.4 A bundle pricing problem 322
7.4.1 Decision variables 323 7.4.2 The objective function 323
7.4.3 Constraints 324 7.5 A production planning problem 328
7.5.1 Cost and other information 328 7.5.2 An integer LP formulation 331
7.6 A river crossing problem 338 7.6.1 An LP formulation 340 7.7 Bond portfolios to satisfy damage judgments 344
7.8 Problems 350 7.9 Solutions to hands-on exercises 354
Part II Decision making under uncertainty 357
8 Introduction to probability models 359
8.1 Elements of probability 359 8.1.1 Random experiments 359
8.1.2 Events 360 8.1.3 Probability functions 362
8.2 Conditional probability 365 8.2.1 Law of Total Probability 367 8.2.2 An auditing example 368
8.3 Independent events 371 8.4 Random variables and their distributions 373
8.4.1 The distribution of a random variable 376
8.5 Expectation 377 8.5.1 Properties of expected values 378
8.5.2 Law of the Unconscious Statistician 378
8.6 Variance and standard deviation 379 8.6.1 A property of variance 380
8.7 Joint distributions 381 8.7.1 Conditional expectation 385
8.8 Bayes' Rule 385 8.9 Common discrete probability distributions 389
8.9.2 Binomial random variables 8.9.3 Geometric random variables 8.9.4 Poisson random variables
8.9.5 Summary of information for some discrete random variables
.10 Continuous random variables
8.10.1 An example: uniform random variables 8.10.2 Exponential random variables
8.10.3 Gamma random variables 8.10.4 Normal random variables
8.10.5 Summary of some common continuous probability distributions
.11 Problems
.12 Solutions to hands-on exercises
389 390 391 392 393 396 399 400 401 407 407 414
9 Decision making under uncertainty
9.1 Classes of decision problems 9.2 Introduction to decision theory 9.3 Non-probabilistic methods
9.3.1 Dominance 9.3.2 Decision criteria
9.3.3 Application: managing seasonal goods inventory
9.4 Probabilistic methods
9.4.1 The modal outcome criterion 9.4.2 Expected value criterion 9.4.3 Expected regret criterion
9.4.4 The Christmas tree problem revisited 9.5 Value of information
9.6 Utility and preference 9.6.1 Certainty equivalents 9.6.2 Utility functions 9.6.3 Attitudes towards risk 9.6.4 Exponential utility functions 9.7 Problems 420 420 421 422 423 423 426 428 429 430 430 431 432 434 434 435 438 440 443
Contents
I
Decision trees 451
10.1 To enter or not to enter? 451 10.2 A new product introduction problem 454
10.3 Depicting the decision process 455 10.3.1 Types of nodes in a decision tree 456
10.4 GM's decision tree 457 10.4.1 Placing data on the tree 459
10.5 Assigning values to the nodes: folding back a
decision tree 461 10.5.1 "Pay-as-you-go" node valuation 462
10.5.2 "Settle-up-at-termination" node valuation 463
10.6 Identifying an optimal strategy 466 10.7 Building decision tree models in EXCEL: using TreePlan 466
10.7.1 Building a new decision tree 467 10.7.2 Modifying a decision tree 468 10.7.3 Hints about printing the decision tree 470
10.8 Evaluating new information 472 10.8.1 Quality of information 472 10.8.2 Updating prior beliefs 474 10.8.3 The modified decision problem 477
10.9 Optimal strategies as optimal contingency plans 479
10.10 The value of information 481 10.10.1 The value of perfect information 482
10.10.2 Perfect information and no information 484
10.11 Sensitivity analysis in decision trees 487 10.11.1 Varying the likelihood probabilities 487
10.12 Decision trees with exponential utility 489
10.12.1 Game Magic revisited 490 10.13 An oil exploration problem 497
10.13.1 Availability of information 498 10.13.2 Resolving an apparent paradox 499 10.13.3 Expected value of perfect information 503
10.14 Bidding for the SS Kuniang 503
10.15 Problems 509 10.16 Solutions to some hands-on exercises 524
11 Management of congested service systems
52611.1 Components of a queueing model 528
11.1.1 Kendall notation 530 11.1.2 A simple deterministic example 530
11.1.3 A general result: Little's Law 532
11.1.4 Another example 534 11.1.5 Steady state versus transient analysis 535
11.2 A basic queueing model 535 11.2.1 Memoryless property of random variables 536
11.2.2 Exponential random variables 536
11.2.3 Poisson arrivals 537 11.2.4 Steady-state analysis 539 11.2.5 Determining the steady-state probabilities 541
11.2.6 Calculating L and W 544 11.2.7 Computing Lq and Wq 545
11.3 Multi-server exponential queues: the M/M/s model 546
11.3.1 The Queue.xls workbook 548 11.3.2 The M/M/s queue with capacity restrictions 549
11.3.3 Erlang's loss formula 550 11.4 An infinite server queue 552 11.5 A finite source exponential queue 553
11.6 M/G/l queues 554 11.7 Designing queueing systems 557
11.7.1 Controlling the number of servers in an
M/M/s queue 558
11.8 Approximate methods for finding the optimal number
of servers 559 11.8.1 The deterministic model 560
11.8.2 Applicability 561 11.9 Analysis under the independence assumption 562
11.9.1 A note of caution 565 11.10 The equilibrium model 565
11.10.1 Non-exponential service times 566
11.11 Controlling the rate of service 566 11.12 Who waits longer: one super-server versus multiple
slow servers? 570 11.12.1 Average waiting times in M/M/l queues 570
11.12.2 Average waiting times in M/M/2 queues 571
xvi Contents
11.12.4 Comparing W(2) and W{\) 572
11.12.5 The general case 573 11.12.6 Conclusions 574 11.13 Scheduling police patrol cars in New York City 574
11.14 Problems 579
12 M o n t e Carlo simulation 593
12.1 An overbooking problem: evaluating a restaurant's
reservation policy 594 12.1.1 Constructing a simulation model 595
12.1.2 An EXCEL simulation model 595
12.2 Another example 599 12.2.1 Replicating random experiments using the
Data Table function 600 12.2.2 Replicating the experiments 600
12.2.3 Summarizing the data 603 12.3 Avoiding automatic recalculation of a worksheet 605
12.4 Using CRYSTAL B A L L , an EXCEL add-in for
Monte Carlo simulation 606 12.4.1 Solving Bistro 220's problem using
CRYSTAL B A L L 6 O 6 12.4.2 Assumption cells 607 12.4.3 Forecast cells 609 12.4.4 Decision cells 609 12.4.5 Choosing preferences 609 12.4.6 Running OptQuest 610 12.4.7 CB's output 613 12.5 A lump-sum investment problem 613
12.5.1 Some generalizations 616
12.5.2 Summary 621 12.6 Another example: State Driver's License Examination Center 622
12.7 An overview of discrete-event simulation 623 12.7.1 Designing a simulation experiment 623 12.8 Generating values of random variables 625
12.8.1 Generating values of empirical random variables 628
12.8.2 The inverse transform method 632 12.8.3 CB built-in random number generators 635
12.9 Simulating the Driver's License Examination Center 636
12.10 Multi-period inventory with random demand 644 12.10.1 Generating the random demands 645
12.10.2 Some modeling details 647 12.10.3 Finding a "good" ordering policy 650
12.11 Kanondell Cycle 652 12.11.1 Problem statement 652 12.11.2 Defining Assumptions in CB 656 12.11.3 Analysis 658 12.12 Problems 659 12.13 Solution to a hands-on-exercise 672 Appendix A n E x c e l Primer 674
A.l Cell addressing 674 A. 1.1 A1 cell addressing 674
A. 1.2 R1C1 cell addressing 675 A. 1.3 Managing cell names 675 A. 1.4 Invalid names 677
A.2 Formulas in EXCEL 677
A. 3 Array (matrix) formulas in EXCEL 678 A.3.1 Example: solving systems of linear equations 682
A.4 Logical functions in EXCEL 684
A.4.1 EXCEL'S AND, OR, and NOT functions 685
A.4.2 EXCEL'S IF function 686
A.5 Data tables 689 A.5.1 A (column-type) one-way data table 689
A.5.2 Two-way data tables 691 A.6 EXCEL'S HLOOKUP and VLOOKUP functions 693
A.7 EXCEL'S Histogram tool 695
A.8 Solutions to some hands-on exercises 700
Index 703
