7/19/26

Business Analytics: Methods, Models, and Decisions

Third Edition

Chapter 11
Spreadsheet Modelling
and
Analysis

Universityof Windsor

Learning Objectives
• Spreadsheet Modelling
• Model Building Strategies
• Building Models Using Influence Diagrams
• Implementing Models on Spreadsheets
• Descriptive Spreadsheet Models
• Predictive Spreadsheet Models
• Prescriptive Spreadsheet Models
• Analyzing Uncertainty with Spreadsheet Models

1
 7/19/26

Spreadsheet Modelling
• Models
Modelsare
are essential
essential to
–
– Understanding
Understanding business decision problems
–
– Predicting
Predicting the outcomes of decision alternatives
–
– Finding
Finding the best decisions

• We use Spreadsheets for building logical business models, which can evaluate future scenarios and ask
“what-if” questions to facilitate better business

• Spreadsheet
SpreadsheetModels
Modelsare
areused
usedin inall
allareas
areas of
of business
business analytics
–
– Financial
Financial analysts
analysts use it for predicting cash flow requirements
–
– Marketing
Marketing analysts
analysts use it for evaluating the impact of advertising decisions
–
– Operations
Operations personnel
personnel use it for production
production planning
planning and staffing

Universityof Windsor

Model-Building Strategies
• Creating
Creating good
good decision models requires:
– Solid understanding of business functional areas
– Knowledge of business practice and research
– Logical skills

• ItItisisbest
bestto
tostart
start simple
simple and
and enrich
enrich models
models as necessary

2
 7/19/26

Example: A Total Cost Decision Model

• Total
TotalCost
Cost(TC)
(TC)==Fixed
FixedCost
Cost (FC)
(FC) ++ Variable
Variable Cost (VC)

• Variable
VariableCost
Cost(VC)
(VC)==Unit Variable Cost (V) ×
UnitVariable × Quantity Produced (Q)

