CHAPTER 8
SPREADSHEET MODELS
Introduction 2
u Spreadsheet models are mathematical and logic-based
models
u Referred to as what-if models
u Provide easy-to-use, sophisticated mathematical and
logical functions
u Allow for easy instantaneous recalculation for a change in
model inputs
u Are less expensive
u Often come preloaded on computers
u Are fairly easy to use
u The most used business analytics tool
Building Good
Spreadsheet Models
INFLUENCE DIAGRAMS
BUILDING A MATHEMATICAL MODEL
SPREADSHEET DESIGN AND IMPLEMENTING THE MODEL IN A SPREADSHEET
Building Good Spreadsheet 4
Models
u Total cost of manufacturing a product is the sum of two
costs:
u Fixed cost: Portion of the total cost that does not depend on the
production quantity and remains the same no matter how much
is produced
u Variable cost: Portion of the total cost that is dependent on and
varies with the production quantity
u Make-versus-buy decision: comparing the costs of
manufacturing in-house to the costs of outsourcing
production to another firm
Building Good Spreadsheet 5
Models
• Example:
• Nowlin Plastics produces a line of cell phone covers.
Nowlin’s best-selling cover is its Viper model, a slim but
very durable black and gray plastic cover. The annual
fixed cost for the Viper cover is $234,000.
• This fixed cost includes management time, advertising,
and other costs that are incurred regardless of the
number of units eventually produced.
• In addition, the total variable cost, including labor and
material costs, is $2 for each unit produced.
Building Good Spreadsheet 6
Models
Influence Diagrams
u An influence diagram is a visual representation
that shows which entities influence others in a
model
u Parts of the model are represented by circular or
oval symbols called nodes, and arrows
connecting the nodes show influence
Figure 8.1: An Influence Diagram 7
for Nowlin’s Manufacturing Cost
Figure 8.2: An Influence Diagram for
8
Comparing Manufacturing Versus
Outsourcing Cost for Nowlin Plastics
Building Good Spreadsheet 9
Models
Building a Mathematical Model
u Consider the cost of manufacturing the required units of
the Viper
u As the influence diagram shows, this cost is a function of
the fixed cost, the variable cost per unit, and the
quantity required
u Define notation for every node in the influence diagram:
u q = quantity (number of units) required
u FC = the fixed cost of manufacturing
u VC = the per-unit variable cost of manufacturing
u TMC(q) = total cost to manufacture q units
Building Good Spreadsheet 10
Models
u The cost-volume model for producing q units is
TMC(q) = FC + (VC × q)
u For the Viper, FC = $234,000 and VC = $2, so
TMC(q) = $234,000 + $2q
u Mathematical model for purchasing q units is
TPC(q) = Pq
P = the per unit purchase cost
TPC(q) = the total cost to outsource or purchase q units
u For the Viper, since P = $3.50, TPC(q) = 3.5q
Building Good Spreadsheet 11
Models
u Mathematical model for the savings associated
with outsourcing
S(q) = TMC(q) – TPC(q)
S(q) = the savings due to outsourcing
u Nowlin has to decide, “For what quantities is it
more cost-effective to outsource rather than
produce the Viper?”
u Mathematically, this question is, “For what values
of q is S(q) > 0?”
Building Good Spreadsheet 12
Models
Spreadsheet Design and Implementing the Model in a
Spreadsheet
u For the Nowlin Plastics problem, we have defined the
following components:
q, FC, VC, TMC(q), P, TPC(q), S(q)
u TMC, TPC, and S are the functions of other components,
whereas q, FC, VC, and P are not
u TMC, TPC, and S will be formulas involving other cells in
the spreadsheet model, whereas q, FC, VC, and P will just
be entries in the spreadsheet
Building Good Spreadsheet 13
Models
u The number of Vipers to make or buy for next year is
really a decision Nowlin gets to make, hence we refer
to quantity q as a decision variable
u FC, VC, and P are measurable factors that define
characteristics of the process we are modelling, hence
we refer to FC, VC, and P as parameters
Figure 8.3: Nowlin Plastics Make-
14
Versus-Buy Spreadsheet Model
Building Good Spreadsheet 15
Models
u The general principles of spreadsheet model design and
construction are:
u Separate the parameters from the model: This enables
the user to update the model parameters without the
risk of mistakenly creating an error in a formula
u Document the model and use proper formatting and
color as needed: A good spreadsheet model is well
documented. Clear labels and proper formatting and
alignment facilitate navigation and understanding
u Use simple formulas: Clear, simple formulas can
reduce errors and make maintaining the spreadsheet
easier. Long and complex calculations should be
divided into several cells
What-If Analysis
DATA TABLES
GOAL SEEK
What-If Analysis 17
Data Tables
u Data Table: Excel tool which quantifies the
impact of changing the value of a specific
input on an output of interest
u One-way data table: summarizes a single
input’s impact on the output
u Two-way data table: summarizes two inputs’
impact on the output
Figure 8.4: The Input for Constructing
18 a
One-Way Data Table for Nowlin Plastics
ten cot nay la saving
Figure 8.5 Results of One-Way 19
Data Table for Nowlin Plastics
20
Figure 8.6: The Input for Constructing a
Two-Way Data Table for Nowlin Plastics
outsourcing cost per unit
2 cai mau hong
la saving
Quantity
Figure 8.7: Results of Two-Way Data
21
Table for Nowlin Plastics
What-If Analysis 22
Goal Seek
u Goal Seek: Excel tool that allows the user to
determine the value of an input cell that will
cause the value of a related output cell to
equal some specified value (the goal)
u In the case of Nowlin Plastics, suppose we
want to know the value of the quantity of
Vipers where it becomes more cost effective
to manufacture rather than outsource
Figure 8.8: Goal Seek Dialog Box
23
for Nowlin Plastics
24
Figure 8.9: Results from Goal Seek for
Nowlin Plastics
Some Useful Excel
Functions for Modeling
SUM AND SUMPRODUCT
IF AND COUNTIF
VLOOKUP
Some Useful Excel Functions for Modeling26
SUM and SUMPRODUCT
u SUM: Function that adds up all of the numbers in a range of cells
u SUMPRODUCT: Function that returns the sum of the products of
elements in a set of arrays
Figure 8.10: What-If Model for 27
Foster Generators
28
Some Useful Excel Functions for Modeling
IF and COUNTIF
u =IF(condition, result if condition is true, result if condition is false)
u =COUNTIF(range, condition)
u Counts the number of components having a positive order
quantity
Illustration:
u Gambrell Manufacturing produces car stereos
u Gambrell likes to keep its components inventory to a minimum
u Hence, it uses an inventory policy known as an order-up-to
policy
u Order-up-to policy: Whenever the inventory on hand drops
below a certain level, enough units are ordered to return the
inventory to that predetermined level
Figure 8.11: Gambrell Manufacturing
29
Component Ordering Model
30
Some Useful Excel Functions for Modeling
VLOOKUP
u This function allows the user to pull a subset of data
from a larger table of data based on some criterion
u General form =VLOOKUP(value, table, index, range)
where,
value = the value to search for in the first column
of the table
table = the cell range containing the table
index = the column in the table containing the
value to be returned
range = TRUE if looking for the first approximate
match of value and FALSE if looking for an
exact match of value
Figure 8.12: Granite Insurance Bonus Model
31
Auditing Spreadsheet
Models
TRACE PRECEDENTS AND DEPENDENTS
SHOW FORMULAS
EVALUATE FORMULAS
ERROR CHECKING
WATCH WINDOW
Auditing Spreadsheet Models 33
u Excel contains a variety of tools to assist you in the
development and debugging of spreadsheet models
u These tools are found in the Formula Auditing group of the
Formulas tab
Auditing Spreadsheet Models 34
Trace Precedents and Dependents
u Trace Precedents button: After selecting cells, this button
creates arrows pointing to the selected cell from cells that
are part of the formula in that cell
u Trace Dependents button: Shows arrows pointing from the
selected cell to cells that depend on the selected cell
u Both of the tools are excellent for quickly ascertaining how
parts of a model are linked
Figure 8.13: The Formula Auditing Group
Figure 8.14: Trace Precedents for Foster 36
Generator
Figure 8.15: Trace Dependents for37
the Foster Generators Model
Auditing Spreadsheet Models 38
Show Formulas
u To see the formulas in a worksheet, simply click on any cell
in the worksheet and then click on Show Formulas—you will
see the formulas residing in that worksheet
u To revert to hiding the formulas, click again on the Show
Formulas button
Auditing Spreadsheet Models 39
Evaluate Formulas
u The Evaluate Formulas button allows you to
investigate the calculations of a cell in great
detail
u Provides an excellent means of identifying the
exact location of an error in a formula
Figure 8.16: The Evaluate Formula Dialog
Box for Gambrell Manufacturing
Figure 8.17: The Evaluate Formula Dialog 41
Box for Gambrell Manufacturing Cell B17
after Four Clicks of the Evaluate Button
Auditing Spreadsheet Models 42
Error Checking
u The Error Checking button provides an automatic
means of checking for mathematical errors within
formulas of a worksheet
u Clicking on the Error Checking button causes
Excel to check every formula in the sheet for
calculation errors
u If an error is found, the Error Checking dialog box
appears
Figure 8.18: The Error Checking Dialog 43
Box for a Division by Zero Error
Auditing Spreadsheet Models 44
Watch Window
u The Watch Window, located in the Formula
Auditing group, allows the user to observe the
values of cells included in the Watch Window box
list
u Useful for large models when not all of the model
is observable on the screen or when multiple
worksheets are used
Figure 8.19: The Watch Window for Cell
45
B17 of the Gambrell Manufacturing
Model