Inventory how-to

How to build a reorder spreadsheet

Build one row per variant with columns for daily sales, lead time, safety days, on hand, on order, reorder point, and order quantity.

Short answer: Build one row per variant with columns for daily sales, lead time, safety days, on hand, on order, reorder point, and order quantity. Update the sales numbers regularly and recalculate the formulas, since a spreadsheet only stays useful if the inputs are fresh.

Set up the columns

Start with one row per SKU or variant. Columns you need: SKU name, average daily sales, supplier lead time in days, safety days, units on hand, units on order, reorder point, suggested order quantity, and case pack size if your supplier requires it.

Add a column for last updated date. This tells you how stale the sales number is, which matters because sales speed changes over time.

Calculate daily sales

Pull recent sales history per variant, ideally the last 28 days, and divide total units sold by the number of days. Avoid using a single day or week, since that can be skewed by a promotion or an out-of-stock gap.

If a variant is seasonal, note that a flat 28-day average will be wrong in the weeks leading into or out of the season. You may need to manually adjust the number during those periods.

Build the formulas

Reorder point = daily sales x (lead time + safety days). When on hand drops below this number, it is time to order.

Order quantity = daily sales x (lead time + days of cover) minus (on hand + on order). Round the result up to the nearest case pack or supplier minimum.

Maintain it

Refresh the sales and stock numbers on a set schedule, weekly works for most small stores. Recheck lead times after any supplier delay, since a lead time that quietly stretches from 14 days to 21 days will make your reorder points too low.

Step by step

  1. List every active variant in its own row, with SKU and supplier.
  2. Pull total units sold over the last 28 days for each variant and divide by 28 to get daily sales.
  3. Add supplier lead time in days and your chosen safety days for each row.
  4. Calculate reorder point: daily sales x (lead time + safety days).
  5. Calculate order quantity: daily sales x (lead time + days of cover) - (on hand + on order), rounded up to case pack size.
  6. Highlight any row where on hand is below the reorder point so you can act fast.
  7. Set a recurring weekly time to refresh sales numbers and re-highlight.

Worked example

A store sells a candle at 6 units a day on average over the last 28 days. Lead time from the supplier is 10 days, safety days are 4, so reorder point is 6 x (10 + 4) = 84 units. On hand is 70 units and nothing is on order, so the store is already below the reorder point. With a target of 20 days of cover, order quantity = 6 x (10 + 20) - (70 + 0) = 180 - 70 = 110 units, rounded up to 120 if the case pack is 12.

How SteadyShelf helps

SteadyShelf does this calculation automatically per variant, using your actual sales window and each supplier's lead time and safety days, then rounds to case packs and drafts the purchase order for you. If you want to keep using a spreadsheet, that is a reasonable manual approach for a small number of SKUs, but it does not scale well and someone has to remember to update it every week.

$29/month flat, unlimited products and suppliers, 30-day free trial. Run the numbers now with the free reorder point calculator.

SteadyShelf is launching on the Shopify App Store. Join early access to get the install link first.

Related guides

All inventory how-to guides ยท Inventory glossary

FAQ

How many safety days should I use?

There is no fixed number. Use more safety days for suppliers with unreliable lead times or for your best sellers, and fewer for slow movers or very reliable suppliers. Start with something like 3 to 7 days and adjust based on how often you run out.

What if a variant has very little sales history?

With only a few weeks of data, your daily sales average will be noisy. Use a longer lookback window if you can, round conservatively, and recheck the number sooner than you would for an established SKU.

Let SteadyShelf do this math for every SKU

Order-by dates per supplier, seasonal forecasts and draft POs for your Shopify store. $29/month after a 30-day free trial.

Get early access