Time-series forecasting

How to forecast inventory demand and set reorder points

Know when to reorder each product, so you stop running out of the items that take weeks to arrive, and stop tying up money in the ones that arrive in days.

  • 10 steps
  • 7 min read
  • Runs in Google Colab

What you’ll build

  • A forecast of how much each item will sell while you wait for a delivery
  • Safety stock for each item, sized from how wrong past forecasts were
  • A day-by-day replay on past sales that shows how often each policy runs out and how much stock it holds

Tools

Python, pandas, NumPy

Step 1: Decide what “good” means

A reorder point is one number for each item: when the stock on hand plus the stock already ordered falls to it, you order more. Set it too low and the shelf is empty before the delivery arrives; set it too high and money sits in the stockroom. So judge every policy on two numbers:

  • Fill rate: the share of what customers wanted that you could sell from stock. Here the target is at least 98%.
  • Stock held: the average value of what’s on the shelves. Lower is better, as long as the fill rate holds.

Also collect each item’s lead time: the days from placing an order to having it on the shelf. Rules of thumb usually ignore it, and it matters most.

Step 2: Get daily sales, and the days you ran out

You need one row per item per day: the units sold, and whether the item was in stock that day. Most shop and warehouse systems can export both. Add a table of items with their lead times and unit costs.

No data to hand? This script makes two years of sample sales for 30 items, with lead times from 3 to 21 days. Each item has its own typical sales, busier Fridays and Saturdays, a season and some growth or decline, plus the occasional customer who buys a lot at once. Now and then the shop ran out for a few days and sold nothing.

CodeOptional: make sample sales.csv and items.csv
pythonRun in Colab
# pip install pandas numpy
import numpy as np
import pandas as pd

rng = np.random.default_rng(3)
days = pd.date_range("2024-01-01", "2025-12-31")
n = 30
items = pd.DataFrame({
    "item": [f"SKU-{i:02d}" for i in range(1, n + 1)],
    "lead_time_days": rng.choice([3, 5, 7, 10, 14, 21], n),    # days from placing an order to having it on the shelf
    "unit_cost": rng.uniform(2, 60, n).round(2),
})
typical = rng.lognormal(1.4, 1.0, n)                         # units a day: from under 1 to about 40
WEEKDAY = np.array([0.85, 0.9, 0.95, 1.0, 1.15, 1.35, 0.8])  # Monday to Sunday
t = np.arange(len(days)) / 365
season = 1 + rng.uniform(0, 0.3, n) * np.sin(2 * np.pi * (t[:, None] - rng.uniform(0, 1, n)))
growth = 1 + rng.normal(0, 0.15, n) * t[:, None]
mean = typical * WEEKDAY[days.day_of_week][:, None] * season * growth
demand = rng.poisson(mean * rng.gamma(5, 1 / 5, mean.shape))           # day-to-day noise on top of the pattern
bulk = rng.random(mean.shape) < 0.01                                     # now and then, one customer buys a lot
demand = demand + bulk * rng.poisson(mean * 4)

# The shop ran out now and then: on those days it sold nothing, whatever customers wanted
empty = np.zeros(mean.shape, bool)
for j in range(n):
    for start in rng.choice(len(days), 6, replace=False):
        empty[start:start + rng.integers(1, 5), j] = True
sales = pd.DataFrame({
    "date": np.repeat(days, n), "item": np.tile(items.item, len(days)),
    "units_sold": np.where(empty, 0, demand).ravel(), "in_stock": ~empty.ravel()})
sales.to_csv("sales.csv", index=False)
items.to_csv("items.csv", index=False)
print(f"{len(sales):,} rows: {n} items, {len(days)} days")
What you’ll see
21,930 rows: 30 items, 731 days
CodeLoad the sales and mark the days you ran out
pythonRun in Colab
import numpy as np
import pandas as pd
pd.set_option("display.width", 120, "display.max_columns", 20)

sales = pd.read_csv("sales.csv", parse_dates=["date"])
items = pd.read_csv("items.csv").set_index("item")
# A day the shelf was empty says nothing about demand: mark it unknown, not 0
sales.loc[~sales.in_stock, "units_sold"] = np.nan
demand = sales.pivot(index="date", columns="item", values="units_sold")   # one row per day, one column per item
print(f"{demand.isna().mean().mean():.1%} of item-days unknown (out of stock)")
print(demand.mean().describe().round(1))   # average units a day, across items
What you’ll see
2.0% of item-days unknown (out of stock)
count    30.0
mean      5.6
std       4.9
min       0.4
25%       1.7
50%       4.7
75%       7.4
max      20.5
dtype: float64

