You do not need a $99/month inventory app to forecast a small Shopify catalog. A well-structured spreadsheet with sales velocity, lead time, safety stock, and a rolling 12-week forecast covers most stores under 500 SKUs. This guide explains the columns to use and provides a downloadable template that opens in Google Sheets or Excel.
Grab the template below, then wire it to your Shopify Orders export or a simple sales report.
When a spreadsheet is enough
- Under about 500 active SKUs.
- Single warehouse or a small handful.
- Weekly, not real-time, replenishment cadence.
- You don't sell across 8 marketplaces with 8 different lead times.
The columns you actually need
- SKU and product name.
- On-hand stock (from Shopify Admin > Inventory).
- Rolling 28-day units sold.
- Daily sales velocity = 28-day units ÷ 28.
- Supplier lead time in days.
- Safety cover in days (typically 7 for stable products, 14+ for volatile).
- Reorder point (ROP) = velocity × (lead time + safety cover).
- Days of cover on hand = on-hand ÷ velocity.
- Reorder now? — TRUE if on-hand ≤ ROP.
- Suggested order quantity — enough to cover 12 weeks minus what's on the way.
Download the template
Use the button below to download the CSV to your computer. Import it into Google Sheets (File > Import > Upload) or open directly in Excel. The template includes formulas for velocity, ROP, and days of cover.
How to use it weekly
- Every Monday, export the last 28 days of orders from Shopify Admin.
- Update the 28-day units sold column.
- Confirm on-hand stock — physically count the top 10 SKUs monthly to catch shrinkage.
- Sort by 'Reorder now?' = TRUE and by days of cover ascending.
- Place supplier orders for anything at or below ROP.
- Adjust safety cover for SKUs with erratic sales.
Where a spreadsheet stops working
When you cross ~500 SKUs, use multiple warehouses, or need per-marketplace splits, upgrade to an inventory app. Signs it is time: manual updates take longer than 30 minutes weekly, or you keep having stockouts on top sellers.
Example calculation (USD)
Sample row
| SKU | On-hand | 28-day sold | Lead time | Safety | ROP | Reorder? |
|---|---|---|---|---|---|---|
| SW-CRW-CHR-L | 85 | 84 | 21 | 7 | 84 | TRUE |
| SW-CRW-NVY-M | 140 | 56 | 21 | 7 | 56 | FALSE |
For the first row, velocity is 3 units/day, ROP is 3 × (21 + 7) = 84. On-hand equals ROP → reorder now.
Common mistakes to avoid
- Using lifetime sales average instead of a rolling window.
- Ignoring supplier lead time variability.
- Setting the same safety cover across every SKU.
- Forgetting to subtract stock already on the way.
- Never doing physical counts.
Best practices
- Refresh the sheet weekly on the same day.
- Physical count top 10 SKUs monthly.
- Log stockouts and add days to safety cover for repeat offenders.
- Split by warehouse if you use more than one.
- Graduate to an app when you cross ~500 SKUs or 2+ warehouses.
Frequently asked questions
Do I need Excel or is Google Sheets fine?
Google Sheets is fine. The CSV imports cleanly and formulas transfer.
How does this connect to Shopify?
Manually — export orders and paste in weekly. For live sync you need an app or a small script using the Shopify Admin API.
How do I forecast a new product with no history?
Set velocity conservatively based on a similar SKU, then update after your first 30 days of real data.