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

7
45340
How to calculate the security stock of a product in your inventory control
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 materials and finished products can be stored. This is the case of the automotive, 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.

Inventory Control Worksheet

Now, regardless of the type of sector, it is necessary to make a control of this stock and, more than that, to understand what is the minimum necessary quantity of each one of them to avoid unnecessary expenses, not to occupy spaces that could be destined for other products or even not to stop selling due to 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

See how this data would look on the chart:

security stock 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.

Curve ABC Stock: What is it and what is it for?

As the delivery time is 5 days (50 caps sold), see that our stock goes from 150 to 100. At that moment, another replacement ends up and the stock returns to 180 caps. From that point on, in the graph, everything will repeat itself normally (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 the available storage space, the cost of having the stock, expiration date and necessary special care.

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 look at an example to make it easier. Assuming you have a pizza selling franchise and you usually sell an average of 100 pizzas a day. Typically, the delivery time for new orders to your franchisor is 3 days. Only with this data can we calculate using the formula.

Safety Stock = 100 x 3 = 300

That is, you need to have at least 300 pizzas in stock. Any amount equal to or below this is already an indicator that you need to place 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 the concept will be used in your day to day. Basically, every time you are going to register a product in a stock control sheet, it is highly recommended that it has the safety stock or minimum stock control functionality. In the example below we have a building materials store and a column to insert the minimum (ideal) quantity of each item.

security stock - minimum stock - product registration

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:

Curve ABC Stock: What is it and what is it for?

  • 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:

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

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:

stock of security - minimum stock - individual stock 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:

Total Security Stock Analysis Chart

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

Inventory Control Worksheet

Do you use the safety stock in your daily life? 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, don't forget to check out our inventory control spreadsheet.

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