Statistics, Data Analysis, and Decision Modeling F O U R T H
E D I T I
O
N
James R. Evans University of Cincinnati
TT Boston Columbus Indianapolis New York San Francisco Upper Saddle River Amsterdam Cape Town Dubai London Madrid Mdan Munich Paris Montreal Toronto Delhi Mexico City Sao Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo
CONTENTS
Preface
PART I:
17
STATISTICS AND DATA ANALYSIS
CHAPTER 1 Data and Business Decisions Introduction 23 Statistical Thinking in Business 25 Six Sigma and Statistical Thinking
26
Data in the Business Environment Metrics and Measurement The Balanced Scorecard
28
30
32
Populations, Samples, and Statistics Using Microsoft Excel 36 Basic Excel Skills 36 Copying Formulas and Cell References Functions 38 Other Useful Excel Tips 41 Excel Add-Ins 41
Working with Data in Excel PivotTables
27
28
Sources and Types of Data Data Classification
23
34
37
42
42
Basic Concepts Review Questions 49 Skill-Building Exercises 49 Problems and Applications 49 Case: A Data Collection and Analysis Project
50
CHAPTER 2 Displaying and Summarizing Data 52 Introduction 53 Displaying Data with Charts and Graphs 53
, I
Column and Bar Charts 53 Line Charts 56 Pie Charts 57 Area Charts 57 Scatter Diagrams 57 Miscellaneous Excel Charts 58 Summary of Graphical Display Methods 60
Descriptive Statistics: Concepts and Applications Excel Descriptive Statistics Tool 63 Measures of Central Tendency 64 Measures of Dispersion 65 Frequency Distributions and Histograms
67
61
21
Measures of Shape 70 Data Profiles 72 Correlation 73
Descriptive Statistics for Categorical Data 81 Basic Concepts Review Questions 83 Skill-Building Exercises 84 Problems and Applications 84 Case: The Malcolm Baldrige National Quality Award 88 Appendix: Descriptive Statistics: Theory and Computation
90
Mean, Variance, and Standard Deviation 90 Statistical Measures for Grouped Data 90 Skewness and Kurtosis 91 Correlation 91
CHAPTER 3 Probability Distributions and Applications Introduction 94 Probability: Concepts and Applications 94 Basic Probability Rules Random Variables 97
Probability Distributions
93
95
97
Discrete Probability Distributions 99 Continuous Probability Distributions 100 Expected Value and Variance of a Random Variable 101
Common Probability Distributions Bernoulli Distribution 103 Binomial Distribution 104 105 Poisson Distribution 107 Uniform Distribution Normal Distribution 107 Triangular Distribution 112 Exponential Distribution 113 Other Useful Distributions 114 Probability Distributions in PHStat
102
117
Joint, Marginal, and Conditional Probability Distributions Monte Carlo Methods in Statistics 119 Random Numbers 120 Random Sampling from Probability Distributions 121 Generating Random Variates in Excel 123 Applications of Monte Carlo Methods in Statistics 125
Sampling Distributions and Sampling Error
127
Standard Error of the Mean 130 Applying Sampling Distributions 131
Basic Concepts Review Questions 131 Skill-Building Exercises 132 Problems and Applications 132 Case: Probability Analysis for Quality Measurements Contents
137
118
Appendix: Probability: Theory and Computation
138
Expected Value and Variance of a Random Variable 138 Binomial Distribution 138 Poisson Distribution 139 Uniform Distribution 139 Normal Distribution 139 Triangular Distribution 140 Exponential Distribution 140 Conditional Probability 140 Bayes's Theorem 141
CHAPTER 4 Sampling and Estimation Introduction 143 Statistical Sampling 143
142
Sample Design 143 Sampling Methods 144 Errors in Sampling 147
Estimation
147
Point Estimates 148 Unbiased Estimators 149 Interval Estimates 150
Confidence Intervals: Concepts and Applications
150
Confidence Interval for the Mean with Known Population Standard Deviation 151 Confidence Interval for the Mean with Unknown Population Standard Deviation 154 Confidence Interval for a Proportion 156 Confidence Intervals for the Variance and Standard Deviation 156 Confidence Interval for a Population Total 159
Using Confidence Intervals for Decision Making Confidence Intervals and Sample Size 161 Additional Types of Confidence Intervals 164 Basic Concepts Review Questions 165 Skill-Building Exercises 165 Problems and Applications 166 Case: Analyzing a Customer Survey 168 Appendix: Theory and Additional Topics 169
160
Theory Underlying Confidence Intervals 169 Sampling Distribution of the Proportion 170 Sample Size Determination 171 Additional Confidence Intervals 171
CHAPTER 5 Hypothesis Testing and Statistical Inference Introduction 175 Basic Concepts of Hypothesis Testing 175
174
Hypothesis Formulation 176 Significance Level 177 Decision Rules 178 Spreadsheet Support for Hypothesis Testing 180
One-Sample Hypothesis Tests
181
One-Sample Tests for Means 181 Using p-Values 183
Contents
One-Sample Tests for Proportions 185 Type II Errors and the Power of a Test 187
Two-Sample Hypothesis Tests
190
Two-Sample Tests for Means 190 Two-Sample Test for Means with Paired Samples 192 Two-Sample Tests for Proportions 193 Hypothesis Tests and Confidence Intervals 194 Test for Equality of Variances 195
ANOVA: Testing Differences of Several Means Assumptions
of
ANOVA
197
199
Tukey-Kramer Multiple Comparison Procedure
199
Chi-Square Test for Independence 201 Basic Concepts Review Questions 203 Skill-Building Exercises 204 Problems and Applications 204 Case: HATCO, Inc. 208 Appendix: Hypothesis-Testing Theory and Computation Two-Sample Tests for Differences in Means 208 Two-Sample Test for Differences in Proportions 209 Test for Equality of Variances 209 Theory of Analysis of Variance 209
CHAPTER 6 Regression Analysis 210 Introduction 211 Simple Linear Regression 212 Least-Squares Regression 214 Coefficient of Determination 218 Application of Regression to Investment Risk 218
Regression as Analysis of Variance 252 Standard Error of the Estimate 253 Adjusted R-Square 253 Confidence Intervals 253 Prediction Intervals 254
CHAPTER 7
Forecasting
255
Introduction 256 Qualitative and Judgmental Methods
257
Historical Analogy 257 The Delphi Method 258 Indicators and Indexes for Forecasting 258
Statistical Forecasting Models 259 Forecasting Models for Stationary Time Series
261
Moving Average Models 261 Error Metrics and Forecast Accuracy 264 Exponential Smoothing Models 266
Forecasting Models for Time Series with Trend and Seasonality
269
Models for Linear Trends 269 Models for Seasonality 269 Models for Trend and Seasonality 269
Choosing and Optimizing Forecasting Models Using CB Predictor Regression Models for Forecasting 275 Autoregressive Forecasting Models 276 Incorporating Seasonality in Regression Models Regression Forecasting with Causal Variables
270
278 280
The Practice of Forecasting 282 Basic Concepts Review Questions 284 Skill-Building Exercises 284 Problems and Applications 284 Case: Energy Forecasting 286 Appendix: Advanced Forecasting Models— Theory and Computation 286 Double Moving Average 286 Double Exponential Smoothing 287 Additive Seasonality 287 Multiplicative Seasonality 287 Holt-Winters Additive Model 288 Holt-Winters Multiplicative Model 288
CHAPTER 8
Statistical Quality Control
289
Introduction 289 The Role of Statistics and Data Analysis in Quality Control Statistical Process Control 291 Control Charts x- and R-Charts
290
292 293
Analyzing Control Charts
298
Sudden Shift in the Process Average Cycles 299
299
Contents
11
Trends 299 Hugging the Center Line Hugging the Control Limits
300 300
Control Charts for Attributes Variable Sample Size
302
304
Process Capability Analysis 307 Basic Concepts Review Questions 309 Skill-Building Exercises 309 Problems and Applications 310 Case: Quality Control Analysis 311
PART II:
DECISION MODELING AND ANALYSIS 313
CHAPTER 9 Building and Using Decision Models Introduction 315 Decision Models 316 Model Analysis 319
315
What-IfAnalysis 320 Model Optimization 324
Tools for Model Building
326
Logic and Business Principles 327 Common Mathematical Functions 328 Data Fitting 328 Spreadsheet Engineering 330
Modeling Examples
331
Gasoline Consumption 331 Revenue Model 331 New Product Development 333
Models Involving Uncertainty
333
Newsvendor Model 334 Monte Carlo Simulation 334 Fitting Probability Distributions to Data
337
Model Assumptions, Complexity, and Realism 339 Basic Concepts Review Questions 342 Skill-Building Exercises 342 Problems and Applications 342 Case: An Inventory Management Decision Model 347 CHAPTER 10 Risk Analysis and Monte Carlo Simulation Introduction 349 Monte Carlo Simulation Using Crystal Ball 351 A Financial Risk Analysis Simulation 351 Defining Model Inputs 351 Running a Simulation 358 Saving Crystal Ball Runs 359 Analyzing Results 359 Crystal Ball Reports and Data Extraction 366 Crystal Ball Functions and Tools 367
12
Contents
349
Applications of Monte Carlo Simulation Newsvendor Model 369 Overbooking Model 373 Cash Budgeting 374 New Product Development Model Project Management
369
379
382
Basic Concepts Review Questions Skill-Building Exercises 388 Problems and Applications 389 Case: The Bellin Project 393
388
CHAPTER 11 Decisions, Uncertainty, and Risk 394 Introduction 395 Decision Making without Uncertainty and Risk 395 Decisions Involving a Single Alternative 396 Decisions Involving Non-Mutually Exclusive Alternatives 396 Decisions Involving Mutually Exclusive Alternatives 397
Decisions Involving Uncertainty and Risk Making Decisions with Uncertain Information Decision Strategies 399 Risk and Variability 401
Expected Value Decision Making
398 398
403
Opportunity Loss and Expected Value of Perfect Information 405 Analysis of Portfolio Risk 406 The "Flaw of Averages" 407
Decision Trees
408
New Drug Development Model 409 Decision Trees and Risk 412 Sensitivity Analysis in Decision Trees
Utility and Decision Making Exponential Utility Functions
414
415 419
Basic Concepts Review Questions 420 Skill-Building Exercises 420 Problems and Applications 421 Case: The Sandwich Decision 427 CHAPTER 12 Queues and Process Simulation Modeling Introduction 429 Queues and Queuing Systems 429
428
Basic Concepts of Queuing Systems 430 Customer Characteristics 430 Service Characteristics 431 Queue Characteristics 432 System Configuration 432 Performance Measures 432
Analytical Queuing Models Single-Server Model Little's Law 435
433
433
Process Simulation Concepts
436 Contents
13
Process Simulation with SimQuick
437
A Queuing Simulation Model 438 Queues in Series with Blocking 443 Grocery Store Checkout Model with Resources 444 Manufacturing Inspection Model with Decision Points 447 Pull System Supply Chain With Exit Schedules 449 Other SimQuick Features and Commercial Simulation Software
451
Continuous Simulation Modeling 453 Basic Concepts Review Questions 456 Skill-Building Exercises 457 Problems and Applications 457 Case: Production/Inventory Planning 462 Appendix: SimQuick Reference Manual 462 CHAPTER 13 Linear Optimization 467 Introduction 467 Building Linear Optimization Models 468 Characteristics of Linear Optimization Models 471
Implementing Linear Optimization Models on Spreadsheets Excel Functions to Avoid in Modeling Linear Programs
Solving Linear Optimization Models Solving the SSC Model 477 Solver Outcomes and Solution Messages Interpreting Solver Reports 480 How Solver Creates Names in Reports Difficulties with Solver 484
473
474 479 484
Applications of Linear Optimization
485
Process Selection 487 Blending 488 Portfolio Investment 489 Transportation Problem 490 Interpreting Reduced Costs 493 Multiperiod Planning 494 A Model with Bounded Variables 497 A Production/Marketing Allocation Model
503
How Solver Works 507 Basic Concepts Review Questions 508 Skill-Building Exercises 508 Problems and Applications 509 Case: Haller's Pub & Brewery 518 CHAPTER 14 Integer and Nonlinear Optimization Introduction 519 Integer Optimization Models 520 A Cutting Stock Problem 520 Solving Integer Optimization Models
521
Integer Optimization Models with Binary Variables Project Selection 524 Site Location Model 526
14
Contents
519
524
472
Computer Configuration 528 A Supply Chain Facility Location Model
Mixed Integer Optimization Models
531
532
Plant Location Model 532 A Model with Fixed Costs 533 Logical Conditions and Spreadsheet Implementation
Nonlinear Optimization
535
535
Hotel Pricing 535 Solving Nonlinear Optimization Models 537 Markowitz Portfolio Model 540 Evolutionary Solver for Nonlinear Optimization 542
Risk Analysis and Optimization 546 Combining Optimization and Simulation A Portfolio Allocation Model
549
549
Using OptQuest 550 Basic Concepts Review Questions Skill-Building Exercises 559 Problems and Applications 559 Case: Tindall Bookstores 568
559
Appendix Table A.I The Cumulative Standard Normal Distribution 572 Table A.2 Critical Values of t 574 2 Table A.3 Critical Values of x 577 Table A.4 Critical Values of F 578 Table A.5 Critical Values3 of the Studentized Range Q 581 Index
