Transcription
mBusinessAnalyticsMethods, Models, and DecisionsJames R. EvansUniversity of CincinnatiTHIRD EDITIONflPearson
ContentsPreface xviiAbout the Author xxvCredits xxviiPart 1: Foundations of Business AnalyticsChapter 1: Introduction to Business Analytics 1Learning Objectives 1What is Business Analytics? 3Using Business Analytics 4 Impacts and Challenges 5Evolution of Business Analytics 6Analytic Foundations 6 Modern Business Analytics 7 Software Supportand Spreadsheet Technology 9Analytics in Practice: Social Media Analytics 10Descriptive, Predictive, and Prescriptive Analytics 11Analytics in Practice: Analytics in the Home Lending and Mortgage Industry 13Data for Business Analytics 14Big Data 16 Data Reliability and Validity 16Models in Business Analytics 17Descriptive Models 19 Predictive Models 21 Prescriptive Models 22 Model Assumptions 23 Uncertainty and Risk 25Problem Solving with Analytics 26Recognizing a Problem 26 Defining the Problem 26 Structuring theProblem 27 Analyzing the Problem 27 Interpreting Results and Making aDecision 27 Implementing the Solution 27Analytics in Practice: Developing Effective Analytical Tools at Hewlett-Packard 28Key Terms 29 * Chapter 1 Technology Help 29 Problems and Exercises 29 Case: Performance Lawn Equipment 31Appendix A1: Basic Excel Skills 33Excel Formulas and Addressing 34Copying Formulas 35Useful Excel Tips 35Excel Functions 36Basic Excel Functions 36 Functions for Specific Applications 37 InsertFunction 38 Date and Time Functions 39Miscellaneous Excel Functions and Tools 40Range Names 40 VALUE Function 43 PasteSpecial 43 Concatenation 44 Error Values 44Problems and Exercises 45vii
Chapter 2: Database Analytics47Learning Objectives 47Data Sets and Databases 49Using Range Names in Databases 50Analytics in Practice: Using Big Data to Monitor Water Usage in Cary,North Carolina 51Data Queries: Tables, Sorting, and Filtering 51Sorting Data in Excel 52 Pareto Analysis 53 Filtering Data 54 Database Functions 56Logical Functions 59Lookup Functions for Database Queries 61Excel Template Design 64Data Validation Tools 65 Form Controls 67PivotTables 70PivotTable Customization 72 Slicers 75Key Terms 76 Chapter 2 Technology Help 76 Problems and Exercises 77 Case: People 's choice Bank 81 Case: Drout Advertising Research Project 82Part 2: Descriptive AnalyticsChapter 3: Data Visualization85Learning Objectives 85The Value of Data Visualization 86Tools and Software for Data Visualization 88Analytics in Practice: Data Visualization for the New York City Police Depnrünent'sDomain Awareness System 88Creating Charts in Microsoft Excel 88Column and Bar Charts 89 Data Label and Data Table Chart Options 90 Line Charts 91 Pie Charts 92 Area Charts 93 Scatter Chartsand Orbit Charts 94 Bubble Charts 95 Combination Charts 96 Radar Charts 97 Stock Charts 97 Charts from PivotTables 97 Geographie Data 98Other Excel Data Visualization Tools 98Data Bars 98 ColorScales 99 Icon Sets 100 Sparklines 101Dashboards 103Analytics in Practice: Driving Business Transformation with IBM BusinessAnalytics 104Key Terms 105 Chapter 3 Technology Help 105 Problems and Exercises 106 Case: Performance Lawn Equipment 107Appendix A3: Additional Tools for Data Visualization 108Hierarchy Charts 108PivotCharts 110Tableau 111Problems and Exercises 113Chapter 4: Descriptive Statistics 115Learning Objectives 115Analytics in Practice: Applications of Statistics in Health care 117
ContentsixMetrics and Data Classification 118Frequency Distributions and Histograms 120Frequency Distributions for Categorical Data 120 Relative FrequencyDistributions 121 Frequency Distributions for Numerical Data 122 GroupedFrequency Distributions 123 Cumulative Relative Frequency Distributions 126 Constructing Frequency Distributions Using PivotTables 127Percentiles and Quartiles 129Cross-Tabulations 130Descriptive Statistical Measures 132Populations and Samples 132 Statistical Notation 133 Measures ofLocation: Mean, Median, Mode, and Midrange 133 Using Measures of Locationin Business Decisions 135 Measures of Dispersion: Range, InterquartileRange, Variance, and Standard Deviation 137 Chebyshev's Theorem and theEmpirical Rules 140 Standardized Values (Z-Scores) 142 Coefficient ofVariation 143 Measures of Shape 144 Excel Descriptive Statistics Tool 146Computing Descriptive Statistics for Frequency Distributions 147Descriptive Statistics for Categorical Data: The Proportion 149Statistics in PivotTables 150Measures of Association 151Covariance 152 Correlation 153 Excel Correlation Tool 155Outliers 156Using Descriptive Statistics to Analyze Survey Data 158Statistical Thinking in Business Decisions 159Variability in Samples 160Analytics in Practice: Applying Statistical Thinking to Detecting Financial Problems 162Key Terms 163 Chapter 4 Technology Help 164 Problems and Exercises 165 *Case: Drout Advertising Research Project 170 Case: Performance Lawn Equipment 170Appendix A4: Additional Charts for Descriptive Statistics in Excel for Windows 171Chapter 5: Probability Distributions and Data Modeling173Learning Objectives 173Basic Concepts of Probability 175Experiments and Sample Spaces 175 Combinations and Permutations 175 Probability Definitions 177 Probability Rules and Formulas 179 Joint andMarginal Probability 180 Conditional Probability 182Random Variables and Probability Distributions 185Discrete Probability Distributions 187Expected Value of a Discrete Random Variable 188 Using Expected Value inMaking Decisions 189 Variance of a Discrete Random Variable 191 BemoulliDistribution 191 Binomial Distribution 192 Poisson Distribution 193Analytics in Practice: Using the Poisson Distribution for Modeling Bids onPriceline 195Continuous Probability Distributions 196Properties of Probability Density Functions 196 Uniform Distribution 197 Normal Distribution 199 The NORM.INV Function 200 Standard NormalDistribution 201 Using Standard Normal Distribution Tables 202 ExponentialDistribution 203 Triangulär Distribution 204
XContentsData Modeling and Distribution Eitting 205Goodness of Fit: Testing for Normality of an Empirical Distribution 207 Chi-Square Goodness of Fit Test 208Analytics in Practice: The value of Good Data Modeling in Advertising 209Key Terms 210 * Chapter 5 Technology Help 210 Problems and Exercises 211 *Case: Performance Lawn Equipment 217Chapter 6: Sampling and Estimation219Leaming Objectives 219Statistical Sampling 220Sampling Methods 221Analytics in Practice: Using Sampling Techniques to lmprove Distribution 223Estimating Population Parameters 224Unbiased Estimators 224 Errors in Point Estimation 225 UnderstandingSampling Error 226Sampling Distributions 228Sampling Distribution of the Mean 228 Applying the Sampling Distribution of theMean 229Interval Estimates 229Confidence Intervals 230 Confidence Interval for the Mean with KnownPopulation Standard Deviation 231 The (-Distribution 232 ConfidenceInterval for the Mean with Unknown Population Standard Deviation 233 Confidence Interval for a Proportion 233 Additional Types of ConfidenceIntervals 235Using Confidence Intervals for Decision Making 235Data Visualization for Confidence Interval Comparison 236Prediction Intervals 237Confidence Intervals and Sample Size 238Key Terms 240 Chapter 6 Technology Help 240 Problems and Exercises 241 Case: Drout Advertising Research Project 244 Case: Performance Lawn Equipment 244Chapter 7: Statistical Inference247Leaming Objectives 247Hypothesis Testing 248Hypothesis-Testing Procedure 249One-Sample Hypothesis Tests 249Understanding Potential Errors in Hypothesis Testing 250 Selecting the TestStatistic 251 Finding Critical Values and Drawing a Conclusion 252 TwoTailed Test of Hypothesis for the Mean 254 Summary of One-SampleHypothesis Tests for the Mean 255 p- Values 256 One-Sample Tests forProportions 257 Confidence Intervals and Hypothesis Tests 258 An ExcelTemplate for One-Sample Hypothesis Tests 258Two-Sample Hypothesis Tests 259Two-Sample Tests for Differences in Means 260 Two-Sample Test for Means withPaired Samples 262 Two-Sample Test for Equality of Variances 264Analysis of Variance (ANOVA) 266Assumptions of ANOVA 268
ContentsxiChi-Square Test for Independence 269Cautions in Using the Chi-Square Test 271 Chi-Square Goodnessof Fit Test 272Analytics in Practice: Using Hypothesis Tests and Business Analytics in a Help DeskService Improvement Project 273Key Terms 274 Chapter 7 Technology Help 274 Problems and Exercises 276 Case: Drout Advertising Research Project 281 Case: Performance Lawn Equipment 281Part 3: Predictive AnalyticsChapter 8: Trendlines and Regression Analysis 283Learning Objectives 283Modeling Relationships and Trends in Data 285Analytics in Practice: Using Predictive Trendline Models at Procter & Gamble 289Simple Linear Regression 289Finding the Best-Fitting Regression Line 291 Using Regression Models forPrediction 291 Least-Squares Regression 292 Simple Linear Regression withExcel 294 Regression as Analysis of Variance 296 Testing Hypotheses forRegression Coefficients 297 Confidence Intervals for Regression Coefficients 297Residual Analysis and Regression Assumptions 298Checking Assumptions 299Multiple Linear Regression 301Analytics in Practice: Using Linear Regression and Interactive Risk Simulators toPredict Performance at Aramark 304Building Good Regression Models 306Correlation and Multicollinearity 308 Practical Issues in Trendline and RegressionModeling 310Regression with Categorical Independent Variables 310Categorical Variables with More Than Two Levels 313Regression Models with Nonlinear Terms 315Key Terms 317 Chapter 8 Technology Help 317 Problems and Exercises 318 Case: Performance Lawn Equipment 322Chapter 9: Forecasting Techniques 325Learning Objectives 325Analytics in Practice: Forecasting Call-Center Demand at EL. Bean 326Qualitative and Judgmental Forecasting 327Historical Analogy 327 The Delphi Method 327 Indicators and Indexes 328Statistical Forecasting Models 329Forecasting Models for Stationary Time Series 331Moving Average Models 331 Error Metrics and Forecast Accuracy 333 Exponential Smoothing Models 335Forecasting Models for Time Series with a Linear Trend 338Double Exponential Smoothing 338 Regression-Based Forecasting for Time Serieswith a Linear Trend 340Forecasting Time Series with Seasonality 341Regression-Based Seasonal Forecasting Models 341 Holt-Winters Models forForecasting Time Series with Seasonality and No Trend 343 Holt-Winters Modelsfor Forecasting Time Series with Seasonality and Trend 345 Selecting AppropriateTime-Series-Based Forecasting Models 348
XiiContentsRegression Forecasting with Causal Variables 348The Practice of Forecasting 349Analytics in Practice: Forecasting at NBCUniversal 350Key Terms 351 Chapter 9 Technology Help 352 Problems and Exercises 352 Gase: Performance Lawn Equipment 354Chapter 10: Introduction to Data Mining355Leaming Objectives 355The Scope of Data Mining 356Cluster Analysis 358Measuring Distance Between Objects 359 Normalizing DistanceMeasures 360 Clustering Methods 360Classification 362An Intuitive Explanation of Classification 363 Measuring ClassificationPerformance 364 Classification Techniques 365Association 370Cause-and-Effect Modeling 372Analytics In Practice: Successful Business Applications ofData Mining 374Key Terms 374 Chapter 10 Technology Help 375 Problems and Exercises 375 Case: Performance Lawn Equipment 376Chapter 11: Spreadsheet Modeling and Analysis377Leaming Objectives 377Analytics in Practice: Using Spreadsheet Modeling and Analysis at Nestle 379Model-Building Strategies 379Building Models Using Logic and Business Principles 379 Building Models UsingInfluence Diagrams 380 Building Models Using Historical Data 381 ModelAssumptions, Complexity, and Realism 382Implementing Models on Spreadsheets 382Spreadsheet Design 383 Spreadsheet Quality 384 Data Validation 386Analytics in Practice: Spreadsheet Engineering at Procter & Gamble 388Descriptive Spreadsheet Models 388Staffing Decisions 389 Single-Period Purchase Decisions 390 OverbookingDecisions 392Analytics in Practice: Using an Overbooking Model at a Student Health Clinic 393Retail Markdown Decisions 393Predictive Spreadsheet Models 395New Product Development Model 395 Cash Budgeting 397 RetirementFlanning 398 Project Management 398Prescriptive Spreadsheet Models 401Portfolio Allocaüon 401 Locating Central Facilities 402 Job Sequencing 404Analyzing Uncertainty and Model Assumptions 406What-If Analysis 406 Data Tables 406 Scenario Manager 409 GoalSeck 410Key Terms 412 Chapter 11 Technology Help 413 Problems and Exercises 414 Case: Performance Lawn Equipment 421
ContentsChapter 12: Simulation and Risk Analysisxiii423Learning Objectives 423Monte Carlo Simulation 425Random Sampling from Probability Distribution« 427Generating Random Variates using Excel Functions 429Discrete Probability Distribution« 429 Uniform Distribution« 430 ExponentialDistribution« 431 Normal Distribution« 431 Binomial Distribution« 433 Triangulär Distribution« 433Monte Carlo Simulation in Excel 435Profit Model Simulation 435 New Product Development 438 RetirementFlanning 440 Single-Period Purchase Decisions 441 OverbookingDecisions 444 Project Management 445Analytics in Practice: Implementing Large-Scale Monte Carlo SpreadsheetModels 446Dynamic Systems Simulation 447Simulating Waiting Lines 449Analytics in Practice: Using Systems Simulation for Agricultural ProductDevelopment 452Key Terms 453 Chapter 12 Technology Help 453 Problems and Exercises 453 Case: Performance Lawn Equipment 463Part 4: Prescriptive AnalyticsChapter 13: Linear Optimization465Learning Objectives 465Optimization Models 466Analytics in Practice: Linear Optimization in Bank Financial Planning 468Analytics in Practice: Using Optimization Models for Sales Planning at NBC 468Developing Linear Optimization Models 469Identifying Decision Variables, the Objective, and Constraints 470 Developing aMathematical Model 471 More About Constraints 472 Implementing LinearOptimization Models on Spreadsheets 474 Excel Functions to Avoid in LinearOptimization 475Solving Linear Optimization Models 476Solver Answer Report 478 Graphical Interpretation of Linear Optimization withTwo Variables 479How Solver Works 485How Solver Creates Name« in Reports 486Solver Outcomes and Solution Messages 487Unique Optimal Solution 487 Alternative (Multiple) Optimal Solutions 487 Unbounded Solution 487 Infeasibility 489Applications of Linear Optimization 491Blending Models 491 Dealing with Infeasibility 492 Portfolio InvestmentModels 493 Scaling Issues in Using Solver 495 TransportationModels 498 Multiperiod Production Planning Models 501 MultiperiodFinancial Planning Models 505
Analytics in Practice: Linear Optimization in Bank Financial Planning 508Key Terms 509 Chapter 13 Technology Help 29 Problems and Exencises 510 Case: Performance Lawn Equipment 522Chapter 14: Integer and Nonlinear Optimization523Learning Objectives 523Integer Linear Optimization Models 524Models with General Integer Variables 525 Workforce-Scheduling Models 528 Alternative Optimal Solutions 531Models with Binary Variables 533Using Binary Variables to Model Logical Constraints 534 Applications in SupplyChain Optimization 535Analytics in Practice: Supply Chain Optimization at Procter & Gamble 539Nonlinear Optimization Models 539A Nonlinear Pacing Decision Model 539 Quadratic Optimization 543 Practica!Issues Using Solver for Nonlinear Optimization 544Analytics in Practice: Applying Nonlinear Optimization at Prudential Securities 545Non-Smooth Optimization 546Evolutionary Solver 546 Evolutionary Solver for Sequencing and SchedulingModels 549 The Traveling Salesperson Problem 551Key Terms 553 Chapter 14 Technology Help 553 Problems and Exercises 554 'Case: Performance Lawn Equipment 563Chapter 15: Optimization Analytics565Learning Objectives 565What-If Analysis for Optimization Models 566Solver Sensitivity Report 567 Using the Sensitivity Report 572 Degeneracy 573 Interpreting Solver Reports for Nonlinear OptimizationModels 573Models with Bounded Variables 575Auxiliary Variables for Bound Constraints 578What-If Analysis for Integer Optimization Models 581Visualization of Solver Reports 583Using Sensitivity Information Correctly 590Key Terms 594 Chapter 15 Technology Help 594 Problems and Exercises 594 Case: Performance Lawn Equipment 601Part 5: Making DecisionsChapter 16: Decision Analysis603Learning Objectives 603Formulating Decision Problems 605Decision Strategies without Outcome Probabilities 606Decision Strategies for a Minimize Objective 606 Decision Strategies for aMaximize Objective 608 Decisions with Conflicting Objectives 608Decision Strategies with Outcome Probabilities 610Average Payoff Strategy 610 Expected Value Strategy 610 EvaluatingRisk 611
ContentsXVDecision Trees 612Decision Trees and Risk 614 Sensitivity Analysis in Decision Trees 617The Value of Information 618Decisions with Sample Information 619Bayes's Rule 620Utility and Decision Making 621Constructing a Utility Function 622 Exponential Utility Functions 625Analytics in Practice: Using Decision Analysis in Drug Development 626Key Terms 627 Chapter 16 Technology Help 627 Problems and Exercises 628 Gase: Performance Lawn Equipment 632Online Supplements: Information about how to access and use Analytic Solver Basicare available for download at www.pearsonhighered.com/evans.Gelting Started with Analytic SolverUsing Advanced Regression Techniques in Analytic SolverUsing Forecasting Techniques in Analytic SolverUsing Data Mining in Analytic SolverModel Analysis in Analytic SolverUsing Monte Carlo Simulation in Analytic SolverUsing Linear Optimization in Analytic SolverUsing Integer and Nonlinear Optimization in Analytic SolverUsing Optimization Parameter Analysis in Analytic SolverUsing Decision Trees in Analytic SolverAppendix A 633Glossary 657Index 665
Evolution of Business Analytics 6 Analytic Foundations 6 Modern Business Analytics 7 Software Support and Spreadsheet Technology 9 Analytics in Practice: Social Media Analytics 10 Descriptive, Predictive, and Prescriptive Analytics 11 Analytics in Practice: Analytics in the Home Lending and Mortgage Industry 13 Data for Business .
