Transcript MGTSC 352: Operations Management Lecture 1

MGTSC 352:
Operations Management
Lecture 1
My name is ...
Kenneth Schultz
Office 340G Business
Telephone 492-3068
Email klschult
This course is …
… a continuation of MGTSC 312
Not ...
Mgtsc != Stats
“Traditional” University Course
• Class
–
–
–
–
Come to class (sometimes)
Listen to The Prof (maybe not)
Take notes (perhaps)
Get bored
• Study
– Read the text (maybe not)
– Memorize stuff (wondering why—maybe not)
• Write exams
– Sometimes multiple choice
– Sometimes regurgitation
This course
• Class
– Come to class, try to follow the lecture, participate
– Come to lab/work on your own and try to repeat
what was done in lecture
• Study
– Read the notes/text
– Read/post to discussion forums
– Do the HWs
• Do exams (on-line)
We want you to…
•
•
•
•
•
… think with us (lectures, labs)
… interact with us
… take initiative/responsibility
… experiment aggressively
… learn by DOING
• This ain’t no sit-back-and-relax, you-paysyour-fees-and-you-gets-your-credits course.
Evaluation
25%
Assignments
25%
Quizzes
Best 8 out of 9
Must get 40% to pass (10 out of 25)
Best 2: 10% each
Worst one: 5%
Must get 40% to pass
(30 out of 75)
50%
Final exam
Grade Distribution
• Similar to other 3rd / 4th year courses
• Your relative mark is what matters
Number of students
MGTSC 352, Fall 2005
70
60
50
40
30
20
10
0
66
58
51
49
36
32
25
8
7
F
D
4
21
9
D+ C-
C
C+ BGrade
B
B+ A-
A
A+
Active Learning
• Form groups of two
• Whose birthday is earlier in the year?
– You’re the recorder
• Question: What have you heard about this
course?
• Time: 1 minute
Percent recall
Why Active Learning?
100
90
80
70
60
50
40
30
20
10
0
0
5
10
15
20
Time spent lecturing (min.)
25
30
What is this course about?
Production and delivery of …
… goods and services
• Forecasting
• Simulation
• Aggregate Planning
• Distribution Planning
• Inventory Management
• Congestion Management
Show me a chart
Corporate Strategy
Marketing Strategy
Operations Strategy
Finance Strategy
Operations Management
Operations:
Inputs
Parts
Processes
Planning
People
Plants
Outputs
Another Chart: the “Process View”
Information
structure
Inputs
Process
Management
Network of
Activities and Buffers
Outputs
Goods
Services
Flow units
(customers, data,
material, cash, etc.)
Labor & Capital
Resources
Example: Amazon.Com
• Inputs:
– Customer orders
– Books, CDs
– Packing material
• Outputs
– Shipped orders
• Flow units
– Customer orders
– Cash
– Books
• Resources:
– Capital: contact centres,
warehouses
– Labor: agents, orderpickers, web programmers
– Inventory
• Activities: Order taking,
order filling, shipping
• Process management:
Warehouses, inventory,
distribution, capacity.
• Information structure:
Transaction data for each
order
Active Learning
• In your groups again
• Task: fill in as much of the next slide as
you can
• Time: 2 minutes
Example: Business School
• Inputs:
• Outputs:
• Flow units:
• Resources:
– Capital:
– Labor:
• Activities:
• Process
management:
• Information structure:
Do I have to take this course?
Majors that need 352 ASAP
Majors that require 352
• Operations Management
• Decision and Information Systems
• Distribution Management
• Accounting
• Business Studies
• Finance
• International Business
• Management Info. Systems
• Marketing
• Retailing
Majors that do not require 352
• Business Economics and Law
• Entrepreneurship and Small Business
• Human Resource Management
• ______ Studies (language programs)
• Organizational Studies
Who are we?
• Instructor: Kenneth Schultz
• Lab Masters:
– Morgan Skowronski
– Jen Tyrkalo
• Grading: Jared Coulson
• Tech Master: Angela Kercher
• Lab Accelerators
Kenneth Schultz
•
•
•
•
Wharton Undergraduate
12 Years United States Army
Ph.D. 1997, Cornell
Research: Including human behavior in
Operations Management models.
My course priorities are:
I’m fair
You learn
Morgan Skowronski
Jen Tyrkalo
Things To Do Before Next Class
• Course web
– Read the “things to do” page
WINTER 2007 MGTSC 352 LEC B1 > COURSE DOCUMENTS > RESOURCES > GENERAL
RESOURCES
– Read FAQ
WINTER 2007 MGTSC 352 LEC B1 > COURSE DOCUMENTS > RESOURCES > GENERAL
RESOURCES > FREQUENTLY ASKED QUESTIONS
– Get familiar with course web and discussion forums
• Read Introduction chapter (Course pack)
• Read syllabus
Musical Break ... do not leave
Excel Basics
• Jan 20, 11 – 1, B24/B28
• Free
• Basic Excel skills
Course Packs
•
•
•
•
$20
Today, 3-5 in B20
Wed, 10-12 in B20
Friday in labs
Model
• Selective abstraction of reality
– Model airplane
– Floor plan of a house
– Map of Alberta
• Spreadsheet (algebraic) models
– Define decision cells (variables)
– Express relations between cells (formulas)
Inputs
MODEL
Revenue = Quantity x Price
Output
Inputs
Outputs
Why model?
•
•
•
•
•
Provides a precise and concise problem statement
Establishes what data are necessary for decision
Clarifies relationships between variables
Enables the use of known solution methods
Enables us to generalize knowledge to solve
problems we haven’t encountered before, to go
beyond experiential learning.
Example
Fisheries
Management
•
•
•
•
•
Lake currently has 1,000 trout
Carrying capacity = 100,000 trout
Fish population expands in May and June
Fishing allowed in September
Trout population at end of August:
PAug = PApr + (a – (b  PApr))  PApr),
a = 0.45, b = a / capacity.
• Each fish can be sold for $11 in any year
• Discount rate is 6%.
• Which policy maximizes the NPV?
Come again?
May population = 12,000
August population?
b = a / Cap = .45 / 100,000
PAug = PApr + (a – b  PApr)  PApr)
=?
In your groups!
Time: 1 min.
Come again?
May population = 12,000
August population?
b = a / Cap = .45 / 100,000
PAug = PApr + (a – b  PApr)  PApr)
= 12,000 + (0.45 – (0.45 / 100,000  12,000))  12,000
= 12,000 + (0.396)*12,000
= 16,752
Recap
• Data
– Starting population
– Capacity
– Growth parameter (a)
– Discount rate
– Price
• Variables: # of fish caught, for every year.
• Output: NPV (and fish population every year)
The Operations Management Club organizes industry mixers,
seminars, technical workshops, and conferences for students with
an interest in Operations Management and Management Science.
If you are interested in joining the OM Club, or are considering a
major in Operations Management and have any questions about
the degree, we would like to hear from you.
For more information on the club, membership, and events, visit
http://studentweb.bus.ualberta.ca/om/
or email [email protected]
Meeting: Tuesday, January 16 at 5:00 PM, Bus 4-10
Announcements
• HW 1 due Wednesday, 11:59 PM
• OM Club Excel workshops
– Jan 20, 11 AM – 1 PM
– Free
– Watch for a sign up link on the course page
• Don’t have course pack yet?
– Get one Friday in Lab
MGTSC 352
Lecture 2: Forecasting
Why forecast?
Types of forecasts
“Simple” time series forecasting methods
Including SES = Simple Exponential Smoothing
Performance measures
Plant Site Selection
• Alberta Manufacturer
• Has one old plant, in Calgary
• Planning to build new plant, but where?
– Edmonton or Calgary?
Recent Demand Figures
Calgary
Edmonton
Fort McMurray
Red Deer
2001
2002
2003
2004
2005
What Would you Do?
Perspectives on Forecasting
• Forecasting is difficult, especially if it's about
the future!
Niels Bohr
• Rule #0: Every forecast is wrong!
– Provide a range
More sarcastic quotes about forecasting:
http://www.met.rdg.ac.uk/cag/forecasting/quotes.html
What is the Driver Doing?
Forecasting
• Technological forecasts
– New product, product life cycle
(Ipod, Blackberry)
– Moore’s Law
– Gates’ Law
• Economic forecasts
– Macro level (unemployment, inflation, markets, etc.)
• Demand forecasts
– Focus in MGTSC 352
Moore's Law: Computing power
doubles about every two years.
1,000,000,000
100,000,000
Transistors
10,000,000
1,000,000
Gates’
Law: “The speed of software
halves every 18 months.”
100,000
10,000
1,000
1965
1975
1985
Year
1995
2005
Data from ftp://download.intel.com/museum/Moores_Law/Printed_Materials/Moores_Law_Backgrounder.pdf
Economic Forecasts
An economist is an expert who will
know tomorrow why the things he
predicted yesterday didn't happen
today.
Evan Esar
Why do economists make forecasts?
“We forecast because people with money ask us to.”
Kenneth Galbraith
Forecasting – Quantitative
• Time series analysis: uses only past
records of demand to forecast future demand
– moving averages
– exponential smoothing
– ARIMA
• Causal methods: uses explanatory variables
(timing of advertising campaigns, price changes)
– multiple regression
– econometric models
Active learning
• Groups of two
• Recorder: person that is born closest to
Telus 150.
• Task: think of three quantities that you’d like
to forecast
• 1 minute
Choosing a Forecasting Method
START
Is forecast
important?
Yes
No
Are accurate historical
data available?
No
Yes
Is forecast
important?
Yes
No
Is there at least 1-2
Flip a coin;
months before
use your intuition;
forecast is needed? No look at your horoscope;
consult an economist
Yes
Select appropriate
qualitative method
Are you willing to
pay for greater
accuracy?
Yes
No
Use a causal Use time-series
method
method
END
Simple models
• Notation
– Dt = Actual demand in time period t
– Ft = Forecast for period t
– Et = Dt - Ft = Forecast error for period t
• Problem: Forecast the TSX index
4 simple models
Excel
(Simple) Exponential Smoothing
• Generalization of the WMA method
• Uses a single parameter for weights
0  LS  1
• Three steps
– Initialization ... F2 = D1
– Calibration ... Ft+1 = LS Dt + (1 - LS) Ft
– Prediction ... same formula
Note the formula is a weighted average of Demand and Forecast from last period
Excel
SES weights
• Decrease “exponentially” as data age
• Most recent data gets a weight of LS
• Ft+1 = [LS Dt ] + [(1 - LS) Ft ]
Rearrange...
• Ft+1 = Ft + LS (Dt - Ft)
= Ft + LS Et
• A learning model
How do we choose LS
• Active learning (1 min.):
– High LS (≈ 1) results in ....
– Low LS (≈ 0) results in ....
• Suggested range for LS: (0.01,0.3)
• Performance measures (formulas in course pack, pg. 21)
–
–
–
–
–
BIAS
MAD
SE
MSE
MAPE
Excel
Famously Incorrect Forecasts
• “I think there is a world market for maybe five
computers.”
Thomas Watson, chairman of IBM, 1943
• “There is no reason anyone would want a
computer in their home.”
Ken Olson, president, chairman and founder of Digital Equipment
Corp., 1977
• “The concept is interesting and well-formed, but
in order to earn better than a 'C,' the idea must
be feasible.”
A Yale University management professor in response to Fred
Smith's paper proposing reliable overnight delivery service. (Smith
went on to found Federal Express Corp.)
HW1 Q5: One Possible Approach
• First, let the population grow
• At some point, start harvesting the growth
– Annual catch = annual growth
• In year 30, catch all but 1,000 fish
– Maybe not be a good idea in reality
• Remaining question: how far should we let
the population grow?
MGTSC 352
Lecture 3: Forecasting
“Simple” time series forecasting methods
Including SES = Simple Exponential Smoothing
Performance measures
“Tuning” a forecasting method to
optimize a performance measure
Components of a time series
DES = Double Exponential Smoothing
Today’s active learning
• Groups of two again
• Recorder: person who got up earlier this
morning
SES is really a WMA (pg. 19)
Ft+1 = LS  Dt + (1–LS)  Ft
t = 6: F7 = LS  D6 + (1–LS)  F6
t = 5: F6 = LS  D5 + (1–LS)  F5
Plug t = 5 equation into t = 6 equation:
t = 4: F5 = LS  D4 + (1–LS)  F4
F7 = LS  D6 + (1–LS)  (LS  D5 + (1–LS)  F5)
t = 3: F4 = LS  D3 + (1–LS)  F3
Active learning: Multiply out
t = 2: F3 = LS  D2 + (1–LS)  F2
t = 1: F2 = D1
F7 = LS  D6 + LS  (1–LS)  D5 + (1–LS)2  F5
Repeat for t = 4, 3, 2, 1
Final result:
F7 = [LS  D6] + [LS  (1–LS)  D5] + [LS  (1–LS)2  D4]
+ [LS  (1–LS)3  D3] + LS  (1–LS)4  D2] + (1–LS)5  D1
The Weights
LS = 0.5
0.40
0.20
D5
D4
D3
0.40
0.30
D2
D1
LS = 0.3
0.20
0.10
D6
D5
D4
D3
D2
D1
0.80
Weight
D6
Weight
Weight
0.60
0.60
LS = 0.1
0.40
0.20
D6
D5
D4
D3
D2
D1
• Weights get smaller and smaller for
demand that is further and further in the
past – except:
– Oldest data point may have more weight than
second oldest data point.
– Only matters for small data sets and small LS
Simple Models Recap
• LP, AVG, SMA, WMA, SES
• Three phases:
– Initialization
– Learning
– Prediction
• Prediction: so far, we’ve only done one-periodinto-the-future
• k periods-into-the-future: Ft+k = Ft+1, k = 2, 3, …
• Active learning: translate formula into English
Performance Measures
•
•
•
•
•
BIAS = Bias
MAD = Mean Absolute Deviation
SE = Standard Error
MSE = Mean Squared Error
MAPE = Mean Absolute Percent Error
(formulas in course pack, p. 21)
Excel
Pg. 23
Components of a Time Series
– level
– trend
– seasonality
– cyclic (we will ignore this)
– random (unpredictable by definition)
• (Simple) Exponential Smoothing incorporates...
– Level only
– Will lag trend
– Miss seasonality
Level, Trend, Seasonality
Level + random
Level + trend +
random
Level + trend +
seasonality + random
Level, Trend, Seasonality
• Additive trend, multiplicative seasonality
• (Level + Trend)
 seasonality index
