For business users of Microsoft Excel Free guides and templates

Tame Your Seasonal Data

How to Create a Rolling Forecast of  Seasonal Sales in Excel

The extreme variability caused by seasonal sales makes it difficult to forecast sales. You can do so easily if you first deseasonalize your sales data to reveal your hidden sales trend.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
How do you forecast seasonal sales in Excel?

This Excel chart shows the typical saw-tooth pattern of seasonal sales.

Seasonal sales have about the same pattern every year, every week, or both. In some specific months or days, sales are always above average; and in other specific periods, sales are below average.

Seasonal sales typically are difficult to analyze. In good periods, it's hard to know whether good sales are better than usual; in bad periods, it's hard to know if bad sales are worse than usual.

Forecasting seasonal sales can be even more difficult. It's hard to picture any method that could generate a reliable sales forecast that has a pattern like the one shown above.

Years ago, I faced these issues when I was the CFO for a small company whose monthly sales varied from a low in winter months of about $50,000 to a high in the spring of about $1,000,000 per month.

To deal with my analytical challenges, I worked out some spreadsheet techniques that really helped. The most useful technique was to report, analyze, and forecast deseasonalized sales.

Deseasonalized Sales

First deseasonalize your sales to forecast them in Excel.The sawtooth blue line in this Excel chart shows actual sales over the past four and a half years. The black line shows deseasonalized sales...sales with the effects of seasonality removed.

By looking at deseasonalized data, we easily can see that sales fell in 2009 and in 2011, and increased slightly in the other periods.

To forecast sales, we estimate the trend of the deseasonalized sales, and then seasonalize the trend to produce a specific sales forecast.

Let's see how that's done...

How to Deseasonalize Your Sales

Data for the sales forecastThe idea behind deseasonalized sales is easy to understand. For each month of the year we calculate a Seasonal Index (column C in this figure). This value tells us, on average, how much each month's sales vary from average sales for the year. Then to deseasonalize sales, we divide the sales for each month by its Seasonal Index. 

The most important step is to calculate the correct value for the average sales. Specifically, you need to calculate the "centered moving average" (CMA) for each period in your historical data. To find the daily CMA within a week, the averaging period is seven days. To find a monthly CMA within a year, the averaging period is 12 months...well, sort of.

Let's start with a week. If you average the sales for Monday through Sunday, you find the centered moving average for Thursday, because Thursday is at the mid-point of those seven days, as shown in this figure:

An illustration of a centered moving average for deseasonalizing sales.

To find the CMA for Friday, you average the sales for Tuesday through Monday. And the CMA for Saturday is the average of the sales for Wednesday through Tuesday, as shown here:

Three centered moving averages for weekly deseasonalization.

Finding the CMA for seven days is easy. This is because seven is an odd number of days, which therefore has a center day.

But a year has an even number of months. So when we try to calculate the CMA for the twelve months of January through December, we have this problem:

Problem with deseasonalizing an even number of periods.

This is a problem, because the average of the sales for January through December doesn't apply to either June or July. (And no, we can't cheat by arbitrarily assigning the average to one of the two months.)

To get around this problem, we also find the average for February through January of the next year, and then average these two averages, as shown here:

How to handle even number of periods when deseasonalizing data.

To find the CMA for August, we simply copy the Excel formula for July's CMA to the next cell. And we do the same for each month thereafter.

What are those Excel formulas? Well, let's see.

How to Calculate Centered Moving Averages and the Sales Ratios

How to calculate centered moving averages of sales ratios

Column A in this figure shows the sequence number for each row of data. Columns B and C show actual sales by month for the fictional company. The dashed line shows where I hid rows so that you can see the top and bottom of the data area.

To get started, enter the labels in row 3 and then format the gray cells in row 4. Copy the row of gray cells to row 65.  

Select the range A3:E65. Then in:

  • Classic Excel: Choose Insert, Name, Create.
  • New Excel: Choose Formulas, Defined Names, Create From Selection.
  • Either Excel: Choose Alt+INC or Ctrl+Shift+F3

In the Create Names dialog, make sure that only Top Row is checked, then choose OK.

Copy and paste several years of your own historical sales data into column C, and the appropriate monthly dates in column B. Make sure that you paste between the top and bottom gray borders.

Enter the text shown for cell A2 and then enter this formula for the cell shown:

B2:  =COUNT(ActSales)

This formula tells us the actual number of periods of data, even when we have more rows in the table than we have data to fill them.

To name this cell, select the range A2:B2, launch the Create Names dialog again, make sure that only Left Column is checked, then choose OK.

Cell D11 contains a formula that divides the sales in cell C11 by the centered moving average for August 2005. Here's the formula for the cell shown:

D11:  =IF($A11+6<ActPds,$C11/AVERAGE($C5:$C16,$C6:$C17),"")

The IF function in this formula returns a null string (the "" near the end of the formula) when we run out of data at the bottom of the figure. The AVERAGE function in this formula calculates the Centered Moving Average by calculating the average of two ranges of twelve cells: C5:C16 and C6:C17.

Copy the formula in cell D11 down the column to cell D64.

Now that we have the sales ratio, we need to complete another table before we can calculate the deseasonalized sales shown in column E.

How to Calculate the Seasonal Index

The figure below shows the Seasonal Index in column C. To set up this figure, first go to an unused worksheet in your workbook. Add the text shown in rows 1 and 2, and the numbers shown for column A.

Enter this array formula for the cell shown:

B3:  {=AVERAGE(IF((MONTH(Date)=$A3)*(SlsRatio>0),SlsRatio))}

How to calculate the seasonal indexTo emphasize, this is an array formula. You type it into the formula bar as you would any other formula. (Don't type the braces.) Then hold down Ctrl+Shift and press Enter to enter the formula. When you do so, Excel will add the beginning and ending brace as shown.

This formula finds the average of all January sales ratios in column D of the preceding figure...but it does so only for the sales ratios greater than zero.

To calculate similar averages for the other eleven months, copy this formula from cell B3 to the range B4:B14.

Enter the formula for the cell shown:

B15:  =SUM(B3:B14)

The total in cell B15 is almost 12. But we need the Seasonal Index numbers to total exactly 12. So we must scale the averages to make them do so. Therefore, enter this formula for the cell shown:

C3:  =(12/$B$15)*B3

Copy the formula to the range C4:C14.

Copy the SUM formula in cell B15 to cell C15. When you do so, you'll see that the total of the SeasIndex column now equals exactly 12.

To finish off this figure, assign the title in cell C2 as the name of the twelve cells below that cell. To do so, select the range C2:C14. Then in:

  • Classic Excel: Choose Insert, Name, Create.
  • New Excel: Choose Formulas, Defined Names, Create From Selection.
  • Either Excel: Choose Alt+INC or Ctrl+Shift+F3

In the Create Names dialog, make sure that only Top Row is checked, then choose OK.

Set Up the Deseasonalized Sales Formulas

How to set up the deseasonalized sales formulasWith the Seasonal Index calculated, we now can deseasonalize our sales as shown in column E of this figure and as charted near the top of this page.

Enter the formula for the cell shown:

E5:  =C5/INDEX(SeasIndex,MONTH(B5))

Copy the formula down the column as shown.

The formula in cell E5 deseasonalizes the sales for January, 2005, by dividing those sales by the Seasonal Index for January. The formula in cell E6 divides the sales for February by the Seasonal Index for February, and so on.

The key to this formula is the INDEX function, which uses this version of the function:

=INDEX(array, row_or_column_num)

Here, the "array" is the SeasIndex range name. And the row number calculated by the MONTH function. When this function references a date serial number, it returns 1 for January, 2 for February, and so on.  We can use MONTH this way in the formula because the first row of SeasIndex has January's data; the second row has February's data, and so on.

Seasonal Index FAQs

As you start to work with your seasonal index, you'll probably have a few questions. Here are some of the most likely ones:

  Now that I have my seasonal index and deseasonalized sales, what do I do with this information?

You can chart your deseasonalized sales to get a better idea about the trend in your sales volume.

You can forecast monthly sales by multiplying your estimated sales for next year by the seasonal index for each month. Or you can estimate a 12-month trend for your deseasonalized sales and then apply the seasonal index to forecast your actual sales amounts.

You can plan your staffing and spending more accurately by using the Average Seasonal Index table to estimate your changes in sales volume during the year.

You can calculate seasonal indexes for different years of your sales history, and then compare them to see whether changes in your market are changing your seasonality over time. (For example, fifty years ago Jackson Hole, Wyoming, was packed with tourists in the Summer but shrank to only about 100 full-time residents in the Winter. Now, however, the Winter brings skiers. So seasonal patterns have changed significantly. Your seasonal patterns might be changing as well.)


  How many months of history do I need?

To calculate the seasonal index for January, 2008, you must start with July, 2007. And to calculate the seasonal index for December, 2008, you must end with June, 2009. So at the minimum, you need two years of sales data to calculate one year of seasonal indexes.

However, because seasonal indexes deal with repeating patterns, it would be best to average at least two years of seasonal indexes. This means you need at least 36 months of data.

Seasonal sales data

How should I add new rows to the table when I need to do so?

Carefully. Here's the issue:

When you need more rows, you'll insert them between rows 64 and 65 in this table. But formulas in the six cells above cell D65 all reference that cell. That means that when you insert rows for new data you'll mess up those six formulas in column D. That's not a problem if you follow these steps:

1. Insert rows immediately above the bottom gray border. (Here, between rows 64 and 65.) It's okay to insert enough rows for several years of data.

2. Copy the formulas in the range D11:E11 to cell D12 through the last non-gray cell in your table. (Here, to the range D12:D64.)

By copying from the top of the table, you copy formulas that you know weren't distorted when you inserted the rows. By pasting those formulas to existing formulas and to the new empty cells, you make sure that all the formulas in columns D and E reference the correct rows of data.

  Why don't you just use the OFFSET function in your formulas in column D? That would eliminate the problems you just described?

This question says that I could replace this formula...

D11:  =IF($A11+6<ActPds,$C11/AVERAGE($C5:$C16,$C6:$C17),"")

...with this formula...

D11:  =IF($A11+6<ActPds,$C11/AVERAGE(OFFSET($C11,-6,0,12,1),OFFSET($C11,-5,0,12,1)),"")

...where the OFFSET function returns a reference using these arguments...

=OFFSET(reference, rows, cols, height, width)

That's probably a good idea. The longer version certainly is safer. But if you don't use OFFSET much, the longer version probably is more difficult to understand. It's your choice.


Create Cycle Plots in Excel to Chart Seasonal Sales Data

Excel Charts, Seasonality, & Analysis: Five Lessons from the WSJ

Use Excel's SUMPRODUCT To Summarize Worksheet Data

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.