How to calculate the security stock of a product in your inventory control

7
42418
Excel Spreadsheets

In this article we will talk about:

In the video below we summarize this post in an easy way. If you want content faster, just watch!

What is the minimum stock or security stock?

Stock is the amount of material or product stored for future use. In each sector of the economy you will have different types of stocks, see:

  • Industry - raw material and finished products can be stored. This is the case of the automobile, textile and food industries.
  • Trade - normally the goods and products for resale are stocked before being sold - this is the case of supermarkets, clothing stores and commerce in general. They receive the finished products, store and sell them.
  • Services - stock supplies needed to provide services - a good example is airlines, which sell tickets but need to store the food items that are offered during the flight.

Now, regardless of the type of sector, it is necessary to control this stock and, more than that, to understand what is the minimum quantity needed of each of them to avoid unnecessary expenses, not to occupy spaces that could be destined to other products or even to not stop selling because of lack of stock.

It is in this context that knowledge about security stock, also known as minimum stock, that as the name itself says, is the minimum quantity of a certain product that you need to have in stock so you do not run out of it at any given time.

Stock Chart

The logic to understand the minimum stock is simple. In the chart below you will see that the x (horizontal) axis corresponds to the time and that the y (vertical) axis corresponds to the quantity in stock of a given product. Let's say we are talking here about a clothing store and that this is the minimum stock chart for caps. Let's see some data:

  • Max inventory: 200 hats
  • Medium (or minimum) stock: 150
  • Stock: 100
  • Supply Term: 5 days
  • Renewal Deadline: 8 days
  • Daily Sales: 10 hats

Here's how that data would look on the chart:

Note that in an ideal world and with constant sales, we would have the process repeating itself continuously. From the time of the first receipt of 80 caps, our stock on from 100, to 180 caps. After 3 days (and selling 30 caps in that period), we hit 150 caps, which is our minimum stock, also known as the order point. At that time a new shipment is requested from suppliers.

As the delivery time is 5 days (50 caps sold), you see that our stock leaves 150 until you reach 100. At that point, another replacement ends up and the stock returns to the 180 caps. From that point, on the chart, everything will repeat itself normally (already in real life, we know that these lines would not be so uniform).

Just to clarify some of the items that we address here, it is worth to understand that:

  • Supply Term: is the replacement time for items between the order placed, the delivery, the receipt and the final procedures to leave the item available for use or sale. It is also known as lead time.
  • Maximum stock: will vary according to available storage space, stocking cost, shelf life and special care required.

Safety stock formula

Now that you have understood all you had to understand about safety stock, it pays to delve a little deeper into your formula, since it is it that will provide you with the minimum stock number that you will use in practice:

Security stock = Average demand (sales) daily x delivery time (in days)

Let's see an example to facilitate. Assuming you have a franchise selling pizza and you usually sell 100 pizzas on average per day. Normally, the delivery time for new orders to your franchisor is 3 days. Only with this data we can already make the calculation using the formula.

Security Inventory = 100 x 3 = 300

That is, you need to have 300 pizzas in stock at the very least. Any value equal to or below this is already an indicator that you need to make a new order for your suppliers.

How to calculate the safety stock in practice

After seeing all the theoretical part, nothing better than a little practice to understand exactly how it will be to use this concept in your day to day. Basically, every time you go to register a product in a stock control sheet, it is strongly indicated that it has the security inventory or minimum inventory control functionality. In the example below we have a building materials store and a column to enter the minimum quantity (ideal) of each item.

As I already said, to arrive at this calculation it is necessary to understand 2 fundamental factors:

  • Delivery time of new goods by suppliers
  • Approximate daily demand for the product in question

Let's understand why I put 1000 as the minimum stock of my first item, the nails:

  • Daily Nail Sales Amount = 250
  • Delivery time for new nails after order made to supplier = 4 days
  • Security stock = 250 x 4 = 1000 nails

The process is quite simple, but when you have a large amount of items in stock, the work can be great and come with errors and that is precisely why it should be done in an automated way. You do not have to do calculations all the time, just take a look at your stock control sheet to find out which items are below the minimum inventory:

In our example are only 5 items, but imagine looking at 1.000 or 50.000 items daily to see need to make new orders. Another important point of security inventory analysis is whether the provisioning of a specific product is happening correctly. That is why in some moments it will make sense to have an individual analysis:

In these more detailed analyzes you usually find order errors or some poorly done process in relation to your inventory control. In our example above, we see an analysis of the requests made for hammers. The problem is that the safety stock of hammers is 20 and throughout the year there were no stock outs of it. Therefore, the April and June applications should not have been made.

A brief review already provides this kind of insight and insight for anyone who uses a stock control worksheet with key functionalities necessary for complete management of their supplies, from stock to orders placed for new products.

To close, always have a visual analysis of your security inventory control, identifying the percentage of your products that are below the minimum margin:

How do you control your security stock?

Some of the benefits of those who already control the security stock are:

  • Do not run out of a product due to lack of stock
  • Compliance with deadlines for customers, no problem for having finished some item
  • Continuous production or sales flow
  • Stock area occupied in a balanced way
  • Avoid urgent (and sometimes more expensive) purchases

And you, use the security stock in your day to day? What method do you use to always keep stock levels below the maximum and above the minimum? Share in the comments and if you need help, be sure to check out our stock control sheet.

Excel Spreadsheets

7 COMMENTS

  1. Good Morning! Is the minimum stock not equal to the reserve stock and the average stock - consumption over a period of time? In your example the average stock is = average stock.

  2. Good Morning! In the example of minimum stock = average stock, but wouldn't the minimum stock be the reserve stock? What about the average stock consumption over a period of time?

  3. Eigentlich ist ein Artikel, der automatisch übersetzt wird. Unsere Sprache ist Englisch, aber danke für die freundlichen Worte

  4. In this case it is always possible for you to work with lower inventory levels, which can therefore bring you a greater risk of running out of product / raw material at any given time

  5. Hello
    I was not comfortable with the calculation of the security stock. It will cost a lot the capital employed.

LEAVE AN ANSWER

Please, write your comment
Please enter your name