• Example:
– Level: 1000
– Trend: 10
– Seasonality index: 1.1
– Forecast: (1000 + 10)  1.1 = 1111
Models
• Double Exponential Smoothing
– Level, Trend
– Today
• Triple Exponential Smoothing
– Next week
• Simple Linear Regression with Seas. Indices
– Next week
Pg. 25
Double Exponential Smoothing
• Initialization
– Level, Trend
• Learning
• Prediction
• Formulas in course pack
• Work on an example
Excel
Learning
L t  LS  Dt  (1  LS)  (L t 1  Tt 1)
Tt  TS  (L t  L t 1)  (1  TS)  Tt 1
In general: UPDATED = S NEW + (1 – S)  OLD
Marking Philosophy
• Feasibility: could the plan you proposed
be used in reality
• Consistency: are your numbers internally
consistent?
• Optimality: is your plan the best possible,
or close to it?
Example: Marking of HW1, Q5
• You submitted:
– The plan: # to catch in years 0 – 30
– The consequence: NPV
• We plug your plan into a correct model and
check:
– Feasibility: is fish population always non-negative?
– Consistency: does your plan result in the NPV you
reported?
– Optimality: how does your NPV compare to the best
possible NPV?
From the Grading Manager
• Put only numbers in cells for numerical
answers
– 1234
– $1,234
– 1 234  Excel interprets this as text, not a
number (because of the space)
– 1234 fish  ditto
Reminders
• HW 2 due Wednesday at 11:59 pm
MGTSC 352
Lecture 4: Forecasting
Methods that capture Level, Trend, and Seasonality:
TES = Triple Exponential Smoothing
Intro to SLR w SI = Simple Linear Regression
with Seasonality Indices
Forecasting: Common Mistakes
• Computing forecast error when either the
data or the forecast is missing
• MSE: dividing with “n” instead of “n-1”
• MSE: SSE/n – 1 instead of SSE/(n – 1)
• Simple methods: forgetting that the
forecasts are the same for all future time
periods
Recap: How Different Models
Predict
• Simple models:
– Ft+k = Ft+1, k = 2, 3, …
• DES:
– Ft+k = Lt + (k  Tt ), k = 1, 2, 3, …
– Linear trend
• TES and SLR w SI (cover today):
– Ft+k = (Lt + k  Tt)  (Seasonality Index)
What’s a Seasonality Index (SI)?
• Informal definition: SI = actual / level
• Example:
– Average monthly sales = $100M
– July sales = $150M
– July SI = 150/100 = 1.5
• SI = actual / level means:
– Actual = level  SI
– Level = actual / SI
TES tamed
Works in three phases
• Initialization
• Learning
• Prediction
Tracks three components
• Level
• Trend
• Seasonality
Actual data
Level
Prediction
Prediction
Initialization
Learning
Actual data
Level
Forecast = (predicted level)  SI
Prediction
predicted level
k periods
into future
k  trend
Time to try it out – Excel
Pg. 29
TES - Calibration (p = # of seasons)
L t  LS
Dt
 (1  LS)(L t 1  Tt 1)
S t p
Tt  TS(Lt  Lt 1)  (1  TS)Tt 1
St  SS
Dt
 (1  SS)S t p
Lt
Always: UPDATED = (S) NEW + (1-S) OLD
One-step Forecast: Ft+1 = (Lt + Tt) St+1-p
Level: learning phase
L(t) = LS * D(t) / S(t-p) + ( 1 - LS )*( L(t-1) + T(t-1) )
•
NEW: D(t) / S(t-p) = de-seasonalize data for period t using
seasonality of corresponding previous season  level = actual / SI
•
OLD: L(t-1) + T(t-1) = best previous estimate of level for period t
Trend: learning phase
T(t) = TS * ( L(t) - L(t-1) ) + ( 1 - TS ) * T(t-1)
•
NEW: L(t) - L(t-1) = growth from period t-1 to period t
•
OLD: T(t-1) = best previous estimate for trend for period t
Seasonality: learning phase
S(t) = SS * D(t) / L(t) + ( 1 - SS ) * S(t-p)
•
NEW: D(t) / L(t) = actual / level  SI = actual / level
•
OLD: S(t-p) = previous SI estimate for corresponding season
25
One-step forecasting: the past
F(t+1) = [L(t) + T(t)] * S(t+1-p)
"To forecast one step into the future, take the previous period’s level,
add the previous period’s trend, and multiply the sum with the
seasonality index from one cycle ago."
Pg. 30
k-step forecasting: the future
(“real” forecast)
F(t+1) = [L(t) + k*T(t)] * S(t+1-p)
Active learning: translate the formula into English
• One minute, in pairs
TES vs SLRwSI
• TES
Ft+k = (Lt + k  Tt)  St+k-p
• SLRwSI
additive trend
multiplicative
seasonality
Ft+k = (intercept + (t + k)  slope)  SI
TES vs SLRwSI
• Both estimate Level, Trend, Seasonality
• Data points are weighted differently
– TES: weights decline as data age
– SLRwSI: same weight for all points
• Hence, TES adapts, SLRwSI does not
Which Method Would Work Well for This Data?
500
450
400
350
Data
300
250
200
150
100
50
0
1
2
3
4
5
6
7
8
9 10 11 12 13 14 15 16 17 18 19 20
Patterns in the Data?
• Trend:
– Yes, but it is not constant
– Zero, then positive, then zero again
• Seasonality?
– Yes, cycle of length four
Comparison
• TES:
SE = 24.7
• SLRwSI:
500
450
400
350
300
250
200
150
100
50
0
Data
TES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
• TES trend is adaptive
500
450
400
350
300
250
200
150
100
50
0
SE = 32.6
Data
SLR w SI
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
• SLR uses constant trend
One-minute paper
• Don’t put on your coat put your books away or
whatnot, pull out a piece of paper instead.
• Review today’s lecture in your mind
– What were the two main things you learned?
– What did you find most confusing?
– Who is going to win the Superbowl?
• Don’t put your name on the paper.
• Stay in your seats for 1 minute.
• Hand in on your way out
MGTSC 352
Lecture 5: Forecasting
Choosing LS, TS, and SS
SLR w SI = Simple Linear Regression
with Seasonality Indices
Range estimates
Choosing Weights
• Find the values for LS, TS and SS that
minimize* some performance measure.
* Exception?
• Two methods:
– Table – If you want to use more than one
performance measure
– Solver – If you want to ‘optimize’ against one
performance measure only
What’s This Solver Thing?
• In Excel: Tools  Solver, to bring up:
Optimize something
(maximize profit, minimize cost, etc.)
By varying some decision variables
(“changing cells”)
Keeping in mind any restrictions
(“constraints”) on the decision
variables
Pg. 33
Using Solver to Choose LS, TS, SS
• What to optimize: minimize SE
– Could minimize MAD or MAPE, but solver
works more reliably with SE
• For the geeks: because SE is a smooth function
• Decision variables: LS, TS, SS
• Constraints:
Something a bit
bigger than zero
(f. ex.: 0.01, 0.05)
LS
≤
TS
SS
≤
Something a bit
smaller than one
(f. ex.: 0.99, 0.95)
Let’s try it out …
Why Solver Doesn’t Always
Give the Same Solution
Everywhere I look is uphill!
I must have reached the lowest point.
local optimum
global optimum
Pg. 34
SLR w SI
= Simple Linear Regression with Seasonality Indices
• Captures level, trend, seasonality, like TES
• Details are different
• SLR Forecast
– Ft+k = (intercept + [(t + k)  slope])  SI
Excel
TES vs SLRwSI
• TES
Ft+k = (Lt + k  Tt)  St+k-p
• SLRwSI
additive trend
multiplicative
seasonality
Ft+k = (intercept + (t + k)  slope)  SI
TES vs SLRwSI
• Both estimate Level, Trend, Seasonality
• Data points are weighted differently
– TES: weights decline as data age
– SLR w SI: same weight for all points
• TES adapts, SLR w SI does not
Which Method Would Work Well for This Data?
500
450
400
350
Data
300
250
200
150
100
50
0
1
2
3
4
5
6
7
8
9 10 11 12 13 14 15 16 17 18 19 20
Patterns in the Data?
• Trend:
– Yes, but it is not constant
– Zero, then positive, then zero again
• Seasonality?
– Yes, cycle of length four
Comparison
• TES:
SE = 24.7
• SLRwSI:
500
450
400
350
300
250
200
150
100
50
0
Data
TES
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
• TES trend is adaptive
500
450
400
350
300
250
200
150
100
50
0
SE = 32.6
Data
SLR w SI
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
• SLR uses constant trend
Pg. 38
How Good are the Forecasts?
• TES (optimized):
Year 5, Quarter 1 sales = 1458.67
– Are you willing to bet on it?
• Forecasts are always wrong
– How wrong will it be?
• Put limits around a “point forecast”
– “Prediction interval”
– 95%* sure sales will be between low and high
– How do we compute low and high?
* (give or take)
Forecast Error Distribution
Errors
Frequency
20
15
10
5
0
0 50 50 50
5
-4 -3 -2 -1
0
-5
50 15 0 25 0 35 0 45 0 ore
M
Forecast Error
Approximate with Normal
Distribution
“Standard Error” of the forecast errors
Errors
20
18
16
14
12
10
8
6
4
2
0
Forecast Error
or
e
M
45
0
35
0
25
0
15
0
50
Standard
Error = 127
-5
0
-4
50
-3
50
-2
50
-1
50
Frequency
Average Error = .3
95% Prediction Interval
• 1-step Point forecast + bias  2  StdError
• 9 Jan TSX
= 12654 + .3  2  127
= 12654  254
=[12400, 12908]
=[low, high]
• Actual 12,467.99
Are TES and SLR w SI it?
• Certainly not
– Additive seasonality models
• TES’ or SLR w SD
– Multiplicative trend models
• TES’’ or Nonlinear Regression (Dt+1 = 1.1Dt)
Pg. 39
Steps in a Forecasting Project
-1: Collect data
0: Plot the data (helps detect patterns)
1: Decide which models to use
–
–
–
level – SA, SMA, WMA, ES
level + trend – SLR, DES
level + trend + seas. – TES, SLR w SI, ...
2: Use models
3: Compare and select (one or more)
4: Generate forecast and range (prediction interval)
More on selection
Pg. 41
How to select a model?
• Look at performance measures
– BIAS, MAD, MAPE, MSE
• Use holdout strategy
•
•
•
•
•
Example: 4 years of data
Use first 3 years to fit model(s)
Forecast for Year 4 and check the fit(s)
Select model(s)
Refit model(s) adding Year 4 data
• If you have more than one good model...
COMBINE FORECASTS
Appropriate model...
linear
Nonlinear
(ex. power)
S-curve (ex. any CDF)
JA
N
JU
N
N
O
V
AP
R
SE
P
FE
B
JU
L
D
EC
M
A
Y
O
C
M T
A
R
AU
G
JA
N
JU
N
N
O
V
AP
R
SE
P
FE
B
JU
L
D
E
M C
A
Y
O
C
M T
A
R
AU
G
JA
N
JU
N
N
O
V
AP
R
SE
P
FE
B
JU
L
D
EC
Sales in $ millions
DATA
Building Material, Garden Equipment and Supply Dealers
40,000
35,000
30,000
25,000
20,000
15,000
10,000
5,000
-
1992 - 2004
TES vs. SLR w/ SI
TES
BIAS
MAD
MAPE
MSE
127
628
2.86%
711,039
SLR w/ SI
BIAS
6
MAD
713
MAPE
3.32%
MSE
1,002,189
Which method would you choose?
Holdout Strategy
1. Ignore part of the data (the “holdout data”)
2. Build models using the rest of the data
3. Optimize parameters
4. Forecast for the holdout data
5. Calculate perf. measures for holdout data
6. Choose model that performs best on holdout data
7. Refit parameters of best model, using all data
15,000
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
TES vs. SLR w/ SI
…in holdout period
40,000
35,000
30,000
25,000
20,000
holdout
period
2001
2002
Sales
2003
TES
SLR w/ SI
2004
TES vs. SLR w/ SI
…in holdout period
TES
BIAS
MAD
MAPE
MSE
1,025
1,319
4.29%
2,530,775
SLR w/ SI
BIAS
2,995
MAD
2,995
MAPE
9.41%
MSE
11,566,373
Now which method would you choose?
1200
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Calgary EMS Data
1300
Number of calls / month
1100
1000
900
800
Trend?
700
Seasonality?
600
500
2000
2001
2002
2003
2004
Checking for (Yearly) Seasonality
Number of calls / month
Dec
Nov
Oct
Sep
Aug
Jul
Jun
May
Apr
Mar
Feb
2000
2001
2002
2003
2004
Jan
1300
1200
1100
1000
900
800
700
600
500
Weekly Seasonality
Avg. # of calls / hr., 2004
140
120
100
80
60
40
20
0
Sun
Mon
Tue
Wed
Thu
Fri
Sat
Reminders
• HW 3 Posted
• HW 1 Graded and Posted
• Grading appeal process
MGTSC 352
Lecture 6: Forecasting
Wrap-up of Forecasting
Holdout strategy
Debugging Forecasting Models
Monte Carlo Simulation
Playing Roulette with Excel
Bard Outside example
95% Prediction Interval
• Technically correct formula;
– Forecast + Bias + 2 x Std Error
• Heuristic for use in this class;
– Forecast  2  SE
Pg. 39
Steps in a Forecasting Project
-1: Collect data
0: Plot the data (helps detect patterns)
1: Decide which models to use
–
–
–
level – SA, SMA, WMA, ES
level + trend – SLR, DES
level + trend + seas. – TES, SLR w SI, ...
2: Use models
3: Compare and select (one or more)
4: Generate forecast and range (prediction interval)
More on selection
Appropriate model...
linear
Nonlinear
(ex. power)
S-curve (ex. any CDF)
1200
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Calgary EMS Data
1300
Number of calls / month
1100
1000
900
800
Trend?
700
Seasonality?
600
500
2000
2001
2002
2003
2004
Checking for (Yearly) Seasonality
Number of calls / month
Dec
Nov
Oct
Sep
Aug
Jul
Jun
May
Apr
Mar
Feb
2000
2001
2002
2003
2004
Jan
1300
1200
1100
1000
900
800
700
600
500
Weekly or Hourly Seasonality
Avg. # of calls / hr., 2004
140
120
100
80
60
40
20
0
Sun
Mon
Tue
Wed
Thu
Fri
Sat
Pg. 41
How to select a model?
• Look at performance measures
– BIAS, MAD, MAPE, MSE
• Use holdout strategy
•
•
•
•
•
Example: 4 years of data
Use first 3 years to fit model(s)
Forecast for Year 4 and check the fit(s)
Select model(s)
Refit model(s) adding Year 4 data
• If you have more than one good model...
COMBINE FORECASTS
Example: Building Materials, Garden
Equipment, and Supply Dealers
$40,000
Sales
$35,000
$30,000
$25,000
$20,000
$15,000
$10,000
$5,000
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
J
M
M
J
S
N
$1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004
TES vs. SLR w SI
(Both optimized to minimize SE)
TES
SLR w SI
BIAS
$130
BIAS
$5
MAD
$628
MAD
$733
MAPE
2.9%
MAPE
3.3%
SE
$843
SE
$1,016
Which method would you choose?
One possibility:
Combining Forecasts
weight
TES
+ (1 - weight)
SLR w SI
Minimize SE of the combined
forecast to find the best weight
Holdout Strategy
1. Ignore part of the data (the “holdout data”)
2. Build models using the rest of the data
3. Optimize parameters
4. Forecast for the holdout data
5. Calculate perf. measures for holdout data
6. Choose model that performs best on holdout data
7. Refit parameters of best model, using all data
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
TES vs. SLR w/ SI
…in holdout period
$35,000
$33,000
$31,000
$29,000
$27,000
$25,000
$23,000
$21,000
2000
Sales
$19,000
SLR w SI
$17,000
TES
$15,000
holdout
period
2001
2002
2003
2004
TES vs. SLR w SI …
… in holdout period
TES
SLR w SI
BIAS
$1,266
BIAS
$2,956
MAD
$1,514
MAD
$2,956
MAPE
10.6%
SE
$3,356
MAPE
SE
4.9%
$1,824
Now which method would you choose?
Holdout Strategy Recap
• Performance during holdout period: a.k.a.
“out of sample” performance
• In other words: how well does the method
perform when forecasting data it hasn’t
“seen” yet?
• Question: Why is SE during holdout period
worse than SE during “training period”?
Do we have to implement these
models from scratch?
• Forecasting software survey
– http://lionhrtpub.com/orms/surveys/FSS/FSS.html
• General statistics program
– Minitab, NCSS, SAS, Systat
• Dedicated forecast software
– AutoBox, Forecast Pro (MGTSC 405)
Do Spreadsheet Models
Have Errors?
• Field audits of real-world spreadsheets:
94% had errors
http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm
• What are the consequences of
spreadsheet errors?
– Incorrect financial statements
– Bad publicity, loss of investor confidence
– Lawsuits
– Loss of election
– See http://www.eusprig.org/stories.htm for more
Debugging – Finding Your Mistakes
• Before entering a formula:
– Pause and predict the result
• After entering a formula:
– Double-click to see where numbers are
coming from
• Try simple test values: 0, 1
• Graph your results
• ctrl+~ – use to look for breaks in patterns
To Excel
Playing roulette with Excel
0
0.9
0.1
0.8
0.2
0.7
0.3
0.6
0.4
0.5
To Excel …
Game 1
Spin the spinner once
Payoff = (spinner outcome)  ($1 Million)
Q1: What would you pay to play this game?
Q2: Suppose the game were played 10,000 times. What do you
think the payoff distribution will look like?
?
$0
$1 -99
00 K
-1
$2 99
00 K
-2
$3 99
00 K
-3
$4 99
00 K
-4
$5 99
00 K
-5
$6 99
00 K
-6
$7 99
00 K
-7
$8 99
00 K
$9 -89
00 9K
-1
00
0K
# of occurences
•
•
•
•
Payoff
Game 2
19
9K
$2
00
-3
99
$4
K
00
-5
99
$6
K
00
-7
99
$8
K
00
-9
$1
99
00
K
011
$1
99
20
K
013
$1
99
40
K
015
$1
99
60
K
017
$1
99
80
K
020
00
K
?
$0
-
# of occurences
• Spin the spinner twice
• Payoff = ($1 Million) x (spinner outcome 1 + spinner outcome 2)/2
Q1: What would you pay to play this game?
• Q2: Suppose the game were played 10,000 times. What do you
think the payoff distribution will look like?
Payoff
Game 1 payoff distribution
?
or
neither
?
?
Game 2 payoff distribution
?
or
neither
?
?
Using Excel to get the right answer
• Simulate one spin: =RAND()
• Repeat 10,000 times
• Plot histogram
– To Excel
Pg. 43
Excel Details
• Using Data tables to replicate a simulation
• Enter replication numbers (1, …, n) in
leftmost column
• Enter formulas for outputs in top row
• Highlight table
• Data  Table …
– Column input cell: any empty cell
More Excel Details
• “Freezing” simulated
values:
– Copy the values
– Paste special … 
values
• Frequency distributions:
(see also pg. 134)
– Generate sample
– Enter “bins” values
– Highlight range where
frequencies should be
calculated
– =FREQUENCY(sample,
bins)
– “Ctrl + shift + enter”
instead of just “enter.”
Bard Outside
• The Bard Outside theatre group puts on plays
by Shakespeare 20 times every summer in a
200-seat outdoor theatre.
• Data:
– Attendance and weather (rain / no rain) for last five
seasons (5 x 20 = 100 shows)
– Revenue = $10 per customer
– Cost = $1,600 per show
• Question: how much would profit increase if the
number of seats were increased?
Data Analysis
• What’s the probability of rain?
• What is the mean and standard deviation
of demand when it rains?
• How about when it doesn’t rain?
• How can we simulate demand?
To Excel …
Simulating Profit per show
•
•
•
•
•
•
•
Simulate weather
Simulate demand
Make sure 0 ≤ demand ≤ capacity
Calculate revenue
Subtract cost
Replicate!
Remember: freeze tables of simulation
results
Final results
Avg. profit / show
$200
$150
$100
$50
$200
210
Seats
220
Preparing for Quiz 1
•
•
•
•
Review notes, assignments
Take practice quiz
Read Tips on Taking On-line Exams
Get a good night's rest
• Quiz 1 coverage: up to and including
wrap-up of forecasting
Quiz Schedule
Lab
section
Enter lab
Quiz
begins
Quiz
ends
8 am
7:55
8:00
8:40
9 am
8:55
9:00
9:40
11 am
10:55
11:00
11:40
12 pm
11:55
12:00
12:40
All lab sections treated the same
When you come to the lab
• Find your assigned computer
• Logon to the course web
• You may copy materials to the desktop before
the quiz starts
– From USB key, CD, or email
• You may not use a USB key, CD, email, etc.
during the quiz
• Listen carefully to instructions
• Have OneCard ready.
Reminders
• Quiz 3 is now on 30 March
• HW 3 due Wed
• Quiz Review Session, Thu 5 – 6:30 pm,
BUS B-24+28
– Optional
– Q&A session, no new material
MGTSC 352
Lecture 7: Monte Carlo Simulation
Bard Outside example
Bard Outside
• The Bard Outside theatre group puts on plays
by Shakespeare 20 times every summer in a
200-seat outdoor theatre.
• Data:
– Attendance and weather (rain / no rain) for last five
seasons (5 x 20 = 100 shows)
– Revenue = $10 per customer
– Cost = $1,600 per show
• Question: how much would profit increase if the
number of seats were increased?
Profit
•
•
•
•
•
Profit = Revenue – Expenses
Revenue =
Expenses =
What do we need to find out?
How can we do this?
Data Analysis
• What’s the probability of rain?
• What is the mean and standard deviation
of demand when it rains?
• How about when it doesn’t rain?
• How can we simulate demand?
To Excel …
Simulating Profit per show
•
•
•
•
•
•
•
Simulate weather
Simulate demand
Make sure 0 ≤ demand ≤ capacity
Calculate revenue
Subtract cost
Replicate!
Remember: freeze tables of simulation
results
Simulating a value from a Normal Distribution:
Breaking the formula down
• ROUND(NORMINV(RAND(),mean,stdev),0)
• Step 1: generate random number
RAND()
• Step 2: convert random number to normal
distribution
NORMINV(RAND(),mean,stdev)
• Step 3: round to whole number
ROUND(NORMINV(RAND(),mean,stdev),0)
Converting random number to a
normal distribution
Cumulative Distribution Function
Probability
1.00
0.80
=RAND()
0.60
0.40
0.20
-
=NORMINV(…)
Value = 990.3
500 Simulated 1000
Demand
1500
Final results
Avg. profit / show
$200
$150
$100
$50
$200
210
Seats
220
Comparing Different Capacities
• Want to compare 200 seats
and 210 seats
• Approach 1:
– Simulate demand for 100 days
– Compute profit for each
simulated day, assuming 200
seats
– Simulate demand for another
100 days
– Compute profit for each
simulated day, assuming 210
seats
– Compare average profits
• Approach 2:
– Simulate demand for 100 days
– Compute profit for each
simulated day, assuming 200
seats
– Compute profit for each
simulated day, assuming 210
seats (reuse the 100
simulated demands)
– Compare average profits
• Active learning: which
approach is better?
– 1 min., in pairs
– List as many pros and cons as
you can
Pros and Cons
• Approach 1
(simulate 2  100)
• Approach 2
(simulate 1  100)
Bard Outside Example: A
“Newsvendor Problem”
• Bard Outside:
– Decision: # of seats
– Uncertain future
demand
– Demand > # of seats
 lost revenue
– Demand < # of seats
 empty seats
• A newsvendor:
– Decision: # of
newspapers to get
– Uncertain future
demand
– Demand > # of papers
 lost revenue
– Demand < # of papers
 disposal costs
Active Learning
• In pairs, 1 min.
• Think of three other examples of
newsvendor problems
• Examples:
Bard Outside Revisited
• We estimated the average profit per show
with 200 seats to be about $11 per night
• Bard Outside’s accountant says they’ve
been earning an average of $100 per night
• What’s wrong?
Another look at the No Rain
Attendance Distribution
Attendance
(up to 199)
300
250
200
150
100
50
200 or more: 51% of
the time
What we did: Fit a Normal Distribution
with Mean = 176, Stdev = 39
Attendance of 200 or more: 51%
Demand of 200 or more: 27%
Demand
Can we do better?
300
250
200
150
100
50
Attendance
How about this: Normal Distribution
with Mean = 200, Stdev = 50
Attendance of 200 or more: 51%
Demand of 200 or more: 50%
Demand
300
250
200
150
100
50
Attendance
The attendance distribution is a “censored” version of the demand
distribution. We need to “uncensor” it before using it to simulate.
How Much Difference Does this
Make?
Avg.
profit
($/show)
200
seats
210
seats
220
seats
Extra
profit per
seat
Take 1
$11
$24
$33
$1.10
Take 2
$113
$146
$173
$3.00
Preparing for Quiz 1
•
•
•
•
Review notes, assignments
Take practice quiz
Read Tips on Taking On-line Exams
Get a good night's rest
• Quiz 1 coverage: up to and including
wrap-up of forecasting
Quiz Schedule
Lab
section
Enter lab
Quiz
begins
Quiz
ends
8 am
7:55
8:00
8:40
9 am
8:55
9:00
9:40
11 am
10:55
11:00
11:40
12 pm
11:55
12:00
12:40
All lab sections treated the same
Transition periods are crucial
When you come to the lab
• Find assigned computer, go to course web
• You may copy materials to the desktop
before the quiz starts
– From USB key, CD, or email
• You may not use a USB key, CD, email,
etc. during the quiz
• Listen carefully to instructions
• Have OneCard ready.
When the quiz begins
• Take a deep breath!
• If the first question looks too simple, it is
During the quiz
•
•
•
•
•
Keep breathing!
Save often
Submit early, submit often
Do not worry about decimals, formatting
Later questions may depend on earlier ones.
Feel free to make up answers.
• If your computer freezes, raise your hand right
away. You will be given extra time for computer
problems beyond your control.
Near the end
• 5-minute warning
• Stop, save, submit
• Check that responses appear on
confirmation web page
• If you have time, do more work
• Don’t risk late penalty !
• When done: delete files from desktop
Things to watch for…
• Practice finding good solutions without
Solver
• Error messages in Solver:
– “Error in set target cell not met”
– If you see a message you do not recognize,
raise your hand immediately and we will help
with the tech issue
– Do not try to fix this for 20 min and then tell us
since we will not be able to give you an extra
20 min on the quiz
Reminders
• Quiz Review Session, Thu 5:30 – 6:30 pm,
BUS B-24+28
– Optional
– Q&A session, no new material
MGTSC 352
Lecture 9: Aggregate Planning
Overview of Planning:
Matching Demand and Capacity
Case 2: Mountain Wear
Leduc Control Example
Overview of Planning (pg. 46)
Short-range
Intermediate
Long-range
•Job assignment
Aggregate levels of:
•Product design
•Machine loading
•Workforce
•Location
•Job sequencing
•Inventory
•Layout
•Lot sizing
•Output
•Capacity
•Order quantities
•Subcontracting
•Process
•Backorders
0
2 mo.
18 mo.
5 yrs.?
Sequence of Planning (pg. 47)
Corporate
Strategy
External
Conditions
Demand
Forecasts
Aggregate
Plan
Manufacturing
Master Production
Schedule
Service
Weekly Workforce +
Customer Schedule
MRP = Materials
Requirements
Planning
Daily Schedule
Matching Demand and Capacity
Influencing demand
• ?
Changing capacity
• ?
Matching Demand and Capacity
(pg.48)
Influencing demand
• Pricing
• Promotion
• Back orders
• New demand
Changing capacity
• Hiring/firing
• Overtime/slack time
• Part-time workers
• Subcontracting
• Inventories
Case 2: Mountain Wear (pg. 96)
12,000
Aggregate demand
Possible production with 22 employees
Units of production
10,000
8,000
6,000
4,000
2,000
Q1
Q2
Q3
Q4
Case 2: Mountain Wear
Decide …
• how much to produce
• how much inventory to carry
• how many people to hire or lay off
• how much overtime to use
… in order to satisfy demand and minimize cost
AGGREGATE PLANNING
For next week: read case (pg. 96), fill in the blanks on
pages 49-50 in course pack
Let’s look at the first aggregate plan in the case …
Leduc Control (pgs.52-53)
• The mysteries of solver unraveled …
– … slowly
• How many units of each product to produce for
the next period?
– Simpler than Mountain Wear
Leduc Control
• Products: AS 1012 and HL 734
• Production planning meeting:
– Howie Jones (CEO)
– Homer Simpson (Production)
– Andy Marshall (Marketing)
– Tania Tinoco (Accountant)
– Kim Becalm (you)
Homer
Resource
AS 1012 HL 734 Available
PSoC
1
1
200
Assembly
9 hrs.
6 hrs.
1,566
Programming 12 hrs. 16 hrs.
2,880
Andy
• Can sell all we produce
• No room to raise prices
Tania
Resource
AS 1012 HL 734 Unit Cost
PSoC
1
1
$720
Assembly
9
6
$20
Programming
12
16
$20
Var. cost / unit $1,140 $1,160
More From Tania
AS 1012
HL 734
Selling price
$1,490
$1,460
Var. cost
($1,140)
($1,160)
Net margin
$350
$300
Less: allocated fixed costs
($310)
($310)
Profit / unit
$40
($10)
Tania’s conclusion: produce 200 AS 1012 and 0 HL 734
Do you agree?
Leduc Control Example (pg. 60)
• A linear problem
– The “set cell” is linear function of changing cells
– All constraints are linear functions of changing cells
• A linear function is one that involves
–
–
–
–
addition (or subtraction)
multiplication of a constant with a changing cell
no other operations
mathematically
ax + by  linear function of two variables (x and y)
Linear vs. nonlinear
• If possible, use a linear formulation
– Solver will work more reliably
• Convert Y/X ≤ 0.5 to Y ≤ 0.5X
• Quick-and-dirty approach:
– Click “Assume Linear Model” and solve
– If solver complains, unclick, try again
Leduc Control Example –
Alternative Representations (pg. 61)
• Spreadsheet formulation (what we did in class)
• In English
– Maximize net contribution
– By varying the production levels of the two products
– Subject to constraints:
•
•
•
•
Use no more than 200 PSoCs
Use no more than 1566 hours of assembly time
Use no more than 2880 hours of programming
(Do not produce negative units)
Algebraic Formulation
Matrix Formulation
Formulation in AMPL
(= Algebraic Mathematical Programming Language)
param NUM_RESOURCES;
param NUM_PRODUCTS;
set RESOURCES:=1..NUM_RESOURCES;
set PRODUCTS:=1..NUM_PRODUCTS;
param c {PRODUCTS} >= 0; # net margin per unit
param A {RESOURCES, PRODUCTS} >= 0; # per-unit resource requirements
param b {RESOURCS} >= 0; # resource availability
var x {PRODUCTS} >=0; # number to make of each product
# Objective:
# Maximize the total net margin
maximize total_net_margin: sum {i in PRODUCTS} c[i]*x[i];
# Constraints:
# resource availability constraints
subject to res_constr {j in RESOURCS}: sum{i in PRODUCTS} A[i,j] x[i] <= b[j];
Which Formulation is Best?
• Depends on what you want to do:
– Understand the problem
– Solve the problem
• Small problem
• Big problem
– Communicate the problem
– Develop a new/improved solver
Possible Solver Outcomes (pg. 63)
Optimization Model
Run Solver
Optimal Solution
Found 
Unbounded
Problem 
Infeasible
Problem 
Unbounded Problem
• How will you know:
• What it means:
– Possible to achieve infinite profit
• Either you will become filthy rich, or (more likely)
there is something wrong with your model
• How to fix it: look for missing constraints
Infeasible Problem
• How will you know:
• What it means:
– Impossible to satisfy all constraints
• Possible reasons:
– You need more resources
– You over-constrained the problem
Unbounded/Infeasible Problem
• Means solver cannot solve
• The values returned are meaningless
– You need to look at your model
Is the plan still optimal?
If not, how will it change? (pg. 65)
1.
Howie realizes that he underestimated the net margin for
each AS by $65.
2.
Howie realizes that he overestimated the net margin for
each AS by $65.
3.
Howie discovers a new market where he can sell both
AS and HLs at a 20% higher net margin than originally
estimated.
More Post-Optimality Analysis
4.
Another semiconductor supplier offers Howie 5 more PsoCs for a
premium of $150 each (above and beyond the going rate of $720
per unit). Should Howie buy these PSoCs?
5.
Howie sometimes helps out with programming the LCDs, thereby
increasing the amount of available programming time. Should he
help out in this cycle? If so, how long should he help out?
6.
Howie’s nephew offers to work in assembly for a premium rate of
$12 per hour (above and beyond the going rate of $20 per hour).
Should Howie hire his nephew? For how many hours?
SolverTable (pg. 67)
• Combines Solver and Data Table
• Solves the problem repeatedly and reports
all solutions
• Free add-in
– see COURSE DOCUMENTS >
RESOURCES > SOFTWARE on course web
Excel Solver Advantages (pg. 69)
• comes with Excel (no additional cost)
• has the same familiar user interface as
other Excel components
• can solve problems with integer
constraints and nonlinear problems
• can be automated using VBA
Excel Solver Disadvantages
• limited to 200 variables and 100 constraints
(Premium: 800 variables, no limit on constraints)
• somewhat inconvenient
(Ex: B12 + B13 ≤ B14 not allowed)
• can be slow when solving large problems with
integer constraints (Premium Solver much faster)
• not very reliable (sometimes fails to find a solution)
(Premium is more robust)
Other solvers
• Survey
– http://lionhrtpub.com/orms/surveys/LP/LP-survey.html
• $1,000 ... $10,000
• Can solve very large problems (200,000 constraints)
• Usually require front-end modeling language
• Premium solver: $1,000 http://www.solver.com/
MGTSC 352
Lecture 9: Aggregate Planning
Case 2: Mountain Wear
Take 2 (there will be one more)
Leduc Control Example:
Possible solver outcomes
Linearity
Post-optimality analysis
How does Solver Work?
• Creates a ‘feasibility space’ which is
‘inside’ all the constraints
• If everything is linear then optimum will
contain a ‘corner point’ – where two
constraints cross
• Go around the outside checking all the
corners until you can’t get any better
Let’s take a graphical look
Possible Solver Outcomes (pg. 63)
Optimization Model
Run Solver
Optimal Solution
Found 
Unbounded
Problem 
Infeasible
Problem 
Unbounded Problem
• How will you know:
• What it means:
– Possible to achieve infinite profit
• Either you will become filthy rich, or (more likely)
there is something wrong with your model
• How to fix it: look for missing constraints
Infeasible Problem
• How will you know:
• What it means:
– Impossible to satisfy all constraints
• Possible reasons:
– You need more resources
– You over-constrained the problem
Unbounded/Infeasible Problem
• Means solver cannot solve
• The values returned are meaningless
– You need to look at your model
Post-Optimality Analysis
• What if one or more input estimates are off
(forecast error)?
• Will the optimal solution change?
– Solution / plan = values of decision variables
• Will the optimal profit change?
• Ways to answer such questions:
–
–
–
–
–
Graphical analysis
Sensitivity report (pg. 64)
Re-solve (manually, or with Solver Table)
Reformulate
Logic
Is the plan still optimal?
If not, how will it change? (pg. 65)
1. Howie realizes that he underestimated the net
margin for each AS by $65.
2. Howie realizes that he overestimated the net
margin for each AS by $65.
3. Howie discovers a new market where he can sell
both AS and HLs at a 20% higher net margin
than originally estimated.
SolverTable (pg. 67)
• Combines Solver and Data Table
• Solves the problem repeatedly and reports
all solutions
• Free add-in
– see COURSE DOCUMENTS >
RESOURCES > SOFTWARE on course web
More Post-Optimality Analysis
4.
Another semiconductor supplier offers Howie 5 more PsoCs for a
premium of $150 each (above and beyond the going rate of $720
per unit). Should Howie buy these PSoCs?
5.
Howie sometimes helps out with programming the LCDs, thereby
increasing the amount of available programming time. Should he
help out in this cycle? If so, how long should he help out?
6.
Howie’s nephew offers to work in assembly for a premium rate of
$12 per hour (above and beyond the going rate of $20 per hour).
Should Howie hire his nephew? For how many hours?
One More
• Howie notices that with the currently
optimal production plan, 168 of the
available programming hours are not
used. Howie wonders whether he could
increase production and profits by training
the programmers to help out with
assembly. What would the optimal total net
margin be if all programmers were also
trained to do assembly?
Other solvers
• Survey
– http://lionhrtpub.com/orms/surveys/LP/LP-survey.html
• $1,000 ... $10,000
• Can solve very large problems (200,000 constraints)
• Usually require front-end modeling language
(such as AMPL)
• Premium solver: $1,000 http://www.solver.com/
Mountain Wear Case
• What decisions does Nathan Leung need
to make to generate an aggregate plan for
Mountain Wear?
Active Learning
1 min., in pairs
• What constraints (restrictions) must
Nathan keep in mind?
• Write down as many as you can think of
Summary of Data (pg. 49)
•
•
•
•
•
•
•
•
•
•
•
Materials cost:
per unit
Labour requirements:
hrs/unit
Labour availability:
hours/employee/quarter
# of workers at beginning of year:
Labour cost:
employee/quarter
Overtime labour cost:
per hour
Hiring cost:
Layoff cost:
Inventory holding cost:
per unit/quarter
Inventory at beginning of year:
Required safety stock:
Look at Nathan’s plans in Excel
Tradeoffs: “So which one of those
did you want?” (pg. 50)
Production Inventory
Plan 1
Plan 2
Plan 3
Level and chase:
Workforce
Overtime
MGTSC 352
Lecture 10: Aggregate Planning
Leduc Control Example:
Complete post-optimality analysis
Case 2: Mountain Wear
Set up and use solver to find minimum cost plan
Announcements
• HW 3 grading
• For next week read
– Air Alberta p. 72
– Crazy Joey’s p. 77
More Post-Optimality Analysis
4.
Another semiconductor supplier offers Howie 5 more PsoCs for a
premium of $150 each (above and beyond the going rate of $720
per unit). Should Howie buy these PSoCs?
5.
Howie sometimes helps out with programming the LCDs, thereby
increasing the amount of available programming time. Should he
help out in this cycle? If so, how long should he help out?
6.
Howie’s nephew offers to work in assembly for a premium rate of
$12 per hour (above and beyond the going rate of $20 per hour).
Should Howie hire his nephew? For how many hours?
One More
• Howie notices that with the currently
optimal production plan, 168 of the
available programming hours are not
used. Howie wonders whether he could
increase production and profits by training
the programmers to help out with
assembly. What would the optimal total net
margin be if all programmers were also
trained to do assembly?
Other solvers
• Survey
– http://lionhrtpub.com/orms/surveys/LP/LP-survey.html
• $1,000 ... $10,000
• Can solve very large problems (200,000 constraints)
• Usually require front-end modeling language
(such as AMPL)
• Premium solver: $1,000 http://www.solver.com/
Active Learning
1 min., in pairs
Mountain Wear
• What decisions does Nathan Leung need
to make to generate an aggregate plan for
Mountain Wear?
• What constraints (restrictions) must
Nathan keep in mind?
• Write down as many as you can think of
Mountain Wear Case
• What decisions does Nathan Leung need
to make to generate an aggregate plan for
Mountain Wear?
Mountain Wear Case
• What constraints (restrictions) must
Nathan keep in mind?
Summary of Data (pg. 49)
•
•
•
•
•
•
•
•
•
•
•
Materials cost:
per unit
Labour requirements:
hrs/unit
Labour availability:
hours/employee/quarter
# of workers at beginning of year:
Labour cost:
employee/quarter
Overtime labour cost:
per hour
Hiring cost:
Layoff cost:
Inventory holding cost:
per unit/quarter
Inventory at beginning of year:
Required safety stock:
Look at Nathan’s plans in Excel
Tradeoffs: “So which one of those
did you want?” (pg. 50)
Production Inventory
Plan 1
Plan 2
Plan 3
Level and chase:
Workforce
Overtime
Mountain Wear …
• Can we
find the
lowest
cost plan
with
solver?
Production Planning for Mountain Wear
Year
Quarter
Demand forecast
Units produced
Inventory:
Beginning-of-quarter
End-of-quarter
2001
4
500
Inventory cost
Production cost
Number hired
Number laid off
Workforce:
Beginning-of-quarter
End-of-quarter
Hours of overtime
Hiring cost
Layoff cost
Regular labor cost
Overtime cost
1
7000
7000
500
500
$3,000
$210,000
2
0
20
2002
2
3000
7000
500
4500
$15,000
$210,000
0
0
3
1000
7000
4
10000
7000
4500
10500
10500
7500
$45,000
$210,000
0
0
This is "Plan 1" from
"Case 2: Mountain Wear."
Unit costs Total costs
$54,000
$6
$117,000
$210,000
$30
$840,000
0
0
20
22
22
22
22
22
22
22
0
0
0
0 Unit costs Total costs
$6,000
$0
$0
$0
$3,000
$6,000
$0
$0
$0
$0
$1,500
$0
$220,000 $220,000 $220,000 $220,000
$10,000
$880,000
$0
$0
$0
$0
$23
$0
Labor hours:
Coefficients
Available
10,560
10,560
10,560
10,560
Required
10,500
10,500
10,500
10,500
hours per employee,
480 per quarter
hours of labor, per
1.5 unit
Total cost
$1,843,000
Active Learning: Formulate
Mountain Wear Problem in English
• 1 min., in pairs
• Template:
– Maximize / minimizes …
– By changing …
– Subject to …
Extending the
Mountain Wear Formulation
• Should we include additional constraints?
– Limit on overtime?
– Limit on hirings / firings?
–?
• How do the additional constraints impact
cost?
Air Alberta (pg. 72)
Air Alberta is doing aggregate planning of flight attendant staffing for
the next 6 months. They have forecast the number of flight attendant
hours needed per month for March to August, based on scheduled
flights, and wish to determine how many new attendants to hire each
month. Each trained attendant on staff supplies 150 hours per month.
A newly hired attendant is called a trainee during the first month, and
each trainee’s net contribution is negative (-100 hours) because (s)he
requires supervision, which detracts from the productivity of other
attendants. Each trained attendant costs $1500 in salary and benefits
per month while each trainee costs $700 per month. Normal attrition
(resignations and dismissals) in this occupation is high, 10% per
month, so Air Alberta never has any planned layoffs. Trainees are
hired on the first day of each month and become attendants on the
first day of the next month (with no attrition). As of March 1, Air Alberta
has 60 trained attendants.
Go to Excel
What do you mean hire 1.413
attendants?
• You can’t do that, right?
– Right.
– But: sometimes it’s better to ignore such
details
– Especially if the numbers are large:
• Not much difference between hiring 123 and 124
people, so might as well allow fractional values
Integer Constraints:
Include or leave out?
For
• More realistic
Against
• No sensitivity report
• May take longer to
solve
• Not that important if
numbers are big
Air Alberta (pg. 73)
1. Solver settings to find a least cost staffing plan:
2. The least cost staffing plan:
3. The total cost of this plan is $
4. In which month does Air Alberta have the most
excess attendant hours?
5. Air Alberta is considering running extra charter
flights requiring 1000 flight attendant hours in
either June or July. Which month would you
choose to minimize total staffing costs? Why?
Announcements
• Appeals on Q8 based on correct
calculation with bad input will be heard
• Appeals will not be successful on the
basis of:
– Running out of time
– Copying the wrong cell
– Order of weights on the WMA question
• For next class read Crazy Joey’s p. 77
MGTSC 352
Lecture 11: Aggregate Planning
Case 2: Mountain Wear
Set up and use solver to find minimum cost plan
Air Alberta
Tradeoffs: “So which one of those
did you want?” (pg. 50)
Production Inventory
Workforce
Overtime
Plan 1
Level
Changing
Level
0
Plan 2
Chase
500
Changing
0
Plan 3
Chase
500
Level
Changing
Level and chase:
Mountain Wear …
• Can we
find the
lowest
cost plan
with
solver?
Production Planning for Mountain Wear
Year
Quarter
Demand forecast
Units produced
Inventory:
Beginning-of-quarter
End-of-quarter
2001
4
500
Inventory cost
Production cost
Number hired
Number laid off
Workforce:
Beginning-of-quarter
End-of-quarter
Hours of overtime
Hiring cost
Layoff cost
Regular labor cost
Overtime cost
1
7000
7000
500
500
$3,000
$210,000
2
0
20
2002
2
3000
7000
500
4500
$15,000
$210,000
0
0
3
1000
7000
4
10000
7000
4500
10500
10500
7500
$45,000
$210,000
0
0
This is "Plan 1" from
"Case 2: Mountain Wear."
Unit costs Total costs
$54,000
$6
$117,000
$210,000
$30
$840,000
0
0
20
22
22
22
22
22
22
22
0
0
0
0 Unit costs Total costs
$6,000
$0
$0
$0
$3,000
$6,000
$0
$0
$0
$0
$1,500
$0
$220,000 $220,000 $220,000 $220,000
$10,000
$880,000
$0
$0
$0
$0
$23
$0
Labor hours:
Coefficients
Available
10,560
10,560
10,560
10,560
Required
10,500
10,500
10,500
10,500
hours per employee,
480 per quarter
hours of labor, per
1.5 unit
Total cost
$1,843,000
Active Learning: Formulate
Mountain Wear Problem in English
• 1 min., in pairs
• Template:
– Maximize / minimizes …
– By changing …
– Subject to …
Extending the
Mountain Wear Formulation
• How do we fire 10.625 people
• Should we include additional constraints?
– Limit on overtime?
– Limit on hirings / firings?
–?
• How do the additional constraints impact
cost?
Air Alberta (pg. 72)
Air Alberta is doing aggregate planning of flight attendant staffing for
the next 6 months. They have forecast the number of flight attendant
hours needed per month for March to August, based on scheduled
flights, and wish to determine how many new attendants to hire each
month. Each trained attendant on staff supplies 150 hours per month.
A newly hired attendant is called a trainee during the first month, and
each trainee’s net contribution is negative (-100 hours) because (s)he
requires supervision, which detracts from the productivity of other
attendants. Each trained attendant costs $1500 in salary and benefits
per month while each trainee costs $700 per month. Normal attrition
(resignations and dismissals) in this occupation is high, 10% per
month, so Air Alberta never has any planned layoffs. Trainees are
hired on the first day of each month and become attendants on the
first day of the next month (with no attrition). As of March 1, Air Alberta
has 60 trained attendants.
Go to Excel
What do you mean hire 1.413
attendants?
• You can’t do that, right?
– Right.
– But: sometimes it’s better to ignore such
details
– Especially if the numbers are large:
• Not much difference between hiring 123 and 124
people, so might as well allow fractional values
Integer Constraints:
Include or leave out?
For
• More realistic
Against
• No sensitivity report
• May take longer to
solve
• Not that important if
numbers are big
Air Alberta (pg. 73)
1. Solver settings to find a least cost staffing plan:
2. The least cost staffing plan:
3. The total cost of this plan is $
4. In which month does Air Alberta have the most
excess attendant hours?
5. Air Alberta is considering running extra charter
flights requiring 1000 flight attendant hours in
either June or July. Which month would you
choose to minimize total staffing costs? Why?
Pg. 74
More about Integer Constraints
• Problems with integer constraints
– Are harder for solver
– Can take a long time to find optimal solution
for large problems
– By default, solver stops when “within 5% of
optimal”
• What does it mean?
• How can we change the default?
What does it mean?
4
gap
= (5.1 – 3.8)/5.1
= 25%
Solution to LP
relaxation, profit = 5.1
y
3
Solver stops
when “gap” is
less than “integer
tolerance”
2
Best known feasible
solution (incumbent
solution), profit = 3.8
1
0
0
1
2
x
3
Changing the default
Standard Solver
Premium Solver
Options
 Integer Options
 Set “integer tolerance” to zero
Change to zero
Reading Assignment
For after break read:
• Case 6: WestPlast (pg. 104)
• Case 8: Lot Sizing at Altametal (pg. 111)
MGTSC 352
Lecture 12: Aggregate Planning
Air Alberta Example
Aggregate planning in the service sector
Crazy Joey’s Example
A model that can be linear or nonlinear depending on
how you set it up
Air Alberta (pg. 72)
Air Alberta is doing aggregate planning of flight attendant staffing for
the next 6 months. They have forecast the number of flight attendant
hours needed per month for March to August, based on scheduled
flights, and wish to determine how many new attendants to hire each
month. Each trained attendant on staff supplies 150 hours per month.
A newly hired attendant is called a trainee during the first month, and
each trainee’s net contribution is negative (-100 hours) because (s)he
requires supervision, which detracts from the productivity of other
attendants. Each trained attendant costs $1500 in salary and benefits
per month while each trainee costs $700 per month. Normal attrition
(resignations and dismissals) in this occupation is high, 10% per
month, so Air Alberta never has any planned layoffs. Trainees are
hired on the first day of each month and become attendants on the
first day of the next month (with no attrition). As of March 1, Air Alberta
has 60 trained attendants.
Go to Excel
Air Alberta (pg. 73)
1. Solver settings to find a least cost
staffing plan:
2. The least cost staffing plan:
3. The total cost of this plan is $
4. It is the beginning of April. Only 5
attendants have left. What are you going
to do now?
In Groups
Recorder is person with most vowels in their name
• What happens if we add integer constraints?
• What happens if we limit new hires to 15% of
trained attendants?
• In which month does Air Alberta have the most
excess attendant hours?
• Air Alberta is considering running extra charter
flights requiring 1000 flight attendant hours in
either June or July. Which month would you
choose to minimize total staffing costs? Why?
Integer Constraints:
Include or leave out?
For
• More realistic
Against
• No sensitivity report
• May take longer to
solve
• Not that important if
numbers are big
Pg. 74
More about Integer Constraints
• Problems with integer constraints
– Are harder for solver
– Can take a long time to find optimal solution
for large problems
– By default, solver stops when “within 5% of
optimal”
• What does it mean?
• How can we change the default?
What does it mean?
4
gap
= (5.1 – 3.8)/5.1
= 25%
Solution to LP
relaxation, profit = 5.1
y
3
Solver stops
when “gap” is
less than “integer
tolerance”
2
Best known feasible
solution (incumbent
solution), profit = 3.8
1
0
0
1
2
x
3
Changing the default
Standard Solver
Premium Solver
Options
 Integer Options
 Set “integer tolerance” to zero
Change to zero
Crazy Joey’s (pg. 77)
Crazy Joey's Electronic Liquidation store must determine how to stock
its shelves. Joey, being the shrewd (although crazy) character that he
is, has come to you with the information that he feels is relevant for
the decision. Joey's showroom has 5,000 sq. ft. to display his
merchandise. He uses two main suppliers: Double-E Televisions Inc,
and The Wavelength Radio Company. Joey is in the enviable position
of demand for the TVs and Radios he sells outstripping his capacity to
sell them. TVs usually stay for an average of one week on the shelf
before being sold, and the same is true of Radios. The relevant data
is shown below. Joey’s insurance specifies that he can keep at most
$100,000 of capital in the showroom. Joey has a long-standing and
positive relationship with Wavelength Radio, and he has agreed to
always keep at least 50% of his showroom inventory (measured in sq.
ft.) full of Radios.
Go to Excel
Crazy Joey’s (pg. 78)
Joey should keep ________ TVs and ______ radios in the showroom to
maximize profit. This will result in a weekly profit of _____________
Joey is considering two strategies to increase his profit even more.
Evaluate each of them separately.
1) StellarTech Corp. had come out with a new high definition TV. It offers $65
profit, but it takes up 15 sq. ft., and $215 in capital. If Joey were to start
buying from StellarTech as well, how many high definitions TVs should he
stock? ______ What will his profit per week be? ________
2) Joey notices that his insurance policy is keeping him from keeping his
showroom full of merchandise, and he thinks this is just silly. He can
increase his coverage, but he must do so in increments of $50,000 and
the premium will increase by $100 per week for every additional $50,000
of coverage. Should he increase his coverage? _____ If yes, then by how
much? ____ How much will this increase his profit per week? ____
Reading Assignment
• For next class: Case 8: Lot Sizing at
Altametal (pg. 111)
MGTSC 352
Lecture 13: Aggregate Planning
WestPlast Case
How to deal with multiple objectives
How to use binary variables
WestPlast (based on a true story)
• Plastic pellets
• Continuous chemical process
– Product switching results in waste
• Capacity < Demand
– Cap: 335,000 tonnes (est.)
(could be as high as 425,000 tonnes)
• Contractual obligations and forecasts
Active Learning
• Pairs, 1 min.
• How does Westplast evaluate the
“goodness” of a production plan?
• What do you think of their approach?
WestPlast Criteria
•
•
Maximize Revenue
Maximize Plant Capability Index (PCI)
1.
2.
3.
4.
5.
plant output rate
quality compared to industry standards
raw material quality needed
overhead burden
process aggravation
each subcriteria has a “weight” (10% - 30%)
PCI Example
1.
plant output rate
–
–
2.
quality compared to industry standards
–
–
3.
Product G: 100 (excellent quality)
Product H: 80 (slightly lower quality)
raw material quality needed
–
–
4.
5.
Product X: 100
Product Y: 70 (slower)
Product Q: 100 (least expensive raw material)
Product W: 60 (more expensive raw material)
overhead burden (low OH = 100)
process aggravation (low aggravation = 100)
PCI Example continued
Product Score Card
.20 (output score)
+ .10 (quality score)
+ .30 (raw mat. score)
+ .15 (overhead score)
+ .25 (aggravation score)
The result is a score between 0 and 100 with more
desirable products scoring closer to 100.
Pgs. 79 - 80
Questions
• Is their plan (pg. 80, column G) a good plan?
• Can we find a better plan? How?
• What is ‘better’?
Excel
Pgs. 79 - 80
Questions
• Our “better plan” produces 9 products. Suppose
that, on the average, adding a product takes
machine time equivalent to 10,000 lbs of output
per product.
• Does WestPlast want to make more than 9
products?
• Less than 9 products?
• How can we find out?
Pgs. 85-86
Using Binary Variables to
Limit # of Products
• Add binary decision variable for each product
(1 = produce, 0 = don’t produce)
• Add binary constraints
• Want:
– If binary variable = 0 then amt. produced = 0
– If binary variable = 1 then amt. produced ≤ demand
• IF() formulas would make the problem nonlinear
• Instead:
– Add constraint
amt. produced ≤ (binary variable)  demand
WestPlast wants to:
•
•
•
•
Maximize Revenue
Maximize PCI = Plant Capability Index
By changing product mix
Subject to:
– Contractual obligations
– Don’t produce more than forecast demand
• How can we optimize two criteria at the same
time?
MGTSC 352
Lecture 14: Aggregate Planning
WestPlast Case
How to deal with multiple objectives
How to use binary variables
AltaMetal Case
Aggregating into multiple product groups
An example where having integer constraints makes
the problem much harder to solve
WestPlast Criteria
•
•
Maximize Revenue
Maximize Plant Capability Index (PCI)
1.
2.
3.
4.
5.
plant output rate
quality compared to industry standards
raw material quality needed
overhead burden
process aggravation
each subcriteria has a “weight” (10% - 30%)
Pgs. 79 - 80
Questions
• Our “better plan” produces 9 products. Suppose
that, on the average, adding a product takes
machine time equivalent to 10,000 lbs of output
per product.
• Does WestPlast want to make more than 9
products?
• Less than 9 products?
• How can we find out?
Pgs. 85-86
Using Binary Variables to
Limit # of Products
• Add binary decision variable for each product
(1 = produce, 0 = don’t produce)
• Add binary constraints
• Want:
– If binary variable = 0 then amt. produced = 0
– If binary variable = 1 then amt. produced ≤ demand
• IF() formulas would make the problem nonlinear
• Instead:
– Add constraint
amt. produced ≤ (binary variable)  demand
Binary Variables
•
•
•
•
Assignment Problems
Location Problems
Routing Problems
Product Selection Problems
Anything with an either yes or no option
WestPlast wants to:
•
•
•
•
Maximize Revenue
Maximize PCI = Plant Capability Index
By changing product mix
Subject to:
– Contractual obligations
– Don’t produce more than forecast demand
• How can we optimize two criteria at the same
time?
How can we optimize two criteria
at the same time?
• Short answer: we can’t
– The criteria may be in conflict
– Maximizing revenue may mean minimizing
PCI
• Long answer: we can’t, but we can find the
“efficient frontier”
= “Set of Pareto optimal solutions”
= “Set of non-dominated solutions”
= “Production possibilities curve”
AltaMetal Ltd.
(Case 8, pg. 111, and pgs. 87 – 92)
• Another aggregate planning problem
– 1,000 products aggregated to 9 groups
AltaMetal Ltd.
(Case 8, pg. 111, and pgs. 87 – 92)
5,000
4,500
Total demand
Capacity
4,000
3,500
Tons
3,000
2,500
2,000
1,500
Is it possible to satisfy demand?
1,000
If so, how? (production plan by product group)
500
0
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Excel …
Active Learning
• Pairs, 1 min.
• Formulate AltaMetal’s problem in English
• What to optimize, by changing what,
subject to what constraints …
To many change-overs …
• The JIT (“just-in-time”) plan we found may
require too many changeovers
• What if we require a minimum lot size of 30
tons?
– Daily capacity = 90 tons
 At most 3 lots per day
