Service level and Availability rate : calculations & Excel

Too often neglected, the service level or fill rate is the primary indicator in Supply Chain in my opinion. It can be a source of joy as much as a source of stress, especially when the product is out of stock. This creates frustration for both business and customers, which is why it is important to measure the best way we can.
(Service level and video / Please activate the automatics subtitle in English) 

excel supply chain
Download the Service Level Excel of the video

Triangle of supply chain performance

How to have a profitable Supply Chain? The answer may seem obvious : improve the level of service for your customers while minimizing inventory levels as well as production costs.

Even if it seems logical, in the reality of the field, it can be more complicated. In order to help you, we will focus on the service level, how to measure it to be able to improve it, no matter your industry.

Measure your customer service level

Whichever your position in the Supply Chain, it is imperative to measure your service rate to improve your performance and your customer satisfaction.

You will always measure this rate between a seller / supplier and a customer via 3 main approaches :

  • Quantity or volume
  • Quality
  • Lead time

Differences between availability rate and service rate?

Let’s start with an example between Coca-Cola and Walmart. Walmart was number 1 in distribution (before being overtaken by Amazon), and has very large supermarkets mainly in the United States. It naturally distributes Coca-Cola products in stores, but also on its website for home deliveries.
First, you have the Coca-Cola factory, which will deliver to the Walmart warehouse where pallets of Coca-Cola products are going to be stored. The first step in measuring the level of service is to measure the percentage of service between these two players, therefore the percentage of quantities delivered, the level of quality and the level of lead time.

Example: If Walmart orders 100,000 bottles but Coca-Cola can only deliver 90,000, with the requested specifications and the lead time, we have a service rate of: 90%.
After that, the Walmart warehouse will receive orders from its stores. The same formula applies to measure the service rate with the 3 axes: quantity, quality and lead time.

Next, we are going to have a customer who will arrive in front of the Coca-Cola department at the Walmart supermarket. In this situation you won’t be able to calculate a service rate because the customer will not order in advance. You will just take a picture and see if the product is available or not, so with a fill rate of 100% (available) or 0% (not available).
Finally, the customer can choose to order directly from the website. Here we have the same situation than the store because the customer will not order in advance. We can therefore only measure whether the product is available or not. When the product is available on the website, it’s usually related to warehouse availability.

To summarize: when we have an order, we can refer to the service level according to 3 points (quantity, quality, lead time). If we do not have an order, we must rely on the availability rate, either 100% if we have at least 1 product, or 0% if it is out of stock.

Quantity ? Value ? Volume? Weight?

This is a question people often ask me. Usually, companies work in terms of quantity, but it depends on your industry.
The first parameter to which it is important to pay attention to is the price difference, therefore, between the selling price and the cost price. If you have very large price differences, you can work in terms of volume, especially if you are selling/supplying raw materials. If you work in distribution, my recommendation is to work in selling price ( or cost price / purchase price if you work in industry).
The goal is increasing its revenue and its profit, so working in value ​​will allow you to weight the products that generate the most income.

Calculations and examples on Excel

Let’s calculate these indicators in Excel with concrete examples. You can download the full Excel below:

Availability rate : calculations on Excel

Discontinued: products at the end of the collection.
Available stock: available products (and not physical stock).

We will only measure the availability rate on active products. If you have discontinued products / Skus they should not be part of the calculation because you cannot guarantee the availability of discontinued products to your customers (example product 7)
If the available stock > 0 then Availability rate = 100% (example Product 1)
If available stock = 0 then Availability rate = 0% (example Product 2)
Please note that the calculation must only be carried out on active items and not on end of life / end of collection (discontinued items).
To have the total availability rate you must calculate the average of all of these products (therefore removing the products at the end of the collection). I also recommend using an ABC classification.

Service level : calculation on Excel

The formula of the fill rate or service level is a bit more complex than the previous one. We always have the same catalog of 10 products, with the quantities ordered and the quantities delivered. We will obtain the percentage of quantities delivered (DIF = Delivery In Full) by dividing the quantities delivered by the quantities ordered.
The total for calculating the conventional service rate is a sum, not an average.
Fill Rate = DIF (Delivery in Full) = %Quantity = Qty supplied / Qty Ordered
If there is 0 quantity delivered then : Service level = 0% (Use of the IFERROR formula on Excel in case of error displayed)
Example Product 2 : 100/200 = 50%
To have the total service rate, it is important not to average the rates per item but to take the sum of the quantities delivered on order.
Example below : DIF Total = 1205/1625 = 74%
If you want to go into more details and measure the entire level of service, you can add the points I told you about at the beginning of the article, which are quality (DOQ) and lead time (DOT).

