MGTSC 352: Operations Management Lecture 1

1
MGTSC 352 Operations ManagementLecture 1
2
My name is ...

• Kenneth Schultz
• Office 340G Business
• Telephone [phone]
• Email klschult

3
This course is

• a continuation of MGTSC 312

Not ... Mgtsc ! Stats
4
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 whymaybe not)
• Write exams
• Sometimes multiple choice
• Sometimes regurgitation

5
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)

6
We want you to

• think with us (lectures, labs)
• interact with us
• take initiative/responsibility
• experiment aggressively
• learn by DOING
• This aint no sit-back-and-relax,
  you-pays-your-fees-and-you-gets-your-credits
  course.

7
Evaluation
8
Grade Distribution

• Similar to other 3rd / 4th year courses
• Your relative mark is what matters

9
Active Learning

• Form groups of two
• Whose birthday is earlier in the year?
• Youre the recorder
• Question What have you heard about this course?
• Time 1 minute

10
Why Active Learning?
11
What is this course about?
Production and delivery of goods and services

• Forecasting
• Simulation
• Aggregate Planning
• Distribution Planning
• Inventory Management
• Congestion Management

12
Show me a chart
13
Another Chart the Process View
14
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, order-pickers, web programmers
• Inventory
• Activities Order taking, order filling, shipping
• Process management Warehouses, inventory,
  distribution, capacity.
• Information structure Transaction data for each
  order

15
Active Learning

• In your groups again
• Task fill in as much of the next slide as you
  can
• Time 2 minutes

16
Example Business School

• Inputs
• Outputs
• Flow units

• Resources
• Capital
• Labor
• Activities
• Process management
• Information structure

17
Do I have to take this course?

• Majors that need 352 ASAP
• Operations Management
• Decision and Information Systems
• Distribution Management

• Majors that require 352
• 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

18
Who are we?

• Instructor Kenneth Schultz
• Lab Masters
• Morgan Skowronski
• Jen Tyrkalo
• Grading Jared Coulson
• Tech Master Angela Kercher
• Lab Accelerators

19
Kenneth Schultz

• Wharton Undergraduate
• 12 Years United States Army
• Ph.D. 1997, Cornell
• Research Including human behavior in Operations
  Management models.

20
My course priorities areIm fairYou learn
21
Morgan Skowronski
22
Jen Tyrkalo
23
Things To Do Before Next Class

• Course web
• Read the things to do pageWINTER 2007 MGTSC
  352 LEC B1 COURSE DOCUMENTS RESOURCES
  GENERAL RESOURCES
• Read FAQWINTER 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

24
Excel Basics

• Jan 20, 11 1, B24/B28
• Free
• Basic Excel skills

25
Course Packs

• 20
• Today, 3-5 in B20
• Wed, 10-12 in B20
• Friday in labs

26
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)

27
Inputs
Outputs
MODEL
Revenue Quantity x Price
28
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 havent encountered before, to go
  beyond experiential learning.
• Example

29
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?

30
Come again?

• May population 12,000
• August population?
• PAug PApr (a b ? PApr) ? PApr)
• ?
• In your groups!
• Time 1 min.

b a / Cap .45 / 100,000
31
Come again?

• May population 12,000
• August population?
• 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

b a / Cap .45 / 100,000
32
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)

33

• 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 eshin_at_ualberta.ca
• Meeting Tuesday, January 16 at 500 PM, Bus 4-10

34
Announcements

• HW 1 due Wednesday, 1159 PM
• OM Club Excel workshops
• Jan 20, 11 AM 1 PM
• Free
• Watch for a sign up link on the course page
• Dont have course pack yet?
• Get one Friday in Lab

35
MGTSC 352

• Lecture 2 Forecasting
• Why forecast?
• Types of forecasts
• Simple time series forecasting
  methodsIncluding SES Simple Exponential
  Smoothing
• Performance measures

36
Plant Site Selection

• Alberta Manufacturer
• Has one old plant, in Calgary
• Planning to build new plant, but where?
• Edmonton or Calgary?

37
Recent Demand Figures
38
What Would you Do?
39
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.ht
  ml

