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, 20052014
The Father of Spreadsheet Dashboard Reports

This
Excel chart shows the typical sawtooth 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
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
The
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 midpoint of those seven days, as shown in this
figure:
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:
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:
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:
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
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))}
To
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
With
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 12month 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 fulltime residents in the Winter. Now, however, the
Winter brings skiers. So seasonal patterns have changed significantly.
Your seasonal patterns might be changing as well.)
Etc.
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.
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 nongray 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.
