BusinessAnalyticsMethods, Models, andDecisionsJames R. Evans i University of CincinnatiGLOBAL EDITIONSECOND EDITIONPEARSONBoston Columbus Indianapolis New York San FranciscoAmsterdam Cape Town Dubai London Madrid Milan Munich Paris Montreal TorontoDelhi Mexico City Säo Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo

ContentsPreface 17About the AuthorCredits 2523Part 1: Foundations of Business AnalyticsChapter 1: Introduction to Business Analytics27Learning Objectives 27What Is Business Analytics? 30Evolution of Business Analytics 31Impacts and Challenges 34Scope of Business Analytics 35Software Support 38Data for Business Analytics 39Data Sets and Databases 40 Big Data 41 Metrics and DataClassification 42 * Data Reliability and Validity 44Models in Business Analytics 44Decision Models 47 Model Assumptions 50 Uncertainty and Risk 52 Prescriptive Decision Models 52Problem Solving with Analytics 53Recognizing a Problem 54 Defining the Problem 54 Structuring theProblem 54 Analyzing the Problem 55 » Interpreting Results and Makinga Decision 55 Implementing the Solution 55Key Terms 56 Fun with Analytics 57 Problems and Exercises 57 *Case: Drout Advertising Research Project 59 Case: Performance LawnEquipment 60Chapter 2: Analytics on Spreadsheets 63Learning Objectives 63Basic Excel Skills 65Excel Formulas 66 Copying Formulas 66 Other Useful Excel Tips 67Excel Functions 68Basic Excel Functions 68 Functions for Specific Applications 69 Insert Function 70 Logical Functions 71Using Excel Lookup Functions for Database Queries 73Spreadsheet Add-Ins for Business Analytics 76Key Terms 76 Problems and Exercises 76 Case: Performance LawnEquipment 787

BContentsPart 2: Descriptive AnalyticsChapter 3: Visualizing and Exploring Data79Learning Objectives 79Data Visualization 80Dashboards 81 Tools and Software for Data Visualization 81Creating Charts in Microsoft Excel 82Column and Bar Charts 83 Data Labels and Data Tables ChartOptions 85 Line Charts 85 Pie Charts 85 Area Charts 86 Scatter Chart 86 Bubble Charts 88 * MiscellaneousExcel Charts 89 Geographie Data 89Other Excel Data Visualization Tools 90Data Bars, Color Scales, and Icon Sets 90 Sparklines 91 Excel CameraTool 92Data Queries: Tables, Sorting, and Filtering 93Sorting Data in Excel 94 Pareto Analysis 94 Filtering Data 96Statistical Methods for Summarizing Data 98Frequency Distributions for Categorical Data 99 Relative FrequencyDistributions 100 Frequency Distributions for Numerical Data 101 Excel Histogram Tool 101 Cumulative Relative FrequencyDistributions 105 Percentiles and Quartiles 106 Cross-Tabulations 108Exploring Data Using PivotTables 110PivotCharts 112 Slicers and PivotTable Dashboards 113Key Terms 116 Problems and Exercises 117 * Case: Drout Advertising ResearchProject 119 Case: Performance Lawn Equipment 120Chapter 4: Descriptive Statistical Measures 121Learning Objectives 121Populations and Samples 122Understanding Statistical Notation 122Measures of Location 123Arithmetic Mean 123 Median 124 Mode 125 Midrange 125 Using Measures of Location in Business Decisions 126Measures of Dispersion 127Range 127 Interquartile Range 127 Variance 128 StandardDeviation 129 Chebyshev's Theorem and the Empirical Rules 130 Standardized Values 133 Coefficient of Variation 134Measures of Shape 135Excel Descriptive Statistics Tool 136Descriptive Statistics for Grouped Data 138Descriptive Statistics for Categorical Data: The Proportion 140Statistics in PivotTables 140