40
What is the Driver Doing?
41
Forecasting

• Technological forecasts
• New product, product life cycle (Ipod,
  Blackberry)
• Moores Law
• Gates Law
• Economic forecasts
• Macro level (unemployment, inflation, markets,
  etc.)
• Demand forecasts
• Focus in MGTSC 352

42
Moore's Law Computing power doubles about every
two years.
Gates Law The speed of software halves every
18 months.
Data from ftp//download.intel.com/museum/Moores_L
aw/Printed_Materials/Moores_Law_Backgrounder.pdf
43
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

44
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

45
Active learning

• Groups of two
• Recorder person that is born closest to Telus
  150.
• Task think of three quantities that youd like
  to forecast
• 1 minute

46
Choosing a Forecasting Method
47
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

48
(Simple) Exponential Smoothing

• Generalization of the WMA method
• Uses a single parameter for weights
• 0 ? LS ? 1
• Three steps
• Initialization ... F2 D1
• Calibration ... Ft1 LS Dt (1 - LS) Ft
• Prediction ... same formula
• Note the formula is a weighted average of Demand
  and Forecast from last period
• Excel

49
SES weights

• Decrease exponentially as data age
• Most recent data gets a weight of LS
• Ft1 LS Dt (1 - LS) Ft Rearrange...
• Ft1 Ft LS (Dt - Ft)
• Ft LS Et
• A learning model

50
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

51
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.)

52
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?

53
MGTSC 352

• Lecture 3 Forecasting
• Simple time series forecasting
  methodsIncluding SES Simple Exponential
  Smoothing
• Performance measures
• Tuning a forecasting method to optimize a
  performance measure
• Components of a time series
• DES Double Exponential Smoothing

54
Todays active learning

• Groups of two again
• Recorder person who got up earlier this morning

55
SES is really a WMA (pg. 19)

• Ft1 LS ? Dt (1LS) ? Ft
• t 6 F7 LS ? D6 (1LS) ? F6
• t 5 F6 LS ? D5 (1LS) ? F5
• t 4 F5 LS ? D4 (1LS) ? F4
• t 3 F4 LS ? D3 (1LS) ? F3
• t 2 F3 LS ? D2 (1LS) ? F2
• t 1 F2 D1

• Plug t 5 equation into t 6 equation
• F7 LS ? D6 (1LS) ? (LS ? D5 (1LS) ? F5)
• Active learning Multiply out
• F7 LS ? D6 LS ? (1LS) ? D5 (1LS)2 ? F5
• Repeat for t 4, 3, 2, 1

Final result F7 LS ? D6 LS ? (1LS) ?
D5 LS ? (1LS)2 ? D4
LS ? (1LS)3 ? D3 LS ? (1LS)4 ? D2
(1LS)5 ? D1
56
The Weights
LS 0.5
LS 0.3
LS 0.1
57

• 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

58
Simple Models Recap

• LP, AVG, SMA, WMA, SES
• Three phases
• Initialization
• Learning
• Prediction
• Prediction so far, weve only done
  one-period-into-the-future
• k periods-into-the-future Ftk Ft1, k 2, 3,
  
• Active learning translate formula into English

59
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

60
Components of a Time Series
Pg. 23

• level
• trend
• seasonality
• cyclic (we will ignore this)
• random (unpredictable by definition)
• (Simple) Exponential Smoothing incorporates...
• Level only
• Will lag trend
• Miss seasonality

61
Level, Trend, Seasonality
Level random
Level trend random
Level trend seasonality random
62
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

63
Models

• Double Exponential Smoothing
• Level, Trend
• Today
• Triple Exponential Smoothing
• Next week
• Simple Linear Regression with Seas. Indices
• Next week

64
Double Exponential Smoothing
Pg. 25

• Initialization
• Level, Trend
• Learning
• Prediction
• Formulas in course pack
• Work on an example

Excel
65
Learning
In general UPDATED S ?NEW (1 S) ? OLD
66
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?

67
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?

68
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

69
Reminders

• HW 2 due Wednesday at 1159 pm

70
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

71
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

72
Recap How Different Models Predict

