December 3, 2020    < 1 min read

The more your business grows the more difficult it is to maintain ideal stock levels across your products. Setting a reorder point for your most important SKUs helps you determine when to replenish your stock, so that you have neither too much nor too little of any given item. A carefully calculated reorder point is therefore critical to good stock control, and keeps your key metrics looking healthy for your next inventory report.

Let’s talk about how you calculate the reorder point for your products (with some examples) and how you can use it in your business.

How you can use the reorder point in your business

The reorder point is a stock level threshold that gives you enough time to order new stock before you dip into your safety stock. The reorder point is be used to determine:

  • When it’s the right time to order more supplies
  • When it’s the right time to manufacture more products

What is the reorder point formula?

To calculate the reorder point multiply the average daily usage and the average lead time in days to find demand lead time. Then add the demand lead time to safety stock to get the reorder point. The reorder point formula is:

Reorder point = (Average daily usage x Average lead time in days) + Safety stock

How to calculate the reorder point

First, you need to know your safety stock. The formula for safety stock is:

Safety stock = (maximum daily usage x maximum lead time in days) – (average daily usage x average lead time in days)

Secondly, you need to know the average daily usage – which is how much of the product you’re using. You should also know the lead time from your supplier.

Then, put it all together using the reorder point formula.

bakery goods

Example 1: Becky’s bakery

Becky runs a small but successful bakery and she’s trying to determine her reorder point for flour.

First, she needs to determine the safety stock for flour. She knows that she uses at most 10 bags a day and her supplier has a maximum lead time of 5 days. On an average day, she will use 4 bags a day and her supplier has an average lead time of 3 days. She determines that her safety stock is 38 bags of flour:

Safety stock = (maximum daily usage x maximum lead time in days) – (average daily usage x average lead time in days)
Safety stock = (10 bags x 5 days) – (4 bags x 3 days)
Safety stock = 38 bags of flour

Now that she knows the safety stock and the lead time demand, she can use the reorder point formula:

Reorder Point = (Average daily usage x Average lead time in days) + Safety stock
Reorder Point = (4 bags used x 3 days lead time) + 38 bags
Reorder Point = 50 bags

Becky needs to place a new order with her supplier when she has 50 bags left in stock.

Calculating the reorder point in Excel

reorder point in excel

To calculate the reorder point in Excel, set up a table as in the image above, and use the formula =SUM(F2+G2) where Column F is your Safety Stock figure and Column G is your Lead Time Demand.

In the example of Becky’s Bakery, Becky decides to track her stock on hand in another table and has set it up using conditional formatting. That way, when the stock on hand levels are less than or equal to the reorder point value, it will colour the cell red so she knows she should order more bags of flour:

reorder point in excel example

calculating reorder point in excel Use conditional formatting to highlight cells so you know when you should reorder

Calculating the reorder point in Excel – a caveat

While it is technically possible to use Excel to manage your reorder points, it’s not good business practice and will quickly become a pain point. Growing product businesses should manage their stock using reliable inventory management software. This creates one source of truth for data and enables effortless data analysis and collaboration.

Example 2: The reorder point for retail and distribution

In a retail or distribution environment, instead of average daily usage, we can use average daily sales.

For example, Bryson manages a bike distribution company. He wants to know the reorder point of his most popular bike.

He knows that he wants a safety stock of 200 units. He works out that on average, he sells 300 of this type of bike per day, and he has a supplier lead time of 14 days.

Reorder Point = (Average daily unit sales x Average lead time in days) + Safety stock
Reorder Point = (300 units x 14 days) + 200
Reorder Point = 4,400 units

Bryson now knows that he needs to reorder the bikes when stock levels drop to 4,400.

Understanding Average Daily Unit Sales

The average daily unit sales is simply how much you sell each day on average. It’s easy to calculate — take how many items you’ve sold in a given time period and divide it by the chosen time period. For instance, if you sold 600 units in a 30-day period, you sell 20 units in a day on average during those 30 days.