Contents9Measures of Association 141Covariance 142 Correlation 143 Excel Correlation Tool 145Outliers 146Statistical Thinking in Business Decisions 148Variability in Samples 149Key Terms 151 Problems and Exercises 152 Case: Drout Advertising ResearchProject 155 Case: Performance Lawn Equipment 155Chapter 5: Probability Distributions and Data Modeling157Learning Objectives 157Basic Concepts of Probability 158Probability Rules and Formulas 160 Joint and Marginal Probability 161 Conditional Probability 163Random Variables and Probability Distributions 166Discrete Probability Distributions 168Expected Value of a Discrete Random Variable 169 Using Expected Value inMaking Decisions 170 Variance of a Discrete Random Variable 172 Bernoulli Distribution 173 Binomial Distribution 173 "Poisson Distribution 175Continuous Probability Distributions 176Properties of Probability Density Functions 177 Uniform Distribution 178 Normal Distribution 180 * The NORM.INV Function 182 Standard NormalDistribution 182 Using Standard Normal Distribution Tables 184 Exponential Distribution 184 Other Useful Distributions 186 * ContinuousDistributions 186Random Sampling from Probability Distributions 187Sampling from Discrete Probability Distributions 188 Sampling from CommonProbability Distributions 189 Probability Distribution Functions in Analytic SolverPlatform 192Data Modeling and Distribution Fitting 194Goodness of Fit 196 Distribution Fitting with Analytic Solver Platform 196Key Terms 198 Problems and Exercises 199 Case: Performance LawnEquipment 205Chapter 6: Sampling and Estimation207Learning Objectives 207Statistical Sampling 208Sampling Methods 208Estimating Population Parameters 211Unbiased Estimators 212 Errors in Point Estimation 212Sampling Error 213Understanding Sampling Error 213

10ContentsSampling Distributions 215Sampling Distribution of the Mean 215 * Applying the Sampling DistributionoftheMean 216Interval Estimates 216Confidence Intervals 217Confidence Interval for the Mean with Known Population StandardDeviation 218 The f-Distribution 219 Confidence Interval for theMean with Unknown Population Standard Deviation 220 Confidence Intervalfor a Proportion 220 Additional Types of Confidence Intervals 222Using Confidence Intervals for Decision Making 222Prediction Intervals 223Confidence Intervals and Sample Size 224Key Terms 226 Problems and Exercises 226 Case: Drout AdvertisingResearch Project 228 Case: Performance Lawn Equipment 229Chapter 7: Statistical Inference231Learning Objectives 231Hypothesis Testing 232Hypothesis-Testing Procedure 233One-Sample Hypothesis Tests 233Understanding Potential Errors in Hypothesis Testing 234 Selecting the TestStatistic 235 Drawing a Conclusion 236Two-Tailed Test of Hypothesis for the Mean 238p-Values 238 One-Sample Tests for Proportions 239 Confidence Intervalsand Hypothesis Tests 240Two-Sample Hypothesis Tests 241Two-Sample Tests for Differences in Means 241 Two-Sample Test for Means withPaired Samples 244 Test for Equality of Variances 245Analysis of Variance (ANOVA) 247Assumptions of ANOVA 249Chi-Square Test for Independence 250Cautions in Using the Chi-Square Test 252Key Terms 253 * Problems and Exercises 254 Case: Drout Advertising ResearchProject 257 Case: Performance Lawn Equipment 257Part 3: Predictive AnalyticsChapter 8: Trendlines and Regression Analysis259Learning Objectives 259Modeling Relationships and Trends in Data 260Simple Linear Regression 264Finding the Best-Fitting Regression Line 265 Least-Squares Regression 267Simple Linear Regression with Excel 269 Regression as Analysis ofVariance 271 Testing Hypotheses for Regression Coefficients 271 Confidence Intervals for Regression Coefficients 272

