Business analytics by camm et al 3rd edition cengage

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

BUSINESS ANALYTICS

Data Analysis and Decision Making

SEVENTH EDITION

S. Christian Albright Kelly School of Business,

Indiana University, Emeritus

Wayne L. Winston Kelly School of Business,

Indiana University, Emeritus

Australia • Brazil • Mexico • Singapore • United Kingdom • United States

09953_fm_ptg01_i-xxiv.indd 1 04/03/19 5:53 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for materials in your areas of interest.

Important Notice: Media content referenced within the product description or the product text may not be available in the eBook version.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

© 2020, 2017 Cengage Learning, Inc.

Unless otherwise noted, all content is © Cengage

ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be reproduced or distributed in any form or by any means, except as permitted by U.S. copyright law, without the prior written permission of the copyright owner.

For product information and technology assistance, contact us at Cengage

Customer & Sales Support, 1-800-354-9706 or support.cengage.com.

For permission to use material from this text or product,

submit all requests online at www.cengage.com/permissions.

Library of Congress Control Number: 2019935644

Student Edition: ISBN: 978-0-357-10995-3

Loose-leaf Edition: ISBN: 978-0-357-10996-0

Cengage 20 Channel Center Street Boston, MA 02210 USA

Cengage is a leading provider of customized learning solutions with employees residing in nearly 40 different countries and sales in more than 125 countries around the world. Find your local representative at www. cengage.com.

Cengage products are represented in Canada by Nelson Education, Ltd.

To learn more about Cengage platforms and services, register or access your online learning solution, or purchase materials for your course, visit www.cengage.com.

Business Analytics: Data Analysis and Decision Making, 7e

S. Christian Albright and Wayne L. Winston

Senior Vice President, Higher Ed Product, Content, and Market Development: Erin Joyner

Product Director: Jason Fremder

Senior Product Manager: Aaron Arnsparger

Senior Learning Designer: Brandon Foltz

Content Manager: Conor Allen

Digital Delivery Lead: Mark Hopkinson

Product Assistant: Christian Wood

Marketing Manager: Chris Walz

Production Service: Lumina Datamatics Ltd.

Designer, Creative Studio: Erin Griffin

Text Designer: Erin Griffin

Cover Designer: Erin Griffin

Cover Image(s): Ron Dale/ShutterStock.com

Intellectual Property Analyst: Reba Frederics

Intellectual Property Project Manager: Nick

Barrows

Printed in the United States of America Print Number: 01 Print Year: 2019

09953_fm_ptg01_i-xxiv.indd 2 05/03/19 9:35 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

To my wonderful wife Mary—my best friend and companion; and to Sam, Lindsay,

Teddy, and Archie S.C.A

To my wonderful family W.L.W.

09953_fm_ptg01_i-xxiv.indd 3 04/03/19 5:53 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

S. Christian Albright got his B.S. degree in Mathematics from Stanford in 1968 and his PhD in Operations Research from Stanford in 1972. He taught in the Operations & Decision Technologies Department in the Kelley School of Business at Indiana University (IU) for close to 40 years, before retiring from teaching in 2011. While at IU, he taught courses in management science, computer simulation, statistics, and computer programming to all levels of business students, including undergraduates, MBAs, and doctoral students. In addition, he taught simula- tion modeling at General Motors and Whirlpool, and he taught database analysis for the Army. He published over 20 articles in leading operations research journals in the area of applied probabil- ity, and he has authored the books Statistics for Business and Economics, Practical Management Science, Spreadsheet Modeling and Applications, Data Analysis for Managers, and VBA for Mod- elers. He worked for several years after “retirement” with the Palisade Corporation developing training materials for its software products, he has developed a commercial version of his Excel® tutorial, called ExcelNow!, and he continues to revise his textbooks.

On the personal side, Chris has been married for 47 years to his wonderful wife, Mary, who retired several years ago after teaching 7th grade English for 30 years. They have one son, Sam, who lives in Philadelphia with his wife Lindsay and their two sons, Teddy and Archie. Chris has many interests outside the academic area. They include activities with his family, traveling with Mary, going to cultural events, power walking while listening to books on his iPod, and reading. And although he earns his livelihood from quantitative methods, his real passion is for playing classical piano music.

