Transcript Practical Guidelines for Forecasting

Session 8: Conclusions
Demand Forecasting and
Planning in Crisis
30-31 July, Shanghai
Joseph Ogrodowczyk, Ph.D.
Conclusions

Session agenda





Forecasting changes
Evaluating forecasting capabilities of software
A word on Microsoft Excel
Creating a forecaster training program
Resources
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis
30-31 July, Shanghai
2
Conclusions

Forecasting changes



Very difficult to forecast sharp or hard-to-reverse changes
No magic bullet for forecasting quantities
Need tools that will guide a forecasting process
Linear models estimate levels (quantities) of the dependent
variable
Non-linear models can estimate the probability of the state of
the dependent variable
Probability model


Good
Linear model
1.0
Probability of Good
6
Value of Dependent
4
2
0
0.8
0.5
-2
0.3
-4
-6
0
2
4
6
8
Value of Independent
Session 8
Joseph Ogrodowczyk, Ph.D.
10
Bad
0.0
0
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
2
4
6
8
10
12
Value of Independent
3
Conclusions
Forecasting changes

Two model types

Modeling probabilities for future events
1.





Session 8
Joseph Ogrodowczyk, Ph.D.
Statistical models called probit and logit estimate the
probability that an event will occur
Independent variables are correlated with an event
ISM purchasing manager’s index, price of oil, stock price
index, etc.
Event is the state of the macroeconomy (Good, Bad)
As the independent variables move, the probability of the
dependent variable being either Good or Bad adjusts
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
4
Conclusions
Forecasting changes

Two model types

Modeling probabilities for future events
1.





Session 8
Joseph Ogrodowczyk, Ph.D.
Choosing the “right” independent variables
Variables can differ in effect on various occurrences
(simple models are structurally more stable than complex
models)
Try to include recent history as a basis (past events are
not always an indication of future events)
Historical macroeconomic data are subject to change (retest models for explanatory robustness)
Key independent variables can change (test the
relevance of the independent variables)
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
5
Conclusions
Forecasting changes

Two model types

Decision-directed forecasting
2.








Session 8
Joseph Ogrodowczyk, Ph.D.
Forecasting tool using after a major disruption
Manager experience and statistical models may not be
able produce reliable forecasts
Goal is to identify probable scenarios as guidelines for
making operational decisions
Steps to the process
Manager identifies decision options and scenarios
Management assigns probabilities to future outcomes
Forecasters calculate new probabilities as new data
become available (Bayesian calculations)
When one scenario becomes more likely, manager
selects the appropriate course of action
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
6
Conclusions
Forecasting changes

Two model types

Decision-directed forecasting: Example
2.



Session 8
Joseph Ogrodowczyk, Ph.D.
Casino actions in Las Vegas following 9/11
Following 9/11, casinos would need to incorporate a
sharp change in macroeconomic environment
Decision actions included: Business as usual (revenue
decreases are short term), Plan for short term disruption
(mandatory vacations, hold on capital projects, etc.),
Recovery might never fully materialize (short and long
term cutbacks), New business strategy (new ventures in
other industries)
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
7
Conclusions
Forecasting changes

Two model types

2.
Decision-directed forecasting: Example
Probabilities in mid-September 2001
Scenario
Description
Revenues return within 3 months
A
Revenues fall flat for 3 months
B
but return by 9 months
Revenues fall flat for 6 months
C
and increase at prior rate
Revenues continue to decline
D
Business option
Business as usual
Plan for short term
disruption
Recovery might never
fully materialize
New business strategy
Manager's first
probability
15%
35%
35%
15%
Updated probabilities in mid-October 2001
Scenario
Description
Revenues return within 3 months
A
Revenues fall flat for 3 months
B
but return by 9 months
Revenues fall flat for 6 months
C
and increase at prior rate
Revenues continue to decline
D
Session 8
Joseph Ogrodowczyk, Ph.D.
Business option
Business as usual
Plan for short term
disruption
Recovery might never
fully materialize
New business strategy
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
Manager's first
probability
10%
38%
38%
14%
8
Conclusions
Forecasting changes

Two model types

