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.
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.
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.
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).
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.
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, we’ll send you a book as a thank-you. Bill’s next MrExcel Webcast, on consolidating data for financial reporting, is Thursday, July 14, at 2:00 Eastern Time.