Regression Statistics & Forecasting Method Excel Questions

Based on Evans (2020) Chapters 8, 9, 13, and 16 end-of-chapter exercises:

Chapter 8 #28;

Chapter 9 # 8;

Chapter 13 #5, 12;

Chapter 16# 3, 10.

INSTRUCTION:

Complete the Analysis using Excel.

28. The Excel file Salary Data provides information on current salary, beginning salary, previous experience (in months) when hired, and total years of education for a sample of 100 employees in a firm. a. Find the multiple regression model for predicting current salary as a function of the other variables. b. Find the best model for predicting current salary using the t-value criterion.

8. The Excel file Closing Stock Prices provides data for four stocks and the Dow Jones Industrial Average over a one-month period. a. Develop a spreadsheet for forecasting each of the stock prices and the DJIA using a simple twoperiod and three-period moving average. b. Compute MAD, MSE, and MAPE and determine whether two or three moving average periods is better.

5) A brand manager for ColPal Products must determine how much time to allocate between radio and television advertising during the next month. Market research has provided estimates of the audience exposure for each minute of advertising in each medium, which it would like to maximize. Costs per minute of advertising are also known, and the manager has a limited budget of $25,000. The manager has decided that because television ads have been found to be much more effective than radio ads, at least 75% of the time should be allocated to television. Suppose that we have the following data: Type of Ad Exposure/Minute Cost/Minute Radio 350 $400 TV 800 $2,000 a. Identify the decision variables, objective function, and constraints in simple verbal expressions. b. Mathematically formulate a linear optimization model.

12. Implement the linear optimization model that you developed for ColPal Products in Problem 5 on a spreadsheet and use Solver to find an optimal solution. Interpret the Solver Answer Report, identify the binding constraints, and verify the values of the slack variables by substituting the optimal solution into the model constraints.

*3. The DoorCo Corporation is a leading manufacturer of garage doors. All doors are manufactured in their plant in Carmel, Indiana, and shipped to distribution centers or major customers. DoorCo recently acquired another manufacturer of garage doors, Wisconsin Door, and is considering moving its wood door operations to the Wisconsin plant. Key considerations in this decision are the transportation, labor, and production costs at the two plants. Complicating matters is the fact that marketing is predicting a decline in the demand for wood doors. The company developed three scenarios: a. Demand falls slightly, with no noticeable effect on production. b. Demand and production decline 20%. c. Demand and production decline 40%. The following table shows the total costs under each decision and scenario. Slight Decline 20% Decline 40% Decline Stay in Carmel $1,000,000 $800,000 $840,000 Move to Wisconsin $1,100,000 $950,000 $750,000 What decision should DoorCo make using each of the following strategies? a. aggressive strategy b. conservative strategy c. opportunity-loss strategy.

