Inventory Forecasting: Methods, Formulas & Examples

What inventory forecasting is (and why most small biz gets it wrong)

Inventory forecasting predicts how many units of each SKU you'll sell over a future window — typically the next 4 to 12 weeks. It's the input that drives every downstream decision: how much to order, how much safety stock to carry, how much cash to tie up, how much shelf space to allocate.

The most common mistake is the "last year, same month" approach. It feels rigorous because it uses real data, but it ignores three things: trend (your business is bigger or smaller than last year), variance (last year was one sample, not the average), and changed conditions (new SKUs, new competitors, new prices).

The second most common mistake is over-engineering. Someone reads about ARIMA, buys a tool with "AI" in the name, and now there's a model nobody understands producing numbers nobody trusts. For most SMBs with under 1,000 SKUs and basic seasonality, a properly applied moving average plus seasonal index will beat a fancy model run badly.

You want a method that's:

• Transparent enough that you can explain why next week's forecast is 47 and not 60.
• Cheap enough to run weekly without dedicating a person to it.
• Accurate enough to feed your inventory planning cycle.

Five methods clear that bar.

The 5 forecasting methods that work for SMBs

In rough order of complexity:

1. Naive forecast. Next period equals this period. "We sold 50 last week, so we'll sell 50 next week." Useful as a baseline — if your fancy model can't beat naive, the fancy model is broken. Also fine for very stable SKUs.

2. Simple moving average (SMA). Average of the last N periods. Smooths out noise. Best for stable SKUs without strong trend or seasonality.

3. Weighted moving average (WMA). Same as SMA but recent periods count more. Useful when demand is shifting (a growing SKU, a fading SKU) and you want the forecast to react faster.

4. Exponential smoothing (ES). A weighted average where the weights decay exponentially. Smoother than WMA, more responsive than SMA, controlled by a single parameter (α). Most SMB forecasting tools use some flavor of this under the hood.

5. Seasonal decomposition. Split demand into level, trend, and seasonal index. Apply the seasonal index back when forecasting future periods. Required if you have clear seasonality — holiday retail, summer beverages, school supplies.

You'll usually combine methods. Exponential smoothing for the base signal, then a seasonal index layered on top. That's roughly what Holt-Winters does, and Holt-Winters is the workhorse of SMB forecasting.

Moving average — walked through with numbers

Let's forecast next month's demand for a single SKU using a 3-month moving average. Here's 6 months of sales:

3-month moving average forecast for May:

That's it. You'd typically round to 132 and call that your point forecast for May.

Now let's check how it would've performed historically. Forecast for April using Jan/Feb/Mar:

That 13% error matters. It tells you how much safety stock you need to cover forecast misses. A bigger error → more safety stock. The forecasting and reorder point work are joined at the hip.

When SMA breaks: if demand is trending up, SMA lags. By the time the moving average catches up, you've been stocking out for a month. Switch to a weighted MA (give April weight 0.5, March 0.3, February 0.2) or exponential smoothing. Same data, sharper response.

Exponential smoothing — when and why

Exponential smoothing is a moving average that gives more weight to recent data, with the weights decaying exponentially as you go back in time. The formula for simple exponential smoothing:

α controls responsiveness. Higher α (say 0.5) means the forecast snaps to recent changes. Lower α (say 0.1) means it smooths heavily and ignores short-term noise. For most SMB SKUs, α between 0.2 and 0.4 is a sensible starting point.

Let's run our coffee mug data through it with α = 0.3, starting from a seed forecast of 120 (just use the first actual):

The May forecast comes out at ~129 — a touch lower than the 3-month MA's 132, because ES has more memory of the slower Jan/Feb numbers. Neither is "right." Both are defensible.

How to pick α: in a spreadsheet, try 0.1, 0.2, 0.3, 0.4, 0.5. Compute mean absolute error (MAE) against your actuals for each. Pick the α with the lowest MAE. That's parameter tuning, and it takes about 10 minutes per SKU once you have the template.

Handling seasonality and promotions

Seasonality is what kills naive forecasting. Sell ice cream? June isn't January. Sell tax software? March isn't June. You need to split the signal.

Two ways to model seasonality:

Additive: seasonal effect adds a fixed number of units. Demand = level + trend + seasonal offset. Use this when the seasonal swing is roughly the same size regardless of overall volume (e.g., +30 units every December, whether the baseline is 100 or 200).

Multiplicative: seasonal effect is a percentage. Demand = (level + trend) × seasonal index. Use this when the swing scales with volume. December is 1.5× a normal month — whether the normal month is 100 or 1,000.

Multiplicative is what most retail and consumer goods need. To build seasonal indices in a spreadsheet:

1. Get 24+ months of sales by month.
2. Compute the 12-month moving average centered on each month. This is your "deseasonalized" baseline.
3. For each month, divide actual / baseline. That's the raw seasonal ratio.
4. Average the ratios across years for each calendar month. That's the seasonal index.
5. Normalize so the 12 indices average to 1.0.

Apply it: forecast the level using exponential smoothing on deseasonalized data, then multiply by the month's index. The inventory planning guide section 4 walks through how this feeds into your safety stock once the forecast lands.

Promotions. Treat as a separate adjustment, not part of the base forecast. If a 20%-off promo historically lifts unit sales by 1.8×, the planner adds that multiplier on top of the base forecast for the promo week. Keep the lift assumption documented — promo lifts decay over time and you'll want to re-measure.

Forecasting in a spreadsheet vs. inventory software

You can run all of the above in a spreadsheet. Excel has FORECAST.ETS built in, which does Holt-Winters automatically and even produces confidence intervals. Google Sheets has the same function. For 50–200 SKUs and one location, a well-structured workbook is fine.

The pain shows up at scale. Three failure modes:

• Refresh fatigue. Pulling fresh sales data, deduplicating it, joining to the SKU master, and refreshing 200 forecast rows takes time. Skip a week and the model goes stale. Skip a month and it's useless.
• No connection to action. A spreadsheet forecast doesn't trigger a PO. Someone reads the forecast, then types numbers into a different system. That handoff is where errors creep in.
• No history of changes. You can't tell which forecasts were good or bad after the fact, because the spreadsheet just shows the current version.

Inventory software solves all three. The forecast lives next to the perpetual inventory record. Reorder points update automatically as forecasts shift. History is logged. You can ask "did our March forecast for SKU 12 match actual?" and get an answer in a click.

The cost is real but not crushing — usually less per month than the cost of one stockout on a top-20 SKU. If you've outgrown the spreadsheet, the math has already paid for the software.

A side note on inventory turnover: a good forecast is the single biggest lever for improving turnover. Better forecast → less buffer needed → leaner inventory → higher turns. The turnover ratio is the scoreboard, but forecasting is how you put points on it.

Frequently asked questions

What to read next