Wayne L. Winston taught in the Operations & Decision Technologies Department in the Kelley School of Business at Indiana University for close to 40 before retiring a few years ago. Wayne received his B.S. degree in Mathematics from MIT and his PhD in Operations Research from Yale. He has written the successful textbooks Operations Research: Applications and Algorithms, Mathematical Programming: Applications and Algorithms, Simulation Modeling Using @RISK, Practical Management Science, Data Analysis and Decision Making, Financial Models Using Simulation and Optimization, and Mathletics. Wayne has published more than 20 articles in lead- ing journals and has won many teaching awards, including the school-wide MBA award four times. He has taught classes at Microsoft, GM, Ford, Eli Lilly, Bristol-Myers Squibb, Arthur Andersen, Roche, PricewaterhouseCoopers, and NCR, and in “retirement,” he is currently teach- ing several courses at the University of Houston. His current interest is showing how spread- sheet models can be used to solve business problems in all disciplines, particularly in finance and marketing.

Wayne enjoys swimming and basketball, and his passion for trivia won him an appearance several years ago on the television game show Jeopardy!, where he won two games. He is married to the lovely and talented Vivian. They have two children, Gregory and Jennifer.

ABOUT THE AUTHORS

09953_fm_ptg01_i-xxiv.indd 4 04/03/19 5:53 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

BRIEF CONTENTS Preface xvi

1 Introduction to Business Analytics 1

PART 1 Data Analysis 37 2 Describing the Distribution of a Variable 38 3 Finding Relationships among Variables 84 4 Business Intelligence (BI) Tools for Data Analysis 132

PART 2 Probability and Decision Making under Uncertainty 183 5 Probability and Probability Distributions 184 6 Decision Making under Uncertainty 242

PART 3 Statistical Inference 293 7 Sampling and Sampling Distributions 294 8 Confidence Interval Estimation 323 9 Hypothesis Testing 368

PART 4 Regression Analysis and Time Series Forecasting 411 10 Regression Analysis: Estimating Relationships 412 11 Regression Analysis: Statistical Inference 472 12 Time Series Analysis and Forecasting 523

PART 5 Optimization and Simulation Modeling 575 13 Introduction to Optimization Modeling 576 14 Optimization Models 630 15 Introduction to Simulation Modeling 717 16 Simulation Models 779

PART 6 Advanced Data Analysis 837 17 Data Mining 838 18 Analysis of Variance and Experimental Design (MindTap Reader only) 19 Statistical Process Control (MindTap Reader only) APPENDIX A: Quantitative Reporting (MindTap Reader only)

References 873

Index 875

09953_fm_ptg01_i-xxiv.indd 5 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

CONTENTS Preface xvi

1 Introduction to Business Analytics 1

1-1 Introduction 3

1-2 Overview of the Book 4

1-2a The Methods 4 1-2b The Software 6

1-3 Introduction to Spreadsheet Modeling 8

1-3a Basic Spreadsheet Modeling: Concepts and Best Practices 9 1-3b Cost Projections 12 1-3c Breakeven Analysis 15 1-3d Ordering with Quantity Discounts and Demand Uncertainty 20 1-3e Estimating the Relationship between Price and Demand 24 1-3f Decisions Involving the Time Value of Money 29

1-4 Conclusion 33

PART 1 Data Analysis 37

2 Describing the Distribution of a Variable 38

2-1 Introduction 39

2-2 Basic Concepts 41

2-2a Populations and Samples 41 2-2b Data Sets, Variables, and Observations 41 2-2c Data Types 42

2-3 Summarizing Categorical Variables 45

2-4 Summarizing Numeric Variables 49

2-4a Numeric Summary Measures 49 2-4b Charts for Numeric Variables 57

2-5 Time Series Data 62

2-6 Outliers and Missing Values 69

2-7 Excel Tables for Filtering, Sorting, and Summarizing 71

2-8 Conclusion 77

Appendix: Introduction to StatTools 83

3 Finding Relationships among Variables 84

3-1 Introduction 85

3-2 Relationships among Categorical Variables 86

09953_fm_ptg01_i-xxiv.indd 6 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

C O N T E N T S     v i i

3-3 Relationships among Categorical Variables and a Numeric Variable 89

3-4 Relationships among Numeric Variables 96

3-4a Scatterplots 96 3-4b Correlation and Covariance 101

3-5 Pivot Tables 106

3-6 Conclusion 126

Appendix: Using StatTools to Find Relationships 131

4 Business Intelligence (BI) Tools for Data Analysis 132

4-1 Introduction 133

4-2 Importing Data into Excel with Power Query 134