10. For the DoorCo Corporation decision in Problem 3, suppose that the probabilities of the three scenarios are estimated to be 0.15, 0.40, and 0.45, respectively. Find the best expected value decision. Business Analytics
This page intentionally left blank
Business Analytics
Methods, Models, and Decisions
James R. Evans
University of Cincinnati
THIRD EDITION
GLOBAL EDITION
Harlow, England • London • New York • Boston • San Francisco • Toronto • Sydney • Dubai • Singapore • Hong Kong
Tokyo • Seoul • Taipei • New Delhi • Cape Town • São Paulo • Mexico City • Madrid • Amsterdam • Munich • Paris • Milan
Please contact https://support.pearson.com/getsupport/s/contactsupport with any queries on this content
Cover image by Allies Interactive / Shutterstock.
Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and related
graphics published as part of the services for any purpose. All such documents and related graphics are provided “as is” without warranty of any
kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information, including all warranties
and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and non-infringement. In no event shall
Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any damages whatsoever resulting from loss of
use, data or profits, whether in an action of contract, negligence or other tortious action, arising out of or in connection with the use or performance of
information available from the services.
The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically added
to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the program(s)
described herein at any time. Partial screen shots may be viewed in full within the software version specified.
Microsoft® and Windows® are registered trademarks of the Microsoft Corporation in the United States and other countries. This book is not sponsored
or endorsed by or affiliated with the Microsoft Corporation.
Pearson Education Limited
KAO Two
KAO Park
Hockham Way
Harlow
Essex
CM17 9SR
United Kingdom
and Associated Companies throughout the world
Visit us on the World Wide Web at: www.pearsonglobaleditions.com
© Pearson Education Limited 2021
The rights of James R. Evans, to be identified as the author of this work, has been asserted by him in accordance with the Copyright, Designs and
Patents Act 1988.
Authorized adaptation from the United States edition, entitled Business Analytics, 3rd Edition, ISBN 978-0-13-523167-8 by James R. Evans,
published by Pearson Education © 2020.
PEARSON, ALWAYS LEARNING, and MYLAB are exclusive trademarks owned by Pearson Education, Inc. or its affiliates in the U.S.
and/or other countries.
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic,
mechanical, photocopying, recording or otherwise, without either the prior written permission of the publisher or a license permitting restricted copying
in the United Kingdom issued by the Copyright Licensing Agency Ltd, Saffron House, 6–10 Kirby Street, London EC1N 8TS.
All trademarks used herein are the property of their respective owners. The use of any trademark in this text does not vest in the author or publisher
any trademark ownership rights in such trademarks, nor does the use of such trademarks imply any affiliation with or endorsement of this book by
such owners. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights and
Permissions department, please visit www.pearsoned.com/permissions/.
This eBook is a standalone product and may or may not include all assets that were part of the print version. It also does not provide access to other
Pearson digital products like MyLab and Mastering. The publisher reserves the right to remove any material in this eBook at any time.
Print ISBN 10: 1-292-33906-3
Print ISBN 13: 978-1-292-33906-1
eBook ISBN 13: 978-1-292-33904-7
British Library Cataloguing-in-Publication Data
A catalogue record for this book is available from the British Library
1 20
Typeset in Times NR MT Pro by SPi Global
Brief Contents
Preface 17
About the Author
Credits 27
25
Part 1 Foundations of Business Analytics
Chapter 1
Chapter 2
Introduction to Business Analytics
Database Analytics 75
29
Part 2 Descriptive Analytics
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Data Visualization 113
Descriptive Statistics 143
Probability Distributions and Data Modeling
Sampling and Estimation 247
Statistical Inference 275
201
Part 3 Predictive Analytics
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Trendlines and Regression Analysis 311
Forecasting Techniques 353
Introduction to Data Mining 383
Spreadsheet Modeling and Analysis 405
Simulation and Risk Analysis 451
Part 4 Prescriptive Analytics
Chapter 13 Linear Optimization 493
Chapter 14 Integer and Nonlinear Optimization
Chapter 15 Optimization Analytics 593
551
Part 5 Making Decisions
Chapter 16 Decision Analysis
631
Appendix A 661
Glossary 685
Index 693
5
This page intentionally left blank
Contents
Preface 17
About the Author
Credits 27
25
Part 1: Foundations of Business Analytics
Chapter 1: Introduction to Business Analytics 29
Learning Objectives 29
What Is Business Analytics?
31
Using Business Analytics
32
• Impacts and Challenges
Evolution of Business Analytics
33
34
Analytic Foundations 34 • Modern Business Analytics
and Spreadsheet Technology 37
35
• Software Support
Analytics in Practice: Social Media Analytics 38
Descriptive, Predictive, and Prescriptive Analytics 39
Analytics in Practice: Analytics in the Home Lending and Mortgage Industry
Data for Business Analytics 42
Big Data 44
• Data Reliability and Validity
Models in Business Analytics
41
44
45
Descriptive Models 47 • Predictive Models 49 • Prescriptive Models 50
Model Assumptions 51 • Uncertainty and Risk 53
Problem Solving with Analytics
•
54
Recognizing a Problem 54 • Defining the Problem 54 • Structuring the
Problem 55 • Analyzing the Problem 55 • Interpreting Results and Making a
Decision 55 • Implementing the Solution 55
Analytics in Practice: Developing Effective Analytical Tools at Hewlett-Packard
56
Key Terms 57 • Chapter 1 Technology Help 57
Case: Performance Lawn Equipment 59
•
Appendix A1: Basic Excel Skills
61
Excel Formulas and Addressing
62
• Problems and Exercises
57
Copying Formulas 63
Useful Excel Tips 63
Excel Functions 64
Basic Excel Functions 64 • Functions for Specific Applications 65
Function 66 • Date and Time Functions 67
Miscellaneous Excel Functions and Tools
• Insert
68
Range Names 68 • VALUE Function 71 • Paste
Special 71 • Concatenation 72 • Error Values 72
Problems and Exercises 73
7
8
Contents
Chapter 2: Database Analytics 75
Learning Objectives 75
Data Sets and Databases 77
Using Range Names in Databases 78
Analytics in Practice: Using Big Data to Monitor Water Usage in Cary,
North Carolina 79
Data Queries: Tables, Sorting, and Filtering 79
Sorting Data in Excel 80
Database Functions 84
• Pareto Analysis
81
• Filtering Data
82
•
Analytics in Practice: Discovering the Value of Database Analytics at Allders
International 86
Logical Functions 87
Lookup Functions for Database Queries 89
Excel Template Design 92
Data Validation Tools 93
• Form Controls
95
• Slicers
103
PivotTables 98
PivotTable Customization 100
Key Terms 104 • Chapter 2 Technology Help 104 • Problems and Exercises 105
Case: People’s Choice Bank 109 • Case: Drout Advertising Research Project 110
•
Part 2: Descriptive Analytics
Chapter 3: Data Visualization 113
Learning Objectives 113
The Value of Data Visualization
114
Tools and Software for Data Visualization 116
Analytics in Practice: Data Visualization for the New York City Police Department’s
Domain Awareness System 116
Creating Charts in Microsoft Excel 116
Column and Bar Charts 117 • Data Label and Data Table Chart Options 118 •
Line Charts 119 • Pie Charts 120 • Area Charts 121 • Scatter Charts
and Orbit Charts 122 • Bubble Charts 123 • Combination Charts 124 •
Radar Charts 125 • Stock Charts 125 • Charts from PivotTables 125 •
Geographic Data 126
Other Excel Data Visualization Tools
Data Bars 126
• Color Scales
126
127
• Icon Sets
128
• Sparklines
129
Dashboards 131
Analytics in Practice: Driving Business Transformation with IBM Business
Analytics 132
Key Terms 133 • Chapter 3 Technology Help 133
Case: Performance Lawn Equipment 135
Appendix A3: Additional Tools for Data Visualization
Hierarchy Charts 136
Waterfall Charts 136
PivotCharts 138
Tableau 139
Problems and Exercises 141
• Problems and Exercises
136
134
•
9
Contents
Chapter 4: Descriptive Statistics 143
Learning Objectives 143
Analytics in Practice: Applications of Statistics in Health Care
Metrics and Data Classification 146
Frequency Distributions and Histograms 148
145
Frequency Distributions for Categorical Data 148 • Relative Frequency
Distributions 149 • Frequency Distributions for Numerical Data 150 • Grouped
Frequency Distributions 151 • Cumulative Relative Frequency Distributions 154 •
Constructing Frequency Distributions Using PivotTables 155
Percentiles and Quartiles 157
Cross-Tabulations 158
Descriptive Statistical Measures
160
Populations and Samples 160 • Statistical Notation 161 • Measures of
Location: Mean, Median, Mode, and Midrange 161 • Using Measures of Location
in Business Decisions 163 • Measures of Dispersion: Range, Interquartile
Range, Variance, and Standard Deviation 165 • Chebyshev’s Theorem and the
Empirical Rules 168 • Standardized Values (Z-Scores) 170 • Coefficient of
Variation 171 • Measures of Shape 172 • Excel Descriptive Statistics Tool 174
Computing Descriptive Statistics for Frequency Distributions 175
Descriptive Statistics for Categorical Data: The Proportion 177
Statistics in PivotTables 178
Measures of Association 179
Covariance 180 • Correlation 181 • Excel Correlation Tool 183
Outliers 184
Using Descriptive Statistics to Analyze Survey Data 186
Statistical Thinking in Business Decisions 187
Variability in Samples 188
Analytics in Practice: Applying Statistical Thinking to Detecting Financial Problems 190
Key Terms 191 • Chapter 4 Technology Help 192 • Problems and Exercises 193 •
Case: Drout Advertising Research Project 198 • Case: Performance Lawn Equipment 198
Appendix A4: Additional Charts for Descriptive Statistics in Excel for Windows
199
Problems and Exercises 200
Chapter 5: Probability Distributions and Data Modeling 201
Learning Objectives 201
Basic Concepts of Probability
203
Experiments and Sample Spaces 203 • Combinations and Permutations 203 •
Probability Definitions 205 • Probability Rules and Formulas 207 • Joint and
Marginal Probability 208 • Conditional Probability 210
Random Variables and Probability Distributions
Discrete Probability Distributions 215
213
Expected Value of a Discrete Random Variable 216 • Using Expected Value in
Making Decisions 217 • Variance of a Discrete Random Variable 219 • Bernoulli
Distribution 219 • Binomial Distribution 220 • Poisson Distribution 221
Analytics in Practice: Using the Poisson Distribution for Modeling Bids on
Priceline 223
10
Contents
Continuous Probability Distributions
224
Properties of Probability Density Functions 224 • Uniform Distribution 225 •
Normal Distribution 227 • The NORM.INV Function 228 • Standard Normal
Distribution 229 • Using Standard Normal Distribution Tables 230 • Exponential
Distribution 231 • Triangular Distribution 232
Data Modeling and Distribution Fitting
233
Goodness of Fit: Testing for Normality of an Empirical Distribution 235
Analytics in Practice: The Value of Good Data Modeling in Advertising
237
Key Terms 238 • Chapter 5 Technology Help 238 • Problems and Exercises 239 •
Case: Performance Lawn Equipment 245
Chapter 6: Sampling and Estimation 247
Learning Objectives 247
Statistical Sampling 248
Sampling Methods
249
Analytics in Practice: Using Sampling Techniques to Improve Distribution
Estimating Population Parameters 252
251
Unbiased Estimators 252 • Errors in Point Estimation 253 • Understanding
Sampling Error 254
Sampling Distributions
256
Sampling Distribution of the Mean 256 • Applying the Sampling Distribution of the
Mean 257
Interval Estimates
257
Confidence Intervals 258 • Confidence Interval for the Mean with Known
Population Standard Deviation 259 • The t-Distribution 260 • Confidence
Interval for the Mean with Unknown Population Standard Deviation 261 •
Confidence Interval for a Proportion 261 • Additional Types of Confidence
Intervals 263
Using Confidence Intervals for Decision Making
263
Data Visualization for Confidence Interval Comparison 264
Prediction Intervals 265
Confidence Intervals and Sample Size
266
Key Terms 268 • Chapter 6 Technology Help 268 • Problems and Exercises 269 •
Case: Drout Advertising Research Project 272 • Case: Performance Lawn Equipment 273
Chapter 7: Statistical Inference 275
Learning Objectives 275
Hypothesis Testing 276
Hypothesis-Testing Procedure 276
One-Sample Hypothesis Tests
277
Two-Sample Hypothesis Tests
287
Understanding Potential Errors in Hypothesis Testing 278 • Selecting the Test
Statistic 279 • Finding Critical Values and Drawing a Conclusion 280 • TwoTailed Test of Hypothesis for the Mean 282 • Summary of One-Sample
Hypothesis Tests for the Mean 283 • p-Values 284 • One-Sample Tests for
Proportions 285 • Confidence Intervals and Hypothesis Tests 286 • An Excel
Template for One-Sample Hypothesis Tests 286
11
Contents
Two-Sample Tests for Differences in Means 288 • Two-Sample Test for Means with
Paired Samples 290 • Two-Sample Test for Equality of Variances 292
Analysis of Variance (ANOVA)
Assumptions of ANOVA
294
296
Chi-Square Test for Independence
297
Cautions in Using the Chi-Square Test 299 • Chi-Square Goodness of Fit Test 300
Analytics in Practice: Using Hypothesis Tests and Business Analytics in a Help Desk
Service Improvement Project 301
Key Terms 302 • Chapter 7 Technology Help 302 • Problems and Exercises 304 •
Case: Drout Advertising Research Project 309 • Case: Performance Lawn Equipment 309
Part 3: Predictive Analytics
Chapter 8: Trendlines and Regression Analysis 311
Learning Objectives 311
Modeling Relationships and Trends in Data 313
Analytics in Practice: Using Predictive Trendline Models at Procter & Gamble
Simple Linear Regression 317
317
Finding the Best-Fitting Regression Line 319 • Using Regression Models for
Prediction 319 • Least-Squares Regression 320 • Simple Linear Regression with
Excel 322 • Regression as Analysis of Variance 324 • Testing Hypotheses for
Regression Coefficients 325 • Confidence Intervals for Regression Coefficients 325
Residual Analysis and Regression Assumptions
326
Checking Assumptions 327
Multiple Linear Regression 329
Analytics in Practice: Using Linear Regression and Interactive Risk Simulators to
Predict Performance at Aramark 332
Building Good Regression Models 334
Correlation and Multicollinearity 336 • Practical Issues in Trendline and Regression
Modeling 338
Regression with Categorical Independent Variables
338
Categorical Variables with More Than Two Levels 341
Regression Models with Nonlinear Terms
343
Key Terms 345 • Chapter 8 Technology Help 345 • Problems and Exercises
Case: Performance Lawn Equipment 350
346 •
Chapter 9: Forecasting Techniques 353
Learning Objectives 353
Analytics in Practice: Forecasting Call-Center Demand at L.L.Bean
Qualitative and Judgmental Forecasting 355
354
Historical Analogy 327 • The Delphi Method 355 • Indicators and Indexes 356
Statistical Forecasting Models 357
Forecasting Models for Stationary Time Series
359
Moving Average Models 359 • Error Metrics and Forecast Accuracy
Exponential Smoothing Models 363
Forecasting Models for Time Series with a Linear Trend
366
361 •
Double Exponential Smoothing 366 • Regression-Based Forecasting for Time Series
with a Linear Trend 368
12
Contents
Forecasting Time Series with Seasonality
369
Regression-Based Seasonal Forecasting Models 369 • Holt-Winters Models for
Forecasting Time Series with Seasonality and No Trend 371 • Holt-Winters Models
for Forecasting Time Series with Seasonality and Trend 373 • Selecting Appropriate
Time-Series-Based Forecasting Models 376
Regression Forecasting with Causal Variables 376
The Practice of Forecasting 377
Analytics in Practice: Forecasting at NBCUniversal
378
Key Terms 379 • Chapter 9 Technology Help 380 • Problems and Exercises 380 •
Case: Performance Lawn Equipment 382
Chapter 10: Introduction to Data Mining 383
Learning Objectives 383
The Scope of Data Mining
Cluster Analysis 386
384
Measuring Distance Between Objects 387 • Normalizing Distance
Measures 388 • Clustering Methods 388
Classification
390
An Intuitive Explanation of Classification 391 • Measuring Classification
Performance 392 • Classification Techniques 393
Association
398
Cause-and-Effect Modeling 400
Analytics In Practice: Successful Business Applications of Data Mining
Key Terms 402 • Chapter 10 Technology Help 403
Case: Performance Lawn Equipment 404
402
• Problems and Exercises
403 •
Chapter 11: Spreadsheet Modeling and Analysis 405
Learning Objectives 405
Analytics in Practice: Using Spreadsheet Modeling and Analysis at Nestlé
Model-Building Strategies 407
407
Building Models Using Logic and Business Principles 407 • Building Models Using
Influence Diagrams 408 • Building Models Using Historical Data 409 • Model
Assumptions, Complexity, and Realism 410
Implementing Models on Spreadsheets
410
Spreadsheet Design 411 • Spreadsheet Quality 412 • Data Validation 414
Analytics in Practice: Spreadsheet Engineering at Procter & Gamble
Descriptive Spreadsheet Models 416
Staffing Decisions
Decisions 420
416
417 • Single-Period Purchase Decisions 418 • Overbooking
Analytics in Practice: Using an Overbooking Model at a Student Health Clinic
Retail Markdown Decisions
421
Predictive Spreadsheet Models
423
New Product Development Model 423 • Cash Budgeting
Planning 426 • Project Management 426
Prescriptive Spreadsheet Models
429
421
425 • Retirement
Portfolio Allocation 429 • Locating Central Facilities 430 • Job Sequencing 432
13
Contents
Analyzing Uncertainty and Model Assumptions
434
What-If Analysis 434 • Data Tables 434 • Scenario Manager
Seek 438
437 • Goal
Key Terms 440 • Chapter 11 Technology Help 441 • Problems and Exercises 442 •
Case: Performance Lawn Equipment 449
Chapter 12: Simulation and Risk Analysis 451
Learning Objectives 451
Monte Carlo Simulation 453
Random Sampling from Probability Distributions 455
Generating Random Variates using Excel Functions 457
Discrete Probability Distributions 457 • Uniform Distributions 458 • Exponential
Distributions 459 • Normal Distributions 459 • Binomial Distributions 461 •
Triangular Distributions 461
Monte Carlo Simulation in Excel
463
Profit Model Simulation 463 • New Product Development 466 • Retirement
Planning 468 • Single-Period Purchase Decisions 469 • Overbooking
Decisions 472 • Project Management 472
Analytics in Practice: Implementing Large-Scale Monte Carlo Spreadsheet
Models 474
Dynamic Systems Simulation 475
Simulating Waiting Lines 477
Analytics in Practice: Using Systems Simulation for Agricultural Product
Development 480
Key Terms 481 • Chapter 12 Technology Help 481 • Problems and Exercises 481 •
Case: Performance Lawn Equipment 491
Part 4: Prescriptive Analytics
Chapter 13: Linear Optimization 493
Learning Objectives 493
Optimization Models 494
Analytics in Practice: Using Optimization Models for Sales Planning at NBC
Developing Linear Optimization Models 497
496
Identifying Decision Variables, the Objective, and Constraints 498 • Developing a
Mathematical Model 499 • More About Constraints 500 • Implementing Linear
Optimization Models on Spreadsheets 502 • Excel Functions to Avoid in Linear
Optimization 503
Solving Linear Optimization Models
504
Solver Answer Report 506 • Graphical Interpretation of Linear Optimization with
Two Variables 507
How Solver Works
513
How Solver Creates Names in Reports 514
Solver Outcomes and Solution Messages
515
Unique Optimal Solution 515 • Alternative (Multiple) Optimal Solutions 515 •
Unbounded Solution 515 • Infeasibility 517
14
Contents
Applications of Linear Optimization
519
Blending Models 519 • Dealing with Infeasibility 520 • Portfolio Investment
Models 521 • Scaling Issues in Using Solver 523 • Transportation
Models 526 • Multiperiod Production Planning Models 529 • Multiperiod
Financial Planning Models 533
Analytics in Practice: Linear Optimization in Bank Financial Planning
536
Key Terms 537 • Chapter 13 Technology Help 537 • Problems and Exercises 538 •
Case: Performance Lawn Equipment 550
Chapter 14: Integer and Nonlinear Optimization 551
Learning Objectives 551
Integer Linear Optimization Models
552
Models with General Integer Variables 553 • Workforce-Scheduling Models 556 •
Alternative Optimal Solutions 559
Models with Binary Variables
561
Using Binary Variables to Model Logical Constraints 562 • Applications in Supply
Chain Optimization 563
Analytics in Practice: Supply Chain Optimization at Procter & Gamble
Nonlinear Optimization Models 567
567
A Nonlinear Pricing Decision Model 567 • Quadratic Optimization 571
Issues Using Solver for Nonlinear Optimization 572
• Practical
Analytics in Practice: Applying Nonlinear Optimization at Prudential Securities
Non-Smooth Optimization 574
573
Evolutionary Solver 574 • Evolutionary Solver for Sequencing and Scheduling
Models 577 • The Traveling Salesperson Problem 579
Key Terms 581 • Chapter 14 Technology Help 581 • Problems and Exercises 582 •
Case: Performance Lawn Equipment 591
Chapter 15: Optimization Analytics 593
Learning Objectives 593
What-If Analysis for Optimization Models
594
Solver Sensitivity Report 595 • Using the Sensitivity Report 600 •
Degeneracy 601 • Interpreting Solver Reports for Nonlinear Optimization
Models 601
Models with Bounded Variables
603
Auxiliary Variables for Bound Constraints
606
What-If Analysis for Integer Optimization Models
Visualization of Solver Reports 611
Using Sensitivity Information Correctly 618
609
Key Terms 622 • Chapter 15 Technology Help 622 • Problems and Exercises 622 •
Case: Performance Lawn Equipment 629
Part 5: Making Decisions
Chapter 16: Decision Analysis 631
Learning Objectives 631
Formulating Decision Problems 633
Decision Strategies Without Outcome Probabilities
634
Contents
15
Decision Strategies for a Minimize Objective 634 • Decision Strategies for a
Maximize Objective 636 • Decisions with Conflicting Objectives 636
Decision Strategies with Outcome Probabilities
638
Average Payoff Strategy 638 • Expected Value Strategy
Risk 639
Decision Trees
638 • Evaluating
640
Decision Trees and Risk 642 • Sensitivity Analysis in Decision Trees 645
The Value of Information 646
Decisions with Sample Information
Utility and Decision Making 649
647 • Bayes’s Rule 648
Constructing a Utility Function 650 • Exponential Utility Functions 653
Analytics in Practice: Using Decision Analysis in Drug Development 654
Key Terms 655 • Chapter 16 Technology Help 655 • Problems and Exercises 656 •
Case: Performance Lawn Equipment 660
Online Supplements: Information about how to access and use Analytic Solver Basic
are available for download at www.pearsonglobaleditions.com.
Getting Started with Analytic Solver
Using Advanced Regression Techniques in Analytic Solver
Using Forecasting Techniques in Analytic Solver
Using Data Mining in Analytic Solver
Model Analysis in Analytic Solver
Using Monte Carlo Simulation in Analytic Solver
Using Linear Optimization in Analytic Solver
Using Integer and Nonlinear Optimization in Analytic Solver
Using Optimization Parameter Analysis in Analytic Solver
Using Decision Trees in Analytic Solver
Appendix A 661
Glossary 685
Index 693
This page intentionally left blank
Preface
In 2007, Thomas H. Davenport and Jeanne G. Harris wrote a groundbreaking book, Competing on Analytics: The New Science of Winning (Boston: Harvard Business School
Press). They described how many organizations are using analytics strategically to make
better decisions and improve customer and shareholder value. Over the past several years,
we have seen remarkable growth in analytics among all types of organizations. The Institute for Operations Research and the Management Sciences (INFORMS) noted that analytics software as a service is predicted to grow at three times the rate of other business
segments in upcoming years.1 In addition, the MIT Sloan Management Review in collaboration with the IBM Institute for Business Value surveyed a global sample of nearly
3,000 executives, managers, and analysts.2 This study concluded that top-performing
organizations use analytics five times more than lower performers, that improvement of
information and analytics was a top priority in these organizations, and that many organizations felt they were under significant pressure to adopt advanced information and
analytics approaches. Since these reports were published, the interest in and the use of
analytics has grown dramatically.
In reality, business analytics has been around for more than a half-century. Business
schools have long taught many of the core topics in business analytics—statistics, data
analysis, information and decision support systems, and management science. However,
these topics have traditionally been presented in separate and independent courses and
supported by textbooks with little topical integration. This book is uniquely designed to
present the emerging discipline of business analytics in a unified fashion consistent with
the contemporary definition of the field.
About the Book
This book provides undergraduate business students and introductory graduate students
with the fundamental concepts and tools needed to understand the role of modern business
analytics in organizations, to apply basic business analytics tools in a spreadsheet environment, and to communicate with analytics professionals to effectively use and interpret
analytic models and results for making better business decisions. We take a balanced,
holistic approach in viewing business analytics from descriptive, predictive, and prescriptive perspectives that define the discipline.
1Anne Robinson, Jack Levis, and Gary Bennett, INFORMS News: INFORMS to Officially Join Analytics Movement. http://www.informs.org/ORMS-Today/Public-Articles/October-Volume-37-Number-5/
INFORMS-News-INFORMS-to-Officially-Join-Analytics-Movement.
2“Analytics: The New Path to Value,” MIT Sloan Management Review Research Report, Fall 2010.
17
18
Preface
This book is organized in five parts.
1. Foundations of Business Analytics
The first two chapters provide the basic foundations needed to understand business
analytics and to manipulate data using Microsoft Excel. Chapter 1 provides an introduction to business analytics and its key concepts and terminology, and includes an
appendix that reviews basic Excel skills. Chapter 2, Database Analytics, is a unique
chapter that covers intermediate Excel skills, Excel template design, and PivotTables.
2. Descriptive Analytics
Chapters 3 through 7 cover fundamental tools and methods of data analysis and
statistics. These chapters focus on data visualization, descriptive statistical measures, probability distributions and data modeling, sampling and estimation, and
statistical inference. We subscribe to the American Statistical Association’s recommendations for teaching introductory statistics, which include emphasizing
statistical literacy and developing statistical thinking, stressing conceptual understanding rather than mere knowledge of procedures, and using technology for
developing conceptual understanding and analyzing data. We believe these goals
can be accomplished without introducing every conceivable technique into an
800–1,000 page book as many mainstream books currently do. In fact, we cover
all essential content that the state of Ohio has mandated for undergraduate business statistics across all public colleges and universities.
3. Predictive Analytics
In this section, Chapters 8 through 12 develop approaches for applying trendlines
and regression analysis, forecasting, introductory data mining techniques, building and analyzing models on spreadsheets, and simulation and risk analysis.
4. Prescriptive Analytics
Chapters 13 and 14 explore linear, integer, and nonlinear optimization models
and applications. Chapter 15, Optimization Analytics, focuses on what-if and sensitivity analysis in optimization, and visualization of Solver reports.
5. Making Decisions
Chapter 16 focuses on philosophies, tools, and techniques of decision analysis.
Changes to the Third Edition
The third edition represents a comprehensive revision that includes many significant
changes. The book now relies only on native Excel, and is independent of platforms,
allowing it to be used easily by students with either PC or Mac computers. These changes
provide students with enhanced Excel skills and basic understanding of fundamental concepts. Analytic Solver is no longer integrated directly in the book, but is illustrated in
online supplements to facilitate revision as new software updates may occur. These supplements plus information regarding how to access Analytic Solver may be accessed at
http://www.pearsonglobaleditions.com.
Key changes to this edition are as follows:
■■
Also available for purchase (separately) is MyLab Statistics, a teaching and learning platform that empowers you to reach every student. By combining trusted
author content with digital tools and a flexible platform, MyLab personalizes the
Preface
■■
■■
■■
■■
■■
■■
■■
■■
■■
■■
■■
■■
19
learning experience and improves results for each student. For example, new Excel
and StatCrunch Projects help students develop business decision-making skills.
Each chapter now includes a short section called Technology Help, which provides useful summaries of key Excel functions and procedures, and the use of
supplemental software including StatCrunch and Analytic Solver Basic.
Chapter 1 includes an Appendix reviewing basic Excel skills, which will be used
throughout the book.
Chapter 2, Database Analytics, is a new chapter derived from the second edition
that focuses on applications of Excel functions and techniques for dealing with
spreadsheet data, including a new section on Excel template design.
Chapter 3, Data Visualization, includes a new Appendix illustrating Excel tools
for Windows and a brief overview of Tableau.
Chapter 5, Probability Distributions and Data Modeling, includes a new section
on Combinations and Permutations.
Chapter 6, Sampling and Estimation, provides a discussion of using data visualization for confidence interval comparison.
Chapter 9, Forecasting Techniques, now includes Excel approaches for double
exponential smoothing and Holt-Winters models for seasonality and trend.
Chapter 10, Introduction to Data Mining, has been completely rewritten to illustrate simple data mining techniques that can be implemented on spreadsheets
using Excel.
Chapter 11, Spreadsheet Modeling and Analysis, is now organized along the analytic classification of descriptive, predictive, and prescriptive modeling.
Chapter 12 has been rewritten to apply Monte-Carlo simulation using only Excel,
with an additional section of systems simulation concepts and approaches.
Optimization topics have been reorganized into two chapters—Chapter 13, Linear Optimization, and Chapter 14, Integer and Nonlinear Optimization, which
rely only on the Excel-supplied Solver.
Chapter 15 is a new chapter called Optimization Analytics, which focuses
on what-if and sensitivity analysis, and visualization of Solver reports; it also
includes a discussion of how Solver handles models with bounded variables.
In addition, we have carefully checked, and revised as necessary, the text and
problems for additional clarity. We use major section headings in each chapter
and tie these clearly to the problems and exercises, which have been revised
and updated throughout the book. At the end of each section we added several
“Check Your Understanding” questions that provide a basic review of fundamental
concepts to improve student learning. Finally, new Analytics in Practice features
have been incorporated into several chapters.
Features of the Book
■■
■■
■■
■■
Chapter Section Headings—with “Check Your Understanding” questions that
provide a means to review fundamental concepts.
Numbered Examples—numerous, short examples throughout all chapters illustrate concepts and techniques and help students learn to apply the techniques and
understand the results.
“Analytics in Practice”—at least one per chapter, this feature describes real
applications in business.
Learning Objectives—lists the goals the students should be able to achieve after
studying the chapter.
20
Preface
■■
■■
■■
■■
Key Terms—bolded within the text and listed at the end of each chapter, these
words will assist students as they review the chapter and study for exams. Key
terms and their definitions are contained in the glossary at the end of the book.
End-of-Chapter Problems and Exercises—clearly tied to sections in each
chapter, these help to reinforce the material covered through the chapter.
Integrated Cases—allow students to think independently and apply the relevant
tools at a higher level of learning.
Data Sets and Excel Models—used in examples and problems and are available
to students at www.pearsonglobaleditions.com.
Software Support
Technology Help sections in each chapter provide additional support to students for using
Excel functions and tools, Tableau, and StatCrunch.
Online supplements provide detailed information and examples for using Analytic
Solver Basic, which provides more powerful tools for data mining, Monte-Carlo simulation, optimization, and decision analysis. These can be used at the instructor’s discretion,
but are not necessary to learn the fundamental concepts that are implemented using Excel.
Instructions for obtaining licenses for Analytic Solver Basic can be found on the book’s
website, http://www.pearsonglobaleditions.com.
To the Students
To get the most out of this book, you need to do much more than simply read it! Many
examples describe in detail how to use and apply various Excel tools or add-ins. We
highly recommend that you work through these examples on your computer to replicate
the outputs and results shown in the text. You should also compare mathematical formulas with spreadsheet formulas and work through basic numerical calculations by hand.
Only in this fashion will you learn how to use the tools and techniques effectively, gain a
better understanding of the underlying concepts of business analytics, and increase your
proficiency in using Microsoft Excel, which will serve you well in your future career.
Visit the companion Web site (www.pearsonglobaleditions.com) for access to the
following:
■■
■■
Online Files: Data Sets and Excel Models—files for use with the numbered
examples and the end-of-chapter problems. (For easy reference, the relevant file
names are italicized and clearly stated when used in examples.)
Online Supplements for Analytic Solver Basic: Online supplements describing
the use of Analytic Solver that your instructor might use with selected chapters.
To the Instructors
MyLab Statistics is now available with Evans “Business Analytics” 3e: MyLab™ Statistics is the teaching and learning platform that empowers instructors to reach every student.
Teach your course your way with a flexible platform. Collect, crunch, and communicate
with data in StatCrunch®, an integrated Web-based statistical software. Empower each
learner with personalized and interactive practice. Tailor your course to your students’
needs with enhanced reporting features. Available with the complete eText, accessible
anywhere with the Pearson eText app.
Preface
21
Instructor’s Resource Center—Reached through a link at www.pearsonglobaleditions.
com, the Instructor’s Resource Center contains the electronic files for the complete
Instructor’s Solutions Manual, PowerPoint lecture presentations, and the Test Item File.
■■
■■
■■
■■
■■
Register, redeem, log in at www.pearsonglobaleditions.com: instructors can
access a variety of print, media, and presentation resources that are available with
this book in downloadable digital format.
Instructor’s Solutions Manual—The Instructor’s Solutions Manual, updated and
revised for the second edition by the author, includes Excel-based solutions for
all end-of-chapter problems, exercises, and cases.
PowerPoint presentations—The PowerPoint slides, revised and updated by the
author, provide an instructor with individual lecture outlines to accompany the
text. The slides include nearly all of the figures, tables, and examples from the
text. Instructors can use these lecture notes as they are or can easily modify the
notes to reflect specific presentation needs.
Test Bank—The TestBank is prepared by Paolo Catasti from Virginia Commonwealth University.
Need help? Pearson Education’s dedicated technical support team is ready to
assist instructors with questions about the media supplements that accompany
this text. The supplements are available to adopting instructors. Detailed descriptions are provided at the Instructor’s Resource Center.
Acknowledgments
I would like to thank the staff at Pearson Education for their professionalism and dedication to making this book a reality. In particular, I want to thank Angela Montoya, Kathleen
Manley, Karen Wernholm, Kaylee Carlson, Jean Choe, Bob Carroll, and Patrick Barbera.
I would also like to thank Gowri Duraiswamy at SPI, and accuracy and solutions checker
Jennifer Blue for their outstanding contributions to producing this book. I also want to
acknowledge Daniel Fylstra and his staff at Frontline Systems for working closely with
me on providing Analytic Solver Basic as a supplement with this book. If you have any
suggestions or corrections, please contact the author via email at james.evans@uc.edu.
James R. Evans
Department of Operations, Business Analytics, and Information Systems
University of Cincinnati
Cincinnati, Ohio
Global Edition Acknowledgments
Pearson would like to thank Alicia Tan Yiing Fei, Taylor’s University Malaysia; Dániel
Kehl, University of Pécs; and Roland Baczur, University of Pécs for their contribution to
the Global Edition.
Get the Most Out of
MyLab Statistics
Statistics courses are continuously evolving to help today’s students succeed. It’s
more challenging than ever to support students with a wide range of backgrounds,
learner styles, and math anxieties. The flexibility to build a course that fits
instructors’ individual course formats—with a variety of content options and
multimedia resources all in one place—has made MyLab Statistics the marketleading solution for teaching and learning mathematics since its inception.
78% of students say MyLab Statistics helped
them learn their course content.*
Teach your course with a consistent author voice
With market-leading author content options, your course can fit your style.
Pearson offers the widest variety of content options, addressing a range of
approaches and learning styles, authored by thought leaders across the business
and math curriculum. MyLab™ Statistics is tightly integrated with each author’s style,
offering a range of author-created multimedia resources, so your students have a
consistent experience.
Thanks to feedback from instructors and students from more than 10,000 institutions,
MyLab Statistics continues to transform—delivering new content, innovative learning
resources, and platform updates to support students and instructors, today and in
the future.
*Source: 2018 Student Survey, n 31,721
pearson.com/mylab/statistics
Resources for Success
MyLab Statistics Online Course for Business Analytics
by James R. Evans
MyLab™ Statistics is available to accompany Pearson’s market leading text offerings.
To give students a consistent tone, voice, and teaching method each text’s flavor and
approach is tightly integrated throughout the accompanying MyLab Statistics course,
making learning the material as seamless as possible.
Enjoy hands off grading with
Excel Projects
Using proven, field-tested technology,
auto-graded Excel Projects let instructors
seamlessly integrate Microsoft Excel
content into the course without manually
grading spreadsheets. Students can
practice important statistical skills in Excel,
helping them master key concepts and gain
proficiency with the program.
StatCrunch
StatCrunch, Pearson’s powerful web-based
statistical software, instructors and students
can access tens of thousands of data sets
including those from the textbook, perform
complex analyses, and generate compelling
reports. StatCrunch is integrated directly
into MyLab Statistics or available as a
standalone product. To learn more, go to
www.statcrunch.com on any laptop, tablet, or
smartphone.
Technology Tutorials and
Study Cards
MyLab makes learning and using a variety
of statistical software programs as seamless
and intuitive as possible. Download data
sets from the text and MyLab exercises
directly into Excel. Students can also access
instructional support tools including tutorial
videos, study cards, and manuals for a
variety of statistical software programs
including StatCrunch, Excel, Minitab, JMP, R,
SPSS, and TI83/84 calculators.
pearson.com/mylab/statistics
This page intentionally left blank
About the Author
James R. Evans
Professor Emeritus, University of Cincinnati, Lindner College of Business
James R. Evans is Professor Emeritus in the Department of Operations, Business Analytics, and Information Systems in the College of Business at the University of Cincinnati.
He holds BSIE and MSIE degrees from Purdue and a PhD in Industrial and Systems Engineering from Georgia Tech.
Dr. Evans has published numerous textbooks in a variety of business disciplines,
including statistics, decision models, and analytics, simulation and risk analysis, network
optimization, operations management, quality management, and creative thinking. He has
published 100 papers in journals such as Management Science, IIE Transactions, Decision Sciences, Interfaces, the Journal of Operations Management, the Quality Management Journal, and many others, and wrote a series of columns in Interfaces on creativity
in management science and operations research during the 1990s. He has also served on
numerous journal editorial boards and is a past-president and Fellow of the Decision Sciences Institute. In 1996, he was an INFORMS Edelman Award Finalist as part of a project
in supply chain optimization with Procter & Gamble that was credited with helping P&G
save over $250,000,000 annually in their North American supply chain, and consulted on
risk analysis modeling for Cincinnati 2012’s Olympic Games bid proposal.
A recognized international expert on quality management, he served on the Board of
Examiners and the Panel of Judges for the Malcolm Baldrige National Quality Award.
Much of his research has focused on organizational performance excellence and measurement practices.
25
This page intentionally left blank
Credits
Text Credits
Chapter 3 page 129 Prem Thomas, MD and Seth Powsner, MD “Data Presentation for
Quality Improvement”, 2005, AMIA.
Appendix A page 661–663 National Institute of Standards and Technology.
Photo Credits
Chapter 1
page 29 NAN728/Shutterstock page 56 hans12/Fotolia
Chapter 2
page 75 NAN728/Shutterstock page 86 2jenn/Shutterstock
Chapter 3
page 113 ESB Professional/Shutterstock
Chapter 4
page 143 Nataliiap/Shutterstock page 191 langstrup/123RF
Chapter 5 page 201 PeterVrabel/Shutterstock page 223 Fantasista/Fotolia
page 237 Victor Correia/Shutterstock.com
Chapter 6 page 247 Robert Brown Stock/Shutterstock page 252 Stephen Finn/
Shutterstock.com
Chapter 7
page 275 Jirsak/Shutterstock page 301 Hurst Photo/Shutterstock
Chapter 8 page 311 Luca Bertolli/123RF page 333 Gunnar Pippel/Shutterstock
page 333 Vector-Illustration/Shutterstock page 333 Claudio Divizia/Shutterstock
page 333 Nataliia Natykach/Shutterstock
Chapter 9
page 353 rawpixel/123RF page 379 Sean Pavone/Shutterstock
Chapter 10
page 383 Laborant/Shutterstock page 402 Helder Almeida/Shutterstock
Chapter 11 page 405 marekuliasz/Shutterstock page 416 Bryan Busovicki/Shutterstock
page 421 Poprotskiy Alexey/Shutterstock
Chapter 12 page 451 Stephen Rees/Shutterstock page 475 Vladitto/Shutterstock
Chapter 13 page 493 Pinon Road/Shutterstock page 496 bizoo_n/Fotolia
page 537 2jenn/Shutterstock
Chapter 14
page 551 Jirsak/Shutterstock page 567 Kheng Guan Toh/Shutterstock
Chapter 15
page 593 Alexander Orlov/Shutterstock
Chapter 16
page 631 marekuliasz/Shutterstock page 655 SSokolov/Shutterstock
Front Matter
James R. Evans
27
This page intentionally left blank
CHAPTER
1
Introduction to
Business Analytics
NAN728/Shutterstock
LEARNING OBJECTIVES
After studying this chapter, you will be able to:
■■ Define business analytics.
■■ Explain the concept of a model and various ways a
■■ Explain why analytics is important in today’s business
model can be characterized.
■■ Define and list the elements of a decision model.
■■ Illustrate examples of descriptive, predictive, and
prescriptive models.
■■ Explain the difference between uncertainty and risk.
■■ Define the terms optimization, objective function, and
optimal solution.
■■ Explain the difference between a deterministic and
stochastic decision model.
■■ List and explain the steps in the problem-solving
process.
environment.
■■ State some typical examples of business applications
in which analytics would be beneficial.
■■ Summarize the evolution of business analytics
and explain the concepts of business intelligence,
operations research and management science, and
decision support systems.
■■ Explain the difference between descriptive, predictive,
and prescriptive analytics.
■■ State examples of how data are used in business.
30
Chapter 1 Introduction to Business Analytics
The purpose of this book is to provide you with a basic introduction to the
concepts, methods, and models used in business analytics so that you will
develop an appreciation not only for its capabilities to support and enhance business decisions, but also for the ability to use business analytics at an elementary
level in your work. In this chapter, we introduce you to the field of business analytics and set the foundation for many of the concepts and techniques that you will
learn. Let’s start with a rather innovative example.
Most of you have likely been to a zoo, seen the animals, had something to eat,
and bought some souvenirs. You probably wouldn’t think that managing a zoo is
very difficult; after all, it’s just feeding and taking care of the animals, right? A zoo
might be the last place that you would expect to find business analytics being used,
but not anymore. The Cincinnati Zoo & Botanical Garden has been an “early
adopter” and one of the first organizations of its kind to exploit business analytics.1
Despite generating more than two-thirds of its budget through its own fundraising efforts, the zoo wanted to reduce its reliance on local tax subsidies even
further by increasing visitor attendance and revenues from secondary sources
such as membership, food, and retail outlets. The zoo’s senior management surmised that the best way to realize more value from each visit was to offer visitors a truly transformed customer experience. By using business analytics to gain
greater insight into visitors’ behavior and tailoring operations to their preferences,
the zoo expected to increase attendance, boost membership, and maximize sales.
The project team—which consisted of consultants from IBM and Brightstar
Partners, as well as senior executives from the zoo—began translating the organization’s goals into technical solutions. The zoo worked to create a business analytics platform that was capable of delivering the desired goals by combining data
from ticketing and point-of-sale systems throughout the zoo with membership
information and geographical data gathered from the ZIP codes of all visitors.
This enabled the creation of reports and dashboards that gave everyone from
senior managers to zoo staff access to real-time information that helped them
optimize operational management and transform the customer experience.
By integrating weather forecast data, the zoo is now able to compare current
forecasts with historic attendance and sales data, supporting better decision making for labor scheduling and inventory planning. Another area where the solution
delivers new insight is food service. By opening food outlets at specific times of
day when demand is highest (for example, keeping ice cream kiosks open in the
1
IBM Software Business Analtyics, “Cincinnati Zoo transforms customer experience and boosts profits,”
© IBM Corporation 2012.
Chapter 1 Introduction to Business Analytics
31
final hour before the zoo closes), the zoo has been able to increase sales significantly. In addition, attendance and revenues have dramatically increased, resulting
in annual return on investment of 411%. The business analytics initiative paid for
itself within three months and delivers, on average, benefits of $738,212 per year.
Specifically,
■■ The zoo has seen a 4.2% rise in ticket sales by targeting potential visitors who
live in specific ZIP codes.
■■ Food revenues increased 25% by optimizing the mix of products on sale and
adapting selling practices to match peak purchase times.
■■ Eliminating slow-selling products and targeting visitors with specific promo-
tions enabled an 18% increase in merchandise sales.
■■ The zoo was able to cut its marketing expenditure, saving $40,000 in the first
year, and reduce advertising expenditure by 43% by eliminating ineffective
campaigns and segmenting customers for more targeted marketing.
Because of the zoo’s success, other organizations such as Point Defiance Zoo
& Aquarium in Tacoma, Washington, and History Colorado Center, a museum in
Denver, have embarked on similar initiatives.
What Is Business Analytics?
Everyone makes decisions. Individuals face personal decisions such as choosing a college or
graduate program, making product purchases, selecting a mortgage instrument, and investing for retirement. Managers in business organizations make numerous decisions every day.
Some of these decisions include what products to make and how to price them, where to
locate facilities, how many people to hire, where to allocate advertising budgets, whether or
not to outsource a business function or make a capital investment, and how to schedule production. Many of these decisions have significant economic consequences; moreover, they
are difficult to make because of uncertain data and imperfect information about the future.
Managers today no longer make decisions based on pure judgment and experience;
they rely on factual data and the ability to manipulate and analyze data to supplement their
intuition and experience, and to justify their decisions. What makes business decisions complicated today is the overwhelming amount of available data and information. Data to support business decisions—including those specifically collected by firms as well as through
the Internet and social media such as Facebook—are growing exponentially and becoming
increasingly difficult to understand and use. As a result, many companies have recently
established analytics departments; for instance, IBM reorganized its consulting business
and established a new 4,000-person organization focusing on analytics. Companies are
increasingly seeking business graduates with the ability to understand and use analytics.
The demand for professionals with analytics expertise has skyrocketed, and many universities now have programs in analytics.2
2
Matthew J. Liberatore and Wenhong Luo, “The Analytics Movement: Implications for Operations
Research,” Interfaces, 40, 4 (July–August 2010): 313–324.
32
Chapter 1 Introduction to Business Analytics
Business analytics, or simply analytics, is the use of data, information technology,
statistical analysis, quantitative methods, and mathematical or computer-based models to
help managers gain improved insight about their business operations and make better,
fact-based decisions. Business analytics is “a process of transforming data into actions
through analysis and insights in the context of organizational decision making and problem solving.”3 Business analytics is supported by various tools such as Microsoft Excel
and various Excel add-ins, commercial statistical software packages such as SAS or
Minitab, and more complex business intelligence suites that integrate data with analytical
software.
Using Business Analytics
Tools and techniques of business analytics are used across many areas in a wide variety of
organizations to improve the management of customer relationships, financial and marketing activities, human capital, supply chains, and many other areas. Leading banks use analytics to predict and prevent credit fraud. Investment firms use analytics to select the best
client portfolios to manage risk and optimize return. Manufacturers use analytics for production planning, purchasing, and inventory management. Retailers use analytics to recommend products to customers and optimize marketing promotions. Pharmaceutical firms
use analytics to get life-saving drugs to market more quickly. The leisure and vacation
industries use analytics to analyze historical sales data, understand customer behavior,
improve Web site design, and optimize schedules and bookings. Airlines and hotels use
analytics to dynamically set prices over time to maximize revenue. Even sports teams are
using business analytics to determine both game strategy and optimal ticket prices.4 For
example, teams use analytics to decide on ticket pricing, who to recruit and trade, what
combinations of players work best, and what plays to run under different situations.
Among the many organizations that use analytics to make strategic decisions and
manage day-to-day operations are Caesars Entertainment, the Cleveland Indians baseball, Phoenix Suns basketball, and New England Patriots football teams, Amazon.com,
Procter & Gamble, United Parcel Service (UPS), and Capital One bank. It was reported
that nearly all firms with revenues of more than $100 million are using some form of business analytics.
Some common types of business decisions that can be enhanced by using analytics
include
pricing (for example, setting prices for consumer and industrial goods, government contracts, and maintenance contracts),
■■ customer segmentation (for example, identifying and targeting key customer
groups in retail, insurance, and credit card industries),
■■ merchandising (for example, determining brands to buy, quantities, and
allocations),
■■ location (for example, finding the best location for bank branches and ATMs, or
where to service industrial equipment),
■■ supply chain design (for example, determining the best sourcing and transportation options and finding the best delivery routes),
■■
3
Liberatore and Luo, “The Analytics Movement”.
Jim Davis, “8 Essentials of Business Analytics,” in “Brain Trust—Enabling the Confident Enterprise with
Business Analytics” (Cary, NC: SAS Institute, Inc., 2010): 27–29. www.sas.com/bareport
4
Chapter 1 Introduction to Business Analytics
33
staffing (for example, ensuring the appropriate staffing levels and capabilities and
hiring the right people—sometimes referred to as “people analytics”),
■■ health care (for example, scheduling operating rooms to improve utilization,
improving patient flow and waiting times, purchasing supplies, predicting health
risk factors),
■■
and many others in operations management, finance, marketing, and human resources—in
fact, in every discipline of business.5
Various research studies have discovered strong relationships between a company’s performance in terms of profitability, revenue, and shareholder return and its use of analytics.
Top-performing organizations (those that outperform their competitors) are three times more
likely to be sophisticated in their use of analytics than lower performers and are more likely
to state that their use of analytics differentiates them from competitors.6 However, research
has also suggested that organizations are overwhelmed by data and struggle to understand
how to use data to achieve business results and that most organizations simply don’t understand how to use analytics to improve their businesses. Thus, understanding the capabilities
and techniques of analytics is vital to managing in today’s business environment.
So, no matter what your job position in an organization is or will be, the study of
analytics will be quite important to your future success. You may find many uses in your
everyday work for the Excel-based tools that we will study. You may not be skilled in all
the technical nuances of analytics and supporting software, but you will, at the very least,
be a consumer of analytics and work with analytics professionals to support your analyses and decisions. For example, you might find yourself on project teams with managers
who know very little about analytics and analytics experts such as statisticians, programmers, and economists. Your role might be to ensure that analytics is used properly to solve
important business problems.
Impacts and Challenges
The benefits of applying business analytics can be significant. Companies report reduced
costs, better risk management, faster decisions, better productivity, and enhanced
bottom-line performance such as profitability and customer satisfaction. For example,
1-800-Flowers.com used analytic software to target print and online promotions with
greater accuracy; change prices and offerings on its Web site (sometimes hourly); and optimize its marketing, shipping, distribution, and manufacturing operations, resulting in a
$50 million cost savings in one year.7
Business analytics is changing how managers make decisions.8 To thrive in today’s business world, organizations must continually innovate to differentiate themselves from competitors, seek ways to grow revenue and market share, reduce costs, retain existing customers and
acquire new ones, and become faster and leaner. IBM suggests that traditional management
5
Thomas H. Davenport, “How Organizations Make Better Decisions,” edited excerpt of an article distributed by the International Institute for Analytics published in “Brain Trust—Enabling the Confident
Enterprise with Business Analytics” (Cary, NC: SAS Institute, Inc., 2010): 8–11. www.sas.com/bareport
6
Thomas H. Davenport and Jeanne G. Harris, Competing on Analytics (Boston: Harvard Business School
Press, 2007): 46; Michael S. Hopkins, Steve LaValle, Fred Balboni, Nina Kruschwitz, and Rebecca Shockley, “10 Data Points: Information and Analytics at Work,” MIT Sloan Management Review, 52, 1 (Fall
2010): 27–31.
7
Jim Goodnight, “The Impact of Business Analytics on Performance and Profitability,” in “Brain Trust—
Enabling the Confident Enterprise with Business Analytics” (Cary, NC: SAS Institute, Inc., 2010): 4–7.
www.sas.com/bareport
8
Analytics: The New Path to Value, a joint MIT Sloan Management Review and IBM Institute for Business
Value study.
34
Chapter 1 Introduction to Business Analytics
approaches are evolving in today’s analytics-driven environment to include more fact-based
decisions as opposed to judgment and intuition, more prediction rather than reactive decisions,
and the use of analytics by everyone at the point where decisions are made rather than relying
on skilled experts in a consulting group.9 Nevertheless, organizations face many challenges in
developing analytics capabilities, including lack of understanding of how to use analytics,
competing business priorities, insufficient analytical skills, difficulty in getting good data and
sharing information, and not understanding the benefits versus perceived costs of analytics
studies. Successful application of analytics requires more than just knowing the tools; it
requires a high-level understanding of how analytics supports an organization’s competitive
strategy and effective execution that crosses multiple disciplines and managerial levels.
In 2011, a survey by Bloomberg Businessweek Research Services and SAS concluded
that business analytics was still in the “emerging stage” and was used only narrowly within
business units, not across entire organizations. The study also noted that many organizations lacked analytical talent, and those that did have analytical talent often didn’t know
how to apply the results properly. While analytics was used as part of the decision-making
process in many organizations, most business decisions are still based on intuition.10
Today, business analytics has matured in many organizations, but many more opportunities
still exist. These opportunities are reflected in the job market for analytics professionals, or
“data scientists,” as some call them. McKinsey & Company suggested that there is a shortage of qualified data scientists.11
CHECK YOUR UNDERSTANDING
1. Explain why analytics is important in today’s business environment.
2. Define business analytics.
3. State three examples of how business analytics is used in organizations.
4. What are the key benefits of using business analytics?
5. What challenges do organizations face in using analytics?
Evolution of Business Analytics
Analytical methods, in one form or another, have been used in business for more than a
century. The core of business analytics consists of three disciplines: business intelligence
and information systems, statistics, and modeling and optimization.
Analytic Foundations
The modern evolution of analytics began with the introduction of computers in the late
1940s and their development through the 1960s and beyond. Early computers provided the
ability to store and analyze data in ways that were either very difficult or impossible to do
manually. This facilitated the collection, management, analysis, and reporting of data, which
9
“Business Analytics and Optimization for the Intelligent Enterprise” (April 2009). www.ibm.com/qbs/
intelligent-enterprise
10
Bloomberg Businessweek Research Services and SAS, “The Current State of Business Analytics: Where
Do We Go From Here?” (2011).
11
Andrew Jennings, “What Makes a Good Data Scientist?” Analytics Magazine (July–August 2013): 8–13.
www.analytics-magazine.org
Chapter 1 Introduction to Business Analytics
35
is often called business intelligence (BI), a term that was coined in 1958 by an IBM
researcher, Hans Peter Luhn.12 Business intelligence software can answer basic questions
such as “How many units did we sell last month?” “What products did customers buy and
how much did they spend?” “How many credit card transactions were completed yesterday?” Using BI, we can create simple rules to flag exceptions automatically; for example, a
bank can easily identify transactions greater than $10,000 to report to the Internal Revenue
Service.13 BI has evolved into the modern discipline we now call information systems (IS).
Statistics has a long and rich history, yet only rather recently has it been recognized as
an important element of business, driven to a large extent by the massive growth of data in
today’s world. Google’s chief economist noted that statisticians surely have one of the best
jobs.14 Statistical methods allow us to gain a richer understanding of data that goes beyond
business intelligence reporting by not only summarizing data succinctly but also finding
unknown and interesting relationships among the data. Statistical methods include the
basic tools of description, exploration, estimation, and inference, as well as more advanced
techniques like regression, forecasting, and data mining.
Much of modern business analytics stems from the analysis and solution of complex
decision problems using mathematical or computer-based models—a discipline known as
operations research, or management science. Operations research (OR) was born from
efforts to improve military operations prior to and during World War II. After the war,
scientists recognized that the mathematical tools and techniques developed for military
applications could be applied successfully to problems in business and industry. A significant amount of research was carried on in public and private think tanks during the
late 1940s and through the 1950s. As the focus on business applications expanded, the
term management science (MS) became more prevalent. Many people use the terms operations research and management science interchangeably, so the field became known as
Operations Research/Management Science (OR/MS). Many OR/MS applications use
modeling and optimization—techniques for translating real problems into mathematics,
spreadsheets, or various computer languages, and using them to find the best (“optimal”)
solutions and decisions. INFORMS, the Institute for Operations Research and the Management Sciences, is the leading professional society devoted to OR/MS and analytics and
publishes a bimonthly magazine called Analytics (http://analytics-magazine.org/). Digital
subscriptions may be obtained free of charge at the Web site.
Modern Business Analytics
Modern business analytics can be viewed as an integration of BI/IS, statistics, and modeling and optimization, as illustrated in Figure 1.1. While these core topics are traditional
and have been used for decades, the uniqueness lies in their intersections. For example,
data mining is focused on better understanding characteristics and patterns among variables in large databases using a variety of statistical and analytical tools. Many standard
statistical tools as well as more advanced ones are used extensively in data mining. Simulation and risk analysis relies on spreadsheet models and statistical analysis to examine
the impacts of uncertainty in estimates and their potential interaction with one another on
the output variable of interest.
12
H. P. Luhn, “A Business Intelligence System.” IBM Journal (October 1958).
Jim Davis, “Business Analytics: Helping You Put an Informed Foot Forward,” in “Brain Trust—Enabling
the Confident Enterprise with Business Analytics,” (Cary, NC: SAS Institute, Inc., 2010): 4–7. www.sas
.com/bareport
14
James J. Swain, “Statistical Software in the Age of the Geek,” Analytics Magazine (March -April 2013):
48–55.
13
36
Chapter 1 Introduction to Business Analytics
▶▶ Figure 1.1
A Visual Perspective of
Business Analytics
Data
Mining
Statistics
Visualization
Simulation
and Risk
Business
Intelligence/
Information
Systems
Decision
Support
Systems
Modeling and
Optimization
Decision support systems (DSSs) began to evolve in the 1960s by combining business intelligence concepts with OR/MS models to create analytical-based computer systems to support decision making. DSSs include three components:
1. Data management. The data management component includes databases for
storing data and allows the user to input, retrieve, update, and manipulate data.
2. Model management. The model management component consists of various
statistical tools and management science models and allows the user to easily
build, manipulate, analyze, and solve models.
3. Communication system. The communication system component provides the
interface necessary for the user to interact with the data and model management
components.15
DSSs have been used for many applications, including pension fund management, portfolio
management, work-shift scheduling, global manufacturing and facility location, advertisingbudget allocation, media planning, distribution planning, airline operations planning, inventory control, library management, classroom assignment, nurse scheduling, blood distribution,
water pollution control, ski-area design, police-beat design, and energy planning.16
A key feature of a DSS is the ability to perform what-if analysis—how specific combinations of inputs that reflect key assumptions will affect model outputs. What-if analysis
is also used to assess the sensitivity of optimization models to changes in data inputs and
provide better insight for making good decisions.
Perhaps the most useful component of business analytics, which makes it truly unique,
is the center of Figure 1.1—visualization. Visualizing data and results of analyses provides
a way of easily communicating data at all levels of a business and can reveal surprising patterns and relationships. Software such as IBM’s Cognos system exploits data visualization
15
William E. Leigh and Michael E. Doherty, Decision Support and Expert Systems (Cincinnati, OH:
South-Western Publishing Co., 1986).
16
H. B. Eom and S. M. Lee, “A Survey of Decision Support System Applications (1971–April 1988),”
Interfaces, 20, 3 (May–June 1990): 65–79.
Chapter 1 Introduction to Business Analytics
37
for query and reporting, data analysis, dashboard presentations, and scorecards linking strategy to operations. The Cincinnati Zoo, for example, has used this on an iPad to display
hourly, daily, and monthly reports of attendance, food and retail location revenues and sales,
and other metrics for prediction and marketing strategies. UPS uses telematics to capture
vehicle data and display them to help make decisions to improve efficiency and performance.
You may have seen a tag cloud (see the graphic at the beginning of this chapter), which is a
visualization of text that shows words that appear more frequently with larger fonts.
Software Support and Spreadsheet Technology
Many companies, such as IBM, SAS, and Tableau Software, have developed a variety of
software and hardware solutions to support business analytics. For example, IBM’s Cognos
Express, an integrated business intelligence and planning solution designed to meet the needs
of midsize companies, provides reporting, analysis, dashboard, scorecard, planning, budgeting, and forecasting capabilities. It is made up of several modules, including Cognos Express
Reporter, for self-service reporting and ad hoc query; Cognos Express Advisor, for analysis and
visualization; and Cognos Express Xcelerator, for Excel-based planning and business analysis.
Information is presented to users in a context that makes it easy to understand; with an easyto-use interface, users can quickly gain the insight they need from their data to make the right
decisions and then take action for effective and efficient business optimization and outcome.
SAS provides a variety of software that integrate data management, business intelligence, and
analytics tools. SAS Analytics covers a wide range of capabilities, including predictive modeling and data mining, visualization, forecasting, optimization and model management, statistical analysis, text analytics, and more. Tableau Software provides simple drag and drop tools
for visualizing data from spreadsheets and other databases. We encourage you to explore many
of these products as you learn the basic principles of business analytics in this book.
Although commercial software often have powerful features and capabilities, they can
be expensive, generally require advanced training to understand and apply, and may work
only on specific computer platforms. Spreadsheet software, on the other hand, is widely
used across all areas of business and used by nearly everyone. Spreadsheets are an effective platform for manipulating data and developing and solving models; they support powerful commercial add-ins and facilitate communication of results. Spreadsheets provide a
flexible modeling environment and are particularly useful when the end user is not the
designer of the model. Teams can easily use spreadsheets and understand the logic upon
which they are built. Information in spreadsheets can easily be copied from spreadsheets
into other documents and presentations. A recent survey identified more than 180 commercial spreadsheet products that support analytics efforts, including data management and
reporting, data- and model-driven analytical techniques, and implementation. 17 Many
organizations have used spreadsheets extremely effectively to support decision making in
marketing, finance, and operations. Some illustrative applications include the following:18
■■
■■
17
Analyzing supply chains (Hewlett-Packard)
Determining optimal inventory levels to meet customer service objectives
(Procter & Gamble)
Thomas A. Grossman, “Resources for Spreadsheet Analysts,” Analytics Magazine (May/June 2010): 8.
www.analytics-magazine.org
18
Larry J. LeBlanc and Thomas A. Grossman, “Introduction: The Use of Spreadsheet Software in the
Application of Management Science and Operations Research,” Interfaces, 38, 4 (July–August 2008):
225–227.
38
Chapter 1 Introduction to Business Analytics
Selecting internal projects (Lockheed Martin Space Systems)
Planning for emergency clinics in response to a sudden epidemic or bioterrorism
attack (Centers for Disease Control)
■■ Analyzing the default risk of a portfolio of real estate loans (Hypo International)
■■ Assigning medical residents to on-call and emergency rotations (University of
Vermont College of Medicine)
■■ Performance measurement and evaluation (American Red Cross)
■■
■■
Some optional software packages for statistical applications that your instructor might
use are SAS, Minitab, XLSTAT and StatCrunch. These provide many powerful procedures
as alternatives or supplements to Excel.
Spreadsheet technology has been influential in promoting the use and acceptance of
business analytics. Spreadsheets provide a convenient way to manage data, calculations,
and visual graphics simultaneously, using intuitive representations instead of abstract
mathematical notation. Although the early applications of spreadsheets were primarily in
accounting and finance, spreadsheets have developed into powerful general-purpose managerial tools for applying techniques of business analytics. The power of analytics in a personal computing environment was noted decades ago by business consultants Michael
Hammer and James Champy, who said, “When accessible data is combined with easy-touse analysis and modeling tools, frontline workers—when properly trained—suddenly
have sophisticated decision-making capabilities.”19
ANALYTICS IN PRACTICE: Social Media Analytics
One of the emerging applications of analytics is helping
businesses learn from social media and exploit social
media data for strategic advantage.20 Using analytics,
firms can integrate social media data with traditional data
sources such as customer surveys, focus groups, and
sales data; understand trends and customer perceptions
of their products; and create informative reports to assist
marketing managers and product designers.
Social media analytics is useful in decision making in
many business domains to understand how user-generated
content spreads and influences user interactions, how
information is transmitted, and how it influences decisions.
A review of research published in social media analytics
provides numerous examples:21
■■
The analysis of public responses from social media
before, during, and after disasters, such as the 2010
Haiti earthquake and Hurricane Sandy in New York City
in 2012, has the potential to improve situational knowledge in emergency and disaster management practices.
■■ Social media platforms enable citizens’ engagement with
politicians, governments, and other citizens. Studies
have examined how voters discuss the candidates during an election, how candidates are adopting Twitter for
campaigning and influencing conversations in the public space, and how presidential candidates in the United
States used Twitter to engage people and identify the
topics mentioned by candidates during their campaigns.
Others have used analytics to track political preference by
monitoring online popularity.
■■ In the entertainment industry, one study analyzed viewer
ratings to predict the impact on revenue for upcoming
movies. Another developed a web intelligence application
to aggregate the news about popular TV serials and identify emerging storylines.
19
Michael Hammer and James Champy, Reengineering the Corporation (New York: HarperBusiness,
1993): 96.
20
Jim Davis, “Convergence—Taking Social Media from Talk to Action,” SASCOM (First Quarter 2011): 17.
21
Ashish K. Rathore, Arpan K. Kar, and P. Vigneswara Ilavarasana, “Social Media Analytics: Literature
Review and Directions for Future Research,” Decision Analysis, 14, 4 (December 2017): 229–249.
Chapter 1 Introduction to Business Analytics
Retail organizations monitor and analyze social media
data about their own products and services and also
about their competitors’ products and services to stay
competitive. For instance, one study analyzed different
product features based on rankings from users’ online
reviews.
■■ The integration of social media application and
health care leads to better patient management
■■
39
and empowerment. One researcher classified various online health communities, such as a diabetes
patients’ community, using posts from WebMD.com.
Another analyzed physical activity–related tweets for
a better understanding of physical activity behaviors.
To predict the spread of influenza, one researcher
developed a forecasting approach using flu-related
tweets.
In this book, we use Microsoft Excel as the primary platform for implementing analytics. In the Chapter 1 Appendix, we review some key Excel skills that you should have
before moving forward in this book.
The main chapters in this book are designed using Excel 2016 for Windows or Excel
2016 for Mac. Earlier versions of Excel do not have all the capabilities that we use in this
book. In addition, some key differences exist between Windows and Mac versions that
we will occasionally point out. Thus, some Excel tools that we will describe in chapter
appendixes require you to use Excel for Windows, Office 365, or Google Sheets, and will
not run on Excel for Mac; these are optional to learn, and are not required for any examples
or problems. Your instructor may use optional software, such as XLSTAT and StatCrunch,
which are provided by the publisher (Pearson), or Analytic Solver, which is described in
online supplements to this book.
CHECK YOUR UNDERSTANDING
1. Provide two examples of questions that business intelligence can address.
2. How do statistical methods enhance business intelligence reporting?
3. What is operations research/management science?
4. How does modern business analytics integrate traditional disciplines of BI, statistics,
and modeling/optimization?
5. What are the components of a decision support system?
Descriptive, Predictive, and Prescriptive Analytics
Business analytics begins with the collection, organization, and manipulation of data and
is supported by three major components:22
1. Descriptive analytics. Most businesses start with descriptive analytics—the
use of data to understand past and current business performance and make
informed decisions. Descriptive analytics is the most commonly used and most
well-understood type of analytics. These techniques categorize, characterize,
consolidate, and classify data to convert them into useful information for the
purposes of understanding and analyzing business performance. Descriptive
22
Parts of this section are adapted from Irv Lustig, Brenda Dietric, Christer Johnson, and Christopher
Dziekan, “The Analytics Journey,” Analytics (November/December 2010). http://analytics-magazine.org/
novemberdecember-2010-table-of-contents/
40
Chapter 1 Introduction to Business Analytics
analytics summarizes data into meaningful charts and reports, for example,
about budgets, sales, revenues, or cost. This process allows managers to obtain
standard and customized reports and then drill down into the data and make
queries to understand the impact of an advertising campaign, such as reviewing
business performance to find problems or areas of opportunity, and identifying
patterns and trends in data. Typical questions that descriptive analytics helps
answer are “How much did we sell in each region?” “What was our revenue and
profit last quarter?” “How many and what types of complaints did we resolve?”
“Which factory has the lowest productivity?” Descriptive analytics also helps
companies to classify customers into different segments, which enables them to
develop specific marketing campaigns and advertising strategies.
2. Predictive analytics. Predictive analytics seeks to predict the future by examining historical data, detecting patterns or relationships in these data, and then
extrapolating these relationships forward in time. For example, a marketer
might wish to predict the response of different customer segments to an advertising campaign, a commodities trader might wish to predict short-term movements in commodities prices, or a skiwear manufacturer might want to predict
next season’s demand for skiwear of a specific color and size. Predictive analytics can predict risk and find relationships in data not readily apparent with traditional analyses. Using advanced techniques, predictive analytics can help detect
hidden patterns in large quantities of data, and segment and group data into
coherent sets to predict behavior and detect trends. For instance, a bank manager might want to identify the most profitable customers, predict the chances
that a loan applicant will default, or alert a credit card customer to a potential
fraudulent charge. Predictive analytics helps to answer questions such as “What
will happen if demand falls by 10% or if supplier prices go up 5%?” “What do
we expect to pay for fuel over the next several months?” “What is the risk of
losing money in a new business venture?”
3. Prescriptive analytics. Many problems, such as aircraft or employee scheduling
and supply chain design, simply involve too many choices or alternatives for
a human decision maker to effectively consider. Prescriptive analytics uses
optimization to identify the best alternatives to minimize or maximize some
objective. Prescriptive analytics is used in many areas of business, including
operations, marketing, and finance. For example, we may determine the best
pricing and advertising strategy to maximize revenue, the optimal amount of
cash to store in ATMs, or the best mix of investments in a retirement portfolio
to manage risk. Prescriptive analytics addresses questions such as “How much
should we produce to maximize profit?” “What is the best way of shipping
goods from our factories to minimize costs?” “Should we change our plans
if a natural disaster closes a supplier’s factory, and if so, by how much?” The
mathematical and statistical techniques of predictive analytics can also be combined with prescriptive analytics to make decisions that take into account the
uncertainty in the data.
A wide variety of tools are used to support business analytics. These include
■■
■■
■■
■■
■■
Database queries and analysis
“Dashboards” to report key performance measures
Data visualization
Statistical methods
Spreadsheets and predictive models
Chapter 1 Introduction to Business Analytics
ANALYTICS IN PRACTICE: Analytics in the Home Lending and
Mortgage Industry23
Sometime during their lives, most Americans will receive
a mortgage loan for a house or condominium. The process starts with an application. The application contains
all pertinent information about the borrower that the lender
will need. The bank or mortgage company then initiates
a process that leads to a loan decision. It is here that
key information about the borrower is provided by thirdparty providers. This information includes a credit report,
verification of income, verification of assets, verification of
employment, and an appraisal of the property. The result
of the processing function is a complete loan file that
contains all the information and documents needed to
underwrite the loan, which is the next step in the process.
Underwriting is where the loan application is evaluated for
its risk. Underwriters evaluate whether the borrower can
make payments on time, can afford to pay back the loan,
and has sufficient collateral in the property to back up the
loan. In the event the borrower defaults on their loan, the
lender can sell the property to recover the amount of the
loan. But if the amount of the loan is greater than the value
of the property, then the lender cannot recoup their money.
If the underwriting process indicates that the borrower is
creditworthy and has the capacity to repay the loan and
the value of the property in question is greater than the
loan amount, then the loan is approved and will move to
closing. Closing is the step where the borrower signs all
the appropriate papers, agreeing to the terms of the loan.
In reality, lenders have a lot of other work to do. First,
they must perform a quality control review on a sample
of the loan files that involves a manual examination of all
the documents and information gathered. This process
is designed to identify any mistakes that may have been
made or information that is missing from the loan file.
Because lenders do not have unlimited money to lend to
borrowers, they frequently sell the loan to a third party so
that they have fresh capital to lend to others. This occurs
in what is called the secondary market. Freddie Mac and
Fannie Mae are the two largest purchasers of mortgages
in the secondary market. The final step in the process
is servicing. Servicing includes all the activities associated with providing the customer service on the loan, like
processing payments, managing property taxes held in
escrow, and answering questions about the loan.
23
Contributed by Craig Zielazny, BlueNote Analytics, LLC.
In addition, the institution collects various operational
data on the process to track its performance and efficiency, including the number of applications, loan types and
amounts, cycle times (time to close the loan), bottlenecks in
the process, and so on. Many different types of analytics are
used:
Descriptive analytics—This focuses on historical reporting,
addressing such questions as
How many loan applications were taken in each of the
past 12 months?
■■ What was the total cycle time from application to close?
■■ What was the distribution of loan profitability by
credit score and loan-to-value (LTV), which is the
mortgage amount divided by the appraised value of
the property?
■■
Predictive analytics—Predictive modeling uses mathematical, spreadsheet, and statistical models and addresses questions such as
What impact on loan volume will a given marketing program have?
■■ How many processors or underwriters are needed for a
given loan volume?
■■ Will a given process change reduce cycle time?
■■
Prescriptive analytics—This involves the use of simulation or
optimization to drive decisions. Typical questions include
What is the optimal staffing to achieve a given profitability
constrained by a fixed cycle time?
■■ What is the optimal product mix to maximize profit constrained by fixed staffing?
■■
The mortgage market has become much more dynamic
in recent years due to rising home values, falling interest
rates, new loan products, and an increased desire by home
owners to utilize the equity in their homes as a financial
resource. This has increased the complexity and variability of
the mortgage process and created an opportunity for lenders
to proactively use the data that are available to them as a tool
for managing their business. To ensure that the process is
efficient, effective, and performed with quality, data and analytics are used every day to track what is done, who is doing
it, and how long it takes.
41
42
Chapter 1 Introduction to Business Analytics
■■
■■
■■
■■
■■
■■
Scenario and “what-if” analyses
Simulation
Forecasting
Data and text mining
Optimization
Social media, Web, and text analytics
Although the tools used in descriptive, predictive, and prescriptive analytics are
different, many applications involve all three. Here is a typical example in retail operations.
EXAMPLE 1.1
Retail Markdown Decisions24
As you probably know from your shopping experiences,
most department stores and fashion retailers clear their seasonal inventory by reducing prices. The key question they
face is what prices should they set—and when should they
set them—to meet inventory goals and maximize revenue?
For example, suppose that a store has 100 bathing suits
of a certain style that go on sale on April 1 and wants to
sell all of them by the end of June. Over each week of the
12-week selling season, they can make a decision to discount the price. They face two decisions: When to reduce
the price, and by how much. This results in 24 decisions to
make. For a major national chain that may carry thousands
of products, this can easily result in millions of decisions that
store managers have to make. Descriptive analytics can be
used to examine historical data for similar products, such as
the number of units sold, price at each point of sale, starting
and ending inventories, and special promotions, newspaper
ads, direct marketing ads, and so on, to understand what the
results of past decisions achieved. Predictive analytics can
be used to predict sales based on pricing decisions. Finally,
prescriptive analytics can be applied to find the best set of
pricing decisions to maximize the total revenue.
CHECK YOUR UNDERSTANDING
1. Define descriptive analytics and provide two examples.
2. Define predictive analytics and provide two examples.
3. Define prescriptive analytics and provide two examples.
Data for Business Analytics
Since the dawn of the electronic age and the Internet, both individuals and organizations have
had access to an enormous wealth of data and information. Most data are collected through
some type of measurement process, and consist of numbers (e.g., sales revenues) or textual data (e.g., customer demographics such as gender). Other data might be extracted from
social media, online reviews, and even audio and video files. Information comes from analyzing data—that is, extracting meaning from data to support evaluation and decision making.
Data are used in virtually every major function in a business. Modern organizations—
which include not only for-profit businesses but also nonprofit organizations—need good
data to support a variety of company purposes, such as planning, reviewing company performance, improving operations, and comparing company performance with competitors’
24
Inspired by a presentation by Radhika Kulkarni, SAS Institute, “Data-Driven Decisions: Role of
perations Research in Business Analytics,” INFORMS Conference on Business Analytics and Operations
O
Research, April 10–12, 2011.
Chapter 1 Introduction to Business Analytics
43
or best-practice benchmarks. Some examples of how data are used in business include the
following:
Annual reports summarize data about companies’ profitability and market
share both in numerical form and in charts and graphs to communicate with
shareholders.
■■ Accountants conduct audits to determine whether figures reported on a firm’s
balance sheet fairly represent the actual data by examining samples (that is, subsets) of accounting data, such as accounts receivable.
■■ Financial analysts collect and analyze a variety of data to understand the contribution that a business provides to its shareholders. These typically include profitability, revenue growth, return on investment, asset utilization, operating margins,
earnings per share, economic value added (EVA), shareholder value, and other
relevant measures.
■■ Economists use data to help companies understand and predict population trends,
interest rates, industry performance, consumer spending, and international trade.
Such data are often obtained from external sources such as Standard & Poor’s
Compustat data sets, industry trade associations, or government databases.
■■ Marketing researchers collect and analyze extensive customer data. These data
often consist of demographics, preferences and opinions, transaction and payment history, shopping behavior, and much more. Such data may be collected
by surveys, personal interviews, or focus groups, or from shopper loyalty
cards.
■■ Operations managers use data on production performance, manufacturing quality, delivery times, order accuracy, supplier performance, productivity, costs, and
environmental compliance to manage their operations.
■■ Human resource managers measure employee satisfaction, training costs, turnover, market innovation, training effectiveness, and skills development.
■■
Data may be gathered from primary sources such as internal company records and business
transactions, automated data-capturing equipment, and customer market surveys and from
secondary sources such as government and commercial data sources, custom research providers, and online research.
Perhaps the most important source of data today is data obtained from the Web. With
today’s technology, marketers collect extensive information about Web behaviors, such as
the number of page views, visitor’s country, time of view, length of time, origin and destination paths, products they searched for and viewed, products purchased, and what reviews
they read. Using analytics, marketers can learn what content is being viewed most often,
what ads were clicked on, who the most frequent visitors are, and what types of visitors
browse but don’t buy. Not only can marketers understand what customers have done, but
they can better predict what they intend to do in the future. For example, if a bank knows
that a customer has browsed for mortgage rates and homeowner’s insurance, they can target the customer with homeowner loans rather than credit cards or automobile loans. Traditional Web data are now being enhanced with social media data from Facebook, cell
phones, and even Internet-connected gaming devices.
As one example, a home furnishings retailer wanted to increase the rate of sales for
customers who browsed their Web site. They developed a large data set that covered more
than 7,000 demographic, Web, catalog, and retail behavioral attributes for each customer.
They used predictive analytics to determine how well a customer would respond to different e-mail marketing offers and customized promotions to individual customers. This not
only helped them to determine where to most effectively spend marketing resources but
44
Chapter 1 Introduction to Business Analytics
also doubled the response rate compared to previous marketing campaigns, with a p rojected
and multimillion dollar increase in sales.25
Big Data
Today, nearly all data are captured digitally. As a result, data have been growing at an overwhelming rate, being measured by terabytes (1012 bytes), petabytes (1015 bytes), exabytes
(1018 bytes), and even by higher-dimensional terms. Just think of the amount of data stored
on Facebook, Twitter, or Amazon servers, or the amount of data acquired daily from scanning items at a national grocery chain such as Kroger and its affiliates. Walmart, for instance,
has over one million transactions each hour, yielding more than 2.5 petabytes of data. Analytics professionals have coined the term big data to refer to massive amounts of business
data from a wide variety of sources, much of which is available in real time. IBM calls these
characteristics volume, variety, and v elocity. Most often, big data revolve around customer
behavior and customer experiences. Big data provide an opportunity for organizations to
gain a competitive advantage—if the data can be understood and analyzed effectively to
make better business decisions.
The volume of data continues to increase; what is considered “big” today will be
even bigger tomorrow. In one study of information technology (IT) professionals in 2010,
nearly half of survey respondents ranked data growth among their top three challenges.
Big data are captured using sensors (for example, supermarket scanners), click streams
from the Web, customer transactions, e-mails, tweets and social media, and other ways.
Big data sets are unstructured and messy, requiring sophisticated analytics to integrate
and process the data and understand the information contained in them. Because much big
data are being captured in real time, they must be incorporated into business decisions at
a faster rate. Processes such as fraud detection must be analyzed quickly to have value. In
addition to volume, variety, and velocity, IBM proposed a fourth dimension: veracity—the
level of reliability associated with data. Having high-quality data and understanding the
uncertainty in data are essential for good decision making. Data veracity is an important
role for statistical methods.
Big data can help organizations better understand and predict customer behavior and
improve customer service. A study by the McKinsey Global Institute noted that, “The
effective use of big data has the potential to transform economies, delivering a new wave of
productivity growth and consumer surplus. Using big data will become a key basis of competition for existing companies, and will create new competitors who are able to attract
employees that have the critical skills for a big data world.”26 However, understanding big
data requires advanced analytics tools such as data mining and text analytics, and new
technologies such as cloud computing, faster multi-core processors, large memory spaces,
and solid-state drives.
Data Reliability and Validity
Poor data can result in poor decisions. In one situation, a distribution system design model
relied on data obtained from the corporate finance department. Transportation costs were
25
Based on a presentation by Bill Franks of Teradata, “Optimizing Customer Analytics: How Customer
Level Web Data Can Help,” INFORMS Conference on Busines…

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Regression Statistics & Forecasting Method Excel Questions ”

Get high-quality paper

NEW! AI matching with writer

Order a unique copy of this paper

Type of paper needed:

Pages:

600 words

Academic level:

We'll send you the first draft for approval by September 11, 2018 at 10:52 AM

Total price:

$26

Our Services

Regression Statistics & Forecasting Method Excel Questions

Order a unique copy of this paper