Contents11Residual Analysis and Regression Assumptions 272Checking Assumptions 274Multiple Linear Regression 275Building Good Regression Models 280Correlation and Multicollinearity 282 Practical Issues in Trendline and RegressionModeling 283Regression with Categorical Independent Variables 284Categorical Variables with More Than Two Levels 287Regression Models with Nonlinear Terms 289Advanced Techniques for Regression Modeling using XLMiner 291Key Terms 294 Problems and Exercises 294 Case: Performance LawnEquipment 298Chapter 9: Forecasting Techniques 299Learning Objectives 299Qualitative and Judgmental Forecasting 300Historical Analogy 300 The Delphi Method 301 Indicators and Indexes 301Statistical Forecasting Models 302Forecasting Models for Stationary Time Series 304Moving Average Models 304 Error Metrics and Forecast Accuracy 308 Exponential Smoothing Models 310Forecasting Models for Time Series with a Linear Trend 312Double Exponential Smoothing 313 Regression-Based Forecasting for Time Serieswith a Linear Trend 314Forecasting Time Series with Seasonality 316Regression-Based Seasonal Forecasting Models 316 Holt-Winters Forecasting forSeasonal Time Series 318 Holt-Winters Models for Forecasting Time Series withSeasonality and Trend 318Selecting Appropriate Time-Series-Based Forecasting Models 320Regression Forecasting with Causal Variables 321The Practice of Forecasting 322Key Terms 324 * Problems and Exercises 324 Case: Performance LawnEquipment 326Chapter 10: introduction to Data Mining327Learning Objectives 327The Scope of Data Mining 329Data Exploration and Reduction 330Sampling 330 Data Visualization 332 Dirty Data 334 ClusterAnalysis 336Classification 341An Intuitive Explanation of Classification 342 Measuring ClassificationPerformance 342 Using Training and Validation Data 344 ClassifyingNew Data 346

2ContentsClassification Techniques 346/fc-Nearest Neighbors (fc-NN) 347 Discriminant Analysis 349 LogisticRegression 354 Association Rule Mining 358Cause-and-Effect Modeling 361Key Terms 364 Problems and Exercises 364 Case: Performance LawnEquipment 366Chapter 11: Spreadsheet Modeling and Analysis367Learning Objectives 367Strategies for Predictive Decision Modeling 368Building Models Using Simple Mathematics 368 Building Models Using InfluenceDiagrams 369Implementing Models on Spreadsheets 370Spreadsheet Design 370 Spreadsheet Quality 372Spreadsheet Applications in Business Analytics 375Models Involving Multiple Time Periods 377 Single-Period PurchaseDecisions 379 Overbooking Decisions 380Model Assumptions, Complexity, and Realism 382Data and Models 382Developing User-Friendly Excel Applications 385Data Validation 385 Range Names 385 Form Controls 386Analyzing Uncertainty and Model Assumptions 388What-If Analysis 388 Data Tables 390 Scenario Manager 392 *Goal Seck 393Model Analysis Using Analytic Solver Platform 394Parametric Sensitivity Analysis 394 Tornado Charts 396Key Terms 397 * Problems and Exercises 397 Case: Performance LawnEquipment 402Chapter 12: Monte Carlo Simulation and Risk Analysis403Learning Objectives 403Spreadsheet Models with Random Variables 405Monte Carlo Simulation 405Monte Carlo Simulation Using Analytic Solver Platform 407Defining Uncertain Model Inputs 407 Defining Output Cells 410 Running a Simulation 410 Viewing and Analyzing Results 412New-Product Development Model 414Confidence Interval for the Mean 417 Sensitivity Chart 418 OverlayCharts 418 Trend Charts 420 Box-Whisker Charts 420 Simulation Reports 421Newsvendor Model 421The Flaw of Averages 421 Monte Carlo Simulation Using HistoricalData 422 Monte Carlo Simulation Using a Fitted Distribution 423Overbooking Model 424The Custom Distribution in Analytic Solver Platform 425

