(Business) Analytics is the use of:
quantitative methods, and
mathematical or computer-based models
to help managers gain improved insight about
their business operations and make better, factbased decisions.
◦ setting prices for consumer and industrial goods, government
contracts, and maintenance contracts
◦ identifying and targeting key customer groups in retail, insurance,
and credit card industries
◦ determining brands to buy, quantities, and allocations
◦ finding the best location for bank branches and ATMs, or where to
service industrial equipment
◦ understand trends and customer perceptions; assist marketing
managers and product designers
Operations research/Management science
Decision support systems
◦ …reduced costs, better risk management, faster
decisions, better productivity and enhanced bottom-line
performance such as profitability and customer
◦ …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.
Descriptive analytics: the use of data to
understand past and current business
performance and make informed decisions
Predictive analytics: predict the future by
examining historical data, detecting patterns or
relationships in these data, and then extrapolating
these relationships forward in time.
Prescriptive analytics: identify the best
alternatives to minimize or maximize some
Database queries and analysis
Dashboards to report key performance measures
Spreadsheets and predictive models
Scenario and “what-if” analyses
Data and text mining
Social media, web, and text analytics
Most department stores clear seasonal inventory
by reducing prices.
Key question: When to reduce the price and by
how much to maximize revenue?
Potential applications of analytics:
Descriptive analytics: examine historical data for similar
products (prices, units sold, advertising, …)
Predictive analytics: predict sales based on price
Prescriptive analytics: find the best sets of pricing and
advertising to maximize sales revenue
IBM 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.
◦ Predictive modeling and data mining, visualization,
forecasting, optimization and model management,
statistical analysis, text analytics, and more.
◦ Simple drag and drop tools for visualizing data from
spreadsheets and other databases.
Data: numerical or textual facts and figures that
are collected through some type of measurement
Information: result of analyzing data; that is,
extracting meaning from data to support
evaluation and decision making.
Financial profitability analysis
Operations management performance
Human resource measurements
page views, visitor’s country, time of view, length of time, origin
and destination paths, products they searched for and viewed,
products purchased, what reviews they read, and many others.
Data set - a collection of data.
◦ Examples: Marketing survey responses, a table of
historical stock prices, and a collection of measurements
of dimensions of a manufactured item.
Database - a collection of related files containing
records on people, places, or things.
◦ A database file is usually organized in a two-dimensional
table, where the columns correspond to each individual
element of data (called fields, or attributes), and the rows
represent records of related data elements.
Fields or Attributes
Big data to refer to massive amounts of business data
from a wide variety of sources, much of which is
available in real time, and much of which is uncertain or
unpredictable. IBM calls these characteristics volume,
variety, velocity, and veracity.
“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.” - McKinsey Global Institute, 2011
Metric - a unit of measurement that provides a
way to objectively quantify performance.
Measurement - the act of obtaining data
associated with a metric.
Measures - numerical values associated with a
Discrete metric - one that is derived from
◦ For example, a delivery is either on time or not; an order
is complete or incomplete; or an invoice can have one,
two, three, or any number of errors. Some discrete
metrics would be the proportion of on-time deliveries; the
number of incomplete orders each day, and the number
of errors per invoice.
Continuous metrics are based on a continuous
scale of measurement.
◦ Any metrics involving dollars, length, time, volume, or
weight, for example, are continuous.
Categorical (nominal) data - sorted into
categories according to specified characteristics.
Ordinal data - can be ordered or ranked
according to some relationship to one another.
Interval data - ordinal but have constant
differences between observations and have
arbitrary zero points.
Ratio data - continuous and have a natural zero.
Reliability - data are accurate and consistent.
Validity - data correctly measures what it is supposed to measure.
◦ A tire pressure gage that consistently reads several pounds of pressure
below the true value is not reliable, although it is valid because it does
measure tire pressure.
◦ The number of calls to a customer service desk might be counted
correctly each day (and thus is a reliable measure) but not valid if it is
used to assess customer dissatisfaction, as many calls may be simple
◦ A survey question that asks a customer to rate the quality of the food in a
restaurant may be neither reliable (because different customers may
have conflicting perceptions) nor valid (if the intent is to measure
customer satisfaction, as satisfaction generally includes other elements
of service besides food).
Model - an abstraction or representation of a real
system, idea, or object.
Captures the most important features
Can be a written or verbal description, a visual
representation, a mathematical formula, or a
The sales of a new product, such as a first-generation iPad or
3D television, often follow a common pattern.
1. Verbal description: The rate of sales starts small as early
adopters begin to evaluate a new product and then begins
to grow at an increasing rate over time as positive
customer feedback spreads. Eventually, the market
begins to become saturated and the rate of sales begins
2. Visual model: A sketch of sales as an S-shaped curve
3. Mathematical model: S = aebect
where S is sales, t is time, e is the base of natural
logarithms, and a, b and c are constants.
Influence diagram - a visual representation of a
descriptive model that shows how the elements of
the model influence, or relate to, others.
An influence diagram is a useful approach for
conceptualizing the structure of a model and can assist in
building a mathematical or spreadsheet model.
total cost = fixed cost + variable cost
variable cost = unit variable cost × quantity produced
total cost = fixed cost + variable cost
= fixed cost + unit variable cost × quantity produced
TC = Total Cost
F = Fixed cost
V = Variable unit cost
Q = Quantity produced
TC = F +VQ
Decision model - a logical or mathematical
representation of a problem or business situation that
can be used to understand, analyze, or facilitate making
◦ Data, which are assumed to be constant for purposes of the
◦ Uncontrollable variables, which are quantities that can change but
cannot be directly controlled by the decision maker.
◦ Decision variables, which are controllable and can be selected at
the discretion of the decision maker.
TC(manufacturing) = $50,000 + $125*Q
TC(outsourcing) = $175*Q
Breakeven Point: TC(manufacturing) = TC(outsourcing)
$50,000 + $125 × Q = $175 × Q
$50,000 = 50 × Q
Q = 1,000
F + VQ = CQ
Q = F/(C - V)
In the grocery industry, managers typically need to know
how best to use pricing, coupons and advertising
strategies to influence sales. Grocers often study the
relationship of sales volume to these strategies by
conducting controlled experiments to identify the
relationship between them and sales volumes. That is,
they implement different combinations of pricing, coupons,
and advertising, observe the sales that result, and use
analytics to develop a predictive model of sales as a
function of these decision strategies.
Sales = 500 – 0.05(price) + 30(coupons) + 0.08(advertising) +
If the price is $6.99, no coupons are offered, and no advertising is done
(the experiment corresponding to week 1), the model estimates sales as
Sales = 500 - 0.05 × $6.99 + 30 × 0 + 0.08 × 0 + 0.25 × $6.99 × 0 =
Assumptions are made to
◦ simplify a model and make it more tractable; that is, able to be
easily analyzed or solved.
◦ better characterize historical data or past observations.
The task of the modeler is to select or build an
appropriate model that best represents the behavior of
the real situation.
Example: economic theory tells us that demand for a
product is negatively related to its price. Thus, as prices
increase, demand falls, and vice versa (modeled by
price elasticity — the ratio of the percentage change in
demand to the percentage change in price).
As price increases, demand falls.
Assumes price elasticity is constant (constant ratio of
% change in demand to % change in price)
Uncertainty is imperfect knowledge of what will happen
in the future.
Risk is associated with the consequences of what
“To try to eliminate risk in business enterprise is futile.
Risk is inherent in the commitment of present resources
to future expectations. Indeed, economic progress can
be defined as the ability to take greater risks. The
attempt to eliminate risks, even the attempt to minimize
them, can only make them irrational and unbearable. It
can only result in the greatest risk of all: rigidity.”
– Peter Drucker
Prescriptive decision models help decision
makers identify the best solution.
Optimization - finding values of decision variables
that minimize (or maximize) something such as
cost (or profit).
Objective function - the equation that minimizes (or
maximizes) the quantity of interest.
Constraints - limitations or restrictions.
Optimal solution - values of the decision variables at the
minimum (or maximum) point.
A firm wishes to determine the best pricing for one
of its products in order to maximize revenue.
Analysts determined the following model:
Sales = -2.9485(price) + 3240.9
Total revenue = (price)(sales)
= price × (-2.9485 × price + 3240.9)
= 22.9485 × price2 + 3240.9 × price
Identify the price that maximizes total revenue,
subject to any constraints that might exist.
Deterministic model – all model input information
is known with certainty.
Stochastic model – some model input
information is uncertain.
◦ For instance, suppose that customer demand is an
important element of some model. We can make the
assumption that the demand is known with certainty; say,
5,000 units per month (deterministic). On the other hand,
suppose we have evidence to indicate that demand is
uncertain, with an average value of 5,000 units per
month, but which typically varies between 3,200 and
6,800 units (stochastic).
1. Recognizing a problem
2. Defining the problem
3. Structuring the problem
4. Analyzing the problem
5. Interpreting results and making a decision
6. Implementing the solution
Problems exist when there is a gap between what is
happening and what we think should be happening.
For example, costs are too high compared with
Clearly defining the problem is not a trivial task.
Complexity increases when the following occur:
- large number of courses of action
- the problem belongs to a group and not an
- competing objectives
- external groups are affected
- problem owner and problem solver are not the
- time limitations exist
Stating goals and objectives
Characterizing the possible decisions
Identifying any constraints or restrictions
Analytics plays a major role.
Analysis involves some sort of experimentation or
solution process, such as evaluating different
scenarios, analyzing risks associated with various
decision alternatives, finding a solution that meets
certain goals, or determining an optimal solution.
Models cannot capture every detail of the real
Managers must understand the limitations of
models and their underlying assumptions and
often incorporate judgment into making a decision.
Translate the results of the model back to the real
Requires providing adequate resources,
motivating employees, eliminating resistance to
change, modifying organizational policies, and
Maintained by an analytics manager at ARAMARK.
Each month a new puzzle is posted.
Many puzzles can be solved using techniques you
will learn in this book.
The puzzles are fun challenges.
A good one to start with is SurvivOR (June 2010).
Many commercial software packages can be used
for Business Analytics.
Spreadsheet software, such as Microsoft Excel, is
widely available and used across all areas of
Spreadsheets provide a flexible modeling
environment for manipulating data and
developing and solving models.
Mac versions of Excel do not have the full
functionality that Windows versions have –
particularly statistical features which are important
to this book.
The Excel add-in that we use in later chapters,
Analytic Solver Platform, only runs on Windows.
Thus, if you use a Mac, you should either run
Bootcamp with Windows or use a third-party
software product such as Parallels or VMWare.
Opening, saving, and printing files
Using workbooks and worksheets
Moving around a spreadsheet
Selecting cells and ranges
Inserting/deleting rows and columns
Entering and editing text, data, and formulas
Formatting data (number, currency, decimal)
Working with text strings
Formatting data and text
Modifying the appearance of a spreadsheet
Tabs - Home, Insert, Page Layout, Formulas, …
Groups - Font, Alignment, Number, Styles, …
Buttons and Menus
- Buttons appear as small icons.
- Menus of additional choices are indicated by
Common mathematical operators are used.
+ would be entered into Excel as:
=a− b*P^5 + c/d
Cell references can be relative or absolute. Using a dollar sign
before a row and/or column label creates an absolute reference.
◦ Relative references: A2, C5, D10
◦ Absolute references: $A$2, $C5, D$10
Using a $ sign before a row label (for example, B$4) keeps the
reference fixed to row 4 but allows the column reference to
change if the formula is copied to another cell.
Using a $ sign before a column label (for example, $B4) keeps
the reference to column B fixed but allows the row reference to
Using a $ sign before both the row and column labels (for
example, $B$4) keeps the reference to cell B4 fixed no matter
where the formula is copied.
Two models for predicting demand as a function of price
D = a – bP
Formula in cell B8:
D = cP-d
Formula in cell E8:
Note how the absolute addresses are used so that as these formulas
are copied down, the demand is computed correctly.
Formulas in cells can be copied in many ways.
Use the Copy button in the Home tab, then use
the Paste button
Use Ctrl-C, then Ctrl-V
Drag the bottom right corner of a cell (the fill
handle) across a row or column
Column and Row Widths
Displaying Formulas in Worksheets
Displaying Grid Lines and Column Headers for
Filling a Range with a Series of Numbers
◦ Excel has other useful COUNT-type functions: COUNTA counts
the number of nonblank cells in a range, and COUNTBLANK
counts the number of blank cells in a range. In addition,
COUNTIFS(range1, criterion1, range2, criterion2,… range_n,
criterion_n) finds the number of cells within multiple ranges that
meet specific criteria for each range.
SUMIF, AVERAGEIF, SUMIFS, and
AVERAGEIFS can be used to embed IF logic
within mathematical functions.
For instance, the syntax of SUMIF is
◦ SUMIF(range, criterion, [sum range]). "Sum range" is
an optional argument that allows you to add cells in a
Example: In the Purchase Orders database, to
find the total cost of all airframe fasteners, use
=SUMIF(D4:D97,"Airframe fasteners", G4:G97)
Net Present Value (or discounted cash flow)
measures the worth of a stream of cash flows,
taking into account the time value of money.
Excel function: =NPV(rate,value1,value2,…)
◦ F is the cash flow ($)
◦ Rate (i) is the discount rate
◦ value1, value2,…are equally-spaced payments or
◦ t is a time period
=NPV(B6, C4:H4) – B5
Click the Insert function
You may type in a
description or search.
=IF(condition, value if true, value if false) – a
returns one value if the condition is true and
another if the condition is false,
=AND(condition1, condition2, …) – returns TRUE
if all conditions are true and FALSE if not,
=OR(condition1, condition2, …) – returns TRUE if
any condition is true and FALSE if not.
=IF(condition, value if true, value if false)
Conditions may include the following:
<> not equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
You may nest up to 7 IF functions, replacing the
value if false with another IF function
=IF(A8 =2,(IF(B3 =5,”YES”,“ ”)),15)
Suppose that orders with quantities of at least
10,000 units are classified as Large.
◦ Cell K4: =IF(F4>=10000, “Large”, “Small”)
Suppose that large orders with a total cost of at
least $25,000 are considered critical.
◦ Cell L4: =IF(AND(K4=“Large”, G4>=25000),“Critical”,“”)
These functions are ...
Purchase answer to see full attachment