A spreadsheet is a perfectly good way to start forecasting stock. It’s free, you can see every formula, and it forces you to think about lead times and buffers. This guide walks through our free template column by column, with the formulas, so you can build or adapt your own, and explains when a spreadsheet stops being enough.
Get the template by email → (Excel .xlsx and a Google Sheets copy)
What’s in the sheet
The template has four tabs:
- Products — one row per product with supplier, lead time parts, MOQ, case pack and service level.
- Sales — paste daily (or weekly) units sold per product, plus a column for whether you were in stock that day.
- Stock — current available stock and units on open purchase orders.
- Plan — the calculations: daily demand, safety stock, reorder point, stockout date, order-by date and suggested order quantity.
How to fill it in
Products tab
| Column | What to enter |
|---|---|
| SKU | Your master SKU (the product you buy) |
| Supplier | Supplier name |
| Manufacturing days | Supplier’s production time |
| Shipping days | Transit time for your usual mode |
| Customs days | Clearance time (0 if domestic) |
| Receiving days | Time to unload and put away |
| Lead time | =SUM(C2:F2) |
| MOQ | Minimum order quantity |
| Case pack | Units per carton |
| Service level | e.g. 95% |
| Target cover days | Days of stock you want after an order arrives, e.g. 60 |
Break lead time into parts. When you switch one shipment to air freight, you only change one cell.
Sales tab
One row per product per day: Date | SKU | Units | In stock (Y/N).
The In stock column is the most important one in the template. Days when you had no stock should be excluded from demand, otherwise your forecast drags down after every stockout and you run out again. The reorder point guide explains this in more detail.
If you sell on several channels, add every channel’s units for that product together, and multiply multipack sales by their pack size. A 2-pack sold on Amazon is 2 units.
Stock tab
SKU | Available | On order (sent/confirmed POs only)
Leave draft POs out, since you haven’t committed to them yet.
The formulas
All formulas below assume the Sales tab is in columns A–D and the Plan tab looks up by SKU in column A. Excel and Google Sheets both support these functions.
Daily demand (in-stock days only)
=SUMIFS(Sales!C:C, Sales!B:B, A2, Sales!D:D, "Y", Sales!A:A, ">="&TODAY()-60)
/ COUNTIFS(Sales!B:B, A2, Sales!D:D, "Y", Sales!A:A, ">="&TODAY()-60)
This is units sold over the last 60 days divided by the number of days you were in stock.
Standard deviation of daily demand
In Excel 365 or Google Sheets:
=STDEV.S(FILTER(Sales!C:C, Sales!B:B=A2, Sales!D:D="Y", Sales!A:A>=TODAY()-60))
Safety stock
=NORM.S.INV(ServiceLevel) * StdDev * SQRT(LeadTime)
See the safety stock guide for how this works and when to adjust it.
Reorder point
=DailyDemand * LeadTime + SafetyStock
Stockout date
=TODAY() + (Available / DailyDemand)
This simple version ignores incoming POs. The template’s version adds open POs to stock if they arrive before you would run out.
Order-by date
=StockoutDate - LeadTime - (SafetyStock / DailyDemand)
Status
=IF(OrderBy <= TODAY(), "ORDER NOW", IF(OrderBy <= TODAY()+14, "ORDER SOON", "OK"))
Suggested order quantity
=MAX(0, CEILING(MAX(MOQ, DailyDemand*(LeadTime+TargetCover) + SafetyStock - Available - OnOrder), CasePack))
The quantity is rounded up to the case pack and is never below the MOQ when an order is needed. Use CEILING.MATH in newer Excel if CEILING behaves differently.
Worked example
A ceramic mug:
- Last 60 days: 420 units sold, in stock for 56 of those days
- Standard deviation of daily demand: 3
- Lead time: 14 + 28 + 3 + 2 = 47 days
- Service level 95% (Z ≈ 1.645)
- Available: 520; on order: 0
- MOQ 300; case pack 36; target cover 60 days
| Calculation | Working | Result |
|---|---|---|
| Daily demand | 420 ÷ 56 | 7.5 |
| Safety stock | 1.645 × 3 × √47 ≈ 1.645 × 3 × 6.856 | 34 |
| Reorder point | 7.5 × 47 + 34 | 387 |
| Stockout date | 520 ÷ 7.5 | 69 days from today |
| Order-by date | 69 − 47 − (34 ÷ 7.5 ≈ 4.5) | day 17 |
| Status | 17 days away | OK (order soon in 3 days) |
| Order qty (when triggered at 387) | 7.5 × 107 + 34 − 387 = 449.5 → round up to 36s | 468 |
Note what dividing by 56 rather than 60 did: 7.5/day instead of 7/day. Over a 47-day lead time, that’s about 24 units of difference.
Making it better
Once the basics work, these tweaks help:
- Trend: compare the last 30 days with the 30 before. If demand is clearly rising, use a weighted average that leans on recent weeks.
- Seasonality: for seasonal products, use the same period last year multiplied by your growth rate, instead of the last 60 days.
- Spikes: cap any day above, say, 4× the median day unless you expect it to repeat, so one viral order doesn’t inflate everything.
- Multiple warehouses: copy the Plan tab per warehouse. For Amazon FBA, treat FBA as its own warehouse and add planned transfers to your main warehouse’s demand. See FBA replenishment.
The limits of spreadsheets
Spreadsheets are great until they aren’t. Signs you’ve outgrown yours:
- Pasting data takes longer than deciding. Exporting orders from four channels every week and joining them by SKU is slow and easy to get wrong.
- SKUs don’t match across channels. The same product has different codes on Shopify, Amazon and eBay, and keeping a lookup table current is a job in itself.
- The data is stale. By the time you’ve refreshed it, a week has passed.
- One formula error ripples everywhere, and nobody notices until stock runs out.
- Several people edit it, and versions multiply.
- Purchase orders live somewhere else, so incoming stock doesn’t move your stockout dates.
- You have hundreds of SKUs across several warehouses.
When to move to software
There’s no magic SKU count. Move when the time spent maintaining the spreadsheet, or the cost of the stockouts and overstock it misses, is more than the cost of a tool.
What software like OmniRestock adds on top of the sheet:
- Sales pulled automatically from Shopify, Amazon, eBay, TikTok Shop, WooCommerce, Linnworks, Mintsoft, ShipBob or CSV
- SKUs linked across channels, with pack quantities and bundles
- Stockout days and spikes handled automatically
- Several forecast methods tested per product, with accuracy shown
- Stockout and order-by dates recalculated daily, including open POs
- Supplier POs and FBA transfers drafted for you to approve
You can keep using the template alongside a trial to check the numbers match your own.
Frequently asked questions
Does the template work in Google Sheets?
Yes. All the formulas work in Google Sheets. FILTER needs Excel 365 or Excel 2021 or later in Excel.
Can I use weekly rather than daily data?
Yes. Convert to daily by dividing by 7, and calculate the standard deviation on weekly data, then divide by √7 to approximate a daily figure.
How many months of history should I paste in?
At least 8 weeks for steady products. For seasonal products, at least 12 months, so last year’s season is included.
Is the template really free?
Yes. We’ll email it to you and send occasional guides on stock planning. You can unsubscribe at any time.
What if a product has never been out of stock?
Then every day counts as in stock, and the formulas work as normal.
Get the template {#get-template}
Enter your email to get the Excel file and a Google Sheets copy link. Or skip the spreadsheet: connect your store and get a free stockout report with no card, or start a 14-day free trial.
Related guides: reorder point · safety stock · multichannel inventory