A seasonal index is a number that tells you how much higher or lower a given month or quarter typically runs compared to the average period in your data. An index value of 1.10 for July, for example, means July sales typically run 10% above the yearly average, while a value of 0.85 for February means February usually falls 15% below it. Finding these indices requires a few layers of calculation, but the core logic is straightforward: strip out the trend, isolate what repeats every cycle, and average those repeating patterns together.
What You Need Before You Start
You need at least three full years of data broken into consistent time periods (months, quarters, or weeks). Three years is a practical minimum for spotting a real pattern versus random noise. Government statistical agencies like the U.S. Bureau of Labor Statistics use 6 to 10 years of data for their seasonal adjustments, and some of their diagnostic tests require 11 to 14 years. More data produces more stable indices, but for business forecasting purposes, three to five years of monthly or quarterly figures will get you a usable result.
Your data should cover complete cycles. If you’re working with monthly data, don’t stop in September. Partial years skew the averages because some months appear more often than others.
The Simple Average Method
This is the fastest approach and works well when your data has no strong upward or downward trend over time. Here’s how it works with quarterly data:
- Step 1: For each year, calculate the annual average. If your four quarterly values for 2022 are 200, 180, 260, and 220, the annual average is 215.
- Step 2: Divide each quarter’s actual value by that year’s annual average. Q1 of 2022 becomes 200 / 215 = 0.93. Q3 becomes 260 / 215 = 1.21.
- Step 3: Repeat for every year in your dataset, so you have a ratio for every single period.
- Step 4: Average all the Q1 ratios together, all the Q2 ratios together, and so on. These averages are your seasonal indices.
The limitation of this method is that it doesn’t account for trends. If your sales doubled over five years, the earlier years pull the index in one direction and the later years pull it in another. Texas A&M researchers demonstrated this with wheat prices: when prices trended sharply upward from 2006 to 2010, the simple average method produced indices with a visible slope that reflected the trend rather than pure seasonality. For data with significant growth or decline, the moving average method is more reliable.
The Ratio-to-Moving-Average Method
This is the standard technique used in most forecasting textbooks and software. It removes the trend before measuring seasonality, which makes it accurate even when your data is climbing or falling over time.
Step 1: Calculate a Centered Moving Average
If you have monthly data, compute a 12-month moving average. For quarterly data, use a 4-quarter moving average. This moving average needs to be “centered,” meaning it aligns with the midpoint of the periods it covers. With an even number of periods (like 12 months), you’ll need to average two consecutive moving averages to center them properly. This centered moving average represents your trend, with seasonal ups and downs smoothed out.
Step 2: Divide Actual Values by the Moving Average
For each period, divide the original data point by the centered moving average for that same period. The result strips away the trend and leaves you with a ratio that captures seasonality plus random irregularity. A ratio of 1.15 means that period ran 15% above what the trend alone would predict. You won’t have ratios for the first and last few periods in your dataset because the moving average can’t be calculated at the edges.
Step 3: Average the Ratios for Each Season
Collect all the January ratios and average them. Do the same for February, March, and every other month. Because the random irregular component bounces above and below unpredictably, averaging it across multiple years causes it to wash out. What remains is the consistent seasonal pattern.
Step 4: Normalize the Indices
The raw averages from Step 3 won’t sum to exactly the right total. For multiplicative indices with monthly data, the 12 index values should sum to 12.00 (or equivalently, 1200%). For quarterly data, they should sum to 4.00. To fix this, multiply each raw index by an adjustment factor: divide the target sum by the actual sum of your raw indices. This rescaling ensures the seasonal pattern nets to zero overall effect across a full year.
Additive vs. Multiplicative Models
The method described above produces multiplicative seasonal indices, where the seasonal effect is expressed as a ratio (like 1.10 or 0.85). This is the right choice when the size of seasonal swings grows as the level of your data grows. If December sales are always about 20% higher than average regardless of whether your baseline is $10,000 or $100,000, the multiplicative model fits.
An additive model expresses seasonality as a fixed amount added or subtracted from the trend. Instead of “20% above average,” an additive index might say “2,000 units above average.” In the additive approach, you subtract the trend from the data instead of dividing, and the seasonal indices for all periods should sum to zero rather than to the number of periods. Use additive indices when the magnitude of seasonal fluctuations stays roughly constant even as your overall level changes.
Most real-world business data behaves multiplicatively: bigger volumes produce bigger seasonal swings. If you’re unsure, plot your data. If the peaks and valleys get wider over time, go multiplicative. If they stay the same height, go additive.
How to Build This in a Spreadsheet
You can calculate seasonal indices entirely in Excel or Google Sheets without any add-ins. Set up your data in a column with one row per period, then build the calculation in adjacent columns:
- Column A: Date or period label
- Column B: Original data values
- Column C: Centered moving average (use AVERAGE over the appropriate window, then average two consecutive results to center)
- Column D: Ratio to moving average (Column B divided by Column C)
- Column E: Use AVERAGEIF to compute the mean ratio for each season (January, February, etc.) across all years
- Column F: Rescale those averages so they sum to the correct total
Once you have your final indices in a reference table, you can use VLOOKUP or INDEX/MATCH to pull the right index into each row based on the month or quarter. This makes it easy to apply the indices to new data or forecasts.
How to Read and Use Seasonal Indices
With multiplicative indices, 1.00 is the baseline. It means that period is perfectly average. A value of 1.25 means demand or price typically runs 25% above the annual average during that period. A value of 0.70 means it runs 30% below.
The most common use is deseasonalizing your data so you can see the true underlying trend. Divide each period’s actual value by its seasonal index, and you get a deseasonalized figure. This lets you compare January to July on equal footing, without the seasonal noise making one look artificially high or low. The formula is simple: deseasonalized value = actual value / seasonal index (for multiplicative) or actual value minus seasonal index (for additive).
The other common use runs in reverse: reseasonalizing a forecast. If your trend model predicts average sales of 5,000 units next March, and March’s seasonal index is 1.18, your seasonally adjusted forecast becomes 5,000 × 1.18 = 5,900 units. This is how seasonal indices plug into larger forecasting systems. You build a trend forecast on deseasonalized data, then layer the seasonal pattern back on to produce realistic period-by-period projections.
Common Mistakes to Avoid
Using too little data is the most frequent problem. With only one or two years, a single unusual month (a supply shortage, a one-time promotion) can distort the index for that season permanently. Three years is the floor; five is safer.
Failing to center the moving average is another common error. An uncentered 12-month moving average is offset by half a period, which shifts all your ratios slightly and introduces a subtle bias. Always center it.
Finally, don’t forget to recalculate your indices periodically. Seasonal patterns can shift over time as consumer behavior, climate, or market conditions change. An index built on 2015 to 2019 data may not reflect post-2020 patterns accurately. Refreshing your indices every year or two, using a rolling window of recent history, keeps your forecasts grounded in current reality.