An inventory forecasting Excel template for ecommerce, and where it stops working

What the template needs

A forecasting sheet answers two questions for every product: how long will my stock last, and when must I order? You do not need advanced statistics for that. You need clean inputs and two or three formulas.

Here are the columns. Put your header names in row 1 and your products from row 2 down.

Column	Name	What goes in it
A	SKU	Your product code (SKU means stock keeping unit, one code per variant)
B	On hand	Units in your stock right now, all locations added up
C	On order	Units you have ordered from suppliers but not received
D	Sold last 30 days	Units sold across every channel
E	Daily sales	Formula: D divided by 30
F	Lead days	Days from placing an order to having it on your shelf
G	Safety days	Extra days of stock you want as a buffer
H	Days of cover	Formula: how many days stock will last
I	Reorder point	Formula: the level that triggers an order
J	Status	Formula: "Order now" or "OK"
K	Suggested qty	Formula: how many to order

The formulas to paste in

These go in row 2 and you fill them down. They work the same in Excel and in Google Sheets.

• Daily sales (E2): =D2/30
• Days of cover (H2): =IF(E2=0,"",(B2+C2)/E2). The IF stops a divide-by-zero error on products that did not sell.
• Reorder point (I2): =E2*(F2+G2). Daily sales times the days you need to cover: the wait for the supplier plus your buffer.
• Status (J2): =IF(B2+C2<=I2,"Order now","OK")
• Suggested quantity (K2): =IF(J2="OK",0,MAX(0,ROUND(E2*(F2+G2+30)-(B2+C2),0))). The 30 is how many days of sales you want each order to cover. Change it to match how often you order.

Add a conditional formatting rule on column J so "Order now" turns red. That one colour is what makes the sheet useful on a busy Monday.

A worked example (made-up figures)

These numbers are invented to show the maths. Replace them with yours.

SKU	On hand	Sold 30 days	Daily	Lead days	Safety days	Days of cover	Reorder point	Status	Suggested qty
MUG-BLUE	410	360	12	21	7	34.2	336	OK	0
CANDLE-SM	90	150	5	14	5	18	95	Order now	155

The mug sells 12 a day. Its lead time plus buffer is 28 days, so the reorder point is 12 times 28, which is 336. You hold 410, so you are fine for now. You will hit 336 in about six days, so it is worth a look this week.

The candle sells 5 a day. Lead time plus buffer is 19 days, so the reorder point is 95. You hold 90, which is below it. The sheet says "Order now". To cover the 19-day wait plus a 30-day cycle you need 5 times 49, which is 245 units. You hold 90, so the suggestion is 155.

Making it work across channels

The hard part is column D, not the formulas. If you sell on Shopify, Amazon and Etsy, each one has its own sales report. Three habits keep this manageable. For the Shopify part, its inventory reports show the quantity and percentage of inventory sold per day, which gives you a check on your column D figure.

1. Make a raw data block. Paste or import each channel's 30-day sales into columns M to P of the same sheet: date, channel, SKU (column O) and units (column P). Use the same SKU on every channel, or add a mapping column.
2. Total with SUMIFS. In D2 use =SUMIFS($P:$P,$O:$O,A2) to add up units for that SKU across all channels. The function SUMIFS adds numbers that match a condition.
3. Use one stock number. If Amazon holds stock in its warehouses, add that to column B so you do not double count. Our guide to catching FBA inventory problems with AI covers the traps in that number.

Improving the forecast without getting fancy

• Use a longer window for slow products. A product that sells 3 a month is noisy over 30 days. Use 90 days divided by 90 instead.
• Adjust for seasons. Compare last year's same month. If November was double October last year, multiply daily sales by about 2 for your next order. This is a rule of thumb, not a forecast.
• Remove one-off spikes. If one wholesale order took 200 units, take it out of column D, or the sheet will tell you to order for a demand that will not repeat.
• Track supplier lead time accurately. Use the slowest of your last three deliveries, not the quote.

Setting it up, step by step

1. Export your product list with SKU and current stock from your store or stock system. Paste it into columns A and B.
2. Ask each supplier for their real delivery time and write it in column F. If a product has two suppliers, use the slower one.
3. Export 30 days of orders from every channel. Paste them into the raw data block, then fill column D with SUMIFS.
4. Type your buffer in column G. Start with 7 days for reliable suppliers and 14 for late ones.
5. Fill the formulas down, add the red rule on column J, and sort by days of cover, lowest first.
6. Every week, refresh columns B, C and the raw data. Order everything marked "Order now".

Set a calendar reminder. Most forecasting sheets fail because the data goes stale, not because the formulas are wrong.

Where the spreadsheet stops working

A sheet like this is good up to a few hundred products and two or three channels. It struggles when:

• Stock is wrong. The sheet only knows what you type. If a sale on Etsy does not reduce your Shopify count, you oversell.
• Updating takes an hour. If the refresh is a weekly chore of exporting and pasting, someone will skip it.
• Several people edit it. One broken formula can hide a stockout.
• You have bundles or kits. Selling a three-pack that uses three single units needs component logic that a sheet handles badly.

At that point you have two routes. One is a ready-made forecasting tool; this comparison of inventory forecasting software lists the main ones with prices, and the wider inventory management system guide covers the stock-counting side. Prices vary: Prediko, a Shopify inventory app, lists $49 a month for its smallest size as of November 2026, so check what you would get before you build anything. The other route is to turn your own sheet into a small web app that pulls sales in by itself. I explain when that makes sense in moving from Excel to a web app.

How I'd approach this

Start with the sheet. It is free, and building it forces you to find out what your real lead times and sales rates are. That knowledge helps whatever you buy later.

Use it for a month and watch for two things: how long the weekly refresh takes, and how often the stock number is wrong. If the refresh passes an hour a week, or you have oversold twice, you have outgrown it. Then try a ready-made tool for its free trial before you consider anything custom.

Custom makes sense when your sheet holds rules a tool cannot copy, such as odd supplier minimums or bundles. In that case a small app that reads your sales, applies your own rules and sends a reorder alert can be a better fit than a tool, and you own it.