Decision-directed forecasting: Example
2.
Probabilities are updated each month.
By January 2002, Scenarios A and D are highly unlikely
By May 2002, Scenario B is the most likely
•
•
•
Updated probabilities in May 2002
Scenario
Description
Revenues return within 3 months
A
Revenues fall flat for 3 months
B
but return by 9 months
Revenues fall flat for 6 months
C
and increase at prior rate
Revenues continue to decline
D
Session 8
Joseph Ogrodowczyk, Ph.D.
Business option
Business as usual
Plan for short term
disruption
Recovery might never
fully materialize
New business strategy
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
Manager's first
probability
0%
72%
28%
0%
9
Conclusions
Forecasting changes

Two model types

Decision-directed forecasting
2.
•
•
•
•
Session 8
Joseph Ogrodowczyk, Ph.D.
As the probabilities are updated each month, a manager can
adjust the business plan
Approach can also be used for new product introductions,
responses to competitors, or new pricing strategies
Other methods for choosing among scenarios include: Payoff
tables and Decision Trees
Techniques are available for assisting in choosing the starting
probabilities
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
10
Conclusions

Evaluating forecasting abilities of software

Background


Companies make IT choices based on broad goals such as
supply chain management (SCM) and enterprise resource
planning (ERP) needs
Demand planning software is often included with SCM or ERP
systems



Stand-alone forecasting software requires additional IT
knowledge, time, and funding
Decision makers must balance the sophistication of the
forecasting techniques against the ease of interconnectivity of the
software
What is the best way for evaluating the forecasting capabilities
of the demand planning packages?
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
11
Conclusions

Evaluating forecasting abilities of software

Steps in evaluation

Designing a test drive


Need to test the software forecast accuracy on the organization’s
data

Work with vendors on a pilot study
Sample data should include a broad range of products and
include all levels of the hierarchies

Keep in mind any model requirements with respect to the
length of the dataset

Include various demand patterns (intermittent, new product,
stable demand, etc.)
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
12
Conclusions

Evaluating forecasting abilities of software

Steps in evaluation

Evaluating results



Choose an evaluation period (ex post)
Choose an accuracy calculation

Preference should be given to the current calculation to ensure
that the software provides a reduction in error
Comparing performance


Accuracy improvement may not necessarily be due to the
forecasting algorithms
Other benefits from the software can include

Incorporation of point-of-sale data

Ability to support Collaborative Planning, Forecasting, and
Replenishment (CPFR)
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
13
Forecasting software options
Modules in Broad Scope Statistical Software:
SAS (ETS)
STATA
SPSS
Business Forecasting Software:
Autobox
Stamp
Forecast Pro
Insightful (S+FinMetrics)
SmartForecasts
R
Decision Time
Demand Planning:
McConnell-Chase (Forecasting for Demand)
Demand Works (Smoothie)
Oracle (Peoplesoft Enterprise Demand Planning)
John Galt (Atlas Planning Suite)
Demand Management (Demand Solutions)
Delphus (Peer Planner)
Modules in SAP, I2, and JDA (formerly Manugistics)
Econometrics Packages:
E-Views
RATS and CATS
PC Give
See http://www.oswego.edu/~economic/econsoftware.htm
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
14
Conclusions

A word on Excel

Overview

Previous sessions of workshop used Excel



Excel can and is used for forecasting
Good for quick estimates and general guideline
However, Excel is not a robust forecasting tool

Specialized forecasting software (or forecasting capabilities
within a demand planning software) is recommended in
support of a forecasting process
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
15
Conclusions

A word on Excel

Tips for using of Excel

Advantages of Excel





Allows data to be visible
Formulas are accessible and can be edited
Calculations can be saved
Scenarios can be planned using parametric analysis
Caution of Excel


Excel is not a statistical software
Statistical procedures do not yield accurate or precise solutions
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
16
Conclusions

A word on Excel

Tips for using of Excel

Guidelines



Match the tool to the job

Excel is a good tool to estimate a range
Understand how to use the tool to accomplish the job

Excel makes it easy for users to think they are properly
applying the wrong model to a data set

Users can program statistical and forecasting equations into
Excel to obtain correct calculations
Increasing the capabilities of the tool can increase the quantity
and quality of the jobs finished by the tool

Consider add-ons for Excel

Education for users
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
17
Conclusions

A word on Excel

Tips for using of Excel

Useful resources for analysis on Excel


