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