4-2a Introduction to Relational Databases 134 4-2b Excel’s Data Model 139 4-2c Creating and Editing Queries 146

4-3 Data Analysis with Power Pivot 152

4-3a Basing Pivot Tables on a Data Model 154 4-3b Calculated Columns, Measures, and the DAX Language 154

4-4 Data Visualization with Tableau Public 162

4-5 Data Cleansing 172

4-6 Conclusion 178

PART 2 Probability and Decision Making under Uncertainty 183

5 Probability and Probability Distributions 184

5-1 Introduction 185

5-2 Probability Essentials 186

5-2a Rule of Complements 187 5-2b Addition Rule 187 5-2c Conditional Probability and the Multiplication Rule 188 5-2d Probabilistic Independence 190 5-2e Equally Likely Events 191 5-2f Subjective Versus Objective Probabilities 192

5-3 Probability Distribution of a Random Variable 194

5-3a Summary Measures of a Probability Distribution 195 5-3b Conditional Mean and Variance 198

5-4 The Normal Distribution 200

5-4a Continuous Distributions and Density Functions 200 5-4b The Normal Density Function 201 5-4c Standardizing: Z-Values 202 5-4d Normal Tables and Z-Values 204

09953_fm_ptg01_i-xxiv.indd 7 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

v i i i     C O N T E N T S

5-4e Normal Calculations in Excel 205 5-4f Empirical Rules Revisited 208 5-4g Weighted Sums of Normal Random Variables 208 5-4h Normal Distribution Examples 209

5-5 The Binomial Distribution 214

5-5a Mean and Standard Deviation of the Binomial Distribution 217 5-5b The Binomial Distribution in the Context of Sampling 217 5-5c The Normal Approximation to the Binomial 218 5-5d Binomial Distribution Examples 219

5-6 The Poisson and Exponential Distributions 226

5-6a The Poisson Distribution 227 5-6b The Exponential Distribution 229

5-7 Conclusion 231

6 Decision Making under Uncertainty 242

6-1 Introduction 243

6-2 Elements of Decision Analysis 244

6-3 EMV and Decision Trees 247

6-4 One-Stage Decision Problems 251

6-5 The PrecisionTree Add-In 254

6-6 Multistage Decision Problems 257

6.6a Bayes’ Rule 262 6-6b The Value of Information 267 6-6c Sensitivity Analysis 270

6-7 The Role of Risk Aversion 274

6-7a Utility Functions 275 6-7b Exponential Utility 275 6-7c Certainty Equivalents 278 6-7d Is Expected Utility Maximization Used? 279

6-8 Conclusion 280

PART 3 Statistical Inference 293

7 Sampling and Sampling Distributions 294

7-1 Introduction 295

7-2 Sampling Terminology 295

7-3 Methods for Selecting Random Samples 297

7-3a Simple Random Sampling 297 7-3b Systematic Sampling 301 7-3c Stratified Sampling 301

09953_fm_ptg01_i-xxiv.indd 8 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

C O N T E N T S     i x

7-3d Cluster Sampling 303 7-3e Multistage Sampling 303

7-4 Introduction to Estimation 305

7-4a Sources of Estimation Error 305 7-4b Key Terms in Sampling 306 7-4c Sampling Distribution of the Sample Mean 307 7-4d The Central Limit Theorem 312 7-4e Sample Size Selection 317 7-4f Summary of Key Ideas in Simple Random Sampling 318

7-5 Conclusion 320

8 Confidence Interval Estimation 323

8-1 Introduction 323

8-2 Sampling Distributions 325

8-2a The t Distribution 326 8-2b Other Sampling Distributions 327

8-3 Confidence Interval for a Mean 328

8-4 Confidence Interval for a Total 333

8-5 Confidence Interval for a Proportion 336

8-6 Confidence Interval for a Standard Deviation 340

8-7 Confidence Interval for the Difference between Means 343

8-7a Independent Samples 344 8-7b Paired Samples 346

8-8 Confidence Interval for the Difference between Proportions 348

8-9 Sample Size Selection 351

8-10 Conclusion 358

9 Hypothesis Testing 368

9-1 Introduction 369

9-2 Concepts in Hypothesis Testing 370

9-2a Null and Alternative Hypotheses 370 9-2b One-Tailed Versus Two-Tailed Tests 371 9-2c Types of Errors 372 9-2d Significance Level and Rejection Region 372 9-2e Significance from p-values 373 9-2f Type II Errors and Power 375 9-2g Hypothesis Tests and Confidence Intervals 375 9-2h Practical Versus Statistical Significance 375

