Shopify inventory forecasting in a spreadsheet, step by step
You do not need software to forecast inventory. You need one Shopify export, four formulas and about half an hour. This walks through the whole thing in Excel or Google Sheets, and the finished template is free at the bottom of this page. Or grab it now:
Excel and Google Sheets, formulas already set up. No email required.
What forecasting actually means here
Forget the word for a second. All you are doing is answering two questions for every product: at what stock level do I need to place an order, and how much do I buy. Get those right and you stop running out of your bestsellers without burying cash in your slow movers. Everything below is in service of those two numbers.
The five steps
- 1Export your sales from Shopify
Go to Analytics, then Reports, and open "Sales by product variant SKU". Set the date range to the last 30 days and export to CSV. You want units sold per SKU, not revenue, because a price change would skew the demand picture.
- 2Cut it down to two columns
Delete everything except SKU and units sold. That is all the sales data the forecast needs. If you also sell wholesale or on a marketplace, add those units into the same row, because you buy for total demand, not for one channel.
- 3Add your supplier reality
Next to each SKU add three numbers you already know: lead time in days (order placed to sellable, and use what actually happens rather than what you were quoted), a buffer in days, and how long you want an order to last before you reorder.
- 4Add the four formulas
This is the whole engine, and it is four columns. Average per day, safety stock, reorder point, and order quantity. Copy them down every row and the sheet does the rest.
- 5Add a flag and check it weekly
One more column compares your stock against the reorder point and says YES or no. Sort by it, order what it flags, and put fifteen minutes in your calendar each week to update the sales column.
The four formulas
Written in plain words rather than cell references, so you can put them wherever your columns happen to sit.
| Column | Formula | What it tells you |
|---|---|---|
| Average per day | = units sold / 30 | Your baseline demand. |
| Safety stock | = average per day * buffer days | Cover for the weeks that beat plan. |
| Reorder point | = average per day * lead time + safety stock | Order when stock hits this. |
| Order quantity | = average per day * (lead time + cover days) - in stock - on order | How much to actually buy. |
You sold 360 units in the last 30 days, so 12 a day. Your supplier takes 14 days and you keep a 7 day buffer. Safety stock is 12 x 7 = 84. Reorder point is 12 x 14 + 84 = 252. The day that product hits 252 units, you order. If you want each order to last another 28 days, the quantity is 12 x (14 + 28) = 504, minus what you hold and what is already on its way.
Change nothing but the supplier and the answer moves a lot. At a 60 day lead time with a 21 day buffer, the same product reorders at 972 units instead of 252. Your reorder point is mostly about your supplier, not your product.
Picking your buffer
The buffer is the only number you have to judge rather than look up. A simple starting point that works well:
- 7 days
Local or Dutch supplier, roughly a 14 day lead time.
- 14 days
Elsewhere in the EU, roughly 30 days.
- 21 days
Asia, roughly 60 days.
The logic is simple. The longer you wait for stock, the more can go wrong while you wait, so the bigger the cushion needs to be. If a supplier has burned you before, be generous. If you want to size the buffer properly from your sales variability and a target service level instead of a rule of thumb, use the reorder point calculator.
The finished template
Rather than building it from scratch, take ours. Every formula above is already wired up, there are a few example rows so you can see it working, and a second tab explains each column. It opens in both Excel and Google Sheets.
No email, no signup. Delete the example rows and paste your own SKUs in.
When the spreadsheet stops working
Being honest about this, because we would rather you use the sheet than pay for something you do not need. It holds up fine while your setup is simple. It starts to break when:
- Stock sits in more than one place
A warehouse plus a 3PL means a reorder point per location, not per product, plus deciding what to move where.
- You sell through more than one channel
Your webshop plus wholesale means adding up demand from two systems before you can calculate anything.
- Several suppliers, shifting lead times
Every supplier has its own lead time, and they move. The sheet only knows what you last typed in.
- Season and promotions
A flat 30 day average does not see Black Friday coming, or a product that has started taking off.
Notice none of those are about how many products you have. Ten SKUs across two warehouses and three channels is more work than a hundred in one warehouse on one channel.
Or let it run itself
OrderBee does exactly this calculation, for every SKU, every day, straight from your Shopify sales history. No exports, no copying. It uses each supplier's real lead time, subtracts what is already in transit, rounds to whole cases and tells you the date you need to order by. It also picks up trend and season instead of a flat average, and adds your wholesale and B2B sales from Moneybird, WeFact or CSV.
Frequently asked questions
Yes, and for a lot of small stores it is genuinely enough. Export your last 30 days of sales from Shopify, work out your average daily sales, add your supplier lead time and a buffer, and you have a reorder point per product. That is the core of what paid forecasting tools do. Where a spreadsheet struggles is keeping up: the numbers change every week, and you have to redo it by hand.
Analytics, then Reports, then "Sales by product variant SKU". Set the date range to the last 30 days and export it to CSV. That report gives you units sold per SKU, which is the only sales number the forecast needs. Avoid the revenue reports, because a price change would distort your demand.
Thirty days is a workable minimum and what this tutorial uses. Ninety days is better, because one unusually good or bad week matters less. If you are seasonal, also look at the same period last year rather than trusting a rolling average on its own.
No. Shopify tracks how much stock you have and can show a low-stock level you set by hand, but it does not predict demand, work out a reorder point from your lead times, or tell you how much to buy. That gap is why people build these spreadsheets in the first place.
When the number of combinations gets away from you. Stock sitting in more than one place, selling through more than one channel, or buying from several suppliers with different lead times all multiply the rows you have to maintain. Product count matters less than you would think: ten products across two warehouses and three channels is harder to plan than a hundred in one warehouse on one channel.