How to Build a Demand Forecast in Excel

You don't need expensive forecasting software to build a decent demand forecast. Most businesses — especially ones with a few hundred SKUs rather than a few hundred thousand — can get a genuinely useful forecast out of Excel. Here's how to actually build one.

Start with clean history, not more data

Before any formula, get your historical demand into a simple structure: one row per SKU per week (or month), one column for actual units sold. Resist the urge to include every field you have — extra columns make the model harder to build and don't improve accuracy on their own.

Clean the obvious distortions first: one-off bulk orders that don't reflect normal demand, stockout periods where sales were capped by supply rather than reflecting true demand, and any data entry errors. A forecast built on dirty history will confidently predict the wrong thing.

Pick a baseline method that matches your demand pattern

You don't need anything exotic. Three methods cover most situations:

Moving average — average of the last N periods. Works well for stable, low-variability demand with no strong trend or seasonality. Simple =AVERAGE() over a rolling window.

Trend-adjusted average — moving average plus a trend factor, using Excel's TREND() or FORECAST.LINEAR() function. Better when demand is steadily growing or declining.

Seasonal index method — calculate an average seasonal pattern (e.g. this month's typical share of annual volume) and apply it to a de-seasonalised trend. More setup, but necessary if your business has real seasonality — think ice cream, gift items, or anything tied to a calendar event.

Start with the simplest method that reasonably fits your data. You can always add sophistication later; you can't easily debug a model that's already too complex.

Build the seasonal index, if you need one

1. Calculate each period's demand as a percentage of that year's total (or a rolling 12-month total)
2. Average that percentage across multiple years for each period, to smooth out one-off noise
3. Apply the resulting index to your trend line: Forecast = Trend × Seasonal Index

This is the part of a forecast that's most often skipped, and it's usually the single biggest accuracy improvement for genuinely seasonal products.

Layer in what the model can't see

The statistical baseline is a starting point, not the finished forecast. Add an adjustment column next to it for known future events the history doesn't capture: a promotion, a new customer listing, a price change, a competitor stockout. Keep the baseline and the adjustment as separate columns rather than editing the baseline directly — you want to be able to see later how much of your forecast was statistics versus judgement.

Track accuracy from day one

Add a simple accuracy check as soon as you have your first forecast versus actuals: =ABS(Actual - Forecast) / Actual gives you a basic percentage error per period. Don't wait until you've "finished" the model to start measuring — accuracy tracking from the start tells you quickly whether your method is actually working, and for which SKUs it isn't.

Where Excel forecasting breaks down

Excel is genuinely fine for tens to low hundreds of SKUs with manageable seasonality. It starts to strain when you have thousands of SKUs, multiple locations, complex promotional calendars, or need automated exception management (flagging only the forecasts that need human attention instead of reviewing everything). That's the point where dedicated forecasting software starts paying for itself — but plenty of businesses never reach that point, and a well-built Excel model outperforms a poorly configured expensive system every time.

Next in this series: How to calculate safety stock in Excel — or practise this method directly on the Nerd Foods dataset, which has 104 weeks of demand history ready to forecast against.