9-3 Hypothesis Tests for a Population Mean 376

9-4 Hypothesis Tests for Other Parameters 380

09953_fm_ptg01_i-xxiv.indd 9 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

x     C O N T E N T S

9-4a Hypothesis Test for a Population Proportion 380 9-4b Hypothesis Tests for Difference between Population Means 382 9-4c Hypothesis Test for Equal Population Variances 388 9-4d Hypothesis Test for Difference between Population Proportions 388

9-5 Tests for Normality 395

9-6 Chi-Square Test for Independence 401

9-7 Conclusion 404

PART 4 Regression Analysis and Time Series Forecasting 411

10 Regression Analysis: Estimating Relationships 412

10-1 Introduction 413

10-2 Scatterplots: Graphing Relationships 415

10-3 Correlations: Indicators of Linear Relationships 422

10-4 Simple Linear Regression 424

10-4a Least Squares Estimation 424 10-4b Standard Error of Estimate 431 10-4c R-Square 432

10-5 Multiple Regression 435

10-5a Interpretation of Regression Coefficients 436 10-5b Interpretation of Standard Error of Estimate and R-Square 439

10-6 Modeling Possibilities 442

10-6a Dummy Variables 442 10-6b Interaction Variables 448 10-6c Nonlinear Transformations 452

10-7 Validation of the Fit 461

10-8 Conclusion 463

11 Regression Analysis: Statistical Inference 472

11-1 Introduction 473

11-2 The Statistical Model 474

11-3 Inferences About the Regression Coefficients 477

11-3a Sampling Distribution of the Regression Coefficients 478 11-3b Hypothesis Tests for the Regression Coefficients and p-Values 480 11-3c A Test for the Overall Fit: The ANOVA Table 481

11-4 Multicollinearity 485

11-5 Include/Exclude Decisions 489

11-6 Stepwise Regression 494

11-7 Outliers 499

09953_fm_ptg01_i-xxiv.indd 10 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

C O N T E N T S     x i

11-8 Violations of Regression Assumptions 504

11-8a Nonconstant Error Variance 504 11-8b Nonnormality of Residuals 504 11-8c Autocorrelated Residuals 505

11-9 Prediction 507

11-10 Conclusion 512

12 Time Series Analysis and Forecasting 523

12-1 Introduction 524

12-2 Forecasting Methods: An Overview 525

12-2a Extrapolation Models 525 12-2b Econometric Models 526 12-2c Combining Forecasts 526 12-2d Components of Time Series Data 527 12-2e Measures of Accuracy 529

12-3 Testing for Randomness 531

12-3a The Runs Test 534 12-3b Autocorrelation 535

12-4 Regression-Based Trend Models 539

12-4a Linear Trend 539 12-4b Exponential Trend 541

12-5 The Random Walk Model 544

12-6 Moving Averages Forecasts 547

12-7 Exponential Smoothing Forecasts 551

12-7a Simple Exponential Smoothing 552 12-7b Holt’s Model for Trend 556

12-8 Seasonal Models 560

12-8a Winters’ Exponential Smoothing Model 561 12-8b Deseasonalizing: The Ratio-to-Moving-Averages Method 564 12-8c Estimating Seasonality with Regression 565

12-9 Conclusion 569

PART 5 Optimization and Simulation Modeling 575

13 Introduction to Optimization Modeling 576

13-1 Introduction 577

13-2 Introduction to Optimization 577

13-3 A Two-Variable Product Mix Model 579

09953_fm_ptg01_i-xxiv.indd 11 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

x i i     C O N T E N T S

13-4 Sensitivity Analysis 590

13-4a Solver’s Sensitivity Report 590 13-4b SolverTable Add-In 593 13-4c A Comparison of Solver’s Sensitivity Report and SolverTable 599

13-5 Properties of Linear Models 600

13-6 Infeasibility and Unboundedness 602

13-7 A Larger Product Mix Model 604

13-8 A Multiperiod Production Model 612

13-9 A Comparison of Algebraic and Spreadsheet Models 619

13-10 A Decision Support System 620

13-11 Conclusion 622

14 Optimization Models 630

14-1 Introduction 631

14-2 Employee Scheduling Models 632

14-3 Blending Models 638

14-4 Logistics Models 644

14-4a Transportation Models 644 14-4b More General Logistics Models 651

