Chapter 11 - Spreadsheet Modeling and Analysis

Business Analytics: Methods, Models,
and Decisions
Third Edition
Chapter 11
Spreadsheet
Modeling and
Analysis
Copyright © 2020, 2016, 2013 Pearson Education, Inc. All Rights Reserved Slide - 1
Model-Building Strategies
• Building decision models is more of an art than a

science.
• Creating good decision models requires:
– solid understanding of business functional areas
– knowledge of business practice and research
– logical skills
• It is best to start simple and enrich models as
necessary.
Example 11.1: A Total Cost Decision
Model
• From basic business principles, we know that

– the total cost of producing a fixed volume of a
product is composed of fixed cost and
variable cost
– the variable cost depends on the unit variable
cost as well as the quantity produced
– the quantity produced is a decision option
Example 11.1 Continued
• Model development:
– Total Cost = Fixed Cost + Variable Cost (11.1)
– Variable Cost = Unit Variable Cost * Quantity
Produced (11.2)
• Therefore:
– Total Cost = Fixed Cost + Unit Variable Cost *
Quantity Produced (11.3)
• Mathematical representation
– C = Total cost
– V = Unit variable cost
– F = Fixed cost
– Q = Quantity produced
• This results in the following model:
– C = F + VQ (11.4)
Building Models Using Influence
Diagrams
• Influence diagram - a visual representation of a
descriptive model that shows how the elements of the
model influence, or relate to, others.
• The elements of the model are represented by circular
symbols called nodes. Arrows called branches connect
the nodes and show which elements influence 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.
Example 11.2: Developing a Decision
Model Using an Influence Diagram
• Cost = fixed cost + variable cost
• Revenue = price times quantity sold
• Profit = Revenue − Cost
Change equation
number to (11.5) I
don’t have the software
to edit this
Building Models Using Historical
Data
• Data used in models can come from existing

databases and other data sources, analysis of
historical data, or surveys, experiments, and
other methods of data collection.
• Statistical methods are often used to estimate
data required in predictive models.
• We might use empirical data and line-fitting
techniques to build a model.
Example 11.3: Building a Decision
Model Using Historical Data
• DTP Corporation – the
effect of media advertising
on profits
• Logarithmic trendlines
– Product 1 Profit =
49.699 + 1.1568 ln(X1)
– Product 2 Profit =
19.913 + 0.4177 ln(X2)
Total Profit = 69.612 + 1.1568 ln(X1) + 0.4177 ln(X2)
Model Assumptions, Complexity, and
Realism
• Models cannot capture every detail of a real problem.
• Validity refers to how well a model represents reality.
– Examine the assumptions made in a model to see
how they agree with our perception of the real world.
– Compare model results to observed results.
• To add more realism to a model generally requires more
complexity.
Implementing Models on
Spreadsheets
• Principles of good spreadsheet design:
– Separate the data, model calculations, and model outputs
clearly in designing a spreadsheet.
– Do not use input data in model formulas, but reference
the spreadsheet cells that contain the data. In this way, if
the data change or you want to experiment with the
model, you need not change any of the formulas, which
can easily result in errors.
Example 11.4: Spreadsheet
Implementation of the Profit Model
• Excel file Profit Model
• Profit (cell C22) = Revenue - Variable Cost - Fixed Cost = C15 - C19 -
C20
Example 11.5: Modeling Net Income
on a Spreadsheet
• Gross profit = sales − cost of goods sold
• Operating expenses = administrative expenses + selling
expenses + depreciation expenses
• Net operating income = gross profit − operating expenses
• Earnings before taxes = net operating income − interest
expense
• Net income = earnings before taxes − taxes
• Simple model:
– net income = sales − cost of goods sold − administrative expenses
− selling expenses − depreciation expenses − interest expense −
taxes
• Data-model format
• Pro forma income statement
Spreadsheet Quality
• Building spreadsheet models (called spreadsheet
engineering) is part art and part science.
• Spreadsheets need to be
– accurate,
– understandable, and
– user friendly.
• Verification is the process of ensuring that a model is
accurate and free from logical errors.
Improving Spreadsheet Quality
• Improve the design and format of the spreadsheet itself.
– Sketch a logical design of the spreadsheet.
– Break complex formulas into smaller pieces
– Design the spreadsheet in a form that the end user can easily
interpret and understand
• Improve the process used to develop a spreadsheet.

