1 / 492

MGTSC 352 Operations ManagementLecture 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 whymaybe 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 aint no sit-back-and-relax,

you-pays-your-fees-and-you-gets-your-credits

course.

Evaluation

Grade Distribution

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

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

Why Active Learning?

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

Another Chart the Process View

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

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

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 areIm fairYou learn

Morgan Skowronski

Jen Tyrkalo

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

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

Outputs

MODEL

Revenue Quantity x Price

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

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?
- PAug PApr (a b ? PApr) ? PApr)
- ?
- In your groups!
- Time 1 min.

b a / Cap .45 / 100,000

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

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

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

MGTSC 352

- Lecture 2 Forecasting
- Why forecast?
- Types of forecasts
- Simple time series forecasting

methodsIncluding 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

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

ml

What is the Driver Doing?

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

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

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 youd like

to forecast - 1 minute

Choosing a Forecasting Method

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 ... Ft1 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
- Ft1 LS Dt (1 - LS) Ft Rearrange...
- Ft1 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

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

Todays active learning

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

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

The Weights

LS 0.5

LS 0.3

LS 0.1

- 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, weve only done

one-period-into-the-future - k periods-into-the-future Ftk Ft1, 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

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

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

Double Exponential Smoothing

Pg. 25

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

Excel

Learning

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

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

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 Prediction

Time to try it out Excel

TES - Calibration (p of seasons)

Pg. 29

Always UPDATED (S) NEW (1-S) OLD

One-step Forecast Ft1 (Lt Tt) St1-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(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."

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

TES vs SLRwSI

- TES
- Ftk (Lt k ? Tt) ? Stk-p
- SLRwSI
- Ftk (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?

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
- TES trend is adaptive

- SLRwSI SE 32.6
- SLR uses constant trend

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

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

Whats This Solver Thing?

- In Excel Tools ? Solver, to bring up

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

Why Solver Doesnt Always Give the Same Solution

Everywhere I look is uphill! I must have

reached the lowest point.

local optimum

global optimum

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

TES vs SLRwSI

- TES
- Ftk (Lt k ? Tt) ? Stk-p
- SLRwSI
- Ftk (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?

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
- TES trend is adaptive

- SLRwSI SE 32.6
- SLR uses constant trend

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)

Forecast Error Distribution

Approximate with Normal Distribution

Standard Error of the forecast errors

Average Error .3 Standard Error 127

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

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)

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

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

Appropriate model...

Nonlinear (ex. power)

linear

S-curve (ex. any CDF)

DATA

TES vs. SLR w/ SI

Which method would you choose?

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

TES vs. SLR w/ SIin holdout period

TES vs. SLR w/ SIin holdout period

Now which method would you choose?

Calgary EMS Data

Number of calls / month

Trend? Seasonality?

Checking for (Yearly) Seasonality

Number of calls / month

Weekly Seasonality

Avg. of calls / hr., 2004

Reminders

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

MGTSC 352

- Lecture 6 Forecasting
- Wrap-up of ForecastingHoldout strategyDebugging

Forecasting Models - Monte Carlo SimulationPlaying Roulette with

ExcelBard Outside example

95 Prediction Interval

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

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

Appropriate model...

Nonlinear (ex. power)

linear

S-curve (ex. any CDF)

Calgary EMS Data

Number of calls / month

Trend? Seasonality?

Checking for (Yearly) Seasonality

Number of calls / month

Weekly or Hourly Seasonality

Avg. of calls / hr., 2004

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

Example Building Materials, Garden Equipment,

and Supply Dealers

TES vs. SLR w SI(Both optimized to minimize SE)

Which method would you choose?

One possibility Combining Forecasts

TES

SLR w SI

weight

(1 - weight)

Minimize SE of the combined forecast to find the

best weight

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

TES vs. SLR w/ SIin holdout period

TES vs. SLR w SI in holdout period

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 hasnt 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

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

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

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?

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?

Game 1 payoff distribution

Game 2 payoff distribution

Using Excel to get the right answer

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

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

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

- 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

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

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

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 630 pm, BUS B-2428
- Optional
- QA 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

- 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

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

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)

Converting random number to a normal distribution

Simulated Value 990.3

Final results

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

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

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 Outsides accountant says theyve been

earning an average of 100 per night - Whats wrong?

Another look at the No Rain Attendance

Distribution

Attendance (up to 199)

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

Attendance

Can we do better?

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.

How Much Difference Does this Make?

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

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
- Dont 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 530 630 pm, BUS

B-2428 - Optional
- QA session, no new material

MGTSC 352

- Lecture 9 Aggregate Planning
- Overview of Planning Matching Demand and

Capacity - Case 2 Mountain WearLeduc Control Example

Overview of Planning (pg. 46)

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

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)

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

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

Andy

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

Tania

More From Tania

Tanias 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 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

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)

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

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?

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

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

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

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)

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

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

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

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

Mountain Wear Case

- What decisions does Nathan Leung need to make to

generate an aggregate plan for Mountain Wear?

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

Tradeoffs So which one of those did you want?

(pg. 50)

Level and chase

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

Announcements

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

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?

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

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

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/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

Tradeoffs So which one of those did you want?

(pg. 50)

Level and chase

Mountain Wear

- Can we find the lowest cost plan with solver?

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

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

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

Integer Constr