• Changing cells:
– Old: # of tons of product X to produce in month Y
– New: # of __ of product X to produce in month Y
Excel …
Tired of Waiting for Solver?
• Hit Escape key
Summary (pg. 92)
JIT
# of different products
per month
Smallest lot
Ton-months of
inventory
30-ton-lots
Formulating Optimization Models (pg. 93)
• Formulate the problem in English
– Or French, or Chinese, or Icelandic, … 
• Start with data in spreadsheet
• Define decision variables – turquoise cells
• Express performance measure (profit, or cost, or
something else) as function of the decision
variables
• Express constraints on decision variables
– Scarce resources
– Physical balances
– Policy constraints
Solving Optimization Problems
• Try simple values of the decision variables to
check for obvious errors
• Guess at a reasonable solution and see if model
is ‘credible’ (sniff test)
– Look for missing or violated constraints
– Is profit (cost) in ballpark?
Optimizing with Solver
• Use Simplex LP method (‘assume linear model’)
whenever possible
• Set Options properly
– automatic scaling, assume non-negative
• Watch for diagnostic messages – do not ignore!
(infeasible, unbounded)
• Interpret solution in real-world terms and again
check for credibility
Things to Remember
• The Simplex LP method always correctly
solves linear programs
• Solver is a slightly imperfect
implementation of the Simplex method
(but you should generally assume that it is
correct)
• The biggest source of errors is in the
model building process (i.e., the human)
MGTSC 352
Lecture 15: Aggregate Planning
Altametal Case
Summary of Optimization Modeling
AltaMetal Ltd.
(Case 8, pg. 111, and pgs. 87 – 92)
• Another aggregate planning problem
– 1,000 products aggregated to 9 groups
AltaMetal Ltd.
(Case 8, pg. 111, and pgs. 87 – 92)
5,000
4,500
Total demand
Capacity
4,000
3,500
Tons
3,000
2,500
2,000
1,500
Is it possible to satisfy demand?
1,000
If so, how? (production plan by product group)
500
0
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Excel …
Active Learning
• Pairs, 1 min.
• Formulate AltaMetal’s problem in English
• What to optimize, by changing what,
subject to what constraints …
To many change-overs …
• The JIT (“just-in-time”) plan we found may
require too many changeovers
• What if we require a minimum lot size of 30
tons?
– Daily capacity = 90 tons
 At most 3 lots per day
