Why forecast at all
Every inventory decision downstream — how much safety stock to hold, when to hit your reorder point, how big a purchase order to place — depends on an assumption about future demand. If that assumption is just "about what we sold last month," you're implicitly forecasting anyway, just without checking whether last month was representative. A basic forecasting method takes 20-30 minutes to build in a spreadsheet and meaningfully reduces both stockout risk and excess-inventory risk versus flying blind.
You don't need machine learning or specialized software to do this well at small-to-mid volume. Three spreadsheet-friendly methods cover most sellers: simple moving average, weighted moving average, and seasonal-index adjustment. Each builds on the last.
Method 1: Simple moving average
Average your most recent N periods (months, in most examples below) and use that average as your forecast for the next period. It smooths out random week-to-week noise.
Worked example. Monthly unit sales for a product over six months: Jan 80, Feb 95, Mar 110, Apr 90, May 130, Jun 140.
A 3-month moving average forecast for July uses the average of Apr, May, Jun:
(90 + 130 + 140) ÷ 3 = 120 units forecast for July.
Simple moving average treats all three months equally. That's its main weakness: if demand is trending upward (as in this example — May and June were both stronger than April), an equal-weighted average understates where you're actually headed, because it lets an older, weaker month drag the number down.
Method 2: Weighted moving average
Assign more weight to the most recent months, since they're usually the better predictor of what's coming next. A common weighting scheme gives the most recent month roughly half the weight, tapering from there.
Worked example, using weights of 3 (most recent), 2, and 1 (oldest) — i.e., June counts 3x, May counts 2x, April counts 1x, divided by the sum of weights (6):
(140 × 3 + 130 × 2 + 90 × 1) ÷ 6 = (420 + 260 + 90) ÷ 6 = 770 ÷ 6 = ≈128 units forecast for July.
That's about 8 units higher than the simple average — a small difference here, but the gap widens the sharper your trend is. For a SKU on a steep growth curve, weighted moving average tracks reality noticeably better.
Method 3: Seasonal index adjustment
Moving averages assume the recent past predicts the near future — reasonable for a stable or steadily trending product, but wrong for anything with a seasonal pattern (holiday gift items, summer outdoor gear, back-to-school). For those, layer a seasonal index on top of a trend forecast.
Building the index: for each month, divide that month's sales by the year's average monthly sales. If your average monthly sales across the year were 100 units and December sold 180, December's seasonal index is 180 ÷ 100 = 1.8. A February that sold 60 units gets an index of 0.6.
Applying it: take your trend-based forecast (from a moving average, ignoring seasonality) and multiply by the seasonal index for the month you're forecasting. If your underlying trend forecast is 120 units and you're forecasting for a December with a 1.8 index:
120 × 1.8 = 216 units forecast for December.
Recalculate your seasonal indices at least once a year as you accumulate more history — a single year's index is noisy (it might reflect a one-off promotion or stockout, not a true seasonal pattern), so an index built from two or three years of data is meaningfully more reliable.
Combining the two adjustments
For a seasonal product with an underlying growth trend, do both steps: compute a weighted moving average to get your trend-adjusted baseline, then multiply by the relevant month's seasonal index. This two-step approach is still entirely spreadsheet-doable and covers the majority of real seller forecasting needs.
How far ahead to forecast, and how often to refresh
Forecast at least as far ahead as your lead time plus review cycle — if it takes your supplier 30 days to deliver and you review inventory monthly, you need a forecast covering at least the next 60 days, not just next week. Refresh the forecast every time you get a new month of actual sales data; don't let a forecast built two quarters ago keep driving today's reorder decisions.
Measuring how good your forecast actually is
Track forecast error: (actual sales − forecasted sales) for each period. If you're consistently forecasting low, your safety stock is quietly absorbing an amount of demand your formula should be capturing directly — worth tightening the forecast rather than just growing the buffer indefinitely. If you're consistently forecasting high, you're tying up cash in inventory that arrives before it's needed.
When to move beyond spreadsheet forecasting
Spreadsheet methods work well up to a few dozen SKUs with reasonably stable patterns. Once you're managing hundreds of SKUs, launching new products with no sales history to average, or running promotions that regularly distort the recent-history baseline, dedicated forecasting functionality inside an inventory management platform becomes worth the cost — it automates what you're doing manually and can factor in more variables (promotional calendars, cross-SKU cannibalization) than a spreadsheet realistically can.