Inventory forecasting for Shopify brands

The Sales history sheet takes 52 weeks of unit sales per SKU, one row per SKU. The Forecast sheet takes the inputs you know for each SKU: lead time in weeks, units on hand, units on open purchase orders, a service level, a seasonality factor, how often you reorder, the supplier's minimum order quantity, and the case pack.

Everything else is a formula. For each SKU you get forecast weekly demand, safety stock, the reorder point, weeks of cover with and without what's on order, a Reorder or OK flag, and a suggested order quantity rounded up to the case pack. Inputs are blue on yellow, formulas are black, and the first row is an example SKU with made-up numbers for you to overwrite.

It sticks to long-standing functions like AVERAGE, STDEV, NORMSINV and CEILING, with no XLOOKUP or dynamic arrays, so it works in Google Sheets and older copies of Excel.

1. Take the average of the last eight weeks of unit sales. That's the base rate, and it moves as demand moves.
2. Multiply it by a seasonality factor for the weeks ahead. A factor of 1.00 says the next stretch will look like the last eight weeks. If last year's same weeks ran 10% above the weeks before them, or a promotion is planned, use 1.10.
3. Set safety stock from how much weekly sales swing. The standard formula is z times the standard deviation of demand times the square root of the lead time. The template uses the last 26 weeks for the standard deviation and gets z from your service level with Excel's NORMSINV function. At a 95% service level, z is about 1.65.
4. The reorder point is forecast weekly demand times lead time in weeks, plus safety stock. When on hand plus on order falls to that level, it's time to order.
5. Order enough to get back to the order-up-to level: forecast demand over the lead time plus one review period, plus safety stock. Then apply the MOQ and round up to the case pack.

The example SKU in the template shows the arithmetic with illustrative numbers. It averaged 145.75 units a week over the last eight weeks, so with a 1.10 seasonality factor the forecast is about 160 a week. Weekly sales had a standard deviation of about 8 units, the lead time is six weeks, and the service level is 95%, so safety stock is about 33 units and the reorder point is about 994. On hand plus on order is 900, so the SKU is flagged. The order-up-to level for six weeks of lead time plus a four-week review period is about 1,636 units, which leaves a gap of 736, rounded up to 744 in cases of 24.

Weeks of cover is units divided by forecast weekly demand. The example has 3.7 weeks on hand and 5.6 including what's on order, against a six-week lead time, so even the stock already coming runs out before a new order could land.

The formulas are simple. Most bad reorder suggestions come from the inputs.

• Weeks where the SKU was out of stock show low sales because there was nothing to sell. Leave them in and the average drops and the standard deviation rises. Replace them with a reasonable estimate or drop them from the window.
• A sitewide sale or a viral week inflates both numbers. Treat it the same way, and plan the next promotion through the seasonality factor.
• Measure lead time from the day you place the PO to the day units are sellable at your 3PL, including receiving. Suppliers quote production time, and transit and check-in add to it.
• Shopify's Inventory sold daily by product report gives average units per day for any period, which you can convert to weeks. Its Inventory remaining per product report divides ending quantity by the average daily sales of the last 28 days, a useful check against the template's weeks of cover.
• From October 1, 2026, Shopify's inventory reports measure stock by On hand rather than Available, so those reports will show higher quantities than before. On hand includes units committed to unfulfilled orders.

The safety stock formula assumes weekly demand is roughly normal and lead time is fixed. If your supplier's lead time varies a lot, the formula needs a lead-time term too, and the template's number will be too low. Slow sellers that sell zero most weeks don't fit the normal assumption either.

The template holds up while one person owns it and the catalog is small enough to review by eye. These are the signs you've outgrown it:

• Somebody spends a day every week exporting sales and pasting them in, and the forecast is already stale when the POs go out.
• You sell the same SKU through Shopify, Amazon, wholesale and retail, and each channel's demand needs its own history.
• Bundles and kits hide component demand. A bundle sale uses up the components, and the spreadsheet only sees whatever you exported.
• You hold stock at more than one 3PL or warehouse, so the question becomes where to send it as well as how much to order.
• Promotions, price changes and launches drive most of your variance, and a seasonality factor typed by hand can't keep up.

At that point the method usually stays the same. What changes is that the data arrives on its own, the reorder suggestions turn into draft purchase orders, and a person reviews exceptions instead of every row. The purchase order automation guide covers that step.

A moving average looks at one SKU's own past. Machine learning models can learn from many SKUs at once and take in the things that move demand: price, promotions, ad spend, day of week, and whether the item was in stock.

A large public test of this was the M5 forecasting competition, which used about 42,000 daily sales series from Walmart, from single items up to totals, along with prices, promotions and other drivers. It was the first of the Makridakis competitions where machine learning led the leaderboard, with LightGBM models and neural networks prominent among the top entries. Walmart's store sales are a long way from a 200-SKU apparel or supplement brand, so read it as evidence that the approach works at scale. It tells you nothing about the accuracy you'd get on your own catalog.

For a Shopify brand, AI forecasting is usually worth it when:

• You have hundreds of active SKUs, so reviewing each one by hand is no longer realistic.
• Promotions, paid media, and launches drive a large share of sales, and you have that history in a usable form.
• Stockouts and overstock are costing more than the software and the setup.

It doesn't fix bad inputs. A model trained on sales that include stockout weeks and miscounted inventory learns the wrong thing faster. Clean up the counts first. The inventory discrepancies guide covers where they go wrong. Then keep a simple baseline like this template running next to the model, so you can see whether it's earning its cost.