Inventory forecasting: methods, formulas & examples
Inventory forecasting is the practice of predicting future demand with a method you can actually defend — not a black box, not a vibe. This guide walks through the forecasting methods that work for small businesses, with worked examples and the math you need. No PhD required.
What is inventory forecasting?
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. Get the forecast roughly right and the rest of your planning has a fighting chance; get it wrong and no reorder policy can save you.
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 a model nobody understands produces numbers nobody trusts.
You want a method that is transparent enough to explain why next week’s forecast is 47 and not 60, cheap enough to run weekly without dedicating a person to it, and accurate enough to feed your inventory planning cycle. Five methods clear that bar.
Inventory forecasting vs. demand forecasting
The two terms get used interchangeably, but the distinction is worth keeping. Demand forecasting predicts raw customer demand — how many units the market wants, independent of whether you can supply them. It’s a pure market signal built from sales history, seasonality, and promotions.
Inventory forecasting takes that demand signal and translates it into what you should actually hold, factoring in what you already have on hand, open purchase orders, lead times, and the buffer you carry. Two businesses with the same demand forecast can have very different inventory forecasts if one has a warehouse full of stock and a fast supplier while the other is empty and waiting eight weeks.
In a small business the same person often does both in one sitting, so the line blurs — but the mental separation matters. Forecast demand first as an honest read of the market, then adjust for your own supply position. Mixing the two is how you end up over-ordering because a strong demand signal made you forget the pallet already in the back.
The 5 forecasting methods that work for SMBs
In rough order of complexity. First, the naive forecast: next period equals this period ("we sold 50 last week, so we’ll sell 50 next week"). It’s useful as a baseline — if a fancy model can’t beat naive, the fancy model is broken — and it’s fine for very stable SKUs. Second, the simple moving average (SMA): the average of the last N periods, which smooths out noise and suits stable SKUs without strong trend or seasonality.
Third, the weighted moving average (WMA): the same as SMA but recent periods count more, useful when demand is shifting and you want the forecast to react faster. Fourth, exponential smoothing (ES): a weighted average where the weights decay exponentially — smoother than WMA, more responsive than SMA, controlled by a single parameter (α). Most small-business forecasting tools use some flavor of ES under the hood.
Fifth, seasonal decomposition: split demand into level, trend, and a seasonal index, then apply the index back when you forecast future periods. It’s required if you have clear seasonality — holiday retail, summer beverages, school supplies. In practice you combine methods: exponential smoothing for the base signal with a seasonal index layered on top. That’s roughly what Holt-Winters does, and Holt-Winters is the workhorse of small-business forecasting.
Moving average, walked through with numbers
Let’s forecast next month’s demand for one SKU with a 3-month moving average. Six months of sales: Nov 120, Dec 145, Jan 110, Feb 130, Mar 125, Apr 140. The May forecast is just the average of the last three months: (Feb + Mar + Apr) / 3 = (130 + 125 + 140) / 3 = 131.7, which you’d round to 132 units. That’s the entire method — no parameters, no software.
Now check how it would have performed. The forecast for April, using Jan/Feb/Mar, is (110 + 130 + 125) / 3 = 121.7. Actual April was 140, so the error is 18.3 units — about 13% MAPE (mean absolute percentage error). That error number is not a footnote: it tells you how much safety stock you need to cover forecast misses. A bigger forecast error means a bigger buffer, which is exactly how forecasting and reorder-point work are joined at the hip.
When does SMA break? When demand is trending up, the moving average lags — by the time it catches up, you’ve been stocking out for a month. The fix is to weight recent periods more (a weighted moving average giving April 0.5, March 0.3, February 0.2) or move to exponential smoothing. Same data, sharper response.
Exponential smoothing, walked through
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 is: Forecast(next) = α × Actual(now) + (1 − α) × Forecast(now), where α (alpha) is between 0 and 1. Higher α (say 0.5) snaps the forecast to recent changes; lower α (say 0.1) smooths heavily and ignores short-term noise. For most SMB SKUs, α between 0.2 and 0.4 is a sensible start.
Run the same six-month data through it with α = 0.3, seeding from the first actual (120). December forecasts 120; January forecasts 0.3 × 145 + 0.7 × 120 = 127.5; February 0.3 × 110 + 0.7 × 127.5 = 122.3; March 0.3 × 130 + 0.7 × 122.3 = 124.6; April 0.3 × 125 + 0.7 × 124.6 = 124.7; and May 0.3 × 140 + 0.7 × 124.7 = 129.3. So exponential smoothing calls May ~129 — a touch lower than the moving average’s 132, because it carries more memory of the slower January and February numbers. Neither is "right"; both are defensible on the same data, which is exactly why seeing them side by side is useful.
How do you pick α? In a spreadsheet, try 0.1, 0.2, 0.3, 0.4, 0.5, compute the mean absolute error against your actuals for each, and keep the α with the lowest error. That’s parameter tuning, and once you have the template it takes about ten minutes per SKU.
Handling seasonality and promotions
Seasonality is what kills naive forecasting — sell ice cream and June isn’t January; sell tax software and March isn’t June. You model it two ways. Additive: the seasonal effect adds a fixed number of units (demand = level + trend + seasonal offset), used when the swing is roughly the same size regardless of volume. Multiplicative: the seasonal effect is a percentage (demand = (level + trend) × seasonal index), used when the swing scales with volume — December is 1.5× a normal month whether that month is 100 or 1,000 units. Most retail and consumer goods need the multiplicative version.
To build seasonal indices in a spreadsheet: get 24+ months of sales by month; compute the 12-month moving average centered on each month (your deseasonalized baseline); divide each actual by its baseline for the raw seasonal ratio; average those ratios across years for each calendar month; then normalize so the 12 indices average to 1.0. Apply it by forecasting the level with exponential smoothing on deseasonalized data, then multiplying by that month’s index.
Promotions get treated as a separate adjustment, not baked into the base forecast. If a 20%-off promo historically lifts unit sales by 1.8×, add that multiplier on top of the base forecast for the promo week only, and keep the lift assumption documented — promo lifts decay over time, so you’ll want to re-measure rather than trust a number from two years ago.
Forecasting in a spreadsheet vs. software
You can run all of the above in a spreadsheet. Excel’s FORECAST.ETS function 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 genuinely fine — and you almost certainly do not need AI or machine learning below roughly 1,000 SKUs. Exponential smoothing plus a seasonal index handles the overwhelming majority of small-business demand patterns; ML only starts to pay off with thousands of SKUs and rich external signals like weather and foot traffic.
The pain shows up at scale, in three failure modes. Refresh fatigue: pulling fresh sales data, deduplicating it, and refreshing 200 forecast rows takes time, and a skipped week goes stale. No connection to action: a spreadsheet forecast doesn’t trigger a PO — someone reads it and retypes numbers into another system, and that handoff is where errors creep in. No history of changes: you can’t tell which past forecasts were good or bad, because the sheet only shows the current version.
Inventory software closes all three gaps — the forecast lives next to the stock record, reorder points update as forecasts shift, and history is logged so you can ask "did March’s forecast for SKU 12 match actual?" and get an answer in a click. And a good forecast is the single biggest lever on inventory turnover: better forecast → less buffer needed → leaner inventory → higher turns. Turnover is the scoreboard; forecasting is how you put points on it.
Inventory forecasting in StockZip
Be clear about what StockZip does and doesn’t do here: it does not compute forecasts for you. Automated, model-driven forecasting is a roadmap item, not a shipped feature — so anyone promising you an AI forecast today would be overselling. What StockZip does is remove the two things that make spreadsheet forecasting go stale: dirty sales data and a manual export step.
Barcode scanning keeps every quantity current as stock moves, so the sales and movement history you forecast from is accurate rather than guessed. Movement and inventory-valuation reports then give you the per-SKU units-sold history — the raw series the moving-average and exponential-smoothing methods above need — without hand-building it. Reporting is a paid feature (Starter and up); the Free plan does not include reports, so on Free you’d export a CSV and run the forecast in a spreadsheet.
Once you have a forecast, the planning side connects back: per-item minimum levels and low-stock alerts (both on the Free plan) turn your forecast-plus-safety-stock number into a working reorder trigger. The honest summary: StockZip supplies the clean history a forecast depends on and the alerts a forecast feeds, while the forecasting method — moving average, smoothing, seasonal — stays in your hands or your spreadsheet.