• Total
TotalCost
CostDecision
Decision Model:
Model: TC × Q)
TC = FC + (V ×

• This
Thisdescriptive
descriptivemodel
modelcan
canbe
beused
usedto
toevaluate
evaluatethe
thetotal
totalcost
cost for
for any
any input value of
the quantity produced

Universityof Windsor

Influence Diagrams
• Influence
Influence Diagram
Diagram - a visual representation of a descriptive model

• Circular symbols (nodes) are the elements of the model

• Arrows (branches) connect the nodes and show which elements influence others

• An influence diagram can assist in building a mathematical or spreadsheet model

3
 7/19/26

Example: Developing a Decision Model Using an Influence Diagram

Profit = Revenue – Cost

Revenue = Price × Quantity Sold

Cost = Fixed Cost + Variable Cost

Universityof Windsor

Example: Developing a Decision Model Using an Influence Diagram

• Profit = Revenue − Cost

P=pxmin{DQ}-F+cQ (11.5)

• Revenue is the Price x Minimum of quantity demanded or sold

• Cost is fixed cost + variable cost

P=profit p = unit price

R=revenue c = unit cost
C =cost F=fixed cost
S = quantity sold
D =demand
Q=quantity
produced

4
 7/19/26

Implementing Models on Spreadsheets

• Spreadsheets
Spreadsheetshave
havethe
theadvantage
advantageof
ofeasily
easilymodifying
modifying the
the model
model inputs
inputs and
and calculating
calculating results

• Principles
Principlesofofgood
goodspreadsheet
spreadsheet design
design
–
– InIndesigning
designing aa spreadsheet,
spreadsheet, always separate (as independent tables) the following three
• Data
Data
• Model
Modelcalculations
calculations
• Model
Modeloutputs
outputs

– Do not use input data in model formulas and reference cells that contain the data
– This way we can experiment with the model with different data without changing formulas

Universityof Windsor

Example 1: Spreadsheet Implementation of the Profit Model

• Open the Excel file Example 1: Profit Model
– Find the Profit using the Spreadsheet Profit Model
– Profit = Revenue – Cost

10

5
 7/19/26

Example 1: Spreadsheet Implementation of the Profit Model

• Open
Openthe
theExcel
Excel file
file Example
Example 1: Profit Model Model

Unit Price $ 40.00

Model Quantity Sold 40000
– Unit Price cell B13 = B5 Revenue $1,600,000.00
– Revenue cell C15 =B13*MIN(B8,B14)
– Unit cost cell B17 = B6 Unit Cost $ 24.00
– Variable Cost cell C19 = =B17*B18 Quantity Produced 40000
– Fixed Cost cell C20 = B7 Variable Cost $ 960,000.00
Fixed Cost $ 400,000.00
– Profit cell C22 = Revenue – (Variable Cost + Fixed Cost)
= C15 – (C19 + C20) Profit
$ 240,000.00

Universityof Windsor

11

Data Validation
• Data Validation is to check if data has been correctly entered in any cell

• Data Validation is an Excel tool that allows you to define acceptable input values and provides an
error alert if an invalid entry is made

12

6
 7/19/26

Example: Data Validation

• Open the Excel file Example 2: Outsourcing Decision Model

– This is a spreadsheet model for the Outsourcing Decision

– The IF function in cell B20 identifies the best decision

– If the cost difference is negative or zero, then the best decision is to “Manufacture”

– Otherwise, the best decision is to “Outsource”

Universityof Windsor

13

Data Validation
• manufacturing cost
Total manufacturing cost cell
cell B16 =B6+B7*B12
• purchase cost
Total purchase cost cell B17 =B12*B10
• Cost difference cell B19 =B16-B17
• cell B20
Best Decision cell B20 =IF(B19<=0,
=IF(B19<=0, "Manufacture",
"Manufacture", "Outsource")

Suppose we know that the unit cost of any item is between $10 and $100
– Check Data Validation for Unit Cost of the Outsourcing Decision Model

14

7
 7/19/26

Data Validation
• Select cell B10

• Data > Data Validation

• Enter the Settings, Input Message, and Enter Alert

Universityof Windsor

15

Data Validation
• Enter any values in cell B10
• If the value is not between 10 and 100,
100, an
an error
error message will pop up

This alerts you for any invalid values, set in the model

16

8
 7/19/26

Descriptive Spreadsheet Models

• Descriptive Spreadsheet Models
Models are
are like dashboards and allow one to answer questions quickly such as

– “How many resources do we need to meet estimated demand?”

– “What would our profit be under different scenarios?”

Please Note
• A useful spreadsheet model need not be complex

Universityof Windsor

17

Example 3: Staffing Models

• Staffing changes require advance planning so that HR managers can make good decisions
about overtime or
or adding
adding or reducing temporary or permanent staff

Open Excel file Example 3: Staffing Model

– The file shows data used by the manager of a loan processing department
– The manager wants to know how many employees will be needed over the next several
months to process a certain number of loan files per month

Universityof Windsor

18

9
 7/19/26

Example 3: Staffing Models

Data
– 700 loan applications are expected in May, 750 in June, 800 in July, and 825 in August
– Each employee works productively for 6.5 hours each day
– There are 22 working days in May, 20 in June, 22 in July, and 22 in August

Predict the number of full-time equivalent (FTE) staff needed each month to ensure that all loans
can be processed

Universityof Windsor

19

Example 3: Staffing Models

For the month of May
– Files/Month cell D12 =B12*$B$5
– Copy paste cell D12 to D13:D22
– Sum in cell D23

– Hours Required cell E12 =C12*D12

– Copy paste cell E12 to E13:E22
– Sum in cell E23

• FTE (Full Time Equivalent staff) required

required for
for May
May cell
cell E25
E25 =E23/(B6*B7)
=E23/(B6*B7) = 14.61

20

10
 7/19/26

Example 3: Staffing Models

Repeat the same process for the months of June, July, and August

• Please note that the spreadsheet model helps in quick calculations if there is any change in the data
(parameters)
–
– Expected number of files to be processed each month
–
– Hours worked per day
–
– Days in month

Universityof Windsor

21

Predictive Spreadsheet Models

• Predictive Spreadsheet Models
Models focus
focus on
on understanding the future

– Practical business models focus on predicting business performance such as profitability or cash
flow, customer retention, product sales, and many other key metrics

– Businesses use predictive spreadsheet models for financial planning, personal budgets, and so on

Universityof Windsor

22

11
 7/19/26

Example 4: New-Product Development

• Open
Openthe
theExcel
Excelfile
fileExample
Example 4:
4: New
New Product
Product Development

• Moore Pharmaceuticals needs to decide whether to conduct clinical trials and seek approval
for a newly developed drug

Universityof Windsor

23

Example 4: New-Product Development

• Data
Dataof
ofMoore
MoorePharmaceuticals
Pharmaceuticals
– 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%

24

12
 7/19/26

Example 4: New-Product Development

• The Excel formulas to be used in this model are Moore Pharmaceuticalsl B C 0 E F

shown in the figure 2 Data

3
6
Market size 2000000
Unit (monthly Rx) revenue 130
• Since the NPV of this scenario is positive, we Unit (monthly Rx) cost 40
Discount rate 0.09
should develop the new product 9
10 Project Costs
11 R&D 700000000
12 CEnical Trials 150000000
• However, this model is based on several 13 Total Project Costs =811+B12
14
assumptions, including 15Model
16
– Market size 17
18
Year 1
Market growth factor
2
0.03
3
0.03
4
0.03
50.03
– Market growth 19
20
Market size =85 =B191+C18=C191+D18=D191+E18=E19(1+F18
0.2 0.2 0.2
8223=22+=22+=22+
Market share growth rate 0.2
21 Market share 0.08
22 Sales =B19*B21 =C19C21 =E19E21
=D19*D21 =F19F21
If these change, the result will be different Annual Revenue =822*$8$6*12 C22*$8$6*12=D22*$8$6*12=E22*$8$6*12=F22*$8$6*12
111116
Annual Costs =B22$8$712 =C22*$B$7*12=D22*$8$7*12=E22*$B$712=F22$8$7*12
Profit=824-825 =C24-C25 =D24-D25 -E24-E25 -F24-F25

Cumulative Net Profit=B26-B13 -28+0 =C28+D26 =D28+E26 -58+56

Net Present Value=NPV(B8,B26:F26)-813

Universityof Windsor

25

Prescriptive Spreadsheet Models

• Prescriptive Spreadsheet 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

– an objective function that minimizes or maximizes some quantity of interest

– and constraints, which are limitations or restrictions imposed on any solution

Universityof Windsor

26

13
 7/19/26

Example 5: Portfolio Allocation

• An investor has $100,000 to invest in four assets
[Link]
Investment Annual Minimum Maximum
Return
[Link] insurance 5% $2,500 $5,000
[Link] mutual funds 7% $30,000 None
[Link] mutual funds 11% $15,000 None
[Link] account 4% None None

• Ihe risk
The risk factor
tactor in each porttollo is
portfolio is
Investment Risk Factor/Dollar Invested
[Link] insurance 0.5
[Link] mutual funds 1.8
[Link] mutual funds 2.1
[Link] account 0.3

Universityof Windsor

27

Example 5: Portfolio Allocation

Objective Function
• How much to invest in each asset to maximize the total expected annual return
Constraints
• Remain
Remain within
withinthe
theminimum
minimum and and maximum
maximum limits for each investment
• The
The investor
investor wants
wants to
to limit
limit the
the weighted
weighted risk
risk per dollar invested to at most 1.0
• That
That means
means the
the sum
sum product
product of risk and investment to be less than 110,000

28

14
 7/19/26

Example 5: Portfolio Allocation

• Open the Excel file Example 5: Portfolio Allocation
• Use the formula shown in the figure

Total amount Invested SUM(B16:B19)

Total weighted risk SUMPRODUCTE6:E9,B16:B19

Total expected return =SUMPRODUCTB6:B9B16:B19

Universityof Windsor

29

Example 5: Portfolio Allocation

Decision Variables
– Cell B16:B19

Constraints
– Cells B16:B19 have minimum and maximum limits defined in
C6:D9
– Total weighted risk cell B22< 110,000

– Use Data>Solver
– OK

Cells B16:B19 display the optimum investment

30

15
 7/19/26

Analyzing Uncertainty and Model Assumptions

• Predictive Analytical Models are based on assumptions about future uncertainty, which can be
handled by What-If Analysis

What-If Analysis (Data tab>What-if Analysis)

• Spreadsheet models allow what-if analysis by changing input values and recalculating model outputs

• Three Methods of What-If Analysis

– Data Tables
– Scenario Manager
– Goal Seek

31

Data Tables
• Data
DataTables
Tables summarize
summarize the impact of one or two inputs on a specified output

• For
Forexample,
example,we
wewant
wantto
toknow
knowthe
themonthly
monthlymortgage
mortgage payment
payment ifif the
the interest
interest rate
rate changes
changes

A B C D
Mortgage Loan Analysis Payments
Down Payment None $672.68
3 Interest Rate 9.50% 9.00% $643.70
Term (months) 360 9.25% $658.14
5 Loan Amount $80,000 9.50% $672.68
Input cell List of values that Excel
substitutes in the input cell,B3

!
!"#$×( )
• Monthly Mortgage
Mortgage Payment Formula = "#

[)* )+ !
! $%&!'
$%&!'
[)* )+ ]
"#