– Work on one part at a time
– Check formula results with simple numbers
• Inspect your results carefully and use appropriate tools
available in Excel.
– Use Excel auditing tools
Data Validation
• An Excel tool that allows you to define

acceptable input values in a spreadsheet and
provides an error alert if an invalid entry is made.
– Data > Data Tools
– Select the cell range, click on Data Validation.
– Specify the criteria that Excel will use to flag
invalid data.
Example 11.6: Using Data Validation
• Outsourcing Decision Model
• Suppose that we know that the unit cost of any item is at
least $10 but no more than $100.
• Data Validation dialog Error Alert
Descriptive Spreadsheet Models
• Descriptive models allow one to answer

questions such as
– “How many resources do we need to meet
estimated demand?”
– “What would our profit be under different
scenarios?”
• A useful spreadsheet model need not be
complex!
Staffing Models
• Staffing changes can be expensive and time-

consuming.
• Advance planning is vital so that managers can
make good decisions about overtime or
reductions in work hours, or adding or reducing
temporary or permanent staff.
Example 11.7: A Predictive Model for
Staffing
• The manager of a loan processing department Products Product Hours Per
wants to know how many employees will be Mix File
needed over the next several months to Product 1 22% 3.50
process a certain number of loan files per Product 2 17% 2.00
month so she can better plan capacity. Product 3 13% 1.50
• Different types of products, such as a 30-year Product 4 12% 5.50
fixed rate mortgage, 7/1 ARM, FHA loan, or a Product 5 9% 4.00
construction loan, each varying in complexity Product 6 9% 3.00
and time to complete. Product 7 6% 2.00
• Data: Product 8 5% 2.00
– Forecasts: 700 loan applications in May, 750 in June, Product 9 3% 1.50
800 in July, and 825 in August.
Product 10 1% 3.50
– Each employee works productively for 6.5 hours each
Misc 3% 3.00
day, and there are 22 working days in May, 20 in
June, 22 in July, and 22 in August. Total 100% Blank
Predict the number of full time equivalent (FTE) staff needed each month to
ensure that all loans can be processed.
• Spreadsheet Model
Single-Period Purchase Decisions
• One-time purchase decisions often must be made in the
face of uncertain demand.
• Newsvendor Problem: How many newspapers to
purchase each day?
– C = cost to purchase a newspaper
– Q = number of newspapers the vendor purchases
– D = number of newspapers demanded
– R = revenue from selling a newspaper
– S = salvage value of unsold newspapers
Example 11.8: A Single-Period
Purchase Decision Model
• A small candy store makes Valentine’s Day gift boxes that
cost $12 and sell for $18.
• In the past, at least 40 boxes have sold by Valentine’s Day
but the actual amount is unknown.
• After the holiday, boxes are discounted 50%.
• C = $12, R = $18, S = $9
•
– Note that D is actually uncertain and can be modeled using a