• Changing cells:
– Old: # of tons of product X to produce in month Y
– New: # of __ of product X to produce in month Y
Excel …
Tired of Waiting for Solver?
• Hit Escape key
LINEAR
INTEGER
NONLINEAR
“Programming” MODELS
A Summary
CLASSIFICATION
Decision Variables
Functions
Fractional
Integer
Linear
LP
ILP
Nonlinear
NLP
INLP
LP
•
•
•
•
•
SIMPLEX method (linear algebra)
Corner point optimality
Move from corner-to-corner, improve obj.
Very efficient
Can solve problems with thousands of
variables and constraints
ILP
•
•
•
•
•
•
•
Branch & Bound (divide-and-conquer)
Solve the LP, ignoring integer constraints
Select a fractional variable, x6 = 15.7
Create two new problems: x6 ≤ 15, x6  16
Solve the new problems
Continue until all branches exhausted
# of branches is exponential in # of var.
NLP
• Gradient method (uses derivatives)
• Repeat until convergence
– Find an improving direction
– Move in the improving direction
• Converges to local optimum
• Multiple starts recommended
INLP
•
•
•
•
•
•
Ignore integer constraints, solve the NLP
Use Branch & Bound
Solve a series of NLPs
Computationally demanding
No guarantee of optimality
YUCK!
Formulating Optimization Models (pg. 93)
• Formulate the problem in English
– Or French, or Chinese, or Icelandic, … 
• Start with data in spreadsheet
• Define decision variables – turquoise cells
• Express performance measure (profit, or cost, or
something else) as function of the decision
variables
• Express constraints on decision variables
– Scarce resources
– Physical balances
– Policy constraints
Solving Optimization Problems
• Try simple values of the decision variables to
check for obvious errors
• Guess at a reasonable solution and see if model
is ‘credible’ (sniff test)
– Look for missing or violated constraints
– Is profit (cost) in ballpark?
Optimizing with Solver
• Use Simplex LP method (‘assume linear model’)
whenever possible
• Set Options properly
– automatic scaling, assume non-negative
• Watch for diagnostic messages – do not ignore!
(infeasible, unbounded)
• Interpret solution in real-world terms and again
check for credibility
Things to Remember
• The Simplex LP method always correctly
solves linear programs
• Solver is a slightly imperfect
implementation of the Simplex method
(but you should generally assume that it is
correct)
• The biggest source of errors is in the
model building process (i.e., the human)
Exam
• Covers everything not covered by the last exam.
Starts with simulation
• If you cant’ get the optimal solution – get a pretty
good one.
• You may run out of time. Don’t spend time
looking for an answer you don’t know until you
have finished what you DO know.
• SAVE AND SUBMIT OFTEN
• The Exams will begin at 5 minutes after the hour.
Everyone will be let into the labs on the hour.
Please do not attempt to enter earlier.
What if you see an unfamiliar
solver message
• Ask a TA / manager
• Possible answers:
– I’ll fix it for you
– We have discussed the meaning of this
message in lecture / labs / assignments
– There is a problem with your model
• Always better to ask than not ask
MGTSC 352
Lecture 16:
Forecast Errors and Aggregate Planning
Air Alberta revisited:
using safety staffing to hedge
against uncertain turnover
Mountain Wear revisited:
using safety stock to hedge
against uncertain demand
Material under
development: not in
course pack
Forecast errors and
aggregate planning
• Forecasts provide input to aggregate
planning
• So far, we have only used point forecasts
• What about forecast errors?
• How do they impact aggregate plans?
Back to Air Alberta
•
•
•
•
“Normal attrition … is 10% per month.”
p = 10% is a point forecast
Suppose we have n = 100 employees
Actual attrition is a random variable:
binomial distribution with n = 100, p = .10.
• Expected value: n  p = 100  0.1 = 10
• Standard deviation:
n  p  (1  p)  100  0.1 0.9  9  3
What’s a Binomial Distribution?
• Toss a die 5 times
• X = number of tosses with a 1 or a 2
• X has a binomial distribution with n = 5, p = 2/6
Probability
40%
32.9%
32.9%
30%
20%
10%
16.5%
13.2%
4.1%
0.4%
0%
0
1
2
3
4
Number of tosses with a 1 or a 2
5
Active Learning
• In pairs, 1 min.
• Think of 3 other situations that can be
described by a binomial distribution
Our goal
Air Alberta
Flight Attendant Staffing Plan
Random values
generated
by the computer March
April
May
June
July
Attendant Hrs. needed
8000
7000
8000
10000
9000
Trained Attendants
60
58
64
74
76
Trainees Hired
2
13
15
7
16
Attrition: mean
6.00
5.80
6.40
7.40
7.60
Attrition: stdev
2.32
2.28
2.40
2.58
2.62
Attrition: actual
4
7
5
5
7
Attendant Hrs. Available
8800
7400
8100
10400
9800
Monthly Staffing Cost $ 91,400.00 $ 96,100.00 $ 106,500.00 $ 115,900.00 $ 125,200.00 $
Surplus
800
400
100
400
800
Shortage
0
0
0
0
0
Turnover rate:
Attendant cost
Trainee cost:
Attendant availability:
Trainee availability:
10% /month
$1,500 /person/month
$700 /person/month
150 hrs/person/month
-100 hrs/person/month
Total Staffing Cost: $
Formula in B9: =ROUND(NORMINV(RAND(),B7,B8),0)
August
12000
85
0
8.50
2.77
14
12750
127,500.00
750
0
662,600.00
Back to Air Alberta
• Actual attrition:
– n = # of trained attendants, p = 0.10
– Binomial distribution with mean = 0.1*n,
standard deviation SQRT(0.09*n)
– We’ll approximate this with a normal
distribution with the same mean and standard
deviation
Breaking the formula down
• ROUND(NORMINV(RAND(),mean,stdev),0)
• Step 1: generate random number
RAND()
• Step 2: convert random number to normal
distribution
NORMINV(RAND(),mean,stdev)
• Step 3: round to whole number
ROUND(NORMINV(RAND(),mean,stdev),0)
Q: What’s the impact of random
attrition?
• A: Attendant hour shortages (and surplus)
• What we’ll do:
– Come up with a hiring plan
– Evaluate the hiring plan by simulating attrition
– Compute shortages
– Replication: simulate n future scenarios
– Summarize results of the future scenarios
Active Learning
• Pairs, 1 min.
• How can we modify a hiring plan to hedge
against uncertain attrition?
• (Hedge = protect / insure)
Safety capacity vs.
staffing cost tradeoff
• Higher safety capacity  higher staffing cost 
less shortage
• Set safety capacity = X% of required hours
• Try X = 0, 2.5, 5
• Generate a hiring plan for each X
• Use simulation to evaluate each hiring plan
Let’s do it …
VBA Seminar
• The OM Club is putting on a free
introductory VBA seminar this Saturday,
March 17 from 12-2pm
• Seminar includes:
– Recording macros
– Basic coding
– Applying VBA to MGTSC problems
• Learn how to make VBA work for you!
Lilydale Site Tour
• Come check out the next OM Club facility
tour
• March 16, 2:30 pm in the lobby of the
Business building
• Transportation will be provided to and from
campus
Announements
• For next lecture, Read
– Rent-a-dent p. 114
– Oil Production p. 118
• Grades on Quiz 2 should be out today if
not already.
MGTSC 352
Lecture 17:
Quiz 2
Forecast Errors and Aggregate Planning
Air Alberta revisited: recap
Mountain Wear revisited:
using safety stock to hedge against uncertain
demand
Q1
Given the Efficient Frontier depicted below, which firm, “A”
“B” or “C” has a competitive advantage?
A
B
C
True / False
•
•
Aggregate planning is about matching
supply with demand. True or False?
Aggregation in aggregate planning
makes the resulting plan more accurate.
True or False?
Short Answer
Many government contracts are awarded to the
lowest bidder. The government agency will put
out for bid the specifications required to meet the
contract. If your product does better than the
specifications it doesn’t benefit you, the contract
goes to the lowest bidder among those who can
meet the specifications. As discussed in class,
price is the order winner. Meeting the
specifications is an example of an order
________ (one word).
Q 10
•
You need to decide how much inventory to order each
day for your company. Your company buys inventory
for $3/$7 per unit. They sell it for $10 per unit. If
demand equals or exceeds the amount you ordered,
everything you order is sold. Inventory is perishable;
any inventory left over immediately becomes obsolete
and is disposed of at no cost. Leftover inventory has
no value. It cannot be saved to be used in later
periods. Demand is random between 50 and 150 units
per day, and every value in that range is equally likely
(uniformly distributed.) You order inventory in packs of
10, full packs only. Forty days of simulated demand are
given in the excel file.
Other Points
• It’s not all about solver.
• It’s not even all math.
• It IS about modeling.
– Why model?
• If stuck, break the problem down.
– What am I trying to achieve?
– What can I change (levers) to achieve it?
– Of the things to change, what are my options?
• Plus, you really will do better on the exam if you
come to class and think about the questions.
Air Alberta Recap
• Identify uncertainty in input data
– Attrition is highly uncertain
• Identify “lever” to hedge against
uncertainty
– Lever: % safety capacity
• Generate multiple plans by “turning the
lever”
– Vary the % safety capacity, run solver for each
value
Air Alberta Recap
• Evaluate each plan
– Compute total staffing cost
– Use simulation to estimate average total
shortage
• Generate trade-off curve
– Plot total staffing cost vs. average total
shortage
• Decide on a plan
Air Alberta Tradeoff Curve
1,000
Avg. total shortage
900
800
700
600
500
400
300
200
100
$640
$645
$650 $655 $660 $665 $670
Total staffing cost (thousands)
$675
Let’s do the same for
Mountain Wear:
• Identify uncertainty in input data
– Demand forecasts are likely to be uncertain
– Assumption 1: actual demand is normally distributed,
with mean = point forecast and std. dev. =
SQRT(point forecast)
– Assumption 2: demand that is not satisfied is lost
• Identify “lever” to hedge against uncertainty
– Safety stock = minimum planned inventory level
• Generate multiple plans by “turning the lever”
– Vary safety stock, run solver for each value
Mountain Wear
• Evaluate each plan
– Use simulation to estimate:
avg. total inventory cost and
avg. total lost demand
• Generate trade-off curve
– Plot avg. total inv. cost vs. avg. total lost
demand
• Decide on a plan
Mountain Wear Tradeoff Curve
Avg. total inventory cost
$24,000
$22,000
$20,000
$18,000
$16,000
$14,000
$12,000
$10,000
-
20
40
60
Avg. total lost demand
80
100
MGTSC 352
Lecture 18:
Distribution Planning
Rent-A-Dent example: a transportation
problem
Oil example: a minimum cost flow problem
Henderson Food Company: shortest path
problem
Distribution Planning
• Rent-a-Dent example:
Transportation problem
• Oil example:
Minimum cost network flow problem
• Henderson Food Company example:
Shortest path problem
Transportation Problem
“arcs” / “links”
Supply nodes 
3 10
$54
15
1
$17
4 10
$18
$24
18
$23
2
5
$19
10
$30
$31
6 3
 Demand nodes
