How to set a reorder level for each product in a spreadsheet

· 6 min read

Most reorder levels are a guess somebody made when the sheet was first built. Ten for everything, or fifteen for the things that sell. The guess is then never looked at again, so the fast sellers run out and the slow ones are reordered long before they need to be.

A reorder level is a small sum, and your spreadsheet already holds most of what goes into it. This guide builds on the two-tab layout from how to track inventory in Google Sheets: a Products tab with one row per product, and a Stock moves tab with one row for every unit that comes in or goes out. If your sheet is laid out differently, the sum is the same and only the cell references change.

The sum behind a reorder level

When you place an order, the delivery takes some days to arrive. During those days you keep selling. The reorder level has to cover those sales, with a little spare in case the delivery is late or a busy week arrives.

Reorder level = units sold per day × (lead time in days + safety days)

  • Units sold per day comes from your own recent sales.
  • Lead time is the number of days from placing an order to having the stock on the shelf. Count from your last two or three orders, not from what the supplier promises.
  • Safety days is the buffer, written as extra days of sales. Three days suits a reliable local supplier. A supplier who is sometimes a week late needs seven.

Take a blue mug that sells 45 in a month. That is 1.5 a day. The supplier takes 7 days and you want 3 safety days: 1.5 × (7 + 3) = 15. When stock falls to 15, you order.

Add four columns to the Products tab

In the inventory layout, SKU is column A, Reorder level is G and Stock left is J, and the tab ends at column L. Add these to the right.

Column Header What goes in it
M Sold last 30 days Formula
N Lead time (days) Typed, per product
O Safety days Typed, per product
P Suggested level Formula
  1. Sold last 30 days (M2) adds up the Out rows for this SKU dated within the last 30 days:

    =SUMIFS('Stock moves'!D:D, 'Stock moves'!B:B, A2, 'Stock moves'!C:C, "Out", 'Stock moves'!A:A, ">"&TODAY()-30)

    On the Stock moves tab, A is Date, B is SKU, C is Type and D is Quantity. The last condition keeps only rows dated after the day 30 days ago. TODAY returns the current date, so the window moves forward by itself each day. This only works if the Date column holds real dates, not text that looks like a date.

  2. Lead time (N2) and Safety days (O2) are plain numbers you type.

  3. Suggested level (P2) does the sum and rounds up to a whole unit:

    =ROUNDUP(M2*(N2+O2)/30, 0)

    Dividing by 30 turns the monthly figure into a daily one. ROUNDUP is used because being one unit over costs less than running out.

Fill M and P down the column. Three example products come out like this:

Product Sold last 30 days Lead time Safety days Suggested level
Blue mug 45 7 3 15
Dinner plate 60 7 3 20
Teapot 6 14 7 5

The teapot shows why one level for everything fails. It sells slowly, 6 in a month, but it comes from a supplier who takes two weeks and is often late. 6 × (14 + 7) ÷ 30 is 4.2, which rounds up to 5. A blanket level of 15 would mean reordering teapots with two and a half months of stock still on the shelf.

Keep your own level, and check it against the suggestion

It is tempting to put the formula straight into the Reorder level column. Keep the level in column G as a typed number, and use the suggestion as advice. You know things the formula does not: a product is being discontinued, a festival is coming, the supplier only ships full cartons.

A fifth column, Level check (Q2), tells you where the two disagree:

=IF(G2<P2, "Too low", IF(G2>2*P2, "High", "OK"))

For the blue mug with a typed level of 15 and a suggestion of 15, this shows OK. If sales double to 90 a month, the suggestion becomes 30 and the cell turns to Too low. "High" marks products where your level is more than twice what sales justify, which is money on the shelf. A product that sold nothing in 30 days will also show High. That is a fair prompt to look at it.

Once a month, filter the column for anything that is not OK and decide row by row.

Three things that make the number wrong

  • Days you were out of stock. If the blue mug was sold out for 10 of the last 30 days, the 45 sold happened in 20 days. The real rate is 2.25 a day, not 1.5. For that product, type a higher level by hand until a full month of sales is in.
  • Write-offs counted as sales. The formula counts every Out row. If you record breakage or samples as Out, a large one-off inflates the figure. This is usually small enough to ignore. If it is not, check the Note column before trusting the suggestion.
  • Seasons. The last 30 days say little about a month that is always busier. Raise the safety days before a known busy period and put them back after.

Make the low rows stand out

The layout already has a column that shows Reorder when stock left is at or below the level. Colouring the whole row makes it hard to miss.

  1. Select the product rows, for example A2:L200.

  2. Choose Format > Conditional formatting.

  3. Under "Format cells if", pick Custom formula is and enter:

    =AND($A2<>"", $J2<=$G2)

  4. Pick a fill colour and click Done.

The dollar signs fix the columns, so every cell in a row looks at that row's Stock left (J) and Reorder level (G). The first condition skips empty rows. Google's page on conditional formatting rules covers custom formulas in more detail.

When and how much are separate questions

The reorder level tells you when to order. It does not tell you how many to order. A simple rule for quantity is to cover the gap until your next order. If you order monthly, a month of blue mugs is 45, rounded to whatever pack size the supplier ships in.

Once an order is placed, the row will keep saying Reorder until the delivery is recorded, and someone may order the same thing twice. A typed On order column fixes that. Put the quantity you ordered in it, change the flag formula to compare J2+R2 (if On order is column R) against the level, and clear the cell when the delivery goes into Stock moves as an In row.

Asking instead of filtering

All of this works in a plain spreadsheet. If people would rather ask than filter, the BoltSheets inventory tracker template uses this same layout, with Reorder level as a typed column. In the demo you can ask "what needs reordering?" or tell it to change a reorder level, and it shows a preview of the change that a person has to confirm. The template does not include the suggested-level columns from this guide. BoltSheets is in early access and works with Google Sheets only.

Whichever way you read the answer, a level worked out from your own sales and lead times beats one number typed down the whole column.

Common questions

Is a reorder level the same as a reorder point or a minimum stock level?

Reorder level and reorder point mean the same thing, the stock figure at which you place the next order. Minimum stock usually means the buffer alone, the amount you never want to dip below. In this layout the buffer is the safety days part of the sum.

How often should I recalculate reorder levels?

The suggested level updates by itself every day, because the formula uses today's date. Look at the check column about once a month, and straight away when a supplier changes its delivery time or a product starts selling much faster or slower.

What reorder level should a new product get?

Type in an estimate. With under 30 days of sales the formula has too little to go on and will suggest a number that is too low. Borrow the daily sales of your most similar product, and switch to the formula after a month.

Do these formulas work in Excel?

Yes. SUMIFS, TODAY, ROUNDUP and IF take the same arguments in Excel, and the tab reference is written the same way, so the formulas can be typed in unchanged.