Safety stock and reorder point calculator: one SKU or Excel list

Reorder point = average daily sales × lead time in days + safety stock.

Lead time is the calendar days from placing the order to having it on the shelf. Average daily sales is units sold in a period ÷ calendar days in that period, with the zero days counted.

Example: an item sells 12 a day, the vendor takes 14 days and safety stock is 25. Reorder point = 12 × 14 + 25 = 193. Order when on hand plus on order drops to 193.

The calculator offers four ways. Use the first when you have daily sales history.

• Service level: safety stock = z × √(lead time × daily std dev² + daily sales² × lead time std dev²), rounded up. This is the formula Oracle NetSuite documents. z is 1.28, 1.65, 2.05 or 2.33 for 90%, 95%, 98% or 99%.
• Max and average: safety stock = max daily sales × max lead time − average daily sales × average lead time.
• Extra days: safety stock = average daily sales × the days of cover you want.
• Your own number: if you already set one.

Example: 12 a day, daily std dev 4, lead time 14 days that never varies, 95%. Safety stock = 1.65 × √(14 × 4²) = 1.65 × 14.97 = 24.7, rounded up to 25.

The service level is the share of order cycles you expect to get through without running out. The formula assumes demand in the lead time follows a normal curve, so NetSuite notes the result is approximate for intermittent, promotional, strongly seasonal or skewed demand.

Put one row per day of sales in B2:B91, with a 0 on days with no sales, and the lead time in days in D1. Then:

• E1, average daily sales: =AVERAGE(B2:B91)
• E2, daily std dev: =STDEV.P(B2:B91)
• E3, z for 95%: =NORM.S.INV(0.95), which returns 1.645
• E4, safety stock: =ROUNDUP(E3E2SQRT(D1),0)
• E5, reorder point: =ROUNDUP(E1*D1+E4,0)

To skip the formulas, paste that column into the one-item calculator above. It fills in the average, the std dev and the highest day for you.

For many items, copy the columns Item, Sold per day, Lead time, On hand and On order from your sheet and paste them into A list of items.

From a sales export reads a sales file with a date, an item and a quantity on each line. An on-hand file is optional and turns the result into a buy list.

• QuickBooks Online sales: Reports > Standard reports > Sales by Product/Service Detail > Export/Print > Export to Excel, then save as CSV. Product heading rows and Total rows are handled.
• QuickBooks Online on hand: run Inventory Valuation Summary the same way for quantity on hand and average cost.
• Shopify sales: Orders > Export > Orders by date > Export orders. Exports by date are emailed to you and the store owner. Canceled orders are left out.
• Shopify on hand: Products > Inventory > Export with All states. On hand (current) and Incoming are read, and locations are added together.
• Any other system: a sheet with a date, an item and a quantity column.

• One-off big orders. A single large job inflates daily sales. Take it out and run it again.
• On-hand accuracy. A reorder point is only as good as the count. The inventory count variance report finds the gaps, and why Shopify inventory is not accurate covers the usual causes.
• Case packs and minimums. Round each order quantity up to what the vendor sells.
• Seasonal items. Use a date range that looks like the months ahead.

In QuickBooks Online Plus or Advanced, enter each result in the product’s Reorder point field (Products & services > Edit > Save). See all next to Low stock then lists what to buy, and Batch actions > Reorder starts a purchase order for one vendor.