To calculate the DOQ (Delivery On Quality), you have to gather the quality problems, which are generally declared at the time of delivery. These are the non-compliant products, and they are calculated by dividing them by the number of products delivered (only products that we can check). Once again, the resulting ratio is a sum and not an average.
DOQ Calculation :
DOQ ( Delivery on Quality) = %Quality= 1 – Qty supplied with quality issues / Qty Ordered
As for the DOT (Delivery On Time), you must check if the promise between the supplier and the customer of time has been respected. Therefore, you gather the quantity of products delivered on time, and you divide them with the total quantity of products delivered in order to obtain a percentage ratio.
Calculation DOT :
DOT = Delivery On Time en Anglais = OTD = On Time delivery= Quantity delivered on Time / Quantity supplied
Exemple product 6 = 500 /580 = 86%
Finally, to get the DIFOT (Delivery In Full, On Time), or the total service level, you will multiply each ratio (quantity, quality, lead time).
DIFOT Calculation :
You just need to multiply the 3 indicators between them.
DIFOT = DIF x DOQ x DOT en %
Example total 10 items : 74% x 90% x 78% = 53%
The most important calculation is the quantity one. You can add the quality and lead time if you want to be more precise in the evaluation of your service rate.

Complete Supply Chain dashboards

You are generally required to work with a lot more products than in my example, or even several brands. In order to see more clearly and to measure its performance, it’s important to create Excel dashboards like the ones I propose in SCM Metrics via different templates and complete dashboards.

5 tips (and mistakes to avoid) to boost your service level

1) Pay attention to the quality of your data

You have to be careful with the quality of the data in your product catalog. This means ensuring that the product codes are correct, indicating whether they are active or inactive. If you get it wrong from the start, your calculations will be wrong.
You should also be careful with your stock by doing inventories frequently. If the data related to your stock are false, so will your calculations and it won’t be as accurate as to the reality of the field.
For the orders, everything must be recorded even those relating to products out of stock.
Moreover you have to be transparent with your suppliers and customers. Sharing information is essential in order to maintain good communication between all parties.
Finally, it’s important to have a maximum of historical results of your service rates to be able to understand trends, analyze and continuously improve your procedures.

2) Do not target 100% service rate and fill rate

It’s mathematically impossible to reach 100% of service rate for 100% of your product all the time. It would be a disaster for the profitability of your business. Sales and marketing departments tend to insist on having a 100% uptime rate, however this is unrealistic.

When you have low stock, you will increase your service rate easily, but above a certain level this will lead to over-stock (and therefore the obligation to make promotions or to throw away).
If you want 100% service you need infinite stock which is impossible. You will then have to find the balance in order to ensure your profitability.

3) Focus 20/80

To find this balance, you need to have different goals per product. For example, you should make sure you get the best service rate for your bestsellers (A CODE), putting the service rate for slow moving products (C Code) at a disadvantage.

In order to define these objectives, I advise you to perform an ABC classification in order to define your priorities. You will find all the necessary information on the ABC method here.
You should also consider including the level of uncertainty of your product to target different service level. See ABC XYZ Analyse here.

4) Automate your reportings

I see a lot of people in companies spending too much time measuring and updating dashboards when they should be spending that time on analysis and optimization.
You have 2 solutions:

  1. Extract your data from your ERP or software (SAP, Sage, Oracle, AS400, JDA …) then calculate your indicators automatically in Excel before making the right decisions and acting on your ERP (Orders, parameters, etc.)
  2. Prepare a mockup on Excel with all your indicators. Once the process has been tested and validated, develop these KPIs on your ERP or BI software (Power BI, Tableau, Qlik, MicroStrategy, etc.)

You need to start your Monday with ready-to-use data, rather than struggling every week for hours to update your Excel charts.

5) Put things into perspective

My last advice, but not the least, is to put into perspective as your service rate is concerned. I see a lot of stress generated by this indicator, whether it is in teams, between customers and suppliers or even between departments.
In my opinion, it’s important to perform well but not to at the cost of your professional and personal well-being. Do your best, be methodical and factual, and don’t add the extra pressure to yourself.

The field of supply chain is very stressful in general, no matter what level you are at. If you want to continue to perform well, you must above all continue to have fun at work.
Now it’s up to you to put all the advice in this article into practice in order to improve your service rate and ultimately your profitability. You will find my downloadable Excels by clicking on the button below.

Download the Excel Kpis template

Download the full service level and fill rate Excel :

Become SCM Metrics Expert

Learn and master the best Supply Chain metrics and become an Expert with my new program SCM Metrics
Join SCM Metrics and become an Expert :

online-course-coaching-metrics-supply-chain-logistics-training


Articles and tools on the same subject :


10 KPIs for your Supply Chain
10 Ways to Optimize your Inventory
Safety Stock Formula & Calculation : 6 best methods

Scroll to Top