Session 3: Data: Overview, Analysis, and Presentation

1
Session 3 Data Overview, Analysis, and
Presentation

• Demand Forecasting and
• Planning in Crisis
• 30-31 July, Shanghai
• Joseph Ogrodowczyk, Ph.D.

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

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

4
Data Overview, Analysis, and Presentation

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

5
Data Overview, Analysis, and Presentation

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

6
Data Overview, Analysis, and Presentation

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

7
Data Overview, Analysis, and Presentation

• 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, dont mix data between life cycles

8
Data Overview, Analysis, and Presentation

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

9
Data Overview, Analysis, and Presentation

• 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

10
Data Overview, Analysis, and Presentation

• 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

11
Data Overview, Analysis, and Presentation

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

12
Data Overview, Analysis, and Presentation

• 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

13
Data Overview, Analysis, and Presentation

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

14
Data Overview, Analysis, and Presentation

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

15
Data Overview, Analysis, and Presentation

• Presenting data Tables and graphs
• Example Monthly wood sales
• Begin with data in table format

16
Data Overview, Analysis, and Presentation

• Presenting data Tables and graphs
• Changing the table format (creating pivot tables)

17
Data Overview, Analysis, and Presentation

• Presenting data Tables and graphs
• Changing the table format (creating pivot tables)
• Layout button

18
Data Overview, Analysis, and Presentation

• Presenting data Tables and graphs
• Changing the table format (creating pivot tables)
• Option button

19
Data Overview, Analysis, and Presentation

• Presenting data Tables and graphs
• Changing the table format (creating pivot tables)
• Copy and paste-special (values) of the pivot table

20
Data Overview, Analysis, and Presentation

• 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

21
Data Overview, Analysis, and Presentation

• Notice that the time horizon and sales quantities
  have changed
• Alternative ways to display the data set that
  depends on the forecast objective

22
Data Overview, Analysis, and Presentation

• Presenting data Tables and graphs

23
Data Overview, Analysis, and Presentation

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

24
Data Overview, Analysis, and Presentation

• Correcting for missing data
• Using only 2004-2008 of the prior data example

25
Data Overview, Analysis, and Presentation

• Correcting for missing data
• Two suggested methods
• 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.
• (99103.3)/2 101.15
• This would be the estimate for February sales

26
Data Overview, Analysis, and Presentation

• Correcting for missing data
• Two suggested methods
• Bookends
• The table below shows the calculated averages of
  the bookend approach with the actual values

27
Data Overview, Analysis, and Presentation

• Correcting for missing data
• Two suggested methods
• 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

28
Data Overview, Analysis, and Presentation

• Correcting for missing data
• Two suggested methods
• 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)

29
Data Overview, Analysis, and Presentation

• Correcting for missing data
• Two suggested methods
• Time bucket average

30
Data Overview, Analysis and Presentation

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