Minimum Cost Flow Problem (pg. 118)
Problem Data
-17
+22
$16
1
$14
$12
4
$4
3
0
$12
$14
$8
2
5
$13
+8
-13
Capacity of each arc: 10 units
To Excel
Task:
Send flow from supply (-) to
demand (+) nodes
Goal:
Minimize transport cost
Changing cells:
flow on each link
Constraints:
flows  arc capacities
flow in + supply  flow out + demand
flows  0
Min Cost Flow: Testing Your Intuition
• What if total supply < total demand, and we use
flow in + supply  flow out + demand?
• What if total supply > total demand, and we use
flow in + supply = flow out + demand
(= instead of )?
Rent-a-Dent vs Oil
• Rent-a-Dent (transportation problem)
– Every node is a supply or demand node
– “Arcs” correspond to routes
– Finds From-To shipments
• Oil (minimum cost flow problem)
– Full network
– Arcs correspond to route segments
– Finds flows on each segment
Relations
• Transportation problem is a special
case of the min cost flow problem
• Shortest path problem is (also) a special
case of min cost flow problem.
– What?
Find the “cheapest” path from
Node 1 to Node 5
• Can you manipulate the problem data so
as to “trick” Excel?
-17
-1
+22
$16
1
$14
$12
4
$4
3
0
$12
$14
$8
2
5
$13
-13
+1
+8
to Excel
Pg. 125
Quickest route from
Lethbridge to Fort McMurray that...
• ..does not go through Calgary?
• ..goes through Rocky Mountain House?
• ..includes the Drumheller-Red Deer arc?
• Suppose you are interested in the shortest
route from Lethbridge to Fort McMurray,
not the fastest route. Now what?
Shortest Path from Vancouver to
Miami that goes through Toronto
Vancouver
Toronto
What’s wrong
with this picture?
Miami
MGTSC 352
Lecture 19:
Distribution Planning
Extended Oil Pipeline: combining facility
location with distribution.
Distribution Game
Distribution Planning
…last time
• Rent-a-Dent example:
Transportation problem
• Oil example:
Minimum cost network flow problem
• Henderson Food Company example:
Shortest path problem
Oil Pipeline Problem Revisited:
Adding New Facilities
-17
$16
1
$14
$4
7
$12
+22
10
4
10
3
3
0
Optimal solution to
original problem
$12
2
$14
Purchase oil:
Fixed cost = $25 / day
Production cost = $13 / unit
Maximum production = 2 units / day
$8
2
5
$13
10
-13
Capacity of each arc: 10 units
+8
New well:
Fixed cost = $4 / day
Production cost = $1 / unit
Maximum production = 7 units / day
New Decisions
• For each potential facility:
– Whether to “open” the facility – binary variable
– Amount to produce
• Relating the two decisions: if facility is not
opened, then amount to produce must be
zero
• How do we ensure this?
New objective
• Total cost
= pumping cost
+ fixed cost
+ production cost
To Excel …
Recap: Problem Formulation
• Minimize total cost
= pumping cost + fixed cost + production cost
• By changing:
– Flows on arcs
– Whether to open facilities (binary variables)
– Amount to produce at each open facility
• Subject to:
– Flow ≤ capacity
– Amount to produce ≤ (binary variable)(max. prod’n)
– Supply + Amount to produce + Flow in
 Demand + Flow out
