Fig. 5 Deseasonalize the Historical Data Next, you should divide each sales ±gure by the seasonal index for that month. The formula in Figure 6 (below) uses a few clever tricks to do this quickly. The INDEX function points to the 12 seasonal indices. It uses MONTH(A2) to return the number 1 for January, 6 for June, 12 for December, and so on. Fig. 6 Create a Forecast from the Deseasonalized Data You can now use any of the straight-line forecasting tools on the deseasonalized data. (I covered four methods of straight-line forecasting during the Webcast. This article will show only one method. You can use any of the desired methods.) Add month headings for future months below the historical months in column A. The syntax for the FORECAST function is =FORECAST(x,Known_y’s, Known_x’s), as illustrated in Figure 7, below. In this case, the Known Y is the range of historical sales, the Known X is the range of months, and X is the month that you are forecasting.
12/4/2016 Run The Numbers: Forecasting Seasonal Data on Excel 4/7 Fig. 7 Seasonalize the forecast by multiplying the FORECAST function by the seasonal index for that month. The formula in column E is the opposite of the formula in column C. Instead of dividing by the seasonal indices, you multiply by the seasonal index for the month (see Figure 8, below). Fig. 8 The outcome in Figure 9 is the result of the three-step deseasonalize, forecast, then seasonalize process. The red-dotted series is the future forecast. Compare this to the regular straight-line forecast in Figure 1. Fig. 9
12/4/2016 Run The Numbers: Forecasting Seasonal Data on Excel 5/7 Powered by 02/28/14 00:40am Nico said IS it possible to send a copy of the actual excel ±le of this example. I want to test this but do not have all the data available as all year’s data is not displayed in the example? Reply CFO contributor Bill Jelen is the author of 32 books on Excel, including Pivot Table Data Crunching. You have the chance to win a copy of one of his books by posting a question to the Community Center on the right. If Bill selects your question as the topic of a future column,
You've reached the end of your free preview.
Want to read all 7 pages?
- Fall '15
- Seasonal Data