ContentsCash Budget Model 426Correlating Uncertain Variables 429Key Terms 433 Problems and Exercises 433 Case: Performance LawnEquipment 440Part 4: Prescriptive AnalyticsChapter 13: Linear Optimization441Learning Objectives 441Building Linear Optimization Models 442Identifying Elements for an Optimization Model 442 Translating ModelInformation into Mathematical Expressions 443 More aboutConstraints 445 Characteristics of Linear Optimization Models 446Implementing Linear Optimization Models on Spreadsheets 446Excel Functions to Avoid in Linear Optimization 448Solving Linear Optimization Models 448Using the Standard Solver 449 Using Premium Solver 451 SolverAnswer Report 452Graphical Interpretation of Linear Optimization 454How Solver Works 459How Solver Creates Names in Reports 461Solver Outcomes and Solution Messages 461Unique Optimal Solution 462 Alternative (Multiple) OptimalSolutions 462 Unbounded Solution 463 Infeasibility 464Using Optimization Models for Prediction and Insight 465Solver Sensitivity Report 467 Using the Sensitivity Report 470 Parameter Analysis in Analytic Solver Platform 472Key Terms 476 Problems and Exercises 476 Case: Performance LawnEquipment 481Chapter 14: Applications of Linear Optimization483Learning Objectives 483Types of Constraints in Optimization Models 485Process Selection Models 486Spreadsheet Design and Solver Reports 487Solver Output and Data Visualization 489Blending Models 493Dealing with Infeasibility 494Portfolio Investment Models 497Evaluating Risk versus Reward 499 Scaling Issues in Using Solver 500Transportation Models 502Formatting the Sensitivity Report 504 Degeneracy 506Multiperiod Production Flanning Models 506Building Alternative Models 508Multiperiod Financial Flanning Models 511

ContentsModels with Bounded Variables 515Auxiliary Variables for Bound Constraints 519A Production/Marketing Allocation Model 521Using Sensitivity Information Correctly 523Key Terms 525 ' Problems and Exercises 525 Case: Performance LawnEquipment 537Chapter 15: Integer Optimization539Learning Objectives 539Solving Models with General Integer Variables 540Workforce-Scheduling Models 544 Alternative Optimal Solutions 545Integer Optimization Models with Binary Variables 549Project-Selection Models 550 Using Binary Variables to Model LogicalConstraints 552 Location Models 553 Parameter Analysis 555 A Customer-Assignment Model for Supply Chain Optimization 556Mixed-Integer Optimization Models 559Plant Location and Distribution Models 559 * Binary Variables, IF Functions, andNonlinearities in Model Formulation 560 Fixed-Cost Models 562Key Terms 564 ' Problems and Exercises 564 Case: Performance LawnEquipment 573Chapter 16: Decision Analysis 579Learning Objectives 579Formulating Decision Problems 581Decision Strategies without Outcome Probabilities 582Decision Strategies for a Minimize Objective 582 Decision Strategies for aMaximize Objective 583 Decisions with Conflicting Objectives 584Decision Strategies with Outcome Probabilities 586Average Payoff Strategy 586 Expected Value Strategy 586 Evaluating Risk 587Decision Trees 588Decision Trees and Monte Carlo Simulation 592 Decision Trees andRisk 592 Sensitivity Analysis in Decision Trees 594The Value of Information 595Decisions with Sample Information 596 Bayes's Rule 596Utility and Decision Making 598Constructing a Utility Function 599 * Exponential Utility Functions 602Key Terms 604 ' Problems and Exercises 604 Case: Performance LawnEquipment 608

Contents15Supplementary Chapter A (online) Nonlinear and Non-Smooth OptimizationSupplementary Chapter B (online) Optimization Models with UncertaintyOnline chapters are available for download at A 611Glossary 635Index 643

Excel Charts 89 Geographie Data 89 Other Excel Data Visualization Tools 90 Data Bars, Color Scales, and Icon Sets 90 Sparklines 91 Excel Camera Tool 92 Data Queries: Tables, Sorting, and Filtering 93 Sorting Data in Excel 94 Pareto Analysis 94 Filtering Data 96 Statistical Methods for Summarizing Data 98