Safety stock calculation. In this screen capture, I will show you how to calculate safety stock by using the combined standard deviation of demand and order lead time.

0:13

Now let’s look at the data that we have to work with. We have 30 day’s worth or period’s worth of demand, and we have six different orders that have arrived and calculated the lead time that it took for those orders to arrive.

0:35

Now, those are the things that we need in order to calculate this safety stock using this approach. There are other approaches that you can find but this is, I would say, the most popular one.

0:51

So in order to calculate safety stock using this formula, we need to know average demand, we need to know the standard deviation of demand, we need to know average lead time, the standard deviation from lead time and we’re going to set a certain service level. Something which I’m going to discuss in a second.

1:15

And we need to know service level, something that I will discuss in a minute.

1:20

Let’s get started looking at the first input. Average demand, if we have a history of demand, it’s a fairly simple calculation because all we

1:32

need to say is the average of a set of numbers.

1:42

And there you have it. So that’s 19.83, the standard deviation of demand follows a very similar format.

1:57

There’s the standard deviation.

1:59

We are doing the same thing with lead time. We calculate the average over all of those values that we have.

2:15

And then, the standard deviation as we stated before.

2:30

Okay, so average demand 19.83, standard deviation of demand, 2.78, average lead time, 5.67 days, standard deviation of that lead time is 1.51, and you see it’s roughly around 5, but it can be as low as 3 days or as high as 7 days.

2:54

Now, in order to calculate the combined standard deviation, we need to implement exactly this formula up here. It looks a little bit complicated, but I’ll walk you right through it. This is square root of the average lead time multiplied by the squared standard deviation of demand. So I’m going to open the parentheses, I’m going to take this cell right here, and raise it to the second power, and I’m going to close the parentheses just in case, plus squared demand, so open parentheses in that just in case. I want to square that.

3:48

And then we multiply it by the squared standard deviation of our lead time.

3:58

And that is everything we need so here’s our lead time, here’s our standard deviation of demand. Here is our squared demand, which is right here. And then our squared standard deviation of lead time.

4:21

Just need to close one more set of parentheses and hit return, and there we have it. The combined standard deviation of demand and lead time is 30.58. Now let’s talk about service level.

4:38

The service level can be expressed as a percentage, but then we use that percentage to calculate a value that represents how many standard deviations we need to cover in order to achieve that level of service. So service level as a percentage means that in 95 or 98% of the cases we will not run out, so 98% of the customers that come to our store will not experience a stock out. 2% in that case will experience a stock out, but we will take that into consideration, because holding more inventory becomes expensive.

5:25

And we are going to trade off stock out rate with how much inventory we hold. More about that in a second, but let’s assume we choose 95%, we can imput the service level that corresponds to that, or how many standard deviations we need to cover, by using

5:49

a function called the inverse of the standard normal distribution. So the normal distribution is what we typically assume most things in nature are going to be distributed on.

6:03

The average is the most likely one, but there are values above and below the average. Following this normal distribution, and look for that, we’re going to pick our 95%. And then our k becomes 1.64.

6:26

So then our safety stock, the actual number of units that we need to hold in inventory, is calculated by multiplying k with our combined standard deviation. And the result is we need to hold 50 units worth of safety stock, we’re going to round down in this case, 50 units worth of safety stock in order to achieve a 95% service level. Now let’s take a look at how different service levels impact the amount of inventory that we need to hold. So what I did was I calculated the service level from 90% all the way up to 99.9%. And I’ll show you the different values that correspond from that calculation. And as a manager you would now have to make the decision how much inventory do I want to hold. And as we see here it does rise up a little bit, but as we move towards higher and higher percentages, that rise is much more dramatic.

7:43

So from a management perspective, you want to hold a good amount of inventory to prevent stock out, but probably someone here in the 99.99% range is going to become very expensive. So if we look at it graphically here, we do see there is a definite kink. And it accelerates tremendously.

8:08

So you need to pick your service level percentage wisely, because picking a percentage that is too high will result in a lot of excess inventory and that becomes expensive.

8:21

But also picking one that is too low may result in unsatisfied customers. So balance is necessary.

# 7. Safety Stock Calculation

Bài học Progress

0% Complete