• Technology
  • CFO.com | US

Spreadsheets: Forecasting Seasonal Data with Excel

Production forecasting with Excel usually entails using straight-line regression. But you'll need to tweak your formulas if you want to incorporate seasonal sales data into the mix. Here's an easy way to run the numbers.

Fig. 4

MrExcel June 29-Fig4

 

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

MrExcel June 29-Fig5

 

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

MrExcel June 29-Fig6

 

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.

Fig. 7

MrExcel June 29-Fig7

 

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

MrExcel June 29-Fig8

 

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

MrExcel June 29-Fig9

 

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.

One thought on “Spreadsheets: Forecasting Seasonal Data with Excel

  1. IS it possible to send a copy of the actual excel file 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?

Discuss

Your email address will not be published. Required fields are marked *