Distribution Game (pg.165)
Retailer
Supplier
Warehouse
Retailer
Retailer
Distribution Game (p.166)
•
•
•
•
•
•
•
•
•
Supplier to warehouse transit time: 15 days
Warehouse to retailer transit time: 5 days
Daily demand per retailer: 0, 1, 2, 3 or 4 units
Selling price: $100/unit
Purchase price: $70/unit
Supplier to warehouse order cost: $200
Warehouse to retailer order cost: $2.75
Warehouse holding cost: $0.040/unit/day
Retailer holding cost: $0.048/unit/day
MGTSC 352
Lecture 19:
Distribution Planning
One last look at shortest paths
Inventory Management
What is it, why keep it, inventory policies
Inventory Management (pg. 132)
Inventory = goods that have not yet been sold
–
–
–
–
raw material
work-in-process
finished goods
supplies
Year
Total
inventory
% of
GDP
Total inventory /
monthly shipments
95
$52 B
6.7%
1.4
More Recent Data
Inventory / monthly shipments
1.70
1.60
1.50
1.40
1.30
1.20
1.10
1.00
1990
1995
2000
2005
2010
Year
Active learning: (1 min. / pairs)
What does the inventory / monthly
shipments ratio mean?
Why Keep Inventory?
1. Seasonality (anticipated variation)
2. Provide flexibility (unanticipated
variation) a.k.a.:
3. Economies of scale
4. Price speculation (not an ops reason)
5. Something to work on
6. NDR,JP
Inventory Policy
Answers two general questions
• When to order? (ROP = reorder point)
• How much to order? (Q = reorder quantity)
Relevant Costs
• Acquisition cost
($/unit purchased)
• Ordering costs
($/order)
– clerical expenses
– delivery, inspection
– setup (prod.)
• Carrying costs = Holding costs
($/unit/time unit)
–
–
–
–
cost of capital
insurance
shrinkage, spoilage, obsolescence
material handling (fork lifts, space)
• Shortage costs
($/unit short)
– lost goodwill, discounts, penalties
– lost sales
– shut down of assembly line (prod.)
A&E Noise (VCRs) (pg. 134)
•
•
•
•
Cost: $150
Price: $175
Ordering cost: $30/order
Holding cost:
0.25150/365 = $0.1/unit-day
• Inventory policy
– Order 80 when inventory position ≤ 60
– Inventory position = inv. on hand + inv. in transit
– Lead time: 5 days
Order 80 when inventory pos. ≤ 60
Lead time: 5 days
1. Inventory POSITION includes inventory in transit.
2. Example 1 - the Distribution Game
3. Example 2 - Today is 21 Aug. 1997, and there
are 50 units in stock. The last two orders were
placed on 9 Aug. and 18 Aug. Should Jane order
today?
I = 50 + 80 = 130 > 60,
do not order today
(Aug 18 order in transit)
Inventory
Maximum inventory
Avg. inventory
ROP
Demand
during
leadtime
Q
Leadtime
Minimum inventory
Time
Finding Good Inventory Policies
• Approach 1: Simulation
– We will use historical sales (instead of generating
random future sales)
– We will assume demand = sales
(an approximation)
– Experiment with different values of Q and ROP
• Approach 2: EOQ + LTD
– An approximate model
– Simpler to use
– More abstract
to Excel
Acquisition Costs (pg. 142)
• Total units sold per year 
(10.12 VCRs/day)(365 days/year) = 3695 VCRs/year
• Total acquisition costs per year 
($150/VCR)(3695 VCRs/year) = $554,350/year
• Total acquisition costs per year if order size were
changed to 90 
$554,350 / year
Acquisition costs are not affected by inventory
policy, as long as demand is satisfied and there
are no volume discounts
Order Costs
• Cost per order =
$20 + ($20 / hour)(0.5 hours) = $30 / order
• Number of orders per year 
(3695 VCRs / year)/(80 VCRs / order)
= 46.2 orders / year
• Total order cost per year 
(46.2 orders / year)($30 / order)
= $1385.63 / year
Inventory
Simulated inventory profile
Time
Inventory
Approximation 1: constant demand
Time
Inventory
Maximum inventory
Avg. inventory
ROP
LTD =
Demand
during
leadtime
Q
Leadtime
Minimum inventory
Time
Holding Costs (pg. 143)
• Minimum inventory 
ROP – LTD = 60 VCRs – (5 days)(10.12 VCRs/day)
= 9.4 units
• Maximum inventory 
Min. inv. + Q = 9.4 + 80 = 89.4 VCRs
• Average inventory 
(min + max)/2 = (9.4 + 89.4)/2 = 49.4 VCRs
• Total VCR-years of inventory per year 
(49.4 VCRs)(1 year) = 49.4 VCR-years
• Total holding cost per year 
(49.4 VCR-years)($37.5 / VCR / year) = $1852.50 / year
Inventory
5 VCRs
4 VCRs
3 VCRs
2 VCRs
1
VCR
What is a “VCR-year” of inventory?
Avg. inventory
1 VCR-year of inventory
1 VCR-year of inventory
Total inventory for the year:
1 VCR-year of inventory
5 VCR-years
1 VCR-year of inventory
1 VCR-year of inventory
1 year
Time
Notes
• Quiz This Friday
• Covers 13 March through today
MGTSC 352
Lecture 21:
Inventory Management
A&E Noise example
Methods for finding good inventory policies:
1) simulation
2) EOQ + LTD models
Using EOQ for the Distribution Game:
Multi-Echelon Systems
Why Keep Inventory?
1. Seasonality (anticipated variation)
2. Provide flexibility (unanticipated
variation) a.k.a.:
3. Economies of scale
4. Price speculation (not an ops reason)
5. Something to work on
6. NDR,JP
Inventory By Where it IS
•
•
•
•
Raw Materials
Finished Goods
Work in Process
Or, with apologies to PS, “One man’s
ceiling is another man’s floor.”
Inventory
Approximation 1: constant demand
Therefore: We let inventory drop to
zero just before an order arrives
Time
Acquisition Costs (pg. 142)
No matter what the inventory policy,
acquisition costs = Demand X Cost
They don’t change,
So they don’t go in the model
(Unless you get quantity discounts, then it matters.)
Order Costs
•
Number of orders per year 
(3695 VCRs / year)/(80 VCRs / order)
= 46.2 orders / year
•
Total order cost per year 
(46.2 orders / year)($30 / order)
= $1385.63 / year
•
Total Order Costs = S * D/Q
Holding Costs (pg. 143)
• Minimum inventory  0 for now
Later = Safety Stock
• Maximum inventory = Q (+SS)
• Average inventory 
Q/2 = (80)/2 = 40 VCRs
• Total holding cost per year 
(40 VCR-years)($37.5 / VCR / year) = $1500 / year
• Total Holding Costs = H*Q/2
pg. 144
EOQ = Economic Order Quantity Model
• Given demand is constant
• Find the Q that minimizes total cost
Acquisition costs don’t
depend on Q
Total cost = acquisition cost + order cost +
carrying cost + shortage cost No shortages, by
assumption
• Total relevant cost = order cost + carrying cost
EOQ Derivation
S = order cost ($/order)
H = carrying cost ($/item/year)
D = demand (units/year)
Relevant cost
RC
RC(Q)
=
=
=
order cost
SN
SD/Q
pg. 147
Q = order quantity
N = number of orders per year
Iavg = average inventory
+
+
+
carrying cost
H  Iavg
HQ/2
Note: you can change
year to day, week, or any
other time unit, as long as
you are consistent
Common mistake:
inconsistent time units
To Excel
EOQ Formula
Relevant cost
RC
=
RC(Q)
=
pg. 147
=
ordering cost +
carrying cost
SN
+
H  Iavg
SD/Q
+
HQ/2
The magic part (optional)
pg. 147
Using EOQ for A&E Noise YNOS XD
D = 10.12 VCRs/day,
S = $30/order,
H = $0.10/VCR/day
 Q* = SQRT(210.1230/0.10) = 77.9
 round to Q* = 78