Universityof Windsor

32

16
 7/19/26

Data Tables
• Open the Excel file Example 6: Mortgage Loan Analysis
– Enter the formula for monthly payment in cell D2
=(B5*B3/12)/(1-(1+B3/12)^-B4)

– Create a range of values for the input cell that you wish to vary
– 9.00%, 9.25%, 9.50% in cells C3:C5

– Select cells C2:D5 (the range of cells that contains both the
formulas and values you want to substitute)
– Data tab>What-if Analysis>Data Table
– Enter the B3 in “Column Input Cell” and click OK

The new monthly calculations will appear in cells D3:D5

Universityof Windsor

33

Scenario Manager
• AAScenario
Scenarioisisaaset
setof
ofvalues
valuesthat
thatExcel
Excel can
can substitute
substitute automatically
–
– Create
Createand
andsave
savedifferent
differentgroups
groups of
of values
values as
as scenarios,
scenarios, and then switch between these scenarios to
view the different results

Open the Excel file Example 7: Scenario Manager

– There are three orders (small, medium and large)
– If we print 10 books, the cost is $15 per book
– If we print 100 books, the cost is $8 per book
– If we print 500 books, the cost is $5 per book
– The sale price of each book is $20
For the given scenario, generate a scenario summary showing the % profit from each order