probability distribution.
• Spreadsheet model
Overbooking Decisions
• Find the number of reservations to accept to effectively fill
capacity knowing that some customers may not use their
reservations.
• A common practice in these industries is to overbook
reservations.
– When more customers arrive than can be handled, the business
usually incurs some cost to satisfy them.
– Therefore, the decision becomes how much to overbook to
balance the costs of overbooking against the lost revenue for
underuse.
Example 11.9: A Hotel Overbooking
Model
• A popular resort hotel has 300 rooms.
• The room rate is $120 per night.
• Reservations can be cancelled by 6:00 p.m.
• Cost of overbooking is $100 per occurrence.
• Decision variable = number of reservations to accept.
• Customer demand and cancellations are actually random variables.
Example 11.10: Modeling Retail
Markdown Pricing Decisions
• In the spring, a department store introduces a new line of bathing suits
that sell for $70.
• The store purchases 1000 of these bathing suits.
• During the prime selling season (50 days), the store sells an average
of 7 units per day at full price.
• Around July 4th, the price is marked down 70% to sell off remaining
inventory.
• During a special sale, the price was reduced to $49 and sales
increased to 32.2 units per day.
• If the stored decides to sell at full retail price for x days and then
discounts the price by y% for the remainder of the selling season,
followed by the clearance sale, what total revenue could they predict?
• Modeling daily sales as a function of price
– Assume a linear trend model between sales and price:
• Modeling total revenue
1. Sell at full retail price for x days:
– Full retail price revenue =
2. Markdown revenue =
– Markdown price =
– Daily sales =
– Units sold at markdown = as long as this is less than or
equal to the number of units remaining in inventory from full retail sales.
3. Clearance revenue
– Clearance inventory
– Clearance price revenue
• Total revenue = sum of full retail, markdown, and clearance revenues

• Spreadsheet model
Predictive Spreadsheet Models
• Predictive models focus on understanding the

future.
– Practical business models focus on predicting
financial performance such as profitability or
cash flow, customer retention, product sales,
and many other key metrics.
– Individuals often use spreadsheet models for
financial planning, personal budgets, and so
on.
Example 11.11: New-Product
Development
• Moore Pharmaceuticals needs to decide whether to
conduct clinical trials and seek FDA approval for a newly
developed drug.
– R&D cost = $700 million
– Clinical trials cost = $150 million
– Market size = 2 million people
– Market size growth = 3% per year
– Market share = 8%
– Market share growth = 20% per year
– Monthly revenue/prescription = $130
– Monthly variable cost/prescription = $40
– Discount rate = 9%
• Spreadsheet Model
• Spreadsheet Formulas
Cash Budgeting
• Cash budgeting is the process of projecting and

summarizing a company’s cash inflows and
outflows expected during a planning horizon,
usually 6 to 12 months.
Example 11.12: A Cash Budget Model
• Excel file Cash Budget Model
Retirement Planning
• Understanding the impact of compounding on

investment growth, saving at an early age, and
selecting the right portfolio of investments is key
to a successful financial future.
Example 11.13: A Retirement-Planning
Model
• Starting salary $80,000; age 24, wants to retire at age 60.
• Company match of 5% of pre-tax salary for 401(k)
• Salary increases 4% per year.
• Contribute 15% of pre-tax income to 401(k) account up to
$18,000.
• Invest in Vanguard Balanced Index Fund, earning 6% per
year and Boston Trust Asset Management Fund earning
6.5% per year.
• Individuals under age 55 can contribute $5,500 to their
Roth IRA annually; and individuals 55 and over can
contribute $6,500 into a Roth IRA.
• Retirement Planning Model assumptions

– annual salary increase and returns on investment will be the
same each year
– annual returns assume that the investment return is applied to
the previous year’s balance and not to the current year’s
contributions
Project Management
• Project management is concerned with scheduling the
activities of a project involving interrelated activities.
• The longest path through the network defines the
minimum project completion time and is called the
critical path.
– Compute the earliest time that each activity can start and the
earliest time it can finish. The earliest time that the last activity
can finish is the minimum project completion time.
– The difference between the latest finish time and earliest finish
time is called the slack. If the slack of an activity is zero, then it
is on the critical path.
Example 11.14: A Project
Management Spreadsheet Model
• Becker Consulting Project Management Model
Prescriptive Spreadsheet Models
• Prescriptive models are often called optimization models.
• An optimization model is most often formulated
mathematically and specifies
– a set of decision variables, numerical quantities that
represent the decision options from which to choose;
– an objective function that minimizes or maximizes
some quantity of interest, profit, revenue, cost, time,
and so on;
– and constraints, which are limitations, requirements,
or other restrictions that are imposed on any solution.
Portfolio Allocation
• Selecting investment portfolios is fundamentally

