4
Learning Objectives
Modeling
➢ Basics of Modeling
➢ Uncertainty (Scenario) Analysis
Excel Related Always try to start with
➢ IF, MIN, MAX an easy example to make
things clearer
➢ Data Tables
➢ One way and Two-way
➢ Goal Seek
➢ Index and Match functions combination
➢ Sumproduct Function
5
Model vs. Modeling
6
Agenda
Introduction to modeling
What is modeling?
How to build a model?
Demonstration using the NCAA T-shirt example
Problem set-up
7
What is a Mathematical Model?
A mathematical model is a quantitative representation of a
real problem
Mathematical expressions
Series of interrelated cells in a spreadsheet
8
What is Modeling?
• Modeling is a process where you abstract the essence of a
real problem into a model or spreadsheet
Formulation
Real world Mathematical
Problem Model
Arrival rate Service rate
A /min S /min
Average wait time 𝑨
𝑾=
in queue?* 𝑺(𝑨 − 𝑺)
*You are not responsible from this queuing model and waiting time expression, this is just an illustration.
9
The 7-step modeling process
• Modeling is a process where you abstract the essence
of a real problem into a model or spreadsheet
Problem Data Model Model Optimize Model Model
Definition Collection Develop verifi- & make communi- Implemen-
Objectives Estimate -ment cation decisions cation tation
parameters Choose the Model and
Inputs Tractable Accurate Implement and
(i.e. input data) decision or recommendatio
Analytical represent monitor
Decision Model ation of strategy ns to
variables reality management
or
Outputs
Simulation
Constraints
or Mixed
Likely feedback loops
10
Advantage of Mathematical Model
Enables managers to understand the problem better.
Constraints? Assumptions?
Generalization.
Allow analysts to apply mathematical tools to come up
with key insights and action items.
Arrival rate Service rate
A /min S /min
11
Basic Modeling Concepts
Inputs
Given or estimated, often fixed (for modeling purposes), values
e.g. fixed cost, unit variable cost, arrival rate, budget, expected demand
Decision Variables
What the decision maker has control over
e.g. How many staffs to hire? Which project to invest in? How many
unites to produce/order? How much to spend on advertising
Outputs
Values of interest that drive decision
Function of inputs & decision variables
e.g. wait time, profit, NPV, market share
What is the objective (function)? 12
Model
Given or estimated data
e.g. Costs, revenues, expected demand, etc.
Inputs
Sensitivity Analysis
Want to
increase
Want to decide on
Outputs
or
decrease
Profit
Decision
Variables
Order Size
Optimize
13
Modeling: Simplified View
𝑜𝑢𝑡𝑝𝑢𝑡 = 𝑓 𝑖𝑛𝑝𝑢𝑡𝑠, 𝑑𝑒𝑐𝑖𝑠𝑖𝑜𝑛 𝑣𝑎𝑟𝑖𝑎𝑏𝑙𝑒𝑠
𝑝𝑟𝑜𝑓𝑖𝑡 = 𝑓(𝑝𝑟𝑖𝑐𝑒, 𝑑𝑒𝑚𝑎𝑛𝑑, 𝑢𝑛𝑖𝑡 𝑐𝑜𝑠𝑡, 𝑓𝑖𝑥𝑒𝑑 𝑐𝑜𝑠𝑡, 𝑜𝑟𝑑𝑒𝑟 𝑠𝑖𝑧𝑒)
𝑝𝑟𝑜𝑓𝑖𝑡 = 𝑝𝑟𝑖𝑐𝑒 ∗ 𝑑𝑒𝑚𝑎𝑛𝑑 − 𝑢𝑛𝑖𝑡 𝑐𝑜𝑠𝑡 ∗ 𝑜𝑟𝑑𝑒𝑟 𝑠𝑖𝑧𝑒 − 𝑓𝑖𝑥𝑒𝑑 𝑐𝑜𝑠𝑡
𝑝𝑟𝑜𝑓𝑖𝑡 = 𝑓 𝑃, 𝐷, 𝑈𝐶, 𝐹𝐶, 𝑄 = 𝑓(𝑄) = 𝑃 ∗ 𝐷 − 𝑈𝐶 ∗ 𝑄 − 𝐹𝐶
Y=f(X)
profit=f(order size)
Do not be scared by the fact that we
have parameters:
P, D, UC, FC,Q.
They are just changing numbers!
14
Spreadsheet Modeling
Spreadsheet modeling is
the process of entering the inputs and decision variables
into a spreadsheet,
relating them appropriately, by means of formulas, to
obtain the outputs
15
Spreadsheet Modeling Best Practices
Clear, logical layout
Separation of model components
Inputs distinct from outputs
Clear headings
Text formatting, colors, …
Documentation
Cell comments, text boxes, …
Range names, etc.
16
Example: Selling NCAA T-Shirts
Selling
NCAA (National Collegiate Athletic Association)
T-Shirts
17
Selling NCAA T-Shirts – 1*
It is March, and the annual NCAA Basketball
Tournament is down to the final 4 teams. Randy
Kitchell is a t-shirt vendor who plans to order t-
shirts with the names of the final 4 teams from a
manufacturer and then sell them to the fans. The
fixed cost of any order is $750, the variable cost per
t-shirt to Randy is $6, and Randy’s selling price is
$10.
*adapted from the following reference books:
Business Analytics: Data Analysis & Decision Making, S. Christian Albright, Wayne L. Winston
Practical Management Science by Wayne L. Winston, S. Christian Albright
18
Selling NCAA T-Shirts - 2
However, this price will be charged only until a week
after the tournament. After that time, Randy figures
that interest in the t-shirts will be low, so he plans to
sell all remaining t-shirts, if any, at $4 each (i.e.,
discounted price). He assumes that all remaining t-
shirts, if any, will be sold due to discounted price. His
best guess is that demand for the t-shirts during the
full-price period will be 1200.
He is thinking about ordering 1400 t-shirts, but he
wants to build a spreadsheet model that will let him
experiment with the uncertain demand and his order
quantity. How should he proceed?
19
Summary: Selling NCAA T-Shirts
The fixed cost: $750 per order
The variable cost: $6 per shirt
Selling price (during tournament): $10 per shirt
Selling price (after tournament): $4 per shirt
The expected demand at full price: 1200 shirts
He is thinking about ordering 1400 t-shirts
… is that a good idea?
20
Inputs, Outputs, Decision Variables
Inputs (Given or estimated)
Fixed Order Cost (FC) : $750
T-shirt Cost (VC) : $6
We have to forecast it; I
Demand (D) : 1200 (?) (Expected)
have no control over it
Initial Price (P1) : $10
Close-out Price (P2) : $4 (discounted price)
Decision Variable We assume that all
remaining t-shirts, if any,
Number of shirts to order (Q) will be sold at the
discounted price
Output
Profit
21
Inputs, Outputs, Decision Variables
Output: 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑅𝑒𝑣𝑒𝑛𝑢𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠
Two streams of revenues:
revenue during the tournament
revenue from leftover T-shirts
22
Inputs, Outputs, Decision Variables
Output: 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑅𝑒𝑣𝑒𝑛𝑢𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠
Revenue during the tournament
Note that Randy cannot sell more than what he has ordered
(Q). He cannot sell more than demand either (D)
Revenue during the tournament = 𝑃1 ∗ 𝑖𝑓(𝑄 > 𝐷, 𝐷, 𝑄)
𝑃1 ∗ min(𝑄, 𝐷)
𝑄1 = 𝑞𝑢𝑎𝑛𝑡𝑖𝑡𝑦 𝑠𝑜𝑙𝑑 𝑏𝑒𝑓𝑜𝑟𝑒/𝑑𝑢𝑟𝑖𝑛𝑔 𝑡𝑜𝑢𝑟𝑛𝑎𝑚𝑒𝑛𝑡
𝑖𝑓 𝑄 > 𝐷, 𝑡ℎ𝑒𝑛 𝑄1 = 𝐷
𝑖𝑓 𝐷 ≥ 𝑄, 𝑡ℎ𝑒𝑛 𝑄1 = 𝑄
Note: If(statement to check, output if yes, output if no)
23
Inputs, Outputs, Decision Variables
Output: 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑅𝑒𝑣𝑒𝑛𝑢𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠
Revenue from leftover T-shirts
Note that this revenue is obtained only when there are leftover
t-shirts; i.e. order size is greater than demand (Q>D).
Revenue after the tournament = 𝑃2 ∗ 𝑖𝑓 (𝑄 > 𝐷, 𝑄 − 𝐷, 0)
𝑃2 ∗ max(𝑄 − 𝐷, 0)
𝑄2 = 𝑞𝑢𝑎𝑛𝑡𝑖𝑡𝑦 𝑠𝑜𝑙𝑑 𝑎𝑓𝑡𝑒𝑟 𝑡𝑜𝑢𝑟𝑛𝑎𝑚𝑒𝑛𝑡
𝑖𝑓 𝑄 − 𝐷 > 0, 𝑡ℎ𝑒𝑛 𝑄2 = 𝑄 − 𝐷
𝑖𝑓 0 ≥ 𝑄 − 𝐷, 𝑡ℎ𝑒𝑛 𝑄2 = 0
Note: If(statement to check, output if yes, output if no)
24
Inputs, Outputs, Decision Variables
Output: 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑅𝑒𝑣𝑒𝑛𝑢𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠
Costs:
Fixed cost, FC
Variable cost, VC, per item
Profit:
Profit = P1 * min(D, Q) + P2 * max(Q - D, 0) - VC * Q - FC
25
Simplified View
Period 1 Period 2
At full price P1 Close out price P2
During Tournament After Tournament
• If Q>D Amount of Sales: D • If Q>D Leftover: Q-D
• If Q≤D Amount of Sales: Q • If Q≤D Leftover: zero
=IF(Q>D, D, Q) =IF(Q>D,Q-D, 0)
=IF(Q≤D,Q, D) =IF(Q≤D, 0, Q-D)
=MIN(Q,D) =MAX(Q-D, 0)
26
Simplified View
Take
Period 1 Period 2
Q=1400
D=1200
At full price P1 Close out price P2
During Tournament After Tournament
• If Q>D Amount of Sales: D • If Q>D Leftover: Q-D
• If Q≤D Amount of Sales: Q • If Q≤D Leftover: zero
True, returns D=1200 True, returns Q-D=200
=IF(Q>D, D, Q) =IF(Q>D,Q-D, 0)
False, returns D=1200 False, returns Q-D=200
=IF(Q≤D, Q, D) =IF(Q≤D, 0, Q-D)
Min (1400,1200)=1200 Max (200,0)=200
=MIN(Q,D) =MAX(Q-D, 0)
27
Simplified View
Take
Period 1 Period 2
Q=1000
D=1200
At full price P1 Close out price P2
During Tournament After Tournament
• If Q>D Amount of Sales: D • If Q>D Leftover: Q-D
• If Q≤D Amount of Sales: Q • If Q≤D Leftover: zero
False, returns Q=1000 False, returns zero
=IF(Q>D, D, Q) =IF(Q>D,Q-D, 0)
True, returns Q=1000 True, returns zero
=IF(Q≤D,Q, D) =IF(Q≤D, 0, Q-D)
Min (1000,1200)=1000 Max (-200,0)=0
=MIN(Q,D) =MAX(Q-D, 0)
28
Key Takeaways
Key components of a model
Input, what do we know?
Decision variables, what do we have control over?
Outputs, what do we want to achieve?
Best practices in Excel modeling
Clean layout
Clear titles
Define names
29
30
Agenda
Demonstration using the NCAA T-shirt example
Profit function
Define names
Sensitivity analysis: 1-way, 2-way tables
Break-even analysis
31
Selling NCAA T-Shirts
Randy Kitchell is an NCAA t-shirt vendor
The fixed cost of any order is $750, the variable cost is $6
per shirt
Randy’s selling price is $10 per shirt until a week after
the tournament when it will drop to $4 a piece
The expected demand at full price is 1200 shirts
He is thinking about ordering 1400 t-shirts
… is that a good idea?
32
Inputs, Outputs, Decision Variables
Inputs (Given)
Fixed Order Cost (FC)
T-shirt Cost (VC)
Demand (D)
Initial Price (P1)
Close-out Price (P2)
Decision Variables
Number of shirts to order (Q)
Output
Profit
33
Inputs, Outputs, Decision Variables
Output: 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑅𝑒𝑣𝑒𝑛𝑢𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠
Revenue during the tournament
𝑃1 ∗ min(𝑄, 𝐷)
Revenue from leftover T-shirts
𝑃2 ∗ max(𝑄 − 𝐷, 0)
34
Inputs, Outputs, Decision Variables
Output: 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑅𝑒𝑣𝑒𝑛𝑢𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠
Costs:
Fixed cost, FC
Variable cost, VC, per item
Profit:
Profit = P1 * min(D, Q) + P2 * max(Q - D, 0) - VC * Q - FC
35
Names
Always use formula reference! Don’t type the numbers!
Define Range Names:
Generic
allows
changes
36
Names
Always use formula reference! Don’t type the numbers!
Define Range Names:
37
Names: MAC paste list
38
Names
Always use formula reference! Don’t type the numbers!
Formular tab →
Name manager:
39
Do we really know demand?
What if we think the demand is uncertain but between
1200 and 1600?
Sensitivity analysis
1-way data table, 2-way data table – ordering range
If we order 1400 shirts, what is the minimum demand to
break even?
Break-even analysis
What if we think it is equally likely that demand is 1200,
1300 and 1600?
Uncertainty analysis
40
Sensitivity Analysis: 1-way Table
Data/What-if
analysis/Data table
Three key points:
What is the output?
Which area to find
input and calculate
output?
What is the input?
• Use Column input to create a column of data!
• Note that this is just ONE cell, B8; not the demand values
• You can also key in the parameter name: Demand. 41
Sensitivity Analysis: 1-way Table
How profit changes according to
demand parameter
Is the position to key in the prototype/output function important?
• YES! Not putting your prototype function in the first row of
your select area causes problem.
42
Break-Even Demand: Goal Seek
43
Sensitivity Analysis: 2-way Table
Decisions of order size should depend on the demand.
What if we want to see what happens when both demand
and order size are changing?
Use two-way data table!
Data/What-if analysis/Data table
44
Sensitivity Analysis: 2-way Table
Same thing, except now requires both column and row input cells
45
Sensitivity Analysis: 2-way Table
Use MAX function to find the optimal profit (fix order size; fix demand).
46
Sensitivity Analysis: 2-way Table
Use the combination of INDEX and MATCH function to find the
optimal demand for each order size:
47
Results
Order Size
1700
1600
1500
1400
1300
1200
1100
1100 1200 1300 1400 1500 1600 1700
Demand
• In hindsight, the result is pretty obvious … since we can order
in blocks of 100, just order the first multiple of 100 that is
larger than demand …
• But do we know demand before we order…?
48
Results
Order Size
1700
1600
1500
1400
1300
1200
1100
1100 1200 1300 1400 1500 1600 1700
Demand
• Let’s consider Demand = 1350
• If Q = 1300, P = 1300*10-1300*6-FC
• If Q = 1400, P = 1350*10+50*4-1400*6-FC
49
Do we really know the demand?
Uncertainty Analysis
Demand is NOT something we can control and we don’t
know its exact value.
We need to make some assumptions.
Assume, that demand is equally likely to be 1200, 1300 or
1600
Each of these cases is a scenario.
We need to find the 𝐸[𝑃𝑟𝑜𝑓𝑖𝑡] for a given order size.
50
Uncertainty Analysis
Two way table of Order and Demand.
Then take the expected profit and find the order quantity
that yields the highest expected profit.
Use Index and Match functions.
51
Key Takeaways
Best practices in Excel
Define names
INDEX, MATCH function revisit
Sensitivity analysis
1-way, 2-way tables
Positions to prototype function
Three key points
Goal seek analysis
Break-even analysis is a special case.
Uncertainty analysis
52