AI Inventory Forecasting in a Spreadsheet: Prompts and Safety Stock Math for D2C

You can forecast demand for a small store with a four-week moving average, set a safety buffer from how jumpy your sales are, and get a reorder point you can type into a spreadsheet. AI is useful around that maths, not instead of it: it writes the formulas, explains what each number means, and flags SKUs where the method is a bad fit. This post gives you the formulas, a short script that I ran with real output, and prompts for the review step.

Status: the sales numbers are made up for the demo (12 weeks for two imaginary SKUs). The script output is real. The prompts are untested templates, and the spreadsheet formulas are unrun in Google Sheets or Excel; I checked them against the script's results by hand only.

What does an inventory forecast actually need to answer?

Three questions, per SKU:

1. How many units will I sell per week, roughly?
2. How long between placing an order with the supplier and having stock I can ship? That is the lead time.
3. How much extra do I hold in case demand or the supplier runs worse than average? That is safety stock.

The reorder point ties them together:

reorder point = (forecast demand per week x lead time in weeks) + safety stock
safety stock  = z x (std dev of weekly demand) x sqrt(lead time in weeks)

When your on-hand plus on-order stock drops to the reorder point, you place the order. The z value comes from the service level you choose. For 95 percent, z is about 1.645.

The square root formula assumes weekly demand varies independently from week to week and the lead time is fixed. Both are simplifications. They are good enough to beat guessing, and not good enough to trust blindly, which is why the later sections cover where it breaks.

How do I prepare the sales data?

Export orders from your store admin and summarise units sold per SKU per week. Menu names change, so look for the orders or analytics export in your own admin rather than trusting a path I give you. Then clean four things before any maths:

• Stockout weeks. If a SKU was out of stock for three days, sales that week understate demand. Mark those weeks and either exclude them or note the shortfall. Forecasting from stockout weeks makes you under-order, which causes more stockouts.
• Promotion weeks. A sale week inflates the average. Tag it so you can exclude it or forecast it separately. The festive season planner covers planning for known peaks.
• Returns and cancellations. Decide whether demand means orders placed or units kept. Pick one and be consistent. For COD-heavy stores the gap can be large, see COD versus prepaid messaging.
• Variants. Forecast at the SKU level (size and colour), then roll up. A kurta that sells 25 units a week may sell 2 in XXL.

Save the result as a CSV with three columns: sku, week, units, oldest week first.

What does the calculation look like in a script?

This is the script I used. It reads the CSV, computes a four-week simple moving average forecast, the standard deviation of weekly demand, safety stock and reorder point, and backtests the moving average against the naive "same as last week" forecast.

import csv, math, statistics, sys
from statistics import NormalDist

rows = list(csv.DictReader(open(sys.argv[1])))
LEAD_WEEKS = float(sys.argv[2])   # supplier lead time in weeks
SERVICE = float(sys.argv[3])      # e.g. 0.95
WINDOW = 4
z = NormalDist().inv_cdf(SERVICE)

by_sku = {}
for r in rows:
    by_sku.setdefault(r["sku"], []).append(float(r["units"]))

print(f"z for {SERVICE:.0%} service level = {z:.3f}, lead time = {LEAD_WEEKS:g} weeks")
for sku, u in by_sku.items():
    sma_err, naive_err = [], []
    for t in range(WINDOW, len(u)):
        sma = sum(u[t-WINDOW:t]) / WINDOW
        sma_err.append(abs(u[t] - sma))
        naive_err.append(abs(u[t] - u[t-1]))
    mean_d = sum(u[-WINDOW:]) / WINDOW
    sigma = statistics.stdev(u)
    ss = z * sigma * math.sqrt(LEAD_WEEKS)
    rop = mean_d * LEAD_WEEKS + ss
    print(f"\n{sku}")
    print(f"  last {WINDOW} weeks: {u[-WINDOW:]}")
    print(f"  SMA{WINDOW} forecast/week: {mean_d:.1f}, std dev/week: {sigma:.1f}")
    print(f"  backtest MAE: SMA{WINDOW} {sum(sma_err)/len(sma_err):.2f} vs last-week {sum(naive_err)/len(naive_err):.2f}")
    print(f"  safety stock: {ss:.0f}, reorder point: {rop:.0f} units")

I ran it on 12 invented weeks for two SKUs, a steady kurta and a lumpy candle, with a 3-week lead time and a 95 percent service level. The invented weekly units, oldest first, so you can reproduce it:

KUR-IND-M:   22 25 19 24 28 21 26 23 27 24 29 25
CAN-SOY-200:  5  0 14  3  0 18  2  6  0 15  4  7

Real output on 2026-10-09 (I re-ran it on these numbers during review and got the same figures):

z for 95% service level = 1.645, lead time = 3 weeks

KUR-IND-M
  last 4 weeks: [27.0, 24.0, 29.0, 25.0]
  SMA4 forecast/week: 26.2, std dev/week: 2.9
  backtest MAE: SMA4 2.59 vs last-week 4.38
  safety stock: 8, reorder point: 87 units