N* = 10.12/78 = 0.13 orders/day = 47.4 orders/year
Order every 365/47.4 = 8 days
Relevant cost:
RC(Q*) = S  (D/Q*) + H  (Q*/2)
= 30  (10.12/78) + 0.10  (78/2)
= 3.90 + 3.90
= $7.80 / day = $2,847 / year
Common mistake:
using inconsistent time units
D = 10.12 VCRs/day, S = $30/order, H =
$37.5/VCR/year
 Q* = SQRT(210.1230/37.5) = 4
• Off by (77.9 – 4)/77.9 = 95%
• Will not be worth a lot of part marks
Pg. 149
More on EOQ: Economies of Scale
The Capital Health Region* operates four hospitals.
Presently each hospital orders its own supplies and
manages its inventory. A common item used is a sterile
intravenous (IV) kit, with a weekly demand of 600 per
week at each hospital. Each IV kit costs $5 and incurs a
holding cost of 30% per year. Each order incurs a fixed
cost of $150 regardless of order size. The supplier takes
one week to deliver an order. Currently, each hospital
orders 6,000 kits at a time.
Question 1: Could costs be decreased by ordering more
often?
Question 2: Would it make sense to centralize inventory
management for the four hospitals?
* Fictional data
Analysis for one Hospital
• D = 600 / week = (600 / week)  (52 weeks/year)
= 31,200 / year
• S = $150 / order
• H = 0.3  5 = $1.50 / kit / year
• Q = SQRT(2  D  S / H) = 2,498 ≈ 2,500
• Costs:
– Q = 6,000:
S  D / Q + H  Q / 2 = $780 + $4,500 = $5,280
– Q = 2,500:
S  D / Q + H  Q / 2 = $1,872 + $1,875 = $3,747
– 29% savings
Analysis for one Hospital
• D = 600 / week = (600 / week)  (52 weeks/year)
= 31,200 / year
• S = $150 / order
• H = 0.3  5 = $1.50 / kit / year
• Q = SQRT(2  D  S / H) = 2,498 ≈ 2,500
• Close your course pack
• Active Learning: How do we change the
analysis if inventory management were
centralized for the four hospitals?
Analysis for four hospitals
managed together
•
•
•
•
•
D = 4  31,200 / year = 124,800 / year
S = $150 / order
H = $1.50 / kit / year
Q = SQRT(2  124,800  150 / 1.5) = 4,996 ≈ 5,000
Costs:
– Each hospital operated independently:
4  $3,747 = $14,988 / year
– All four together:
S  D / Q + H  Q / 2 = $3,744 + $3,750 = $7,494 / year
– 50% savings
• Quadrupling demand doubles the optimal order
quantity and doubles the total relevant cost
Four hospitals managed together
• Costs:
– Each hospital operated independently:
4  $3,747 = $14,988 / year
– All four together:
S  D / Q + H  Q / 2 = $3,744 + $3,750 = $7,494 / year
– 50% savings
• Quadrupling demand doubles the optimal order
quantity and doubles the total relevant cost
Determining ROP with EOQ model
Lead time = 5 days
Demand during lead time = (5 days)  (10.12 VCRs / day)  51 VCRs
 Set ROP = 51 VCRs
Inventory
Problem: this
calculation
assumes constant
demand. May lead
to shortages too
frequently
ROP
demand during
lead time
lead time
Time
Pg. 149
What happens to Holding Cost
when we Increase ROP?
• EOQ: constant demand, zero safety stock
– ROP = avg. demand during lead time
– Iavg = (min + max)/2 = (0+Q)/2 = Q/2
– Holding cost = H  Q / 2
• If we add safety stock = SS, then:
– ROP = avg. demand during lead time + SS
– Iavg = Q/2 + min = SS + Q/2
– Holding cost = H  (SS + Q / 2)
Pg. 152
Inventory
How Shortages Happen
Active learning:
How could we
have avoided the
shortage?
ROP
Demand
during
leadtime
Leadtime
Demand that was not met
Time
Inventory
The demand during the lead
time is uncertain. Here are 4
possibilities.
ROP
We’ll see how to pick
ROP so as to provide
a specified fill rate
… to Excel
Time
LTD Recap
• “LTD” worksheet in A&E Noise workbook
– Purpose: vary ROP (and Q, if desired) and
see what happens to the fill rate
• “LTD-exotic version”: can vary the lead
time
– Useful for comparing suppliers that provide
different lead times
pg. 151
Simulation versus EOQ
Dimension
Simulation
EOQ + LTD
Ease of evaluating a
policy
Need to build model –
time consuming
Simple formula for RC
– back of an envelope
Finding the optimum
Trial and error / data
table
Plug into formula for
Q*
Random demand
fluctuations
Taken into account
Ignored in EOQ
Seasonal demand
fluctuations
Can be taken into
account
Ignored
Shortages
Taken into account
Ignored in EOQ
Likely errors
(common mistakes)
Errors in formulas
Inconsistent units
Pg. 158
Back to the Distribution Game: Can
we use EOQ here?
A “multi-echelon” system
Supplier
Warehouse
Retailer
Retailer
Retailer
Using EOQ for a two-echelon
system
• Upper echelon:
– Use warehouse holding cost rate
• Ignore higher cost of holding inventory at retailers
– Lead time
= 15 (supplier  warehouse) + 5 (warehouse  retailer)
= 20 days
• Lower echelon:
– Use incremental retailer holding cost rate
– Lead time = 5 days
• Coordination: warehouse order size should be a
multiple of the sum of the retailer order sizes
Data
Assume open 250 days / year
•
•
•
•
•
•
•
•
•
Supplier to warehouse transit time: 15 days
Warehouse to retailer transit time: 5 days
Demand per retailer: 500 per year
Selling price: $100/unit
Purchase price: $70/unit
Supplier to warehouse order cost: $200
Warehouse to retailer order cost: $2.75
Warehouse holding cost: $10/unit/year
… To Excel
Retailer holding cost: $12/unit/year
Upper echelon:
Use warehouse holding cost rate
(Ignore higher cost of holding inventory at retailers)
Lead time = 15 (supplier  warehouse) + 5 (warehouse 
retailer)
= 20 days
Upper echelon
Supplier
Warehouse
Retailer
Retailer
Retailer
Lower echelon:
Use incremental retailer holding cost rate
= retailer holding cost rate – warehouse holding cost rate
Lead time = 5 days
Lower echelon
Retailer
Supplier
Warehouse
Retailer
Retailer
Coordination
• Suppose each retailer uses QLower = 20. If
all retailers order at once, the total is 60.
• Active learning: you are the warehouse
manager. Knowing the retailer order
sizes, how would you pick the warehouse
order size?
Using EOQ for a 2-echelon system:
the details
• Upper echelon:
–
–
–
–
–
DUpper = 3  DRetailer
SUpper = SWarehouse
HUpper = HWarehouse
LTUpper = LTSupplier  Warehouse + LTWarehouse  Retailer
ROPUpper = DUpper  LTUpper
• Lower echelon
–
–
–
–
–
DLower = DRetailer
SLower = SRetailer
HLower = HRetailer - HWarehouse
LTLower = LTWarehouse  Retailer
ROPLower = DLower  LTLower
• Coordination: QUpper = n  SUM(QLower)
• Choose n (an integer) and QLower to minimize total cost for
the whole system
Data
Assume open 250 days / year
•
•
•
•
•
•
•
•
•
Supplier to warehouse transit time: 15 days
Warehouse to retailer transit time: 5 days
Demand per retailer: 500 per year
Selling price: $100/unit
Purchase price: $70/unit
Supplier to warehouse order cost: $200
Warehouse to retailer order cost: $2.75
Warehouse holding cost: $10/unit/year
… To Excel
Retailer holding cost: $12/unit/year
Announcements
• Quiz 3 Tomorrow
• If you can’t do what you’re supposed to
do;
DO SOMETHING
• CHECK you submission!
MGTSC 352
Lecture 21:
Inventory Management
A&E Noise example
Methods for finding good inventory policies:
1) simulation
2) EOQ + LTD models
Using EOQ for the Distribution Game:
Multi-Echelon Systems
Coordination
• Suppose each retailer uses QLower = 20. If
all retailers order at once, the total is 60.
• Active learning: you are the warehouse
manager. Knowing the retailer order
sizes, how would you pick the warehouse
order size?
Using EOQ for a 2-echelon system:
the details
• Upper echelon:
–
–
–
–
–
DUpper = 3  DRetailer
SUpper = SWarehouse
HUpper = HWarehouse
LTUpper = LTSupplier  Warehouse + LTWarehouse  Retailer
ROPUpper = DUpper  LTUpper
• Lower echelon
–
–
–
–
–
DLower = DRetailer
SLower = SRetailer
HLower = HRetailer - HWarehouse
LTLower = LTWarehouse  Retailer
ROPLower = DLower  LTLower
• Coordination: QUpper = n  SUM(QLower)
• Choose n (an integer) and QLower to minimize total cost for
the whole system
Data
Assume open 250 days / year
•
•
•
•
•
•
•
•
•
Supplier to warehouse transit time: 15 days
Warehouse to retailer transit time: 5 days
Demand per retailer: 730 per year
Selling price: $100/unit
Purchase price: $70/unit
Supplier to warehouse order cost: $200
Warehouse to retailer order cost: $2.75
Warehouse holding cost: $14.70/unit/year
… To Excel
Retailer holding cost: $17.50/unit/year
Pg. 161
BigBluePills, Inc.
•
•
•
•
•
•
•
•
Expensive drug treatments
Perishable – last only 3 months
Order once every 3 months
Regular cost: $400 per treatment
If demand > order size, place rush order
Rush cost: $1,000 per treatment
Price to patient: $650
How much should they order?
Single period models
• Perishable product
• Past demand data
• Must decide how much to order before
knowing actual demand for the period
• Must live with the consequences
–Q>D
–Q<D
wasted product
lost profit
• We’ve seen this before: it’s called the
“newsvendor problem”
Quarterly demand
> 30
26 - 30
21 - 25
16 - 20
5
10
15
20
25
30
Frequency
1
3
3
5
6
1
1
11 - 15
Category bin
<=5
6 - 10
11 - 15
16 - 20
21 - 25
26 - 30
> 30
7
6
5
4
3
2
1
0
6 - 10
18.00
6.66
5.00
34.00
<=5
Average
Stdev
min
max
Frequency
Five Years of Demand Data
Solution 1
• Average demand = 18, so …
– … let’s order 18 each quarter
– Profit = 18  (650 – 400) = $4,500
– Right?
• Q < D  lose ? / unit
• Q > D  lose ? / unit
• Do these cancel out on average?
Solution 2: simulation
$8,000
Solution 1 predicted this
profit with Q = 18
Average profit
5th percentile
95th percentile
$6,000
$4,000
$2,000
$5
10
15
20
25
-$2,000
-$4,000
-$6,000
-$8,000
-$10,000
Order size
30
35
40
45
The Flaw of Averages
When input is uncertain...
output given average input
may not equal
the average output
Huh?
• Average BigBluePill demand = 18
• Profit for Q = 18, given D = 18: $4,500
• Average profit with Q = 18: $1,740
– Less than half
• Optimal Q = 20, with avg. profit: $1,786
• Using avg. demand (ignoring variability)
• Seriously overestimates profit
• Results in a suboptimal decision
How bad can it get?
• What if rush cost is $1,800 (instead of $1,000)
• The “averaging analyst” will still recommend
Q = 18 and estimate P = $4,500.
• The actual profit with Q = 18 will be -$565.
• Using Q = 20 generates P = $1,217
• Using average inputs is a bad idea.
“How bad” will depend on data.
In general
Profit(AVERAGE(Demand1, Demand2, …, Demandn))

AVERAGE(Profit(Demand1), Profit(Demand2), …,
Profit(Demandn))
Simple example of the flaw of
averages:
• A drunk on a highway
• Random walk
Consider the drunk’s condition
• The AVERAGE location of the drunk
– Middle of the road
• The outcome at the middle of the road
ALIVE
• What do you think the average outcome
for the drunk is?
DEAD
Average inputs do not result in average outputs.
MGTSC 352
Lecture 23:
Inventory Management
Big Blue
Congestion Management
Introduction: Asgard Bank example
Simulating a queue
Types of congested systems, queueing template
Pg. 161
BigBluePills, Inc.
•
•
•
•
•
•
•
•
Expensive drug treatments
Perishable – last only 3 months
Order once every 3 months
Regular cost: $400 per treatment
If demand > order size, place rush order
Rush cost: $1,000 per treatment
Price to patient: $650
How much should they order?
Single period models
• Perishable product
• Past demand data
• Must decide how much to order before
knowing actual demand for the period
• Must live with the consequences
• We’ve seen this before: it’s called the
“newsvendor problem”
Quarterly demand
> 30
26 - 30
21 - 25
16 - 20
5
10
15
20
25
30
Frequency
1
3
3
5
6
1
1
11 - 15
Category bin
<=5
6 - 10
11 - 15
16 - 20
21 - 25
26 - 30
> 30
7
6
5
4
3
2
1
0
6 - 10
18.00
6.66
5.00
34.00
<=5
Average
Stdev
min
max
Frequency
Five Years of Demand Data
Solution 1
• Average demand = 18, so …
– … let’s order 18 each quarter
– Profit = 18  (650 – 400) = $4,500
– Right?
• Q < D  lose ? / unit
• Q > D  lose ? / unit
• Do these cancel out on average?
Tradeoffs
• Q>D
wasted product
– Lose $400
– On product bought but not sold
• Q<D
loss on Sale
– Lose $1000 - $650 = $350
– On Rush order (no lost sales)
• If order = average demand each outcome
will occur half the time.
• Is that what you want?
Solution 2: simulation
$8,000
Solution 1 predicted this
profit with Q = 18
Average profit
5th percentile
95th percentile
$6,000
$4,000
$2,000
$5
10
15
20
25
-$2,000
-$4,000
-$6,000
-$8,000
-$10,000
Order size
30
35
40
45
The Flaw of Averages
When input is uncertain...
output given average input
may not equal
the average output
Huh?
• Average BigBluePill demand = 18
• Profit for Q = 18, given D = 18: $4,500
• Average profit with Q = 18: $1,740
– Less than half
• Optimal Q = 20, with avg. profit: $1,786
• Using avg. demand (ignoring variability)
• Seriously overestimates profit
• Results in a suboptimal decision
How bad can it get?
• What if rush cost is $1,800 (instead of $1,000)
• The “averaging analyst” will still recommend
Q = 18 and estimate P = $4,500.
• The actual profit with Q = 18 will be -$565.
• Using Q = 20 generates P = $1,217
• Using average inputs is a bad idea.
“How bad” will depend on data.
In general
Profit(AVERAGE(Demand1, Demand2, …, Demandn))