a trade-off between return and risk. A major
source of uncertainty is the annual return of each
asset, leaving the decision maker with the risk of
not achieving a desired return.
– Other risks include unanticipated changes in
inflation or industrial production, the spread
between high- and low-grade bonds, and the
spread between long- and short-term interest
rates.
Example 11.15: A Portfolio Allocation
Model
• An investor has $100,000 to invest in four assets.
• Target level for the weighted risk factor: weighted risk per
dollar invested of at most 1.0.
– Then the weighted risk for a $100,000 total
investment will be limited to 100,000.
• The decision problem is to determine how much to invest
in each asset to maximize the total expected annual
return, remain within the minimum and maximum limits
for each investment, and meet the limitation on the
weighted risk.
Locating Central Facilities
• A common problem in designing service systems is to locate a facility in a
“central” location with respect to other facilities to minimize some measure
of distance from the central location to each of the other facilities.
• Distance measures:
Change (A2)
to (11.7) and
(A3) to
(11.8) I
don’t have
the software
Straight line (Euclidean) distance is the hypotenuse of the triangle. to edit this.
straight-line distance between
Rectilinear distance is the sum of the left and bottom sides of the triangle.
rectilinear distance between
Example 11.16: Finding the Best
Location for a Medical Laboratory
• A medical testing laboratory collects blood samples from 5
local hospitals. Managers want to determine the best
location for a new testing facility, taking into consideration
both distance and number of trips per month.
Hospital
Location X-Coordinate Y-Coordinate Trips/month
1 0 0 5
2 20 80 25
3 60 30 20
4 100 100 35
5 70 110 15
• Distance between each hospital and the new facility
is assumed to be straight line.
• Define as the coordinates of hospital i
as the coordinates of the laboratory
Hospital
Location X-Coordinate Y-Coordinate Trips/month
1 0 0 5
2 20 80 25
3 60 30 20
4 100 100 35
5 70 110 15
Job Sequencing
• Job-sequencing problems involve finding an optimal
sequence by which to process jobs.
• Lateness is the difference between completion
time and due date Change (A.10) to
(11.9) and (A.11) to
(11.10). I can’t edit
this
• Tardiness is the amount of time by which

completion time exceeds due date:
Sequencing Rules
• Shortest processing time (SPT) sequencing of
jobs minimizes the average completion time for all
jobs.
• Earliest due date (EDD) sequencing of jobs
minimizes the maximum number of tardy jobs.
• Other criteria such as average tardiness, total
tardiness, or total lateness are also of interest.
Example 11.17: A Spreadsheet Model
for Job Sequencing
• A custom manufacturing company has 10 jobs
waiting to be processed.
• Processing times and due dates are shown below.
Job 1 2 3 4 5 6 7 8 9 10
Time 8 7 6 4 10 8 10 5 9 5
Due date 20 27 39 28 23 40 25 35 29 30
• A sequence of integers for the job ordering is

called a permutation.
• The goal is to find the permutation that optimizes
the chosen criteria.
Analyzing Uncertainty and Model
Assumptions
• Predictive analytical models are based on
assumptions about the future and incorporate
variables that most likely will not be known with
certainty.
• It is usually important to investigate how these
assumptions and uncertainty affect the model
outputs.
What-If Analysis
• Spreadsheet models allow you to easily evaluate
what-if questions by changing input values and
recalculating model outputs.
• Systematic approaches to what-if analysis that are
available in Excel make the process easier and
more useful.
– Data tables
– Scenario Manager
– Goal Seek
Example 11.18: Using Excel for What-
If Analysis
• Uncertain demand in the Profit Model
• Excel table using formulas:
Visualization of
profit:
Data Tables
• Data Tables summarize the impact of one or two
inputs on a specified output.
– A one-way data table evaluates an output variable
over a range of values for a single input variable.
– Two-way data tables evaluate an output variable over
a range of values for two different input variables.
Creating One-Way (Column) Data
Tables in Excel
• Create a range of values for some input cell in your model that
you wish to vary.
• Enter the cell reference for the output variable in your model
that you wish to evaluate in the row above the first value and
one cell to the right of the column of input values.
• Select the range of cells that contains both the formulas and
values you want to substitute.
•
• Type the cell reference for the input
cell in your model in the Column input
cell box.
Example 11.19: A One-Way Data
Table for Uncertain Demand
1. Create a column of demand values
(column E).
2. Enter =C22 in cell F3 (to reference

the output cell).
3. Highlight the range
4.
5. Enter B8 for Column input cell in the

