Let’s start with what a seasonality index is. It is a forecasting tool used to determine demand for various commodities or goods in a given marketplace over the course of a typical year (or a shorter time period). Such an index is based on data from previous years that highlights seasonal differences in consumption. In some industries, the seasonality index experiences huge swings. For example, toy makers are likely to experience peak demand in the months leading up to Christmas.

In Layman’s terms it tells you during a particular month (day/quarter/etc…) how much people are buying compared to other months.  This is represented by percentages that say; this month typically does X percent better/worse than an average month. 

An example of a candy store may have a seasonal index like below.  In October this candy store sells 37% more than an average month, while April sells 21% less.  This may be due to Halloween spiking sells in October while in April the rainy weather has people depressed and not leaving their houses.  By graphing this information you can quickly identify in which months you would expect to sell the most candy.  It is important to note that the average seasonal index for all these months is 100%, we will get further into why later.

Download the exercise file here that will be used from here on.  There are two tabs in the Excel file, “Start” and “Final.”  The Final tab will have all the finished formulas for you to reference or if you want to jump ahead.  The Start tab has the beginnings of our seasonality index including column headers and sales data by month for the last five years. 

Common Questions

Why take moving averages and not the average of all of the sales at once?

So you can account for the growth or decline in sales over the period of time.  In the example file given, there is a clear trend of growth over the years.  Moving averages give you a better understanding of your data over time, and in this case does not over value more recent data just because the numbers are larger.  

Why take a centered moving average?

Because there is an even number of months in a year, there is no exact midpoint that can be easily taken.  By taking the average of the two moving averages on either side you get to midpoint.  Note that if you were doing a seasonality index for the days of a week you would not need to do this as there is an odd number of days in a week and a middle is easily attainable.  

how much sales history do I need to have?

The more sales history you have the better.  I would say in order to get worthwhile data you would need two cycles worth, 24 months in this case (or 14 days if you are doing a weekly seasonality).  

Step By Step

  1. Create a 12 month moving average beginning at the second month; in cell D7 type =AVERAGE(C3:C14)  fill this formula down to cell D54
  2. Create a 12 month moving average beginning at the first month; in cell E7 type =AVERAGE(C2:C13)  fill this formula down to cell E54
  3. Create an average of the 12 month moving averages to find the centered moving average; in cell F7 type =AVERAGE(D7:E7)  fill this formula down to cell F54
  4. Find the initial seasonal estimate; in cell G7 type =C7/F7  fill this formula down to cell G54
  5. Create a table for the average initial seasonal estimate and normalized seasonality by month; type the following column headers beginning on row 67 columns A-D: Month of Year, Month, Average of Initial Seasonal Estimates, Normalized Seasonal Estimates.  Under the newly created Month of Year column type 1-12 (rows 68-79).  Under the newly created Month column type January-December (rows 68-79)
  6. Find the average initial seasonal estimate; in cell C68 type =AVERAGEIF($A$7:$A$54,A68,$G$7:$G$54)  fill this formula down to cell C79. 
  7. Find the normalized seasonality; in cell C80 type =SUM(C68:C79)  in cell C82 type 12   in cell D68 type  =C68/$C$80*$C$82  fill this formula down to cell D80
  8. Input the normalized seasonality for each month; in cell H2 type =$D$68  in cell H3 type  =$D$69  in cell H4 type =$D$70  continue this pattern till cell H13 and then start the process over for cells H14:H25, then repeat this same process all the way down to cell H61 so that every month of the year matches corresponding normalized seasonality.  
  9. Deseasonalize the sales dollars; in cell I2 type =C2/H2  fill this formula down to cell I61
  10. Graph the seasonality index; highlight cells B67:B79 and D67:D79 insert a 2D line chart
  11. Graph the actual vs deseasonalized sales; highlight cells C1:C61 and I1I:I61 insert a 2D line chart

Explanations With Images

  1. Create a 12 month moving average beginning at the second month; in cell D7 type =AVERAGE(C3:C14)  fill this formula down to cell D54.  The purpose here is to get a 12 month moving average, the reason we are offsetting by the first sales month is part of the centered moving average we are ultimately trying to find.  More on this in step 3. 

