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 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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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? Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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? Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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? Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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, don’t mix data between life cycles Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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) Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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? Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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. Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

15. Data: Overview, Analysis, and Presentation • Presenting data: Tables and graphs • Example: Monthly wood sales • Begin with data in table format Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

16. Data: Overview, Analysis, and Presentation • Presenting data: Tables and graphs • Changing the table format (creating pivot tables) Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

17. Data: Overview, Analysis, and Presentation • Presenting data: Tables and graphs • Changing the table format (creating pivot tables) • Layout button Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

18. Data: Overview, Analysis, and Presentation • Presenting data: Tables and graphs • Changing the table format (creating pivot tables) • Option button Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

22. Data: Overview, Analysis, and Presentation • Presenting data: Tables and graphs 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 Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

24. Correcting for missing data Using only 2004-2008 of the prior data example Data: Overview, Analysis, and Presentation Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

25. 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. (99+103.3)/2 = 101.15 This would be the estimate for February sales Data: Overview, Analysis, and Presentation Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

26. Correcting for missing data Two suggested methods Bookends The table below shows the calculated averages of the bookend approach with the actual values Data: Overview, Analysis, and Presentation Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

27. 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 Data: Overview, Analysis, and Presentation Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

28. 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) Data: Overview, Analysis, and Presentation Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

29. Correcting for missing data Two suggested methods Time bucket average Data: Overview, Analysis, and Presentation Demand Forecasting and Planning in Crisis 30-31 July, Shanghai

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. Demand Forecasting and Planning in Crisis 30-31 July, Shanghai