A day the shelf was empty says nothing about how much customers wanted, so it’s marked unknown, not 0. Count those days as 0 and every forecast comes out low: an item that runs out often looks less popular than it is, so it runs out even more. In the sample data, 2.0% of item-days are unknown, and the items sell between 0.4 and 20.5 units a day on average.

Step 3: Forecast demand over the lead time

Keep the forecast simple and easy to explain. An item’s level is its average daily sales over the last 8 weeks, and each weekday gets its share of a typical day, learned from 2024 across all items. The forecast for a lead time adds up the level times each weekday’s share, for every day until a delivery would arrive.

CodeA forecast for the lead time
pythonRun in Colab
learn_end, choose_end = pd.Timestamp("2024-12-31"), pd.Timestamp("2025-06-30")   # learn, choose, then test on Jul–Dec 2025

# Each weekday's share of a typical day, from the first year (pooled across items: steadier than item by item)
history = demand[:learn_end]
weekday = (history / history.mean()).groupby(history.index.day_of_week).mean().mean(axis=1)
print(weekday.round(2))

# The item's level: its average over the last 8 weeks, known at the end of each day (unknown days skipped)
level = (demand / weekday.reindex(demand.index.day_of_week).to_numpy()[:, None]).rolling(56, min_periods=28).mean()

def lead_time_forecast(item, day):
    """Units expected to sell from the day after `day` until an order placed on `day` arrives."""
    ahead = pd.date_range(day + pd.Timedelta(days=1), periods=items.lead_time_days[item])
    return level.at[day, item] * weekday.reindex(ahead.day_of_week).sum()
print(f"SKU-01 over its {items.lead_time_days['SKU-01']} days of lead time, from June 30, 2025: {lead_time_forecast('SKU-01', choose_end):.0f} units")
What you’ll see
date
0    0.87
1    0.91
2    0.90
3    1.02
4    1.14
5    1.32
6    0.83
dtype: float64
SKU-01 over its 14 days of lead time, from June 30, 2025: 75 units

Saturdays sell 1.32 times a typical day and Sundays 0.83 times. SKU-01 has a 14-day lead time, so on June 30, 2025 its forecast is 75 units for the two weeks an order would take.

The data is split three ways by date: 2024 to learn the weekday shares, January to June 2025 to choose the safety stock, and July to December 2025 to test the policy, once. This forecast is simpler than the one in the sales forecast guide, and that’s fine: a reorder point needs a fair forecast plus an honest idea of how wrong it can be, which is the next step.

Step 4: Size safety stock from past forecast errors

Safety stock covers the times demand beats the forecast. Textbook formulas assume forecast errors follow a bell curve; instead, measure them. For every day from January to June 2025, compare what the forecast said the lead time would sell with what actually sold, then take the error that was beaten only 5 times in 100. That also works for slow sellers and bulk buyers, whose errors don’t follow a bell curve.

CodeSafety stock from the 95th percentile of the errors
pythonRun in Colab
def errors(item, start, end):
    """How far the lead-time forecast was off, for each day an order could have been placed between start and end."""
    L = items.lead_time_days[item]
    actual = demand[item].rolling(L, min_periods=L).sum().shift(-L)   # what sold in the L days after each day
    days = [d for d in pd.date_range(start, end - pd.Timedelta(days=L)) if not np.isnan(actual[d])]
    return np.array([actual[d] - lead_time_forecast(item, d) for d in days])

def safety_stock(share):
    """For each item, enough extra stock to cover the forecast error `share` of the time (chosen on Jan to Jun 2025)."""
    return pd.Series({item: max(0.0, np.quantile(errors(item, learn_end, choose_end), share)) for item in items.index})

safety = safety_stock(0.95)
print(pd.DataFrame({"lead_time_days": items.lead_time_days, "average_a_day": demand.mean().round(1),
                    "safety_stock": safety.round(0)}).head(8))
What you’ll see
        lead_time_days  average_a_day  safety_stock
SKU-01              14            5.5          28.0
SKU-02               3            4.6          10.0
SKU-03               5           16.3          30.0
SKU-04               5            8.4          19.0
SKU-05               5            5.6          13.0
SKU-06              14           11.4         108.0
SKU-07              21            3.0          24.0
SKU-08              10            1.8          12.0

Long lead times need much more safety stock. SKU-06 sells about 11 units a day, takes 14 days to arrive and gets 108 units of safety stock, while SKU-03 sells 16 a day, takes 5 days and gets 30. A forecast over many days can be further off, and there’s more time for a surprise.

Step 5: Set up a rule of thumb to beat

Many shops reorder when stock falls to two weeks of average sales, whatever the item. That’s the baseline. It ignores lead time: two weeks is more than enough for an item that arrives in 3 days, and not enough for one that takes 21. The forecast policy reorders at the lead-time forecast plus the safety stock.