Data Table dialog (tells Excel that
column E refers to demand)
Example 11.20: One-Way Data Tables
with Multiple Outputs
• Create a second output column for
revenue.
• Enter =C15 into cell G3.
• Highlight
•
• Enter B8 for Column input cell in the
Data Table dialog.
Two-Way Data Tables
• Type a list of values for one input variable in a column and a list of
input values for the second input variable in a row, starting one row
above and one column to the right of the column list.
• In the cell in the upper left-hand corner immediately above the column
list and to the left of the row list, enter the cell reference of the output
variable you wish to evaluate.
• Select the range of cells that contain this cell reference and both the
row and column of values.
• In the Row input cell of the dialog box, enter the reference for the input
cell in the model that corresponds to the input values in the row.
• In the Column input cell box, enter the reference for the input cell in the
model that corresponds to the input values in the column.
Example 11.21: A Two-Way Data
Table for the Profit Model
• Evaluate the impact of both unit
price and unit cost.
• Create a column of unit prices.
• Create a row of unit costs.
• Enter =C22 in upper left corner.
• Select range of data (not
including headings).
• In Data Table dialog
– Enter B6 for Row input cell.
– Enter B5 for Column input cell.
Scenario Manager
• Allows creation of scenarios – sets of
values that are saved and can be
substituted in worksheets.
•
• Click Add button to open Add Scenario

dialog.
• Enter a Scenario Name.
• In Changing Cells box, enter cell
references to include in scenario.
• In the Scenario Values dialog enter
values for each of the changing cells.
• Click Show button to display the results.
Example 11.22: Using the Scenario
Manager for the Markdown Pricing Model
• Evaluate 4 strategies for pricing and discounts on the
bathing suits in the Markdown Pricing Model.
Goal Seek
• If you know the result that you want from a formula but are not
sure what input value the formula needs to get that result, use
the Goal Seek feature in Excel.
•
• Set cell contains the cell reference for the
formula you want to resolve.
• To value is the target value you want the
formula to return.
• By changing cell is the reference of the input
value that you want to change to achieve the
set cell value.
Example 11.23: Finding the Break-
Even Point in the Outsourcing Model
• Find the value of demand
for which manufacturing
cost equals purchased
cost.
– Set cell: B19
– To value: 0
– By changing cell: B12.

Chapter 11 - Spreadsheet Modeling and Analysis

Uploaded by

Copyright:

Available Formats

Chapter 11 - Spreadsheet Modeling and Analysis

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter 11 - Spreadsheet Modeling and Analysis

Uploaded by

Copyright:

Available Formats

Business Analytics: Methods, Models,

• Building decision models is more of an art than a

• From basic business principles, we know that

• Revenue = price times quantity sold

• Profit = Revenue − Cost

• Data used in models can come from existing

Total Profit = 69.612 + 1.1568 ln(X1) + 0.4177 ln(X2)

• Improve the process used to develop a spreadsheet.

• An Excel tool that allows you to define

• Descriptive models allow one to answer

• Staffing changes can be expensive and time-

– Note that D is actually uncertain and can be modeled using a

– Clearance price revenue

• Total revenue = sum of full retail, markdown, and clearance revenues

• Predictive models focus on understanding the

• Cash budgeting is the process of projecting and

• Understanding the impact of compounding on

• Retirement Planning Model assumptions

• Selecting investment portfolios is fundamentally

straight-line distance between

• Tardiness is the amount of time by which

• A sequence of integers for the job ordering is

2. Enter =C22 in cell F3 (to reference

5. Enter B8 for Column input cell in the

• Click Add button to open Add Scenario

You might also like