• Simple models
• Ftk Ft1, k 2, 3,
• DES
• Ftk Lt (k ? Tt ), k 1, 2, 3,
• Linear trend
• TES and SLR w SI (cover today)
• Ftk (Lt k ? Tt) ? (Seasonality Index)

73
Whats 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

74
TES tamed

• Works in three phases
• Initialization
• Learning
• Prediction

• Tracks three components
• Level
• Trend
• Seasonality

75
Actual data Level Prediction
Prediction
Initialization
Learning
76
Actual data Level Prediction
Time to try it out Excel
77
TES - Calibration (p of seasons)
Pg. 29
Always UPDATED (S) NEW (1-S) OLD
One-step Forecast Ft1 (Lt Tt) St1-p
78
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

79
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

80
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
81
One-step forecasting the past
F(t1) L(t) T(t) S(t1-p) "To forecast
one step into the future, take the previous
periods level, add the previous periods trend,
and multiply the sum with the seasonality index
from one cycle ago."
82
k-step forecasting the future(real forecast)
Pg. 30

• F(t1) L(t) kT(t) S(t1-p)
• Active learning translate the formula into
  English
• One minute, in pairs

83
TES vs SLRwSI

• TES
• Ftk (Lt k ? Tt) ? Stk-p
• SLRwSI
• Ftk (intercept (t k) ? slope) ? SI

84
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

85
Which Method Would Work Well for This Data?
86
Patterns in the Data?

• Trend
• Yes, but it is not constant
• Zero, then positive, then zero again
• Seasonality?
• Yes, cycle of length four

87
Comparison

• TES SE 24.7
• TES trend is adaptive

• SLRwSI SE 32.6
• SLR uses constant trend

88
One-minute paper

• Dont put on your coat put your books away or
  whatnot, pull out a piece of paper instead.
• Review todays 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?
• Dont put your name on the paper.
• Stay in your seats for 1 minute.
• Hand in on your way out

89
MGTSC 352

• Lecture 5 Forecasting
• Choosing LS, TS, and SS
• SLR w SI Simple Linear Regression with
  Seasonality Indices
• Range estimates

90
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

91
Whats This Solver Thing?

• In Excel Tools ? Solver, to bring up

92
Using Solver to Choose LS, TS, SS
Pg. 33

• 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

LS TS SS
Something a bit bigger than zero (f. ex. 0.01,
0.05)
Something a bit smaller than one (f. ex. 0.99,
0.95)


Lets try it out
93
Why Solver Doesnt Always Give the Same Solution
Everywhere I look is uphill! I must have
reached the lowest point.
local optimum
global optimum
94
SLR w SI Simple Linear Regression with
Seasonality Indices
Pg. 34

• Captures level, trend, seasonality, like TES
• Details are different
• SLR Forecast
• Ftk (intercept (t k) ? slope) ? SI
• Excel

95
TES vs SLRwSI

• TES
• Ftk (Lt k ? Tt) ? Stk-p
• SLRwSI
• Ftk (intercept (t k) ? slope) ? SI

96
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

97
Which Method Would Work Well for This Data?
98
Patterns in the Data?

• Trend
• Yes, but it is not constant
• Zero, then positive, then zero again
• Seasonality?
• Yes, cycle of length four

99
Comparison

• TES SE 24.7
• TES trend is adaptive

• SLRwSI SE 32.6
• SLR uses constant trend

100
How Good are the Forecasts?
Pg. 38

• 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)

101
Forecast Error Distribution
102
Approximate with Normal Distribution
Standard Error of the forecast errors
Average Error .3 Standard Error 127
103
95 Prediction Interval

• 1-step Point forecast bias ? 2 ? StdError
• 9 Jan TSX 12654 .3 ? 2 ? 127 12654 ?
  25412400, 12908low, high
• Actual 12,467.99

104
Are TES and SLR w SI it?

• Certainly not
• Additive seasonality models
• TES or SLR w SD
• Multiplicative trend models
• TES or Nonlinear Regression (Dt1 1.1Dt)

105
Steps in a Forecasting Project
Pg. 39

• -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
106
How to select a model?
Pg. 41