CodeThe two policies
pythonRun in Colab
rule = lambda item, day: 14 * level.at[day, item]                          # "reorder at two weeks of sales"
forecast = lambda item, day: lead_time_forecast(item, day) + safety[item]  # lead-time demand plus safety stock

Step 6: Replay both policies on past sales, day by day

The honest way to compare reorder policies is to replay them. This starts each item with a full shelf on July 1, 2025, then goes day by day to December 31: sell what customers wanted, as far as the stock allows, and when the stock on hand plus on order falls to the reorder point, order two weeks of sales, which arrives after the lead time. Both policies order the same amount; only when they order differs. Days with unknown demand use the forecast.

CodeReplay both policies on July to December 2025
pythonRun in Colab
def simulate(item, reorder_point, start="2025-07-01", end="2025-12-31"):
    """Replay a reorder policy on one item, day by day: sell what's on the shelf, and order when stock runs low."""
    L = items.lead_time_days[item]
    on_hand, arriving, served, wanted, log = None, {}, 0, 0, []   # arriving: {day: units}
    for day in pd.date_range(start, end):
        yesterday = day - pd.Timedelta(days=1)
        order_size = np.ceil(14 * level.at[yesterday, item])     # both policies order two weeks of sales
        if on_hand is None:
            on_hand = reorder_point(item, yesterday) + order_size  # start with a full shelf
        on_hand += arriving.pop(day, 0)
        want = demand.at[day, item]
        if np.isnan(want):                                         # unknown demand: use the forecast
            want = level.at[yesterday, item] * weekday[day.day_of_week]
        sold = min(on_hand, want)
        on_hand, served, wanted = on_hand - sold, served + sold, wanted + want
        if on_hand + sum(arriving.values()) <= reorder_point(item, day):
            arrives = day + pd.Timedelta(days=L)
            arriving[arrives] = arriving.get(arrives, 0) + order_size
        log.append((day, on_hand, sold < want))
    return served / wanted, pd.DataFrame(log, columns=["date", "on_hand", "ran_out"]).set_index("date")

def replay(policy):
    """Replay a policy on every item: its fill rate, share of days out of stock and average stock value."""
    rows = {}
    for item in items.index:
        fill, log = simulate(item, policy)
        rows[item] = {"fill_rate": fill, "days_out": log.ran_out.mean(), "stock_value": log.on_hand.mean() * items.unit_cost[item]}
    return pd.DataFrame(rows).T

results = {"Two weeks of sales": replay(rule), "Forecast + safety stock": replay(forecast)}
print(pd.DataFrame({name: {"fill_rate": r.fill_rate.mean(), "days_out": r.days_out.mean(), "stock_value": r.stock_value.sum()}
                    for name, r in results.items()}).T.round(3))
What you’ll see
                         fill_rate  days_out  stock_value
Two weeks of sales           0.966     0.029    90017.219
Forecast + safety stock      0.994     0.005    71560.133

The forecast policy sells 99.4% of what customers wanted, against 96.6% for the rule, and runs out on 0.5% of item-days instead of 2.9%. It also holds less: about $71,600 of stock on the shelves on average, against $90,000. It holds more where it’s needed and less where it isn’t.

Step 7: See where the rule goes wrong

CodeFill rate and stock held, by lead time
pythonRun in Colab
for name, r in results.items():
    print(name)
    print(r.join(items.lead_time_days).groupby("lead_time_days").agg(
        items=("fill_rate", "size"), fill_rate=("fill_rate", "mean"), stock_value=("stock_value", "sum")).round(3))
What you’ll see
Two weeks of sales
                items  fill_rate  stock_value
lead_time_days                               
3                   6      1.000    19341.882
5                   6      1.000    45746.265
7                   6      0.978     3242.301
10                  5      0.984    13556.971
14                  5      0.930     4771.115
21                  2      0.778     3358.685
Forecast + safety stock
                items  fill_rate  stock_value
lead_time_days                               
3                   6      1.000    10233.302
5                   6      1.000    28523.904
7                   6      0.983     2595.144
10                  5      0.992    14216.562
14                  5      0.990     7845.751
21                  2      1.000     8145.471
SKU-07: stock on the shelf, July to December 2025SKU-07’s stock on the shelf each day from Jul 1 to Dec 31, with a 21-day lead time. Two weeks of sales: runs out on 37 days and holds 21 units on average. Forecast + safety stock: never runs out and holds 56 units on average.075150Two weeks of salesForecast + safety stockJul 1Aug 1Sep 1Oct 1Nov 1Dec 1
SKU-07 sells about 3 units a day and takes 21 days to arrive. The gray line is the two-weeks rule, which runs out on 37 days (the amber marks); the teal line is the forecast with safety stock, which never runs out.