http://www.daheiser.info/excel/frontpage.html

Website documents the error and some potential solutions to
those errors
Several studies documenting inconsistencies of statistical
software

(McCullough 1999) SAS, SPSS

(McCullough and Wilson 2005) Excel 2003

Studies also show a willingness to correct errors by SAS and
SPSS but not Microsoft (McCullough and Wilson 2002)
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
18
Conclusions

A word on Excel

Nonlinear curves example

In calculating coefficients of nonlinear functions (such as
exponential functions), Excel transforms the data into a line
Transformation leads to incorrect calculations
 Optimal coefficients of a function are found by a model error
metric (such as the root mean square error or RMSE)
Exponential function defined as
a: intercept (assumed to be 0)
b: growth rate
c: lower limit
T: time (year for this example)
e: mathematical constant of 2.718
growth rate as a percent per year = eb-1


Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
19
Conclusions

A word on Excel

Nonlinear curves example

Sample data
Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sales $301 $320 $372 $423 $500 $608 $721 $826 $978 $1,135 $1,315 $1,530 $1,800 $2,152 $2,491

Graph
Sales
$3,000
$2,500
$2,000
$1,500
$1,000
$500
$0
0
Session 8
Joseph Ogrodowczyk, Ph.D.
5
Year
10
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
15
20
Conclusions
A word on Excel


Nonlinear curves example
Choosing an exponential trend line in Excel
 Excel will find the optimal
coefficient of the following Year ln(Sales)
1
$5.707
transformed data
2
$5.768

ln(Sales)
$10
$9
$8
$7
$6
$5
0
5
Session 8
Joseph Ogrodowczyk, Ph.D.
Year
10
15
3
4
5
6
7
8
9
10
11
12
13
14
15
$5.919
$6.047
$6.215
$6.410
$6.581
$6.717
$6.886
$7.034
$7.182
$7.333
$7.496
$7.674
$7.820
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
21
Conclusions

A word on Excel

Nonlinear curves example

Excel will convert the result from the linear function back into
the exponential function for the fit line of
where growth rate as a percent per year = e.1561-1 = 16.89%
This is the annual growth rate of sales
Notice that Excel assumes an intercept of zero
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
22
Conclusions

A word on Excel
 Nonlinear curves example

A statistical software will optimize the exponential
function directly for a fit line of
where growth rate as a percent per year = e.1608-1 = 17.44%
This is the annual growth rate of sales
Notice that there is a non-zero intercept
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
23
Conclusions

A word on Excel

Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Nonlinear curves example

Comparing
the results
Statistical Statistical
Excel
Sales
$301
$320
$372
$423
$500
$608
$721
$826
$978
$1,135
$1,315
$1,530
$1,800
$2,152
$2,491
forecast
$280.46
$325.90
$379.27
$441.95
$515.56
$602.01
$703.55
$822.79
$962.85
$1,127.33
$1,320.51
$1,547.38
$1,813.84
$2,126.78
$2,494.31
Sum squared errors
Root
16
17
18
Sum
error
-$20.54
$5.90
$7.27
$18.95
$15.56
-$5.99
-$17.45
-$3.21
-$15.15
-$7.67
$5.51
$17.38
$13.84
-$25.22
$3.31
forecast
$277.69
$324.61
$379.45
$443.55
$518.49
$606.08
$708.47
$828.17
$968.08
$1,131.63
$1,322.81
$1,546.29
$1,807.52
$2,112.89
$2,469.85
$2,920.90
54.05
$2,925.96
$3,432.90
$4,028.29
$10,387.15
Session 8
Joseph Ogrodowczyk, Ph.D.
$2,887.12
$3,374.87
$3,945.03
$10,207.02
Excel
error
-$23.31
$4.61
$7.45
$20.55
$18.49
-$1.92
-$12.53
$2.17
-$9.92
-$3.37
$7.81
$16.29
$7.52
-$39.11
-$21.15
Parameters
a=
b=
c=
Statistical
Excel
software Non-linear fit
221.7886 237.5583
0.1608
0.1561
19.9793
0
Percent
Difference Difference
$4,018.70 $1,097.81
37.6%
63.39
$9.35
17.3%
-$38.84
-$58.03
-$83.25
-$180.12
-1.3%
-1.7%
-2.1%
-1.7%
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
24
Conclusions
Creating a forecaster training program