Universityof Windsor

34

17
 7/19/26

Scenario Manager
• Data > What-If Analysis > Scenario Manager
– For small orders find the total cost cell E8 =C8*D8
– Profit cell G8 =(F8-D8)*C8
– % Profit cell H8 =G8/E8

– Click Add button to open Add Scenario dialog

– Enter scenario name: small
Scenario name:
– Changing cells C8:D8
small
Changing cells:
– Similarly add one more scenarios
– Enter scenario name: medium C8:D8
– Changing cells C8:D8

– Similarly add one more scenarios

– Enter scenario name: large
– Changing cells C8:D8

Universityof Windsor

35

Scenario Manager
• Click
ClickScenario
Scenario Summary
Summary

• The
Thescenario
scenariosummary
summarysheet
sheetshows
showsthe
thepercentage
percentage profit
profit for
for each order at a sale price of $20

• We can see the results in another scenario by changing cells shown in grey (the values of
“Number of Books” and “Cost to Print”)

Scenario Summary
Current Values: small medium large
Changing Cells:
$C$8 10 10 100 500
$D$8 15 15 8 5
Result Cells:
$H$8 33% 33% 150% 300%

Universityof Windsor

36

18
 7/19/26

Goal Seek
• If you know the result that you want from a formula but are not sure about input values, use Goal Seek
in Excel

Open the Excel file Example 8: Goal Seek

• We
Wewant
wanttotoknow
knowthe
theRevenue
Revenuefrom
from Product
Product C,C, so that the total target contribution is $150,000
• Percentage
Percentagecontribution
contribution of
of each
each product
product is
is already
already given
given to us

– Data > What-If Analysis > Goal Seek

– Set cell contains Total Contribution, cell F11
– Target Value is 150,000
– By changing cell D10

Universityof Windsor

37

Goal Seek
• We
Weget
getthe
thevalue
valuein
in cell
cell D10
D10 as
as 400,000

• Which
Whichgives
givesaatotal
totaltarget
target contribution
contribution of $150,000 (our goal)

Goal Seek

Set cell: $F$11 Goal Seeking with Cell F11

To value: 150000 found a solution

Cancel
By changing cell: $D$10
Target value: 150000
Current value: $150,000 Step
Cancel OK

Universityof Windsor

38

19