With lead times of 3 or 5 days, both policies sell everything, but the rule holds about 70% more stock than it needs. At 14 days the rule’s fill rate falls to 93.0%, and at 21 days to 77.8%: it reorders when two weeks of stock is left, and the delivery takes three. The forecast policy stays at 98.3% or above for every lead time.

Step 8: Choose how much safety you want

The 95 in 100 in Step 4 is a business choice, not a rule. Try 90 in 100 instead:

CodeLess safety stock
pythonRun in Colab
lower = safety_stock(0.90)
r = replay(lambda item, day: lead_time_forecast(item, day) + lower[item])
print(f"At 0.90: fill rate {r.fill_rate.mean():.1%}, out of stock on {r.days_out.mean():.1%} of days, stock held ${r.stock_value.sum():,.0f}")
What you’ll see
At 0.90: fill rate 98.8%, out of stock on 0.9% of days, stock held $64,207

The fill rate falls to 98.8%, still above the target, and the stock held falls to about $64,200, 10% less again. Higher service always costs more stock, and the cost climbs fast near 100%. You can choose the level item by item: high for products customers can’t do without, lower for the ones they’ll wait for. Choose it on January to June, then check it once on the test months, as above.

Step 9: Run it every day

Once a day, after the day’s sales are in, update each item’s level, work out its reorder point, and list the items whose stock on hand plus on order is at or below it, with how much to order.

CodeToday’s reorder list
pythonRun in Colab
def reorder_list(on_hand, on_order, day):
    """Items to reorder today: those whose stock on hand plus on order is at or below the reorder point."""
    todo = pd.DataFrame({"position": on_hand + on_order,
                         "reorder_point": pd.Series({item: forecast(item, day) for item in items.index}).round(0),
                         "order": np.ceil(14 * level.loc[day])})
    return todo[todo.position <= todo.reorder_point]

# In real use, on_hand and on_order come from your stock system. Here: 10 days of sales on hand, nothing on order.
last = demand.index[-1]
print(reorder_list(on_hand=np.ceil(10 * level.loc[last]), on_order=0, day=last))
What you’ll see
        position  reorder_point  order
SKU-01      77.0          136.0  108.0
SKU-06      91.0          235.0  128.0
SKU-07      22.0           69.0   31.0
SKU-08      22.0           34.0   31.0
SKU-11      29.0           33.0   40.0
SKU-12      12.0           13.0   16.0
SKU-13      37.0           58.0   51.0
SKU-14       4.0            6.0    6.0
SKU-17       9.0           18.0   13.0
SKU-18      54.0          118.0   75.0
SKU-21       5.0            5.0    6.0
SKU-22      18.0           19.0   25.0
SKU-23      76.0          216.0  106.0
SKU-24     108.0          149.0  151.0
SKU-25       4.0            5.0    6.0
SKU-26      31.0           31.0   43.0
SKU-27      62.0          111.0   86.0
SKU-28      20.0           33.0   28.0
SKU-30      18.0           34.0   25.0
  • Recompute safety stock every month from the latest six months of errors, so it follows each item’s season and growth.
  • Keep lead times up to date. A supplier who goes from 7 days to 10 quietly breaks every reorder point that uses 7.
  • Plan promotions and new products by hand. The forecast only knows the past, so tell it when the future will be different.
  • Check the fill rate and stock held every month. If the fill rate slips below the target, the forecast errors have changed. That’s data drift, and recomputing the safety stock fixes most of it.

Common pitfalls

  • Counting days you ran out as zero demand, which makes every forecast too low.
  • Using the same number of days of stock for every item, whatever its lead time.
  • Sizing safety stock with a formula that assumes a bell curve, for items that sell a few units at a time or in bulk.
  • Judging a policy by forecast accuracy alone, instead of replaying it to see how often it runs out and how much it holds.
  • Choosing the safety level on the same months you test on, so the test looks better than real use.

Check what you learned

4 quick questions. Choose an answer to see whether it’s right, and why.

  1. 1. Why are days the item was out of stock marked as unknown instead of 0?

  2. 2. Why is safety stock sized from measured forecast errors rather than a textbook formula?

  3. 3. The “two weeks of sales” rule sells everything for items that arrive in 3 days, but only 77.8% for items that take 21. Why?

  4. 4. Lowering the safety level from 95 in 100 to 90 in 100 does what on the sample data?

Want this tailored to your data, team and budget? Create a plan for your project

More on Time-series forecasting · Next guide: How to build a spam filter, start to finish

Create my plan