Program outline
1.
Program mission statement
Clearly defined objectives based on ideal forecaster
characteristics
Resource requirements



Create a measurement baseline
2.
Classify objectives into core learning areas

Forecasting and supply chain concepts
Technical and software skills
Process management and product knowledge
Interpersonal skills





Collect data on the current ability level of the forecasters
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
25
Conclusions
Creating a forecaster training program

Create development plan through gap analysis
3.
Define critical skills corresponding to core learning areas
Compare current forecaster knowledge with critical skills
Prioritize the gaps based upon criteria such as importance
of core area, size of gap, quantity of forecasters, and
estimated resources needed to close the gap



Implementation of education program
4.
Define the content of core learning areas
Outline methods used for education






Session 8
Joseph Ogrodowczyk, Ph.D.
Separate sessions, week long workshops, etc.
Consultants or internal resources
Quantity of IT support needed
Use of mentoring, coaching, on-the-job training
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
26
Conclusions
Creating a forecaster training program

Evaluation and areas of improvement for program
5.
Maintenance of forecaster education

Continuing education internally, plans for using external
resources or outside education

Lessons learned from implementation

Changing the course schedule or material covered
Incorporating participant feedback


Defining additional supporting infrastructure




Session 8
Joseph Ogrodowczyk, Ph.D.
Guiding principles for demand management education
Roles and responsibilities of the instructors
Future expectations of additions to core areas
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
27
Conclusions

Resources

Forecasting portals

www.appliedforecasting.com


www.forecastingeducation.com


Latest news on forecasting events, tool and papers
Listing of and links to software reviews and special reports on
forecasting software
www.demandplanning.net

S&OP and demand management consulting and courses
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
28
Conclusions

Resources

Forecasting organizations

International Institute of Forecasting


Institute of Business Forecasting


www.forecastingprinciples.com
www.ibf.org
Both offer

Courses, seminars, and publications
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
29
FORESIGHT: Concise, objective and readable
articles on issues essential to the practicing forecaster.
Special Features





Overcoming Challenges in Operational
Forecasting Projects
The Organizational Politics of
Forecasting:6 Steps to Overcome Bias
Forecast Accuracy Metrics for Inventory
Control
The What, Why, and How of Futuring
for Forecasters
Benchmarking of Forecast Accuracy
plus Software Reviews, Book Reviews
and briefs on Hot New Research
www.forecasters.org/foresight
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
30
Conclusions

References (Forecasting changes)




Batchelor, Roy. 2009. Forecasting sharp changes.
Foresight. Spring: 7-12.
Custer, Stephen and Don Miller. 2007. Decision-directed
forecasting for major disruptions: The impact of 9/11 on Las
Vegas gaming revenues. Foresight. Summer: 29-35.
Jain, Chaman L. and Jack Malehorn. 2005. Practical Guide
to Business Forecasting (2nd Ed.). Flushing, New York:
Graceway Publishing Inc.
Sephton, Peter. 2009. Predicting recessions: A regression
(probit) model approach. Foresight. Winter: 26-32.
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
31
Conclusions

References (Forecasting software)
 Fields, Paul. 2006. On the use and abuse of Microsoft Excel. Foresight.
February: 46-47.
 Hesse, Rick. 2006. Incorrect nonlinear trend curves in Excel. Foresight.
February: 39-43.
 Hoover, Jim. 2005. How to evaluate the forecasting ability of demandplanning software. Foresight. June: 47-49.
 McCullough, Bruce D. 1999. Assessing the reliability of statistical
software: Part 2. The American Statistician. 53(2): 149-159.
 McCullough, Bruce D. and B. Wilson. 2005. On the accuracy of
statistical procedures in Microsoft Excel 2003. Computational Statistics
and Data Analysis. 49(4): 1244-1252.
 McCullough, Bruce D. and B. Wilson. 2002. On the accuracy of
statistical procedures in Microsoft Excel 2000 and Excel XP.
Computational Statistics and Data Analysis. 40(4): 713-721.
 McCullough, Bruce D. 2006. The unreliability of Excel’s statistical
procedures. Foresight. February: 44-45.
Session 8
Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis,
30-31 July, Shanghai
32