• 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
107
Appropriate model...
Nonlinear (ex. power)
linear
S-curve (ex. any CDF)
108
DATA
109
TES vs. SLR w/ SI
Which method would you choose?
110
Holdout Strategy

• Ignore part of the data (the holdout data)
• Build models using the rest of the data
• Optimize parameters
• Forecast for the holdout data
• Calculate perf. measures for holdout data
• Choose model that performs best on holdout data
• Refit parameters of best model, using all data

111
TES vs. SLR w/ SIin holdout period
112
TES vs. SLR w/ SIin holdout period
Now which method would you choose?
113
Calgary EMS Data
Number of calls / month
Trend? Seasonality?
114
Checking for (Yearly) Seasonality
Number of calls / month
115
Weekly Seasonality
Avg. of calls / hr., 2004
116
Reminders

• HW 3 Posted
• HW 1 Graded and Posted
• Grading appeal process

117
MGTSC 352

• Lecture 6 Forecasting
• Wrap-up of ForecastingHoldout strategyDebugging
  Forecasting Models
• Monte Carlo SimulationPlaying Roulette with
  ExcelBard Outside example

118
95 Prediction Interval

• Technically correct formula
• Forecast Bias 2 x Std Error
• Heuristic for use in this class
• Forecast ? 2 ? SE

119
Steps in a Forecasting Project
Pg. 39

• -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
120
Appropriate model...
Nonlinear (ex. power)
linear
S-curve (ex. any CDF)
121
Calgary EMS Data
Number of calls / month
Trend? Seasonality?
122
Checking for (Yearly) Seasonality
Number of calls / month
123
Weekly or Hourly Seasonality
Avg. of calls / hr., 2004
124
How to select a model?
Pg. 41

• 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
125
Example Building Materials, Garden Equipment,
and Supply Dealers
126
TES vs. SLR w SI(Both optimized to minimize SE)
Which method would you choose?
127
One possibility Combining Forecasts
TES
SLR w SI
weight
(1 - weight)
Minimize SE of the combined forecast to find the
best weight
128
Holdout Strategy

• Ignore part of the data (the holdout data)
• Build models using the rest of the data
• Optimize parameters
• Forecast for the holdout data
• Calculate perf. measures for holdout data
• Choose model that performs best on holdout data
• Refit parameters of best model, using all data

129
TES vs. SLR w/ SIin holdout period
130
TES vs. SLR w SI in holdout period
Now which method would you choose?
131
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 hasnt seen yet?
• Question Why is SE during holdout period worse
  than SE during training period?

132
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)

133
Do Spreadsheet Models Have Errors?

• Field audits of real-world spreadsheets 94 had
  errorshttp//panko.cba.hawaii.edu/ssr/Mypapers/wh
  atknow.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

134
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

135
Playing roulette with Excel
To Excel
136
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?

137
Game 2

• 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?

138
Game 1 payoff distribution
139
Game 2 payoff distribution
140
Using Excel to get the right answer

• Simulate one spin RAND()
• Repeat 10,000 times
• Plot histogram
• To Excel

141
Excel Details
Pg. 43

• 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

142
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.

143
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?

144
Data Analysis

• Whats the probability of rain?
• What is the mean and standard deviation of demand
  when it rains?
• How about when it doesnt rain?
• How can we simulate demand?
• To Excel

145
Simulating Profit per show

• Simulate weather
• Simulate demand
• Make sure 0 demand capacity
• Calculate revenue
• Subtract cost
• Replicate!
• Remember freeze tables of simulation results

146
Final results
147
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

148
Quiz Schedule
All lab sections treated the same
149
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.

150
Reminders

• Quiz 3 is now on 30 March
• HW 3 due Wed
• Quiz Review Session, Thu 5 630 pm, BUS B-2428
• Optional
• QA session, no new material

151
MGTSC 352

• Lecture 7 Monte Carlo Simulation
• Bard Outside example

152
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?

153
Profit

• Profit Revenue Expenses
• Revenue
• Expenses
• What do we need to find out?
• How can we do this?

154
Data Analysis