AVERAGE(Profit(Demand1), Profit(Demand2), …,
Profit(Demandn))
Simple example of the flaw of
averages:
• A drunk on a highway
• Random walk
Consider the drunk’s condition
• The AVERAGE location of the drunk
– Middle of the road
• The outcome at the middle of the road
ALIVE
• What do you think the average outcome
for the drunk is?
DEAD
Average inputs do not result in average outputs.
Congestion
Asgard Bank ATM
Pg. 168
Time between Arrivals (min.)
> 4.00
3.75 - 4.00
3.50 - 3.75
3.25 - 3.50
3.00 - 3.25
40
2.75 - 3.00
50
2.50 - 2.75
60
2.25 - 2.50
2.00 - 2.25
1.75 - 2.00
70
1.50 - 1.75
1.25 - 1.50
1.00 - 1.25
0.75 - 1.00
0.50 - 0.75
0.25 - 0.50
0.00 - 0.25
Frequency
pg. 168
Asgard Bank: Times Between
Arrivals
(pg. 173)
Average = 1.00 min.
St. dev. = 0.92 min.
Arrival rate =  = ?
30
20
10
0
Asgard Bank: Arrival Rate
• Given: avg. time between arrivals = 1.00 minute
• average arrival rate per hour
==?
Duration of Service (min.)
3.80 - 3.90
3.60 - 3.70
3.40 - 3.50
3.20 - 3.30
3.00 - 3.10
2.80 - 2.90
2.60 - 2.70
2.40 - 2.50
2.20 - 2.30
40
2.00 - 2.10
50
1.80 - 1.90
1.60 - 1.70
1.40 - 1.50
1.20 - 1.30
1.00 - 1.10
0.80 - 0.90
0.60 - 0.70
0.40 - 0.50
0.20 - 0.30
0.00 - 0.10
Frequency
Asgard Bank: Service Times
80
70
60
Average = 0.95 min. (57 sec.)
St. dev. = 0.17 min. (10 sec.)
service rate =  = ?
30
20
10
0
Asgard Bank: Service Rate
• Given: avg. service time = 0.95 minutes
• average service rate per hour
(if working continuously)
==
• Note:
– the service rate is not the rate at which customers are
served
– it’s the rate at which customers could be served, if
there were enough customers
– service rate = capacity of a server
Service
ends
Service
time
Time in
system
Waiting
time
0.00
2.00
2.00
2.75
3.25
3.80
3.90
3.90
3.90
5.20
Service
starts
Arrival time
1
2
3
4
5
6
7
8
9
10
Real system:
Inter-arrival
time
Customer
number
Asgard Bank – Collecting Data
2.00
0.00
0.75
0.50
0.55
0.10
0.00
0.00
1.30
0.00
2.00
2.25
3.00
3.25
3.80
4.00
4.20
4.50
5.20
0.50
2.25
3.00
3.25
3.75
4.00
4.20
4.50
5.00
5.50
0.50
0.25
0.75
0.25
0.50
0.20
0.20
0.30
0.50
0.30
0.50
0.25
1.00
0.50
0.50
0.20
0.30
0.60
1.10
0.30
0.00
0.00
0.25
0.25
0.00
0.00
0.10
0.30
0.60
0.00
• Record arrival time,
service start, service end
• Compute inter-arrival
times, service times,
waiting time, time in
system
Simulating the system:
• Simulate inter-arrival
times, service times
• Compute arrival time,
service start, service end,
time in system, waiting
time
Why Do Customers Wait?
Given:
Average inter-arrival time = 1.00 min.
Average service time = 0.95 min.
0.95
Customer leaves
Next customer arrives
and begins service
Customer arrives
and begins service
What’s missing from this picture?
time
What’s missing from this Picture?
VARIABILITY!
Including Randomness: Simulation
• Service times:
Normal distribution,
mean = 57/3600 of an hour
stdev = 10/3600 of an hour
MAX(NORMINV(RAND(),57/3600,10/3600),0)
• Inter-arrival times:
Exponential distribution,
mean = 1/ 60 of an hour.
(1/60)*LN(RAND())
To Excel …
Simulated Lunch Hour 1:
Customer number
61
51
Waiting time
41
Service time
71 arrivals
31
21
Press F9 to recalculate
11
1
0.00
0.10
0.20
0.30
0.40
0.50
Time (hours)
0.60
0.70
0.80
0.90
1.00
Simulated Lunch Hour 2:
Customer number
61
51
Waiting time
41
Service time
31
50 arrivals
21
Press F9 to recalculate
11
1
0.00
0.10
0.20
0.30
0.40
0.50
Time (hours)
0.60
0.70
0.80
0.90
1.00
Simulated Lunch Hour 3:
Unused capacity
Customer number
61
51
Waiting time
41
Service time
31
21
Press F9 to recalculate
11
1
0.00
0.10
0.20
0.30
0.40
0.50
Time (hours)
0.60
0.70
0.80
0.90
1.00
Causes of Congestion
• Higher than average number of arrivals
• Lower than average service capacity
• Lost capacity due to timing
Lesson: For a service where customers arrive
randomly, it is not a good idea to operate the
system close to its average capacity
Anatomy of a Congested
System (pg. 172)
waiting room =
queue
potential customers
parallel servers
Notation: M/M/s/K/N
1. Inter-arrival time distribution
2. Service time distribution
•
•
M = exponential distribution
G = general distribution
3. s = number of servers
4. K = max. # of customers allowed in system
•
being served + waiting
5. N = population size
• K and N are left out when they are infinite
Types of Congested Systems We
Will Analyze (pg. 173)
Type
Service
time dist’n
Inter# of
# that
arrival time servers can wait
dist’n
# of
potential
customers
M/M/s
Exponential
exponential
s


M/M/s/s+C exponential
exponential
s
C

Finite
Population
exponential
exponential
s

M
M/G/1
general
exponential
1


Analyzing a Congested System
(pg. 174)
Inputs
System Description
Model of the System
Measures of Quality of Service
Outputs
Measures important to Servers
System Description




 (mu) = service rate (per server per time unit)
 (lambda) = arrival rate (per time unit)
s = number of servers
C = maximum number that can wait in line
= queue capacity
 M = number of potential customers
 s (sigma sub S) = standard deviation of service times
S
Measures of Quality of Service
 Wq = average time in queue
 W = average time in system
 Lq = average number of customers in queue
 L = average number of customers in system
 SL = service level = fraction of customers that wait
less than some given amount of time
 PrBalk = fraction of customers that balk
(do not enter system)
 PrWait = fraction of customers that wait
Measures Important to Servers
 r (rho) = utilization
= fraction of time each server is busy
And now the formulas for the
simplest case: M / M / 1
Or would you prefer
an Excel template?
Template.xls
• Does calculations for
–
–
–
–
•
M/M/s
M/M/s/s+C
M/M/s//M
M/G/1
Want to know more? Go to http://www.bus.ualberta.ca/aingolfsson/qtp/
• Asgard Bank Data
–
–
–
–
Model: M/G/1
Arrival rate: 1 per minute
Average service time: 57/60 min.
St. dev of service time: 10/60 min.
Asgard Conclusions
• The ATM is busy 95% of the time.
• Average queue length = 9.3 people
• Average no. in the system = 10.25
(waiting, or using the ATM)
• Average wait = 9.3 minutes
• What if the arrival rate changes to …
– 1.05 / min.?
– 1.06 / min.?
MGTSC 352
Lecture 23:
Congestion Management
Introduction: Asgard Bank example
Simulating a queue
Types of congested systems, queueing template
Ride’n’Collide example
MEC example
Manufacturing example
Analyzing a Congested System
(pg. 174)
Inputs
System Description
Model of the System
Measures of Quality of Service
Outputs
Measures important to Servers
Time between Arrivals (min.)
> 4.00
3.75 - 4.00
3.50 - 3.75
3.25 - 3.50
3.00 - 3.25
40
2.75 - 3.00
50
2.50 - 2.75
60
2.25 - 2.50
2.00 - 2.25
1.75 - 2.00
70
1.50 - 1.75
1.25 - 1.50
1.00 - 1.25
0.75 - 1.00
0.50 - 0.75
0.25 - 0.50
0.00 - 0.25
Frequency
pg. 168
Asgard Bank: Times Between
Arrivals
(pg. 173)
Average = 1.00 min.
St. dev. = 0.92 min.
Arrival rate =  = ?
30
20
10
0
Duration of Service (min.)
3.80 - 3.90
3.60 - 3.70
3.40 - 3.50
3.20 - 3.30
3.00 - 3.10
2.80 - 2.90
2.60 - 2.70
2.40 - 2.50
2.20 - 2.30
40
2.00 - 2.10
50
1.80 - 1.90
1.60 - 1.70
1.40 - 1.50
1.20 - 1.30
1.00 - 1.10
0.80 - 0.90
0.60 - 0.70
0.40 - 0.50
0.20 - 0.30
0.00 - 0.10
Frequency
Asgard Bank: Service Times
80
70
60
Average = 0.95 min. (57 sec.)
St. dev. = 0.17 min. (10 sec.)
service rate =  = ?
30
20
10
0
Including Randomness: Simulation
• Service times:
Normal distribution, mean = 57/3600 hrs, stdev
= 10/3600 hrs.
MAX(NORMINV(RAND(),57/3600,10/3600),0)
• Inter-arrival times:
Exponential distribution, mean = 1/ 60 hrs.
– (1/60)*LN(RAND())
To Excel …
Simulated Lunch Hour 1:
Customer number
61
51
Waiting time
41
Service time
71 arrivals
31
21
Press F9 to recalculate
11
1
0.00
0.10
0.20
0.30
0.40
0.50
Time (hours)
0.60
0.70
0.80
0.90
1.00
Simulated Lunch Hour 2:
Customer number
61
51
Waiting time
41
Service time
31
50 arrivals
21
Press F9 to recalculate
11
1
0.00
0.10
0.20
0.30
0.40
0.50
Time (hours)
0.60
0.70
0.80
0.90
1.00
Simulated Lunch Hour 3:
Unused capacity
Customer number
61
51
Waiting time
41
Service time
31
21
Press F9 to recalculate
11
1
0.00
0.10
0.20
0.30
0.40
0.50
Time (hours)
0.60
0.70
0.80
0.90
1.00
Causes of Congestion
• Higher than average number of arrivals
• Lower than average service capacity
• Lost capacity due to timing
Lesson: For a service where customers arrive
randomly, it is not a good idea to operate the
system close to its average capacity
Template.xls
• Does calculations for
–
–
–
–
•
M/M/s
M/M/s/s+C
M/M/s//M
M/G/1
Want to know more? Go to http://www.bus.ualberta.ca/aingolfsson/qtp/
• Asgard Bank Data
–
–
–
–
Model: M/G/1
Arrival rate: 1 per minute
Average service time: 57/60 min.
St. dev of service time: 10/60 min.
Asgard Conclusions
• The ATM is busy 95% of the time.
• Average queue length = 9.3 people
• Average no. in the system = 10.25
(waiting, or using the ATM)
• Average wait = 9.3 minutes
• What if the service rate changes to …
– 1.05 / min.?
– 1.06 / min.?
Ride’n’Collide (pg. 178)
•
•
•
•
•
Repair personnel cost: $10 per hour
Average repair duration: 30 minutes
Lost income: $50 per hour per car
Number of cars: 20 cars
A car will function for 10 hours on average
from the time it has been fixed until the
next time it needs to be repaired.
• How many repair-people should be hired?
Ride’n’Collide
• Customers =
• Servers =
• Average number in system =
•
•
•
•
Lost revenue per hour =
Arrival rate =
Service rate =
Model to use:
Waiting Line Analysis Template:
Which Model to Use?
• Who are the customers?
• Who are the servers?
• Where is the queue?
… not always obvious
• If you are told how many customers there
are
… then you should consider using the “finite
population” template
waiting room =
queue
Number is small
enough to worry
potential customers about
parallel servers
• If you are told the maximum number of
customers that can wait (the size of the
waiting room)
… then you should consider using the “finite
Q” template
waiting room =
queue
Capacity is small
enough to worry
potential customers about
parallel servers
• If you are told the standard deviation of the
service times, and there is 1 server
… then you should consider using the
“MG1” template
waiting room =
queue
one server, nonexponential service
time distribution
potential customers
• If you are told nothing about the size of the
pool of potential customers, or the
maximum number that will wait, or the
standard deviation of the service times,
… then you should probably use the “MMs”
template
MEC (p. 181)
• One operator, two lines to take orders
– Average call duration: 4 minutes exp
– Average call rate: 10 calls per hour exp
– Average profit from call: $24.76
• Third call gets busy signal
• How many lines/agents?
– Line cost: $4.00/ hr
– Agent cost: $12.00/hr
– Avg. time on hold < 1 min.
Modeling Approaches
• Simulation
• Waiting line analysis template
• We’ll use both for this example
To Excel …
Manufacturing Example (p. 184)
Machine
(1.2 or 1.8/minute)
1/minute
Poisson
arrivals
Exponential
service times
Manufacturing Example
• Arrival rate for jobs: 1 per minute
• Machine 1:
– Processing rate: 1.20/minute
– Cost: $1.20/minute
• Machine 2:
– Processing rate: 1.80/minute
– Cost: $2.00/minute
• Cost of idle jobs: $2.50/minute
• Which machine should be chosen?
To Excel …
Manufacturing Example
• Cost of machine 1 =
$1.20 / min. + ($2.50 / min. / job)  (5.00 jobs)
= $13.70 / min.
• Cost of machine 2 =
$2.00 / min. + ($2.50 / min. / job)  (1.25 jobs)
= $5.13 / min.
 Switching to machine 2 saves money –
reduction in lost revenue outweighs higher
operating cost.
Cost of waiting (Mach. 1)
• Method 1:
– Unit cost × L = ($2.50 / min. job)  (5.00 jobs)
= $13.70 / min
• Method 2:
– Unit cost ×  × W 
= ($2.50 / min. job)  (5.00 min)  (1 job/min)
= $13.70 / min
• Little’s Law
L=×W
MGTSC 352
Lecture 25:
Congestion Management
MEC example
Manufacturing example
MEC (p. 181)
• One operator, two lines to take orders
– Average call duration: 4 minutes exp
– Average call rate: 10 calls per hour exp
– Average profit from call: $24.76
• Third call gets busy signal
• How many lines/agents?
– Line cost: $4.00/ hr
– Agent cost: $12.00/hr
– Avg. time on hold < 1 min.
Modeling Approaches
• Waiting line analysis template
• Simulation
• We’ll use both for this example
To Excel …
The Laws of Queueing
•
eff =  (1 – PrBalk)
 Effective service rate = entering rate * % sticking around
•
r = eff /(s)
 utilization = demand / capacity
• W = Wq + 1/
 Time in the system = time in the Q + time in service
• L = = Lq + s*r
 # people in the system = # in the line + # in service
 # in service = # servers * probability of server being busy
• Little’s Law: L = effW
• Little’s Law for the queue: Lq = effWq
• Little’s Law for the servers: s*r = eff (1/)
Manufacturing Example (p. 184)
Machine
(1.2 or 1.8/minute)
1/minute
Poisson
arrivals
Exponential
service times
Manufacturing Example
• Arrival rate for jobs: 1 per minute
• Machine 1:
– Processing rate: 1.20/minute
– Cost: $1.20/minute
• Machine 2:
– Processing rate: 1.80/minute
– Cost: $2.00/minute
• Cost of idle jobs: $2.50/minute
• Which machine should be chosen?
To Excel …
Manufacturing Example
• Cost of machine 1 =
$1.20 / min. + ($2.50 / min. / job)  (5.00 jobs)
= $13.70 / min.
• Cost of machine 2 =
$2.00 / min. + ($2.50 / min. / job)  (1.25 jobs)
= $5.13 / min.
 Switching to machine 2 saves money –
reduction in lost revenue outweighs higher
operating cost.
Cost of waiting (Mach. 1)
• Method 1:
– Unit cost × L + op cost/min = ($2.50 / min.
job)  (5.00 jobs) + ($2/min)
= $13.70 / min
• Method 2:
– Unit cost ×  × W + op cost/min 
= ($2.50 / min. job)  (5.00 min)  (1 job/min) +
($2/min)
= $13.70 / min
• Little’s Law
L=×W
Manufacturing Example 2(p. 184)
Changed from 1 to 1.1
Machine
(1.2 or 1.8/minute)
1.1/minute
Reminder:
Poisson
arrivals
Exponential
service times
Cost of idle jobs
= holding cost
= $2.50 / minute / job
Manufacturing Example 3(p. 184)
Two machines, each taking twice as long
Machine
(1.2 or 2 at .6/min)
1/minute
Reminder:
Poisson
arrivals
Exponential
service times
Cost of idle jobs
= holding cost
= $2.50 / minute / job