How to Build a Seasonal Forecast in Excel, Step by Step

If you sell anything seasonal, your moving average is lying to you twice a year.

On the way into the peak it forecasts too low, because it is averaging the quiet weeks you just had. On the way out it forecasts too high, because it is averaging the big weeks you just had. The forecast is always one season behind the customer.

The fix is not expensive software. It is a seasonal index, and you can build one in Excel in under an hour. Below is the whole method, tested on real numbers so you can see what it is worth.

The example: a juice with a summer peak

I am using Ruby's Apple Juice 1L from the free Nerd Foods dataset. Nerd Foods is a fictional Australian manufacturer, and the dataset has 104 weeks of demand for each of its products, from September 2024 to August 2026.

The juice is a good test because the season is strong and there are no promotions muddying the picture. Here is its average weekly demand by month in the first year, in cartons:

A January week sells nearly three times what a July week does. Any forecast that ignores that is going to be wrong most of the year.

Step 1: Lay out the history

Put your weekly history in two columns: week start date in column A, actual demand in column B. One row per week, oldest at the top. For the juice that is rows 2 to 105.

Before you go further, clean it. Take out anything that will not repeat: a one-off promotion, a stock-out week where demand was really higher than sales, a customer that has since been delisted. The index will faithfully learn whatever is in the history, including your mistakes.

Step 2: Tag each week with its month

In column C, add the month number:

=MONTH(A2)

Copy it down. This is the bucket each week belongs to.

You can use week of year instead of month, but with only one or two years of history each weekly bucket has just one or two data points and the index gets jumpy. Months give each bucket four or five weeks, which smooths out the noise. Start with months.

Step 3: Calculate the seasonal index

Set up a small table: months 1 to 12 in E2:E13. In F2, calculate the index for that month using the first year of history only (rows 2 to 53):

=AVERAGEIFS($B$2:$B$53, $C$2:$C$53, E2) / AVERAGE($B$2:$B$53)

Copy it down to F13. Each number tells you how a typical week in that month compares to an average week. For the juice:

• January comes out at 1.56, so a January week sells 56% more than average.
• July comes out at 0.56, so a July week sells 44% less than average.

A quick check: the twelve indices should add up to about 12. For the juice they add up to 11.98. If yours are well off, there is a gap or a duplicate in the history.

Step 4: Set the baseline level

The baseline is what an average week looks like with the season taken out. The simplest version is the average of the last full year:

=AVERAGE($B$2:$B$53)

Put it in H1. For the juice in year one it is 941 cartons a week.

Using a full year matters. If you average 13 weeks you get a level that is still contaminated by whatever season those 13 weeks happened to fall in, which is exactly the problem you are trying to fix.

Step 5: Build the forecast

For any future week, the forecast is the baseline multiplied by that month's index:

=$H$1 * XLOOKUP(MONTH(A54), $E$2:$E$13, $F$2:$F$13)

If you are on an older version of Excel without XLOOKUP, INDEX and MATCH or VLOOKUP do the same job.

That is the whole model. One average, twelve ratios and a multiplication.

Step 6: Test it before you trust it

This is the step most people skip. Do not.

I built the index from year one only, then used it to forecast every week of year two, and compared it with what actually sold. I also compared it with a 13-week moving average, which is what a lot of businesses use by default.

The measure is weighted error: the total of the absolute misses divided by total actual demand. In Excel:

=SUMPRODUCT(ABS(B54:B105 - D54:D105)) / SUM(B54:B105)

Here is how the two methods did across the 52 weeks of year two:

The seasonal index cut the error by more than two thirds. Both methods were roughly unbiased over the full year, within 1%. The difference is entirely in when the volume was forecast to arrive, and in a seasonal business that is the only thing that matters.

The month-by-month view shows where the moving average goes wrong:

From September to December the moving average is around 25 to 30% short. From March to July it is around 30 to 50% over. In practice that means not enough juice for Christmas and a warehouse full of it in May.

The seasonal index lands within 6% in ten of the twelve months. It misses in July and August, when year two came in at a different level from year one. That is normal, and it is why you re-test.

Step 7: Add trend if you have it

The juice was flat from year one to year two: average weekly demand of 941 then 946, up half a percent. So no trend adjustment was needed.

If your product is growing or declining, adjust the baseline rather than the index. Work out the growth rate you expect, from the last year's run rate or from what sales and marketing know is coming, and multiply the baseline by it:

=$H$1 * (1 + growth_rate) * XLOOKUP(MONTH(A54), $E$2:$E$13, $F$2:$F$13)

Keep the season and the growth separate. If you let one number carry both, you will never know which one was wrong.

Step 8: Layer in what the model cannot see

The index knows about the season. It does not know about the promotion next month, the new ranging at a major customer, or the price increase going through in March.

Add those on top as separate, labelled adjustments, in their own column. Do not edit the base forecast. When you review accuracy later you want to know whether the miss came from the model or from the adjustment.

Keeping it honest

Three habits keep this working:

• Rebuild the index once a year using the last full year or two of clean history. With two years, average them.
• Track weighted error every month using the formula in step 6. If it starts creeping up, something has changed in the business.
• Check the bias, not just the error. A forecast that is consistently 10% high is easy to fix. A forecast that is 10% high in one month and 10% low the next is a pattern problem.

If you want to follow along with the exact numbers above, the Nerd Foods dataset is free. The juice is SKU NF5001 on the Weekly Demand sheet.

A seasonal index will not make you a perfect forecaster. But a model that knows when summer is coming beats one that finds out three months late. And if your forecast is fine but stock still ends up in the wrong place, read why you can be out of stock and overstocked at the same time.