14-5 Aggregate Planning Models 659

14-6 Financial Models 667

14-7 Integer Optimization Models 677

14-7a Capital Budgeting Models 678 14-7b Fixed-Cost Models 682 14-7c Set-Covering Models 689

14-8 Nonlinear Optimization Models 695

14-8a Difficult Issues in Nonlinear Optimization 695 14-8b Managerial Economics Models 696 14-8c Portfolio Optimization Models 700

14-9 Conclusion 708

15 Introduction to Simulation Modeling 717

15-1 Introduction 718

15-2 Probability Distributions for Input Variables 720

15-2a Types of Probability Distributions 721 15-2b Common Probability Distributions 724 15-2c Using @RISK to Explore Probability Distributions 728

15-3 Simulation and the Flaw of Averages 736

15-4 Simulation with Built-in Excel Tools 738

09953_fm_ptg01_i-xxiv.indd 12 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

C O N T E N T S     x i i i

15-5 Simulation with @RISK 747

15-5a @RISK Features 748 15-5b Loading @RISK 748 15-5c @RISK Models with a Single Random Input 749 15-5d Some Limitations of @RISK 758 15-5e @RISK Models with Several Random Inputs 758

15-6 The Effects of Input Distributions on Results 763

15-6a Effect of the Shape of the Input Distribution(s) 763 15-6b Effect of Correlated Inputs 766

15-7 Conclusion 771

16 Simulation Models 779

16-1 Introduction 780

16-2 Operations Models 780

16-2a Bidding for Contracts 780 16-2b Warranty Costs 784 16-2c Drug Production with Uncertain Yield 789

16-3 Financial Models 794

16-3a Financial Planning Models 795 16-3b Cash Balance Models 799 16-3c Investment Models 803

16-4 Marketing Models 810

16-4a Customer Loyalty Models 810 16-4b Marketing and Sales Models 817

16-5 Simulating Games of Chance 823

16-5a Simulating the Game of Craps 823 16-5b Simulating the NCAA Basketball Tournament 825

16-6 Conclusion 828

PART 6 Advanced Data Analysis 837

17 Data Mining 838

17-1 Introduction 839

17-2 Classification Methods 840

17-2a Logistic Regression 841 17-2b Neural Networks 846 17-2c Naïve Bayes 851 17-2d Classification Trees 854 17-2e Measures of Classification Accuracy 855 17-2f Classification with Rare Events 857

09953_fm_ptg01_i-xxiv.indd 13 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

x i v     C O N T E N T S

17-3 Clustering Methods 860

17-4 Conclusion 870

18 Analysis of Variance and Experimental Design (MindTap Reader only)

18-1 Introduction 18-2

18-2 One-Way ANOVA 18-5

18-2a The Equal-Means Test 18-5 18-2b Confidence Intervals for Differences Between Means 18-7 18-2c Using a Logarithmic Transformation 18-11

18-3 Using Regression to Perform ANOVA 18-15

18-4 The Multiple Comparison Problem 18-18

18-5 Two-Way ANOVA 18-22

18-5a Confidence Intervals for Contrasts 18-28 18-5b Assumptions of Two-Way ANOVA 18-30

18-6 More About Experimental Design 18-32

18-6a Randomization 18-32 18-6b Blocking 18-35 18-6c Incomplete Designs 18-38

18-7 Conclusion 18-40

19 Statistical Process Control (MindTap Reader only)

19-1 Introduction 19-2

19-2 Deming’s 14 Points 19-3

19-3 Introduction to Control Charts 19-6

19-4 Control Charts for Variables 19-8

19-4a Control Charts and Hypothesis Testing 19-13 19-4b Other Out-of-Control Indications 19-15 19-4c Rational Subsamples 19-16 19-4d Deming’s Funnel Experiment and Tampering 19-18 19-4e Control Charts in the Service Industry 19-22

19-5 Control Charts for Attributes 19-26

19-5a P Charts 19-26 19-5b Deming’s Red Bead Experiment 19-29

19-6 Process Capability 19-33

19-6a Process Capability Indexes 19-35 19-6b More on Motorola and 6-Sigma 19-40

19-7 Conclusion 19-43

09953_fm_ptg01_i-xxiv.indd 14 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

C O N T E N T S     x v

APPENDIX A: Quantitative Reporting (MindTap Reader only)

A-1 Introduction A-1

A-2 Suggestions for Good Quantitative Reporting A-2