CAN-SOY-200
  last 4 weeks: [0.0, 15.0, 4.0, 7.0]
  SMA4 forecast/week: 6.5, std dev/week: 6.2
  backtest MAE: SMA4 5.47 vs last-week 9.50
  safety stock: 18, reorder point: 37 units

Check one by hand. For the kurta: 26.25 x 3 = 78.75 units of expected demand during the lead time, plus 1.645 x 2.9 x 1.732 = about 8.3 of safety stock, so about 87. The output matches.

Two readings of this output matter more than the numbers themselves:

• The moving average beat "same as last week" in the backtest on both SKUs, because averaging smooths week-to-week noise. That is a property of this invented data, not a general law. Run the backtest on your own history before trusting any method.
• The candle's standard deviation (6.2) is almost as large as its forecast (6.5). Weekly sales swing between 0 and 18. The formula returns a number, but the normal-distribution assumption behind it is weak for a SKU like this. Treat its reorder point of 37 as a starting point, not an answer.

How do I do the same in Google Sheets or Excel?

If you prefer a spreadsheet, put weeks down column B for one SKU (B2:B13) and use these. They are unrun templates, so check each against the script's output on the same data before relying on them.

STDEV.S is the sample standard deviation, which is what the script's statistics.stdev computes. If you copy a formula from a model that uses STDEV.P, the answer will differ slightly. That is a harmless difference to know about, and a good reason to check the output against a known case.

For when to stop using a spreadsheet and move to a script, see spreadsheets vs Python with AI.

What should I ask the AI to do?

Use the model for three jobs: build the sheet, review the output, and explain it to whoever approves the purchase order. Do not paste 52 rows and ask "what will I sell next month".

Prompt 1: build the sheet (untested template).

I run a small online store. I have a CSV with columns sku, week, units
(oldest first, one row per SKU per week). Write Google Sheets formulas
for one SKU's weekly units in B2:B13 that compute:
- a 4-week average of the most recent 4 weeks
- the sample standard deviation of all weeks
- safety stock = z * std dev * sqrt(lead time in weeks), where z comes
  from NORM.S.INV of a service level I type in
- reorder point = forecast * lead time + safety stock
Put inputs (lead time, service level) in labelled cells. Show each formula
and explain it in one line. Do not invent any sales numbers.

Prompt 2: review my result (untested template).

Here are my inputs and outputs for one SKU: [paste the 12 weekly numbers,
lead time, service level, and the forecast, std dev, safety stock, reorder
point my sheet produced]. Do not recompute from scratch. Instead:
1. Say whether any of the outputs is implausible given the inputs.
2. Tell me if the standard deviation is so large relative to the average
   that the normal-distribution assumption looks weak.
3. List which weeks look like outliers (possible stockouts or promotions)
   and what I should check before trusting the forecast.
Be concise. If you are unsure about a number, say so.

Prompt 3: write the purchase order note (untested template). Paste the reorder point, current on-hand stock, units on order, the supplier's minimum order quantity (MOQ) and ask for a three-line note to whoever approves purchases: what to order, why now, what the risk is. Make the model state the assumptions it was given. This is where it saves time, because the explanation is tedious and the numbers are already computed.

When does this method fail?

Name the failure modes in the sheet so you don't discover them in a stockout.

• Seasonal or festive spikes. A moving average over the last four weeks will lag a rising season and overshoot a falling one. With less than a year of history you cannot estimate seasonality, so plan peaks by hand with the supplier.
• Lead time that varies. The formula assumes a fixed lead time. If your supplier sometimes takes 3 weeks and sometimes 6, use the longer realistic figure or track actual lead times and add their variation to the calculation. I have not shown that extension here.
• Minimum order quantities and batch sizes. The reorder point tells you when. The MOQ and your cash tell you how much. Ordering a 200-unit minimum for a SKU that sells 6 a week locks up cash for most of a year.
• New products. No history means no forecast. Use a comparable product's early weeks as a loose guide and say that is what you did.
• Stockout-censored history. If your data shows zero sales because you were out of stock, the model learns that demand is low. Fix the data first.
• Cash. A statistically correct reorder point can still be unaffordable. Rank SKUs by margin and velocity and fund the ones that earn first.

A weekly routine that stays small

1. Update the sales CSV with last week's units.
2. Re-run the script or sheet for each active SKU.
3. Filter to SKUs where on-hand plus on-order is at or below the reorder point.
4. For each, check the failure list above, then place the order.
5. Once a month, check the backtest column. If the moving average stops beating "same as last week", something about your demand changed. Investigate before ordering.

If your store pulls from several channels, the AI agents for ecommerce automation post describes where this kind of check could be automated, and the Claude with Google Sheets automation guide covers wiring a model into a sheet. Keep a human sign-off on any purchase order. A forecast tells you what the numbers imply, and it cannot know about a supplier's factory closure or an influencer post next week.