• Whats the probability of rain?
• What is the mean and standard deviation of demand
  when it rains?
• How about when it doesnt rain?
• How can we simulate demand?
• To Excel

155
Simulating Profit per show

• Simulate weather
• Simulate demand
• Make sure 0 demand capacity
• Calculate revenue
• Subtract cost
• Replicate!
• Remember freeze tables of simulation results

156
Simulating a value from a Normal
DistributionBreaking the formula down

• ROUND(NORMINV(RAND(),mean,stdev),0)
• Step 1 generate random numberRAND()
• Step 2 convert random number to normal
  distributionNORMINV(RAND(),mean,stdev)
• Step 3 round to whole number ROUND(NORMINV(RAND(
  ),mean,stdev),0)

157
Converting random number to a normal distribution
Simulated Value 990.3
158
Final results
159
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

160
Pros and Cons

• Approach 1(simulate 2 ? 100)

• Approach 2(simulate 1 ? 100)

161
Bard Outside Example A Newsvendor Problem

• Bard Outside
• Decision of seats
• Uncertain future demand
• Demand of seats ? lost revenue
• Demand

• A newsvendor
• Decision of newspapers to get
• Uncertain future demand
• Demand of papers ? lost revenue
• Demand

162
Active Learning

• In pairs, 1 min.
• Think of three other examples of newsvendor
  problems
• Examples

163
Bard Outside Revisited

• We estimated the average profit per show with 200
  seats to be about 11 per night
• Bard Outsides accountant says theyve been
  earning an average of 100 per night
• Whats wrong?

164
Another look at the No Rain Attendance
Distribution
Attendance (up to 199)
200 or more 51 of the time
165
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
Attendance
Can we do better?
166
How about this Normal Distribution with Mean
200, Stdev 50
Attendance of 200 or more 51 Demand of 200 or
more 50
Demand
Attendance
The attendance distribution is a censored
version of the demand distribution. We need to
uncensor it before using it to simulate.
167
How Much Difference Does this Make?
168
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

169
Quiz Schedule
All lab sections treated the same Transition
periods are crucial
170
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.

171
When the quiz begins

• Take a deep breath!
• If the first question looks too simple, it is

172
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.

173
Near the end

• 5-minute warning
• Stop, save, submit
• Check that responses appear on confirmation web
  page
• If you have time, do more work
• Dont risk late penalty !
• When done delete files from desktop

174
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

175
Reminders

• Quiz Review Session, Thu 530 630 pm, BUS
  B-2428
• Optional
• QA session, no new material

176
MGTSC 352

• Lecture 9 Aggregate Planning
• Overview of Planning Matching Demand and
  Capacity
• Case 2 Mountain WearLeduc Control Example

177
Overview of Planning (pg. 46)
178
Sequence of Planning (pg. 47)
Corporate Strategy
External Conditions
Demand Forecasts
Aggregate Plan
Manufacturing
Service
Master Production Schedule
Weekly Workforce Customer Schedule
MRP Materials Requirements Planning
Daily Schedule
179
Matching Demand and Capacity

• Influencing demand
• ?

• Changing capacity
• ?

180
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

181
Case 2 Mountain Wear (pg. 96)
182
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
• Lets look at the first aggregate plan in the
  case

For next week read case (pg. 96), fill in the
blanks on pages 49-50 in course pack
183
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

184
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)

185
Homer
186
Andy

• Can sell all we produce
• No room to raise prices

187
Tania
188
More From Tania
Tanias conclusion produce 200 AS 1012 and 0 HL
734
Do you agree?
189
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)

190
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

191
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)

192
Algebraic Formulation
193
Matrix Formulation
194
Formulation in AMPL ( Algebraic Mathematical
Programming Language)

• param NUM_RESOURCES
• param NUM_PRODUCTS
• set RESOURCES1..NUM_RESOURCES
• set PRODUCTS1..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
  cixi
• Constraints
• resource availability constraints
• subject to res_constr j in RESOURCS sumi in
  PRODUCTS Ai,j xi

195
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

196
Possible Solver Outcomes (pg. 63)
Optimization Model
Run Solver
Optimal Solution Found ?
Unbounded Problem ?
Infeasible Problem ?
197
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