A-2a Planning A-2 A-2b Developing a Report A-3 A-2c Be Clear A-4 A-2d Be Concise A-4 A-2e Be Precise A-5

A-3 Examples of Quantitative Reports A-6

A-4 Conclusion A-16

References 873

Index 875

09953_fm_ptg01_i-xxiv.indd 15 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

PREFACE With today’s technology, companies are able to collect tremendous amounts of data with relative ease. Indeed, many com- panies now have more data than they can handle. However, before the data can be useful, they must be analyzed for trends, patterns, and relationships. This book illustrates in a practical way a variety of methods, from simple to complex, to help you analyze data sets and uncover important information. In many business contexts, data analysis is only the first step in the solution of a problem. Acting on the solution and the information it provides to make good decisions is a critical next step. Therefore, there is a heavy emphasis throughout this book on analytical methods that are useful in decision making. The meth- ods vary considerably, but the objective is always the same—to equip you with decision-making tools that you can apply in your business careers.

We recognize that the majority of students in this type of course are not majoring in a quantitative area. They are typically business majors in finance, marketing, operations management, or some other business discipline who will need to analyze data and make quantitative-based decisions in their jobs. We offer a hands-on, example-based approach and introduce fundamental concepts as they are needed. Our vehicle is spreadsheet software—specifically, Microsoft Excel®. This is a package that most students already know and will almost surely use in their careers. Our MBA students at Indiana University have been so turned on by the required course that is based on this book that almost all of them (mostly finance and marketing majors) have taken at least one of our follow-up elective courses in spreadsheet modeling. We are convinced that students see value in quantitative analysis when the course is taught in a practical and example-based approach.

Rationale for Writing This Book Business Analytics: Data Analysis and Decision Making is different from other textbooks written for statistics and management science. Our rationale for writing this book is based on four fundamental objectives.

• Integrated coverage and applications. The book provides a unified approach to business-related problems by integrat- ing methods and applications that have been traditionally taught in separate courses, specifically statistics and manage- ment science.

• Practical in approach. The book emphasizes realistic business examples and the processes managers actually use to analyze business problems. The emphasis is not on abstract theory or computational methods.

• Spreadsheet-based teaching. The book provides students with the skills to analyze business problems with tools they have access to and will use in their careers. To this end, we have adopted Excel and commercial spreadsheet add-ins.

• Latest tools. This is not a static field. The software keeps changing, and even the mathematical algorithms behind the software continue to evolve. Each edition of this book has presented the most recent tools in Excel and the accompanying Excel add-ins, and the current edition is no exception.

Integrated Coverage and Applications In the past, many business schools have offered a required statistics course, a required decision-making course, and a required management science course—or some subset of these. The current trend, however, is to have only one required course that cov- ers the basics of statistics, some regression analysis, some decision making under uncertainty, some linear programming, some simulation, and some advanced data analysis methods. Essentially, faculty in the quantitative area get one opportunity to teach all business students, so we attempt to cover a variety of useful quantitative methods. We are not necessarily arguing that this trend is ideal, but rather that it is a reflection of the reality at our university and, we suspect, at many others. After several years of teaching this course, we have found it to be a great opportunity to attract students to the subject and to more advanced study.

The book is also integrative in another important aspect. It not only integrates a number of analytical methods, but it also applies them to a wide variety of business problems—that is, it analyzes realistic examples from many business disciplines. We include examples, problems, and cases that deal with portfolio optimization, workforce scheduling, market share analysis, capital budgeting, new product analysis, and many others.

09953_fm_ptg01_i-xxiv.indd 16 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

P R E F A C E     x v i i

Practical in Approach This book has been designed to be very example-based and practical. We strongly believe that students learn best by working through examples, and they appreciate the material most when the examples are realistic and interesting. Therefore, our approach in the book differs in two important ways from many competitors. First, there is just enough conceptual development to give students an understanding and appreciation for the issues raised in the examples. We often introduce important concepts, such as standard deviation as a measure of variability, in the context of examples rather than discussing them in the abstract. Our experience is that students gain greater intuition and understanding of the concepts and applications through this approach.

Second, we place virtually no emphasis on hand calculations. We believe it is more important for students to understand why they are conducting an analysis and to interpret the results than to emphasize the tedious calculations associated with many analytical techniques. Therefore, we illustrate how powerful software can be used to create graphical and numerical outputs in a matter of seconds, freeing the rest of the time for in-depth interpretation of the results, sensitivity analysis, and alternative modeling approaches.

