session 3: data: overview, analysis, and presentation demand forecasting and planning in crisis...
DESCRIPTION
Session 3 Joseph Ogrodowczyk, Ph.D. Demand Forecasting and Planning in Crisis July, Shanghai 3 Data: Overview, Analysis, and Presentation Data as a tool for forecasting Forecasts are only as good as the information and knowledge used to generate them Forecasters have easy access to review and analyze data because of advances in computers More data are not always good for forecasting Need to know how to study the data and understand ways to analyze that dataTRANSCRIPT
Session 3: Data: Overview, Analysis, and Presentation
Demand Forecasting and Planning in Crisis30-31 July, Shanghai
Joseph Ogrodowczyk, Ph.D.
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 2
Data: Overview, Analysis, and Presentation Session agenda
Data as a tool for forecasting Determining the “right” quantity of data Getting good forecasts from bad data Guidelines for addressing poor data Presenting data: tables and graphs Correcting for missing data
Activity: Become familiar with sample data, transform data into pivot table form, and make some simple graphs
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 3
Data: Overview, Analysis, and Presentation
Data as a tool for forecasting Forecasts are only as good as the information and knowledge
used to generate them Forecasters have easy access to review and analyze data
because of advances in computers More data are not always good for forecasting Need to know how to study the data and understand ways to
analyze that data
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 4
Data as a tool for forecasting Questions for data sets
How much data are available? What type of model will be used?
How reliable are the data? What is the source of the data? Has the definition of the data changed?
Are any data missing? Can the missing data points be estimated?
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 5
Data as a tool for forecasting Questions for data sets
Are the data aggregated or disaggregated? What is the underlying organizational hierarchy of the data? What methods will be used to aggregate or disaggregate the
forecasts? Is that method used consistently throughout the company?
What is the product life cycle phase of the data? Is there a structural change in the data?
Did a product group experience a new product line launch? Was there a promotion? Did market conditions change because of an acquisition? Did market conditions change because of an economic or financial crisis?
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 6
Data as a tool for forecasting Questions for data sets
Are there outliers in the data? Can these be corrected or should they be included?
Do the time buckets have different working days? Example: If data are monthly, do all months have the same number
of weeks? Are there seasonal variations in the data?
Are there business cycles in the data? What type of trend do the data contain?
Can assumptions be made about the data trend based on the forecast time horizon?
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 7
Determining the right quantity of data Product life cycle
Mature products have more stable demand New products have increasing demand Aging products have declining demand
Depending on the specific product type, each stage will have varying data lengths Need to match the length of the data set with the life cycle If possible, don’t mix data between life cycles
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 8
Determining the right quantity of data Model type
Different models require different quantities of data Single Exponential smoothing models require less data than Triple
Exponential smoothing models because they don’t need to account for seasonality
Regression models’ requirements depend on the number of independent variables being used to explain demand variation
Models with seasonal components require at least two season cycles
Forecast horizon Short term forecasts require a smaller data set than long term
forecasts and are influenced by recent historical information Long term forecasts need to include trends, seasonality, and
business cycles
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 9
Getting good forecasts from bad data Causes of poor data quality
Data collection Wrong data collected (e.g. shipments instead of backlog) Varying parameters (e.g. prices, advertising, weather) are not
collected or formatted in a usable form Gaps or errors in data collection Change in collection methods leading to essentially two different data
series Data storage
Lack of historical data Not enough detail – aggregation too high
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 10
Getting good forecasts from bad data Causes of poor data quality
Operations Inconsistent product quality causing changes in demand Process changes driving data collection changes Sudden changes in external factors (e.g. strike, weather disruptions,
trade disputes, economic/financial crises) Marketplace
Changes in marketing can disrupt demand Changes in competitive landscape (more or fewer rival firms)
Finance and accounting Financial requirements drive spikes and valleys in demand behavior
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 11
Guidelines for addressing poor quality data The purpose of forecasting data is to predict the future
Modifying data may be necessary to create a viable forecasting data set Create a separate data set Change the level of aggregation or time buckets Calculate missing values or modify outliers Add additional variables to account for the effects of internal factors
(e.g. promotions) or external factors (e.g. business cycles, weather changes, and economic/financial crises)
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 12
Guidelines for addressing poor quality data The purpose of forecasting data is to predict the future
Corporate data organization may be not suitable for forecasting Fiscal periods may not correspond with actual periods Understand the periodicity of the data which may not correspond to
the calendar periodicity Days between holidays, moon cycles, customer purchasing habits
May also regroup customers and products
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 13
Guidelines for addressing poor quality data Understand the data relevant to the forecasts
Statistically test for relevant variables among company tradition Data collection analysis may suggest additional variables
Be clear on the business question Make sure the forecasts address the real problem
Is the forecast too detailed? Is the time horizon long enough?
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 14
Presenting data: Tables and graphs Example: Assume we know that we have enough good data
to be able to produce the necessary forecasts What is our next step?
Always visually inspect the data
The following example uses Microsoft Excel. For the purposes of simple models, Excel is acceptable. For more statistically robust models, I recommend using a forecasting software, and will suggest several packages in Session 7.
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 15
Presenting data: Tables and graphs Example: Monthly wood sales Begin with data in table format
Date WoodFeb-2009 63.87Jan-2009 64.64Dec-2008 67.37Nov-2008 75.57Oct-2008 81.42Sep-2008 85.92Aug-2008 89.81Jul-2008 88.65Jun-2008 92.97May-2008 90.38Apr-2008 91.60Mar-2008 90.12Feb-2008 88.55Jan-2008 88.23Dec-2007 88.89Nov-2007 91.82Oct-2007 99.53Sep-2007 100.57Aug-2007 103.03Jul-2007 101.42Jun-2007 107.00
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec2000 102.3 105.7 108.0 109.1 106.7 109.2 101.6 105.0 105.4 103.7 97.7 90.22001 90.1 92.2 96.2 97.0 98.5 103.5 95.9 102.3 102.7 100.1 95.3 91.72002 94.1 96.4 100.2 102.1 101.2 106.4 99.3 104.3 103.1 103.8 97.0 92.02003 94.4 97.5 98.1 99.9 99.5 104.4 100.2 104.3 104.4 106.4 103.6 96.42004 99.0 102.5 103.3 105.6 105.5 108.1 104.8 108.2 105.2 109.6 103.0 97.72005 104.2 105.2 105.7 109.0 107.9 112.4 108.2 111.1 114.3 121.2 116.1 109.92006 111.9 113.2 114.8 114.5 113.9 116.4 111.7 112.7 109.8 105.0 98.8 97.12007 95.6 97.8 101.3 101.2 102.0 107.0 101.4 103.0 100.6 99.5 91.8 88.92008 88.2 88.5 90.1 91.6 90.4 93.0 88.6 89.8 85.9 81.4 75.6 67.42009 64.6 63.9
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 16
Presenting data: Tables and graphs Changing the table format (creating pivot tables)
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 17
Presenting data: Tables and graphs Changing the table format (creating pivot tables)
Layout button
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 18
Presenting data: Tables and graphs Changing the table format (creating pivot tables)
Option button
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 19
Presenting data: Tables and graphs Changing the table format (creating pivot tables)
Copy and paste-special (values) of the pivot table
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 20
Presenting data: Tables and graphs What is the best way to display the data? It depends on understanding the forecast question (including the
needed time horizon) How much historical information is needed? Line graph with data points for the single table format
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 21
0
20
40
60
80
100
120
140
Jan-72Jan-73Jan-74Jan-75Jan-76Jan-77Jan-78Jan-79Jan-80Jan-81Jan-82Jan-83Jan-84Jan-85Jan-86Jan-87Jan-88Jan-89Jan-90Jan-91Jan-92Jan-93Jan-94Jan-95Jan-96Jan-97Jan-98Jan-99Jan-00Jan-01Jan-02Jan-03Jan-04Jan-05Jan-06Jan-07Jan-08Jan-09
Woods sales
Notice that the time horizon and sales quantities have changed Alternative ways to display the data set that depends on the forecast objective
60
70
80
90
100
110
120
130Wood sales
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 22
Presenting data: Tables and graphs
60
70
80
90
100
110
120
130
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000
2001
20022003
2004
2005
2006
20072008
2009
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 23
Correcting for missing data What happens if we are missing some entries? Should the missing values be equal to zero or to some other
number? Some software packages will ignore missing values while other
will assume a missing value is zero. Some modeling software programs will fail to produce a forecast and will show an error.
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 24
Correcting for missing data Using only 2004-2008 of the prior data example
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec2004 99.0 103.3 105.6 105.5 108.1 104.8 108.2 105.2 109.6 103.0 97.72005 104.2 105.2 105.7 107.9 112.4 108.2 111.1 114.3 121.2 116.1 109.92006 111.9 113.2 114.8 114.5 113.9 111.7 112.7 109.8 105.0 98.8 97.12007 95.6 97.8 101.3 101.2 102.0 107.0 101.4 103.0 100.6 91.8 88.92008 88.2 88.5 90.1 91.6 90.4 93.0 88.6 85.9 81.4 75.6 67.4
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 25
Correcting for missing data Two suggested methods
1. Bookends Calculate an average based upon the preceding and following entries
(months) For 2004, February is missing. January sales were 99 and March
sales were 103.3. (99+103.3)/2 = 101.15 This would be the estimate for February sales
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 26
Correcting for missing data Two suggested methods
1. Bookends The table below shows the calculated averages of the bookend
approach with the actual values
Missing month
Preceeding sales
Following sales
Average sales
Actual sales
2004 February 99 103.3 101.15 105.72005 April 105.7 107.9 106.80 109.02006 June 113.9 111.7 112.80 116.42007 October 100.6 91.8 96.20 99.52008 August 88.6 85.9 87.25 89.8
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 27
Correcting for missing data Two suggested methods
2. Time bucket average Suppose that certain months contain a seasonal component (January
and Chinese New Year) In this case, the preceding and following months may not be a good
estimation for demand If enough data are available, a historical average per month can be
calculated
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 28
Correcting for missing data Two suggested methods
2. Time bucket average Calculate an average using the corresponding time buckets
Other February data, first week of month data, third quarter data February 2004 has a missing value. Use February data from 2005-
2008 (the remaining years in the data set)
Year Sales2005 105.22006 113.22007 97.82008 88.5
Average 101.2
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 29
Correcting for missing data Two suggested methods
2. Time bucket average
Year Sales Year Sales Year Sales Year Sales2004 105.6 2004 108.1 2004 108.2 2004 109.62006 114.5 2005 112.4 2005 111.1 2005 121.22007 101.2 2007 107.0 2006 112.7 2006 105.02008 91.6 2008 93.0 2007 103.0 2008 81.4
Average 103.2 Average 105.1 Average 108.7 Average 104.3
April 2005 June 2006 August 2008 October 2007
Data: Overview, Analysis, and Presentation
Session 3 Joseph Ogrodowczyk, Ph.D.
Demand Forecasting and Planning in Crisis 30-31 July, Shanghai 30
References Bonnell, Ellen. 2007. How to get good forecasts
from bad data. Foresight. Summer: 36-40. Jain, Chaman L. and Jack Malehorn. 2005.
Practical Guide to Business Forecasting (2nd Ed.). Flushing, New York: Graceway Publishing Inc.
Data: Overview, Analysis and Presentation