198
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

199
Unbounded/Infeasible Problem

• Means solver cannot solve
• The values returned are meaningless
• You need to look at your model

200
Is the plan still optimal? If not, how will it
change? (pg. 65)

• Howie realizes that he underestimated the net
  margin for each AS by 65.
• Howie realizes that he overestimated the net
  margin for each AS by 65.
• Howie discovers a new market where he can sell
  both AS and HLs at a 20 higher net margin than
  originally estimated.

201
More Post-Optimality Analysis

• 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?
• 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?
• Howies 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?

202
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

203
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

204
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)

205
Other solvers

• Survey
• http//lionhrtpub.com/orms/surveys/LP/LP-survey.ht
  ml
• 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/

206
MGTSC 352

• Lecture 9 Aggregate Planning
• Case 2 Mountain WearTake 2 (there will be one
  more)
• Leduc Control Example
• Possible solver outcomesLinearity
• Post-optimality analysis

207
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 cant get any better
• Lets take a graphical look

208
Possible Solver Outcomes (pg. 63)
Optimization Model
Run Solver
Optimal Solution Found ?
Unbounded Problem ?
Infeasible Problem ?
209
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

210
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

211
Unbounded/Infeasible Problem

• Means solver cannot solve
• The values returned are meaningless
• You need to look at your model

212
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

213
Is the plan still optimal? If not, how will it
change? (pg. 65)

• Howie realizes that he underestimated the net
  margin for each AS by 65.
• Howie realizes that he overestimated the net
  margin for each AS by 65.
• Howie discovers a new market where he can sell
  both AS and HLs at a 20 higher net margin than
  originally estimated.

214
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

215
More Post-Optimality Analysis

• 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?
• 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?
• Howies 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?

216
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?

217
Other solvers

• Survey
• http//lionhrtpub.com/orms/surveys/LP/LP-survey.ht
  ml
• 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/

218
Mountain Wear Case

• What decisions does Nathan Leung need to make to
  generate an aggregate plan for Mountain Wear?

219
Active Learning1 min., in pairs

• What constraints (restrictions) must Nathan keep
  in mind?
• Write down as many as you can think of

220
Summary of Data (pg. 49)

• Materials cost per unit
• Labour requirements hrs/unit
• Labour availability hours/employee/quart
  er
• 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 Nathans plans in Excel
221
Tradeoffs So which one of those did you want?
(pg. 50)
Level and chase
222
MGTSC 352

• Lecture 10 Aggregate Planning
• Leduc Control Example
• Complete post-optimality analysis
• Case 2 Mountain WearSet up and use solver to
  find minimum cost plan

223
Announcements

• HW 3 grading
• For next week read
• Air Alberta p. 72
• Crazy Joeys p. 77

224
More Post-Optimality Analysis

• 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?
• 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?
• Howies 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?

225
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?

226
Other solvers

• Survey
• http//lionhrtpub.com/orms/surveys/LP/LP-survey.ht
  ml
• 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/

227
Active Learning1 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

228
Mountain Wear Case

• What decisions does Nathan Leung need to make to
  generate an aggregate plan for Mountain Wear?

229
Mountain Wear Case

• What constraints (restrictions) must Nathan keep
  in mind?

230
Summary of Data (pg. 49)

• Materials cost per unit
• Labour requirements hrs/unit
• Labour availability hours/employee/quart
  er
• 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 Nathans plans in Excel
231
Tradeoffs So which one of those did you want?
(pg. 50)
Level and chase
232
Mountain Wear

• Can we find the lowest cost plan with solver?

233
Active Learning Formulate Mountain Wear Problem
in English

• 1 min., in pairs
• Template
• Maximize / minimizes
• By changing
• Subject to

234
Extending the Mountain Wear Formulation

• Should we include additional constraints?
• Limit on overtime?
• Limit on hirings / firings?
• ?
• How do the additional constraints impact cost?

235
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
  trainees 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

236
What do you mean hire 1.413 attendants?

• You cant do that, right?
• Right.
• But sometimes its 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

237
Integer Constr