Spreadsheet-based Teaching We are strongly committed to teaching spreadsheet-based, example-driven courses, regardless of whether the basic area is data analysis or management science. We have found tremendous enthusiasm for this approach, both from students and from faculty around the world who have used our books. Students learn and remember more, and they appreciate the material more. In addition, instructors typically enjoy teaching more, and they usually receive immediate reinforcement through better teaching evaluations. We were among the first to move to spreadsheet-based teaching about two decades ago, and we have never regret- ted the move.

What We Hope to Accomplish in This Book Condensing the ideas in the previous paragraphs, we hope to:

• continue to make quantitative courses attractive to a wide audience by making these topics real, accessible, and interesting;

• give students plenty of hands-on experience with real problems and challenge them to develop their intuition, logic, and problem-solving skills;

• expose students to real problems in many business disciplines and show them how these problems can be analyzed with quantitative methods; and

• develop spreadsheet skills, including experience with powerful spreadsheet add-ins, that add immediate value to stu- dents’ other courses and for their future careers.

New in the Seventh Edition There are several important changes in this edition.

• New introductory material on Excel: Chapter 1 now includes an introductory section on spreadsheet modeling. This provides business examples for getting students up to speed in Excel and covers such Excel tools as IF and VLOOKUP functions, data tables, goal seek, range names, and more.

• Reorganization of probability chapters: Chapter 4, Probability and Probability Distributions, and Chapter 5, Normal, Binomial, Poisson, and Exponential Distributions, have been shortened slightly and combined into a single Chapter 5, Probability and Probability Distributions. This created space for the new Chapter 4 discussed next.

• New material on “Power BI” tools and data visualization: The previous chapters on Data Mining and Importing Data into Excel have been reorganized and rewritten to include an increased focus on the tools commonly included under the Business Analytics umbrella. There is now a new Chapter 4, Business Intelligence Tools for Data Analysis, which includes Excel’s Power Query tools for importing data into Excel, Excel’s Power Pivot add-in (and the DAX language) for even more powerful data analysis with pivot tables, and Tableau Public for data visualization. The old online Chapter 18, Importing Data into Excel, has been eliminated, and its material has been moved to this new Chapter 4.

09953_fm_ptg01_i-xxiv.indd 17 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

x v i i i     P R E F A C E

• Updated for Office 365, Windows or Mac: The 7th Edition is completely compatible with the latest version of Excel, and all screenshots in the book are from the latest version. However, because the changes from previous versions are not that extensive for Business Analytics purposes, the 7th Edition also works well even if you are still using Microsoft Office 2013, 2010, or 2007. Also, recognizing that many students are now using Macs, we have attempted to make the material compatible with Excel for Mac whenever possible.

• Updated Problems: Numerous problems have been modified to include the most updated data available. In addition, the DADM 7e Problem Database.xlsx file provides instructors with an entire database of problems. This file indicates the context of each of the problems and shows the correspondence between problems in this edition and problems in the previous edition.

• Less emphasis on add-ins (when possible): There is more emphasis in this edition on implementing spreadsheet calculations, especially statistical calculations, with built-in Excel tools rather than with add-ins. For example, there is no reliance on Palisade’s StatTools add-in in the descriptive statistics chapters 2 and 3 or in the confidence interval and hypothesis testing chapters 8 and 9. Nevertheless, Palisade’s add-ins are still relied on in chapters where they are really needed: PrecisionTree for decision trees in Chapter 6; StatTools for regression and time series analysis in Chapters 10, 11, and 12; @RISK for simulation in Chapters 15 and 16; and StatTools and NeuralTools for logistic regression and neu- ral networks in Chapter 17.

• New optional add-in: Although it is not an “official” part of the book, Albright wrote a DADM_Tools add-in for Excel (Windows or Mac), with tools for creating summary stats, histograms, correlations and scatterplots, regression, time series analysis, decision trees, and simulation. This add-in provides a “lighter” alternative to the Palisade add-ins and is freely available at https://kelley.iu.edu/albrightbooks/free_downloads.htm.

Software This book is based entirely on Microsoft Excel, the spreadsheet package that has become the standard analytical tool in busi- ness. Excel is an extremely powerful package, and one of our goals is to convert casual users into power users who can take full advantage of its features. If you learn no more than this, you will be acquiring a valuable skill for the business world. However, Excel has some limitations. Therefore, this book relies on several Excel add-ins to enhance Excel’s capabilities. As a group, these add-ins comprise what is arguably the most impressive assortment of spreadsheet-based software accompanying any book on the market.

