A step-by-step guide to computing a monthly seasonality index in Excel from two or three years of sales: monthly totals with SUMIFS, each month as a ratio to its year's average, the index as the average of those ratios, the check that the twelve indices sum to twelve, and three uses: seasonally adjusting this month's figure, building a run rate that does not mislead, and spreading an annual target across months. Includes the exact formulas, what to do with one-off spikes, and the point at which the spreadsheet stops being enough.
A seasonality index is twelve numbers that say what a normal January, February and so on looks like for this business. With them, any month can be read against any other. This guide computes them in Excel and shows three uses.
Sheet Sales: date in A, amount in B. At least two full years. On Calc, years across the top from B2, such as 2023, 2024, 2025; months 1 to 12 down from A3.
Cell B3, filled across and down:
=SUMIFS(Sales!$B:$B,Sales!$A:$A,">="&DATE(B$2,$A3,1),Sales!$A:$A,"<"&DATE(B$2,$A3+1,1))
DATE handles month 13 correctly as January of the next year. Check the block against the ledger: the sum of each column should equal that year's total.
In a second block starting at F3, for the first year:
=B3/AVERAGE(B$3:B$14)
Fill across for each year and down for each month. Dividing by the year's own average removes growth, so a business that doubled between the first and last year still gives comparable ratios.
Average of the ratios across years, J3:
=AVERAGE(F3:H3)
With three or more years and a suspected outlier, use MEDIAN instead.
=SUM(J3:J14)
Twelve, or very close. If it is 11.9 or 12.1, which happens with the median, rescale in column K:
=J3*12/SUM($J$3:$J$14)
| Month | Index |
|---|---|
| January | 0.82 |
| February | 0.88 |
| March | 1.10 |
| April | 0.97 |
| May | 1.02 |
| June | 1.08 |
| July | 0.90 |
| August | 0.74 |
| September | 1.06 |
| October | 1.12 |
| November | 1.15 |
| December | 1.16 |
| Sum | 12.00 |
This business sells a quarter less than average in August and about 15 percent more in the last quarter.
=actual/index
| Month | Actual | Index | Adjusted |
|---|---|---|---|
| July | $900,000 | 0.90 | $1,000,000 |
| August | $760,000 | 0.74 | $1,027,000 |
| September | $1,040,000 | 1.06 | $981,000 |
Read unadjusted, August was a collapse and September a recovery. Adjusted, August was the best of the three and September the weakest. The conversation in the September review should be the opposite of the one the raw figures would produce.
Year to date through August, annualised naively, is year to date times 12 over 8. It ignores that the strongest months are still to come. The adjusted version divides by the share of the year those months normally represent:
=YTD_actual/SUM(index for months 1 to 8)*12
With the indices above, January to August sum to 7.51, not 8. A year to date of $7,500,000 annualises to $11,250,000 naively and $11,984,000 with the index. The run rate and seasonality worked example does the same by hand.
=annual_target*index/12
A $12,000,000 target is $740,000 in August and $1,160,000 in December, not $1,000,000 in each. A rep measured against flat monthly targets is over target every autumn and under every summer regardless of performance, and both the praise and the pressure are noise.
One index for the whole business hides segments with opposite seasons: a merchant whose trade customers peak in spring and whose retail customers peak in December. Add a segment column to the data, a segment criterion to the SUMIFS, and compute an index per segment. If the segment indices are similar, use one; if not, a customer in a given segment should be read against its own.
One year of history. Last year's one-off becomes every year's pattern.
Growth left in. Ratios to the average of all years, not each year's own average, make late months in a growing business look seasonal.
Partial years included. Six months of the current year pull the averages. Use complete years only.
Moving holidays. Easter falls in March or April; a month-level index smears it. For businesses where it matters, combine March and April, or work in weeks with the holiday week flagged.
Trading days ignored. February has fewer selling days than March. For daily-order businesses, compute the index on sales per trading day.
Twelve indices for the whole business is an easy spreadsheet job. Indices by segment and category, applied automatically to every account when judging whether it is ordering below its own pattern, is not; see the four signs a spreadsheet is no longer enough. For when the same-period comparison is the better tool, see seasonality in sales measures. Covirage computes the indices from the ledger and applies them wherever a month is compared with another, so a seasonal dip does not put healthy accounts on the at-risk list.
Two full years at a minimum, three to five if the business has been stable. With one year the index is just that year's shape, one-off events included. With more than five, the early years may describe a different business. Use complete calendar or fiscal years so that every month is counted the same number of times.
Remove or cap it before computing the index. A single large project invoiced in one March will make every future March look weak by comparison. Either exclude that order from the history used for the index, or use the median across years instead of the average, which ignores one outlier when there are three or more years.
It is a good comparison and needs no index. The index adds two things: it lets this month be compared with last month, which same-period comparison cannot do, and it turns a partial year into a sensible run rate. Use both: growth against the same month last year, and the adjusted figure for trend within the year.