Building a seasonal index from your own sales history

July sells 3.3 times what January sells for the same hiking sock. Reorder against a flat monthly average and you carry half a year of dead cover through winter, then go dark every June anyway — demand didn't surprise you; you averaged away a shape you already owned. A seasonal index repairs that with one number per month: that month's sales divided by an average month. Below is the ratio-to-average method, worked end to end on twelve months of hiking-sock sales, with the promo-week cleanup and the exact spot where each multiplier enters a forecast and a reorder point.

Running the season itself — when to plan it, how to handle carryover and markdowns — lives in the seasonal inventory management guide, and the year map of which weeks matter for which category lives in the retail buying calendar. This article owns one thing only: the math that turns your sales history into multipliers you can trust.

What a seasonal index actually is

A seasonal index is a per-month multiplier. An index of 1.0 is an ordinary month. 1.76 means that month runs 76% above ordinary; 0.53 means it does about half an ordinary month's trade. The twelve indices average 1.00 by construction, so the set moves demand around the calendar without changing the annual total — which is what lets it sit on top of any baseline without corrupting it.

Statisticians have done this for a century under the name seasonal adjustment, and the ratio-to-average version is the classical decomposition found in any forecasting text (Hyndman & Athanasopoulos, Forecasting: Principles and Practice, OTexts, accessed November 2026; NIST/SEMATECH e-Handbook of Statistical Methods, accessed November 2026). The same idea runs at national scale in official statistics through the Census Bureau's X-13 seasonal adjustment program (U.S. Census Bureau, X-13ARIMA-SEATS, accessed November 2026). What you're building is the hand-rolled retail version of the same machine, in a spreadsheet, from data you already have.

One boundary worth stating: the index is not a forecast. It's the shape. The level comes from a baseline — how much an average month does next year — and the index bends that baseline into the months. Keeping the two separate is what makes the whole thing auditable later.

The ratio-to-average method, worked on a year of hiking-sock sales

Two steps. First, the monthly average: total units for the year divided by 12. Second, each month divided by that average. Here's a full year for one SKU — SOCK-TR-M-02, a midweight trail sock — with illustrative numbers:

Total for the year: 1,400 pairs. Monthly average: 1,400 ÷ 12 = 116.7 pairs. Then every month against it — July: 205 ÷ 116.7 = 1.76; January: 62 ÷ 116.7 = 0.53 — and so on for all twelve:

Sanity check: unrounded indices always sum to exactly 12 — that's algebra, not luck, because the months sum to the total and the average is the total divided by 12. The table shows 11.99 purely from two-decimal rounding. If your sheet's indices land anywhere far from 12, a range in the spreadsheet is wrong.

The whole build is three formulas:

• Column B: monthly units, pulled from your sales reports by month (Shopify Help Center, accessed November 2026).
• B14: =SUM(B1:B12) and =B13/12 for the total and the average.
• Column D: =B1/$B$14, filled down — the indices.

That's the month-level index. Everything after this section is refinement of the inputs or use of the outputs.

Removing promo weeks before you average

July's 205 pairs included a July 4 flash bundle that moved 70 pairs in one week. Leave it in and the July index describes a promotion, not a season — next year's "normal July" forecast inherits a one-week event, and every reorder sized from that forecast over-buys the peak.

The fix is one subtraction and a rebuild. Organic July: 205 − 70 = 135. Adjusted annual total: 1,400 − 70 = 1,330. Adjusted average: 1,330 ÷ 12 = 110.8. Adjusted July index: 135 ÷ 110.8 = 1.22, down from 1.76. Note that every other month's index also moves, because the denominator fell — January goes from 62 ÷ 116.7 = 0.53 to 62 ÷ 110.8 = 0.56. Subtract promo units from both the month and the annual total, then let the sheet recompute; with the three-formula layout above, that's one cell change (the SUM) and the arithmetic follows.

Two judgment calls to make deliberately:

• Pull-forward is real. A promo moves demand as often as it creates it. If the July bundle stole sales from August, August's index reads a touch low. Either accept that or shift a reasonable share back — just write down which you did, so next year's version of you isn't auditing a guess.
• Identify promo units from order data, not memory. Shopify's sales reports break out discount codes, so the promo week's units are findable rather than estimated (Shopify Help Center, accessed November 2026).

The week-level version for peak trading

Month-level indices are too coarse inside a peak: Black Friday week and the week after it are different animals living in the same November. The method is identical with the denominator swapped — weekly average is annual units ÷ 52. For the sock: 1,400 ÷ 52 = 26.9 pairs a week. Then, illustratively:

• Week containing Black Friday: 61 pairs ÷ 26.9 = 2.27
• Ordinary mid-April week: 24 ÷ 26.9 = 0.89
• Mid-January week: 11 ÷ 26.9 = 0.41

Rules that keep weekly indices accurate:

• Align by event, not calendar week number. Black Friday moves; the week containing Thanksgiving barely wanders. Tag event weeks by name so year-over-year comparisons line up — the full anchor-alignment method, including the 53-week year trap, is covered in the year-over-year calendar alignment guide.
• Blend two years where you have them. A single year of weekly indices jumps around; averaging the same event week across two years steadies it.
• Keep week-level indices off single SKUs. At 26.9 units a week, one bulk order swings a single-SKU weekly index by half a point. Build weekly shapes on store-wide or category-level demand, where denominators are big enough to mean something. Month-level per SKU, week-level per store or category is the split that works.

Applying the index to a forecast and a reorder point

The forecast step is a single multiplication: month forecast = baseline × index. The baseline is your trend-adjusted average month — how to set that cleanly is covered in the baseline demand walkthrough. Say next year's baseline for the sock is 125 pairs a month (illustrative). Then June = 125 × 1.47 = 184 pairs, and July = 125 × 1.76 = 220 pairs.

The reorder point is where merchants drop the ball, because the formula needs demand during the lead time, and the natural mistake is using the rate of the month you order in. The wait covers the weeks you'll sell through while stock is inbound — apply those weeks' indices instead.

Worked example, all illustrative: PO placed May 20, supplier lead time 38 days, stock sellable June 27. The wait covers 12 days of May (index 1.18 → 125 × 1.18 ÷ 31 ≈ 4.8 pairs/day → 57 pairs) and 27 days of June (index 1.47 → 125 × 1.47 ÷ 30 ≈ 6.1 pairs/day → 166 pairs). Demand during the wait ≈ 223 pairs. The flat-average alternative — 1,500 ÷ 365 ≈ 4.1 pairs/day × 38 — says 156. The seasonal answer is 67 pairs higher, and that gap is the difference between covering the year's shape and hoping the peak waits for the container. The full formula, safety stock included, is in the reorder point formula guide. For a SKU entering its second season, the index combines with carryover and correction math — the year-two seasonal buy plan works that end to end.

How much history you need, and where the method breaks

Twelve months is the minimum — less can't see a full cycle at all. Two years is the sweet spot: compute each year's indices against that year's own average, then average the two values per month. If July gave 1.76 last year and 1.68 the year before, the blended index is (1.76 + 1.68) ÷ 2 = 1.72. Because each year is measured against itself, growth between the years doesn't leak into the shape — a store that doubled doesn't grow a fake summer bulge.

The three ways it breaks:

• Out-of-stock months poison the index. A shelf at zero records zero sales, and the index reads that month at half strength when real demand was higher. Patch by substituting the prior year's index for the damaged month, or by scaling the month's units by in-stock days ÷ days in the month — then renormalize so the indices still sum to 12.
• Anomalous years aren't shapes. A year with a long supply gap or one viral spike is an event, not a pattern. Compute its indices separately, and where your ops notes explain a freak month, drop that month's contribution instead of letting it bend the average.
• Respect the error band. An index built from 62 January units has a wide error band; 0.53 and 0.56 are the same number wearing different rounding. The shape — a half-month versus a 1.8-month — is the signal. The second decimal is noise.

What tools will and won't do with seasonality

One note on software, since we build some. InventoryX's planned weekly Buying List is intended to compute days of cover and suggested quantities from in-stock-only velocity — a level, not a seasonal curve. Excluding sales made while a SKU was out of stock keeps the baseline clean, but V1 doesn't build or apply seasonal indices: the curve stays in your spreadsheet, and multiplying a suggested quantity by the right index when an order spans peak weeks remains a manual step. We'd rather leave that seam visible than imply the app smooths it for you.

Common questions

What is a seasonal index? A multiplier that says how a given month compares with an ordinary month: monthly sales ÷ average monthly sales. An index of 1.76 means that month runs 76% above average; 0.53 means it runs about half. All twelve indices average 1.00, so they shift demand between months without changing the annual total.

How do I calculate a seasonal index in a spreadsheet? Divide each month's units by the average month, then check the twelve results sum to 12. In practice: column B holds monthly units, C1 is =AVERAGE(B1:B12), and D1 is =B1/$C$1, filled down. For two or more years, compute each year's indices separately, then average the two values per month.

How many years of history do I need for a seasonal index? Twelve months minimum — less can't see a full cycle at all. Two years is the sweet spot: compute each year's indices against its own average, then average the two values per month, so growth between years doesn't distort the shape. Beyond three years, older patterns often stop applying; refresh annually.

Should I use month-level or week-level seasonal indices? Month-level for reorder decisions on replenishment cycles of several weeks or more; week-level for timing inside a peak, like Black Friday week versus adjacent weeks. Weekly indices on a single SKU are noisy — small weekly counts let one bulk order swing the number — so build week-level indices on store-wide or category demand.

How do I apply a seasonal index to a forecast? Multiply your baseline — the trend-adjusted average month — by that month's index, so a 125-pair baseline and a July index of 1.76 forecast 220 pairs. For reorder points, use the indices of the weeks you'll sell through during the lead time, not the month you place the order; ordering into the peak changes the math.

Next step

Pick the one SKU you most reliably run short of at peak and build its index this afternoon — the whole sheet is three formulas. Check the indices sum to 12, subtract the promo month you already know about, and file it next to your buying calendar. Next time a PO for that SKU comes up, recompute its reorder point with the wait-window rate from the section above (the formula, step by step).