You can compare the average daily unit sales across different accounting periods or compare it to other inventory reporting metrics to see how well your business is performing. You can also calculate and compare the average daily unit sales for different products, departments or retail locations to identify strengths and weaknesses.

product delivery van

Understanding Lead Time

Lead time is the amount of time in days that it takes from the time you order stock until the time it is delivered to you.

Planning around purchase lead times is important, especially if you’re a manufacturer. If your order doesn’t arrive on time, it can delay manufacturing, resulting in idle workers, lost sales and reduced productivity. If your order arrives too early, you could incur additional inventory storage costs.

There are some ways you can improve lead time performance:

  • Order more frequently. Instead of ordering one or two large bulk orders, you can place smaller, more frequent orders. Enough to keep fulfilling orders without being saddled with too much stock
  • Source from local suppliers. Using local suppliers instead of international ones can minimise the time taken for your goods to arrive, and save you money on freight too
  • Keep great suppliers. Drop unreliable suppliers that can’t regularly deliver in the agreed timeframe
  • Go digital. Choose an online inventory management system that talks to your procurement software, keeps data up to date, and minimises human error. By automating re-ordering you can help your procurement team be more efficient and free up time for more value-adding work.

Understanding Safety Stock

As the name suggests, safety stock refers to the excess products you keep on hand in case of an emergency or supply chain failure. Businesses try to forecast future sales and manage inventory according to those predictions but not everything goes to plan. Safety stock is there to make sure you don’t run out of items if your forecast isn’t accurate.

Reorder point and safety stock: what’s the difference?

The reorder point and safety stock are quite similar: they’re there to help you when you’re running out of stock.

The reorder point is there when you’re almost reaching the bottom of the barrel. If you don’t order any more, you’ll end up having to dip into your safety stock. Safety stock is your last resort – it’s what you dip into when you’ve depleted all your stock and is used for emergencies only. If you miss the reorder point and dip into safety stock, you’ll have to order even more stock to replace the safety stock you used.

Your reorder point will always be more than your safety stock level. The reorder point should allow production to flow. Production shouldn’t have to stop and start because you’re waiting on stock to arrive; that’s costly and inefficient.

Does every SKU need a reorder point?

You should be setting a reorder point for each item Stock Keeping Unit (SKU) in your warehouse.

Knowing the reorder point and how that trends can factor into the decision making process. Each SKU’s reorder point is part of the puzzle that helps managers determine which items are selling well, and which are underperforming. This gives them a better understanding of profitability and helps them make strategic business decisions.

Assumptions in the reorder point formula

The reorder point calculation is based on a mathematical model so there are some assumptions:

  • Demand and lead time are constant
  • Price per unit of product is constant
  • You purchase in batches
  • Replacements occur at once

Reorder points and expansion

The reorder point formula is a dynamic model — all its variables will change over time, so your reorder point per item will change too.

The reorder point therefore shouldn’t be set in stone. It will likely fluctuate depending on whether you are approaching peak or low seasons, and change as your business grows.

Imagine you run a canning business and you use 20,000 cans over the last quarter and you reorder stock when you get down to 500 cans. In the next three months, you plan to place an ad in the newspaper and spend double on social media advertising. You anticipate demand to treble. If your reorder point is still 500, you would run out of stock quickly and be forced to ask customers to place backorders or lose out on sales opportunities. Instead, you decide to increase your reorder point to 1000 so you can keep production and sales flowing smoothly.

Generally, it is best practice to review reorder points every three to six months. The more data you have on past demand, the closer you’ll get to an accurate reorder point.

Using the reorder point in your inventory management system

It can be time-consuming and challenging to calculate and manage the reorder point for each product, especially if your stock is managed across several spreadsheets. Use inventory management software like Unleashed to help you reliably manage stock and place reorders quickly.

In Unleashed you can use stock alerts on the dashboard to see at a glance which items are reaching their reorder point. Clicking on these stock alerts will take you to the Reorder Report — your go-to for reviewing your inventory, stock availability, what has been allocated for an order and what is currently being purchased.

Topics: , ,