DecisionTools® Suite Add-in The textbook website for Business Analytics: Data Analysis and Decision Making provides a link to the powerful DecisionTools® Suite by Palisade Corporation. This suite includes seven separate add-ins:

• @RISK, an add-in for simulation

• StatTools, an add-in for statistical data analysis

• PrecisionTree, a graphical-based add-in for creating and analyzing decision trees

• TopRank, an add-in for performing what-if analyses

• NeuralTools®, an add-in for estimating complex, nonlinear relationships

• EvolverTM, an add-in for performing optimization (an alternative to Excel’s Solver)

• BigPicture, a smart drawing add-in, useful for depicting model relationships

We use @RISK and PrecisionTree extensively in the chapters on simulation and decision making under uncertainty, and we use StatTools as necessary in the data analysis chapters. We also use BigPicture in the optimization and simulation chapters to provide a “bridge” between a problem statement and an eventual spreadsheet model.

Online access to the DecisionTools Suite, available with new copies of the book and for MindTap adopters, is an academic version, slightly scaled down from the professional version that sells for hundreds of dollars and is used by many leading companies. It functions for one year when properly installed, and it puts only modest limitations on the size of data sets or models that can be analyzed.

09953_fm_ptg01_i-xxiv.indd 18 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

SolverTable Add-in We also include SolverTable, a supplement to Excel’s built-in Solver for optimization.1 If you have ever had difficulty under- standing Solver’s sensitivity reports, you will appreciate SolverTable. It works like Excel’s data tables, except that for each input (or pair of inputs), the add-in runs Solver and reports the optimal output values. SolverTable is used extensively in the optimization chapters.

Windows versus Mac We have seen an increasing number of students using Macintosh laptops rather than Windows laptops. These students have two basic options when using our book. The first option is to use the latest version of Excel for Mac. Except for a few advanced tools such as Power Pivot (discussed in Chapter 4), the Mac version of Excel is very similar to the Windows version. However, the Palisade and SolverTable add-ins will not work with Excel for Mac. Therefore, the second option, the preferable option, is to use a Windows emulation program (Bootcamp and Parallels are good candidates), along with Office for Windows. Students at Indiana have used this second option for years and have had no problems.

Software Calculations by Chapter This section indicates how the various calculations are implemented in the book. Specifically, it indicates which calculations are performed with built-in Excel tools and which require Excel add-ins.

Important note: The Palisade add-ins used in several chapters do not work in Excel for Mac. This is the primary reason Albright developed his own DADM_Tools add-in, which works in Excel for Windows and Excel for Mac. This add-in is freely available at the author’s website (https://kelley.iu.edu/albrightbooks/free_downloads.htm), together with a Word document on how to use it. However, it is optional and is not used in the book.

Chapter 1 – Introduction to Business Analytics

• The section on basic spreadsheet modeling is implemented with built-in Excel functions.

Chapter 2 – Describing the Distribution of a Variable

• Everything is implemented with built-in Excel functions and charts.

° Summary measures are calculated with built-in functions AVERAGE, STDEV.S, etc. ° Histograms and box plots are created with the Excel chart types introduced in 2016. ° Time series graphs are created with Excel line charts.

• Palisade’s StatTools add-in can do all of this. It isn’t used in the chapter, but it is mentioned in a short appendix, and an Intro to StatTools video is available.

• Albright’s DADM_Tools add-in can do all of this except for time series graphs.

Chapter 3 – Finding Relationships among Variables

• Everything is implemented with built-in Excel functions and charts.

° Summary measures of numeric variables, broken down by categories of a categorical variable, are calculated with built-in functions AVERAGE, STDEV.S, etc. (They are embedded in array formulas with IF functions.)

° Side-by-side box plots are created with the Excel box plot chart type introduced in 2016. ° Scatterplots are created with Excel scatter charts. ° Correlations are calculated with Excel’s CORREL function. A combination of the CORREL and INDIRECT func-

tions is used to create tables of correlations.

• StatTools can do all of this. It isn’t used in the chapter, but it is mentioned in a short appendix.

• DADM_Tools can do all of this.

1 SolverTable is available on this textbook’s website and on Albright’s website, www.kelley.iu.edu/albrightbooks.

P R E F A C E     x i x

09953_fm_ptg01_i-xxiv.indd 19 04/03/19 5:54 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or