The Excel chart below 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 of the week, 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 just as difficult. It’s hard to picture any method that could generate a reliable sales forecast that has a pattern like the one shown here.
Years ago, I faced these issues when I was the CFO for a small lawn-and-garden company whose monthly sales varied from a low in Winter months of about $50,000 to a high in the Spring of about $1 million 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 dark gray line in this Excel chart shows sales over the past four and a half years, with the effects of seasonality removed from the data shown by the blue line.
That is, the dark line shows deseasonalized sales.
By looking at deseasonalized data, we easily can see that sales fell in 2010 and the first part of 2011, fell slightly in 2012, and generally increased in the remaining periods.
To forecast sales, we estimate the general direction of deseasonalized sales, and then seasonalize that trend to produce specific sales estimates.
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.
To calculate the seasonality for days within a week, we create a seasonal index for the days of the week.
The most important step is to calculate the correct value for 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:
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 choose Formulas, Defined Names, Create From Selection. Or 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. (Here, SeasIndex is a column in the preceding table.)
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.)
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 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 OFFSET 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.