2. Create a 12 month moving average beginning at the first month; in cell E7 type =AVERAGE(C2:C13)  fill this formula down to cell E54.  The purpose for this is the second step in finding the centered moving average (step 3). 

3. Create an average of the 12 month moving averages to find the centered moving average; in cell F7 type =AVERAGE(D7:E7)  fill this formula down to cell F54.  This is taking the average of the first two formulas written giving us a centered moving average.  There are two things going on here; first, the reason we are not taking an average of all of the data at once is so we are able to account for a trend in the data.  In this case there is an upward trend of sales dollars that would not be accounted for properly if all of the observations were averaged at once.  Second, we want to find the true middle of these moving averages to provide the best data.  This is accomplished by averaging your moving averages.  The chart below shows the differences in these three averages.  Though they are relatively close, the small variations make a big difference.  Note that if you were looking for the seasonality for days of the week you would not need to do this as there are 7 days (odd number) in a week, so a middle can be found more easily. 

4. Find the initial seasonal estimate; in cell G7 type =C7/F7  fill this formula down to cell G54.  This is taking the sales dollars for that month and dividing it by the centered moving average for the cycle (in this case the cycle is 12 months or one year) telling you what percentage that month was relative to an average month in that cycle. 

5. Create a table for the average initial seasonal estimate and normalized seasonality by month; type the following column headers beginning on row 67 columns A-D: Month of Year, Month, Average of Initial Seasonal Estimates, Normalized Seasonal Estimates.  Under the newly created Month of Year column type 1-12 (rows 68-79).  Under the newly created Month column type January-December (rows 68-79).  All this is doing is getting set up to find the normalized seasonality for each month (steps 6 and 7). 

6. Find the average initial seasonal estimate; in cell C68 type =AVERAGEIF($A$7:$A$54,A68,$G$7:$G$54)  fill this formula down to cell C79.  This is taking the average initial seasonal estimate for each respective month.  What this tells you is how much above or below average each month is. 

7. Find the normalized seasonality; in cell C80 type =SUM(C68:C79)  in cell C82 type 12   in cell D68 type  =C68/$C$80*$C$82  fill this formula down to cell D80.  The normalized seasonality actually tells us what the seasonal index is for each month.  If you notice the formula written in C80 is a sum of our initial seasonal estimates, and the number does not equal 12, which it would if all those numbers were truly an average.  Since they are not we need to normalize them so they sum to 12 and really know where things are relative to the average.  So all we have to do is take the initial estimate for the month and divide it by the sum of the initial estimates then multiply it by what it should be, in this case it is 12.  

8. Input the normalized seasonality for each month; in cell H2 type =$D$68  in cell H3 type  =$D$69  in cell H4 type =$D$70  continue this pattern till cell H13 and then start the process over for cells H14:H25, then repeat this same process all the way down to cell H61 so that every month of the year matches corresponding normalized seasonality.   Note that converting these numbers to percentages makes them easier to view.  The purpose of this is so we can complete later steps and make visualization easier later.  It is also beneficial to see what the initial seasonal estimate is vs the completed and relative to the sales dollars that occurred in that time period. 

9. Deseasonalize the sales dollars; in cell I2 type =C2/H2  fill this formula down to cell I61  Doing this shows you what the sales dollars for the month are if you take out the seasonalization.  For example Oct, 2015 was a good month with $69,099 in sales, but October is typically the best month because of Halloween so you know numbers will be high.  If you deseasonalize the numbers it turns into $50,518 which is still good, but not better than February, 2016 where deseasonalized sales were $53,271.  So even though February had lower sales than October, February did better when factoring in which months typically do better than others.  It is a way of putting numbers into perspective. 

10. Graph the seasonality index; highlight cells B67:B79 and D67:D79 insert a 2D line chart.  I like to rename the chart “Candy Company Seasonality index” because it makes more sense to those reading it.  What this shows you is a graphical representation of each month and the kind of sales you should expect relative to the average month. 

11. Graph the actual vs deseasonalized sales; highlight cells B1:C61 and I1:I61 insert a 2D line chart