Editor's Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com's Spreadsheet Tips Page.
During my Forecasting and Planning Webcast on June 24, I was demonstrating how to use straight-line regression to forecast production. Viewer W.D. asked how to adapt this method when there is seasonality in the sales data. W.D. wins a copy of the new Learn Excel 2007-2010 from CFO and MrExcel for his question.
Consider the data in Figure 1. This shows five years of historical data and a straight-line forecast. The R-Squared value shown on the chart is 0.0999, which means that straight-line forecasting is not going to yield an accurate forecast.
1. Start with sales data by month for several years. It is important that the historical data contain a multiple of 12 months. I used five years of history in this example. The data could run from January 2006 through December 2010, or it could run from July 2007 through June 2011. The important part is that every month is represented by the same number of annual data points. If you have five Decembers, then you should have five Junes. Having five of some month and six of other months will skew the seasonal indices.
2. Select one cell in the data and choose Insert, Pivot Table, OK.
Fig. 1
You can use a pivot table to calculate the seasonal indices. To do that, follow these steps:
3. Move the date field to the Row Labels. Add historical sales to the Values area. You will have a pivot table identical to the original data set.
4. Select one of the date cells in the pivot table. Select Group Field from the Options tab of the ribbon as shown in Figure 2.
Fig. 2
5. In the Grouping dialog, choose only Months. I always say that you should never choose Months without also choosing Years; however, calculating seasonal indices is the one time that you can choose only Months (Figure 3).
Fig. 3
6. You want to change the pivot table to show averages, as well as the numbers as a percentage of the column total. This is easier in Excel 2010. In Excel 2010, choose one of the sales cells. Open the Summarize Values By dropdown and choose Average. Open the Show Values As dropdown and choose % of Column Total (Figure 4). Select the Active Field box and type a name such as Seasonal Indices. On the Design tab, open the Grand Total dropdown and choose Off For Rows and Columns.
Fig. 4
In Excel 2007, you should select one sales cell. Click the Field Settings icon. Type a Custom Name of Seasonal Indices. Choose Average in the Summarize Values By tab. Switch to the Show Values As tab and choose % of Column Total. Click OK.
You will now have a pivot table that shows the seasonal index for each month. If the average monthly sales is 100%, then the value in each month shows how that month compares to the average. In Figure 5, you can see that January is 54.8% of the average month and December is 256.19% of the average month.
Fig. 5
Deseasonalize the Historical Data
Next, you should divide each sales figure 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.
by Amanda Funderburke