Supply Chain

JHU Carey Business School | BU.610.760_SUPPLY CHAIN ANALYTICS_G.AYDIN_MODULE 5_VIDEO 1

INSTRUCTOR: Hi, everyone. Welcome to module 5. In this module, we're going to go back to distribution networks. We're going

to talk about our case, Imagen Healthcare. And most of what we will do in this module will be about the case

itself. And we're going to actually evaluate three things-- one, what's happening in the status quo in the case, two, evaluate option one, which is what we will call pairwise inventory sharing, and evaluate option two, the

collective inventory sharing.

So by now, you read the case. And you're aware of the decisions at hand. So let's dive straight into it. And let's

talk about Imagen Healthcare's status quo. What is the safety stock needed for 90% fill rate at each dealer?

We are told in the case description that each dealer uses order-up-to policy to manage the inventory of the RF

amplifier, that spare part that we are interested in. And the lead time for each dealer, when ordering from the

plant, is four weeks. We have enough data in the case to estimate the weekly demand at each dealer. In other

words, we can fit a distribution to weekly demand data at each dealer. And we are told that each dealer keeps

sufficient safety stock to achieve 90% fill rate.

This is actually similar to examples we covered before. We can set up a model to determine the safety stock

needed at each dealer. And I'll show you an example of that using Buffalo-- the dealer in Buffalo-- as an example. So for our work on this, you can see the spreadsheet Status Quo Buffalo and its video. Let me now go to the

spreadsheet and cover that.

So here's my spreadsheet, Status Quo Buffalo. As usual, on Canvas, I have two versions of the spreadsheet. One

is a template. The other one is a complete version. You can either follow along from the template and fill in with

me, or follow from the complete version, see what we are building up to.

I have two tabs in this spreadsheet. One is called Simulation, the other is called Demand Data. So let me start with the Demand Data tab here. What you're going to notice in the Demand Data tab is that it reproduces the

demand data that we were given in the case. And for each of, I think, 50 weeks of data, or rather 52 weeks of data, for each dealer-- Atlanta, Buffalo, Denver, Houston-- we are told what the demand was in that week. We are

focusing on Buffalo for this example.

The first thing that I want to do with this data is, I have 52 weeks of demand observations in Buffalo. I want to fit a demand distribution to this data so that I have a handle on what kind of demand we are expecting in Buffalo. So let me start @RISK here.

I'll zoom in a little bit. So I'm going to go to Fit. From the dropdown menu, choose Fit. And the next thing that I want to do is I want to make sure that I have the right range of data that I chose to do a fit. I'm looking at Buffalo's demand, all of these. And I'm fitting a distribution to those demand observations. Those are the weekly

demand observations in Buffalo.

It's suggesting a name already for it, based on the title of the column, Buffalo, NY. That name is fine with me. The

next decision we need to make is whether we're going to fit a continuous distribution or a discrete distribution to

this demand data. I will choose discrete here. Continuous would mean that we would allow fractional demand

values, like 10 1/2, 2.5, and so on.

That wouldn't be really much of a problem if I went that way. But I'm going to choose discrete, mainly because

the demand numbers are on the lower side. And these are important, expensive spare parts that we're talking

about. So I don't want to allow fractional inventory. I want to make sure that I treat these units as the individual, distinct units they are.

So I'm going to choose discrete sample data. Click OK. I'll wait for the next dialog box to appear. And we have

seen examples of this earlier. Let me, again, talk about how to interpret what we have in front of us.

First of all, you're going to see under the input column, the blue column, some of the statistical properties of the

input data. This is the input data, as in the 52 weeks of demand data in Buffalo. What was the minimum demand

across to 52 weeks, what was the maximum demand, what was the average demand across 52 weeks in Buffalo, and so on?

And here on the upper left, we are given some choices about what could be a good fit for this demand distri-- for

this demand data. The top choice is a uniform distribution. And the visual graphic here is showing us how the

input data compares to the proposed distribution by @RISK. So for example, the blue bars are showing you that there was-- about 9 1/2 percent of the time, the demand was just below 20. About 9% of the time, the demand

was just about 25. Here, there was about 4% of cases where the demand in Buffalo was 35 or so.

Looking at this data in Buffalo, the @RISK is suggesting that the best fit it could find was a uniform distribution at the top choice. And it's showing you, as red bars, what uniform distribution looks like. As the name implies, uniform distribution assumes uniform, equal chance for every outcome between the minimum, about 2 units of demand, and the maximum, about 35 units of demand.

So we could choose this. We could go with uniform as our best fit. Although, I want to uncheck that one and show

you the next one, too. This is Negbin, short for negative binomial, which is a distribution, really. And it looks like

this one here. It has higher chance of occurrence in the middle. And as you go to the tails, either to the left or to

the right, the chance of occurrence goes down.

So the question is, which one of these do you like better? I could choose either one of the two, because @RISK is

suggesting these as the best possible fit. I'm going to actually go with negative binomial, because I feel like the

red bars shown here for negative binomial are a better representation of what's happening with the actual blue

bars that show my demand in Buffalo.

Integer uniform wasn't quite as good a fit, in my opinion. It wasn't capturing the more likely demands occurring

around this middle point. When I choose negative binomial, at least the peak of the red bars is capturing a little

bit better these high likelihood scenarios shown by the high blue bars here.

So I'm going to choose negative binomial as my distribution. Click right to Excel. I'll accept everything that it's

suggesting here, the names and so on. Click Next.

The next thing you need to choose is where do you want to place this on your spreadsheet. I'm going to place

this in this cell, as the name states, fitted demand distribution for Buffalo. So click OK. And now you'll see a

number there.

Even though you're seeing a number here, remember it's a placeholder. If you double click on that, behind it, you'll see a formula, which is telling you Excel and @RISK know that the demand in Buffalo is a random variable. It's given by this negative binomial distribution with particular parameters. But of course, when you click Enter

there, it's going to have to show you something. So it's showing a number as a placeholder.

Similarly, we'd want to calculate the average demand we have observed in Buffalo. So I'm going to take the

average of all weeks of the demands I observed in Buffalo so that I have a record of what was the demand in

Buffalo on average. That averages about 23.36. So what we have done so far is we got a handle on the demand

in Buffalo. It's this distribution with this average value per week.

So let's now go back to the Simulation tab where we are going to evaluate, remember, our goal. Instead [? of ?] school, we are told Buffalo needs to be achieving 90% fuel rate. So to achieve 90% rate, what kind of safety

stock should they be carrying?

If you look at this spreadsheet, it's going to look similar to some of what we have done before. I have inputs here, which I'm going to answer in just a moment. We have decisions, in particular what safety stock to carry. We have

outputs that we're tracking here. And in particular, we'll have a simulation table here.

Just as some of the examples we have done in previous modules, it's going to be a weekly simulation where

we're keeping track of on-hand inventory, pipeline inventory, inventory position, order-up-to level, orders placed

in each week for the-- for 52 weeks or so as the year progresses.

So let me zoom in a little bit and start entering information in the spreadsheet. The first thing is weekly demand

distribution. What is the weekly demand distribution in Buffalo? Well, I'll get that formula. I'll literally copy and

paste this formula that I got for weekly demand in Buffalo. So I'll put that here. Again, the idea here is that I don't know what the weekly demand in Buffalo is. But I know that it's a random input given by this formula.

Average weekly demand, I will set that equal to what I calculated for average weekly demand in Buffalo in the

next step. Lead time in weeks, the lead time, according to the case, was, I believe, three weeks. But let me make

sure to go back to my notes. It was actually four weeks. OK, so when ordering from the plant, each dealer's lead

time is 4 weeks. So let's put four weeks down for lead time.

Safety stock is our choice. We need to choose it so that Buffalo is achieving 90% fill rate. That's what's

happening in status quo, we are told. I don't know what safety stock will give me 90% fill rate just yet. For now, I'm just going to enter a [? measure of ?] 10 there, and then we'll see how to set it so that we make the fill rate

equal to 90%.

Now safety stock and order-up-to level, there is a 1 to 1 relationship between the two of them, if you remember

from our earlier discussions. In particular, if you tell me what safety stock you choose, I can tell you what order- up-to level it corresponds to. And the way we do that is you look at the lead time plus 1 period, times, you look at the average weekly demand, and to that, we add the safety stock to get the order-up-to level. This is the 1 to 1

relationship between safety stock and order-up-to level that we have seen before, for example, in module 3.

So I have my safety stock here. I have my resulting order-up-to level here. The outputs that we're going to track

are going to be mostly fill rate, what fill rate do we achieve. But before I can calculate the outputs, let me take

care of the simulation of what happens in Buffalo here.

So we have, again, week one-- starting in week one, going all the way to week 52, which will give us a year

simulating what happens in Buffalo. But because week one doesn't exist in a complete vacuum, because things

have been happening before week one, because we're bringing some inventory and some orders into week one, I need to essentially make up, again, some numbers for what happened before week one-- what I might call, and I did call-- week 0, week negative 1, week negative 2, week negative 3 here.

What happened before we got to week one? This is a discussion that came up in module 3 as well. But as a quick

reminder, it doesn't matter too much what initial numbers I enter here, as long as they're not too large or too

small. In the course of 52 weeks of simulation, as things happen in our table, the initial effect of these numbers

will wash away, so to speak, unless, of course, you put so large numbers that your simulation never recovers

from it.

So I don't want to put very large numbers here, or very small numbers. But I'll make up some numbers. What I might make up is, this is order placed at the beginning of the week, so maybe I can set those equal to average

demand as an initial guess. So the average demand here was in C3. So I'll set it equal to that.

Seeing all these decimal points reminds me that maybe I don't want all these decimal points in my average

weekly demand. So I can round that to the [? nearest ?] integer. In Excel, you say round and comma. After the

comma, you put how many digits do you want after the decimal point. If you want integers, just say round to

zero digits after decimal point. And that will be 23.

I'll copy this down for weeks negative 2, negative 1, and negative 0 as well, the result being I'm assuming that in

each of the first four weeks-- before my system really starts up, in each of the first four weeks, I'm assuming that the initial orders placed were 23 units every week. Again, I don't know if that's true or not. The simulation will actually start in earnest in week 1. But I need to make up some numbers here representing the history of what was happening right before simulation starts. And I'm setting the orders just equal to average demand as a

reasonable magnitude.

The other thing that I want to put in here is inventory at the end of the week. And by the way, my notes here

summarize exactly what I said in terms of how I'm choosing these numbers. So we do not know how much

inventory the dealer has at the beginning of week 0 either. But I'm just going to set a number here again, hopefully not too large, not too small. One idea would be, well, let's just set it equal to, again, average weekly

demand, but maybe half of it. Maybe I'm starting with half of average weekly demand in inventory. And once

again, I'm going to round that number-- round the result-- to 0 digits after the decimal point. OK.

The next thing I want to do is I want to enter the demand for the week in Buffalo for each of those 52 weeks. So

that's going to come from the information that I have here. I know that the weekly demand in Buffalo is given by

this formula. So I'm going to copy this cell-- the input of that cell-- and I'm going to paste it here for my weekly

demand in Buffalo.

By doing that, I'm telling Excel and @RISK that I don't know what the demand in Buffalo will be week in, week

out. All I know is that it's a random input, and it has this formula. So put that in there and copy it there.

By the way, let's check the settings of our simulation. This is something that came up in one of our earlier

modules. If you check the settings of our simulation, at the bottom half, you will see, remember, when a

simulation is not running, distributions return. Your choice here governs whether you're showing static values in

the simulation as placeholders, or whether you're showing random values in the simulation as placeholders.

I'm going to-- in the default version of the template, when you start, it will be static values. Let me change that to

random values, meaning that these placeholder values that Excel is showing, they will be drawn randomly

instead of just showing me the same value in every cell. So now we've got that done, let's go back to the

simulation.

In the simulation, week 1, what is on-hand inventory at the beginning of the week? That's the first thing we're

going to calculate. Well, on-hand inventory at the beginning of week 1 is what you had at the end of week 0, those 12 units. Plus, because the lead time here is four weeks, the order that I placed four weeks ago at the

beginning of period negative 3, those units are arriving at the beginning of period 1. So on-hand inventory at the

beginning of period 1 is those 12 units you had at the end of last period, plus the 23 units that you ordered four

weeks ago. That will give me my on-hand inventory now.

Pipeline inventory is what you have ordered and is still on its way to you. In this case, it would be the sum of my

last three orders. Because remember, the lead time is 4. So the orders that I placed one week ago, two weeks

ago, three weeks ago, they are still on their way to me. I haven't received them yet. The pipeline inventory right now is 69 in this example.

Inventory position at the beginning of the week, that's defined as on-hand inventory plus pipeline inventory. My

order-up-to level, we calculated it here. So I'm just going to copy it from here, except that this order-up-to level is

now a decimal number, and I don't really want that. So let me round that to an integer, too. So round that number to 0 digits after the decimal point. OK.

Order placed at the beginning of the week, well, that comes from, look at your order-up-to level. That's where

you want to be, minus, look where your inventory position is currently, so F25 minus E25. On the off chance that this number turns out to be negative, when it's negative, it means you don't have to order. I'm not going to place

a negative order. I'm going to put a max around it. And I'll say take the max of that and 0. If it turns out to be

negative, you'll cap it at 0, meaning that we're not going to place an order.

Then we get to see the demand for the week. In this case, with the numbers we have right now, it's 35. And we

can calculate our inventory at the end of the week. Our inventory at the end of the week would be how much did

we have on hand at the beginning, here, minus, what did the demand turn out to be for this week?

Inventory at the end of the week could be negative or positive. When it's negative, it means we have back

orders. When it's positive, it means we have actual, physical leftover inventory. In the last column, I'll check if I have back orders. And if I have back orders, I will note it in the last column.

So I'll put and if statement here. I'll say, if the inventory at the end of the week-- right now, for example, it's

negative 8. If it is less than 0, then it means I have back orders. Then take the negative of that negative number, which will come out to be positive, as your back order and display. Otherwise, put a 0 here. If inventory at the

end of the week is already positive, then you did not have back orders.

Whoops. I forgot to put a comma there after less than 0. So let me add that. Yep.

So that's the first row of the simulation, what happens in week 1. I'll do it one more time for week 2, gives me a

chance to explain one more time. And then I'll get faster and copy everything down.

So on-hand inventory at the beginning of week 2, that would be what was your on-hand inventory at the end of week 1. So that's 5 right now, with the current numbers. That's how much I had at the end of week 1, plus, what did I order four periods ago? Four periods ago, 1, 2, 3, 4, it's here. That order that I placed four periods ago is

arriving now. So my on-hand inventory is the sum of those. Right now, it's turning out to be negative 2, because

we had a very negative, ending inventory last period.

Pipeline inventory-- pipeline inventory is the sum of the last three orders. So the last three orders are here, the

orders I placed one week ago, two weeks ago, and three weeks ago. Because together, those are the orders that I placed. And I'm still waiting for them to arrive. So that's the pipeline inventory.

Inventory position is your on-hand inventory, plus pipeline inventory. Order-up-to level is-- well, in this example, order-up-to level will be the same every single period. If you remember, we have our safety stock choice here

that implies a certain order-up-to here. And we're using that same order-up-to level every period. So I'm just going to put dollar signs around C8 here so that when I copy it down, it will continue referring to the order-up-to

level in C8, and I'll copy it there. So it's 127 again.

Order placed at the beginning of the week, that number is-- well, look at your order-up-to level, minus, look at your current inventory position. Order the difference. Bring your inventory position to the order-up-to level. And

again, just in case this turns out to be negative, because I don't want to place negative orders, I'll put a max of data as 0, so that if it turns out to be negative, I will say I order 0 units.

Then we observe our demand. With the numbers we have right now, it turns out to be 17. And once we observe

our demand, we can now say, well, here's how much inventory we had at the beginning, minus, here is what the

demand turned out to be. And that's my inventory at the end of the week.

And finally, I'll put my if statement here to check if there were back orders. If the inventory at the end of the week

is negative, then it means I have back orders. Then I take the negative of that negative number, which gives me

a positive number indicating what my back orders was, and I put that in this column. Otherwise, if inventory at the end of the week was positive, then I had no back orders. I'll just put a 0 here.

And the same thing repeats over and over in every row. So I'm just going to copy the rest of it down-- copy down

the one-hand inventory formula, pipeline inventory formula, inventory position formula. Order-up-to up to level should be 127 throughout with the numbers that I have. Order placed at the beginning of the week, copy that one down. Inventory at the end of the week and back order, the amount at the end of the week, copy those

formulas down.

So at the end of this exercise, I have my 52 weeks of simulation table prepared. I can now go back to my outputs

that I'm interested in, calculate those. Let me zoom in just a little bit here.

So the first output is total back order for the year. I can get this by looking at my back order column here, right?

I've been keeping track of what the back order was every week. So if I add all of these back orders across all the

weeks, all of these together gives me my total back orders for the year.

How about total demand for the year? Same idea, I can look at the demand numbers week in, week out. Control+Shift_Enter gets you all the way to the bottom. So choose all of those numbers. Add them up. That's

your total demand for the year.

Once you have your total back order and total demand for the year, you can calculate the fill rate for the year. So

remember what fill rate refers to. It's the fraction of demand we were able to meet without having to use back

orders. So the total demand-- let me put a parentheses here. The total demand was 1184 with the numbers we

have right now, minus the back order was 172. So that's how much of the demand I was able to meet without using back orders, 1184 divided by 172, divided by the total demand again. Total demand was in cell C12, the

cell immediately above. And that gives you your fill rate.

And remember, this is 85% that you're seeing right now. It's what might happen in one year with the numbers

that you see in the table right now. Then we run the simulation. I'm going to run 1,000 iterations-- 1,000 versions

of what might happen in a year. And I will want to summarize what happened on an average year across those

1,000 iterations. So I'm going to say, take the mean-- risk mean-- of the fill rate across all 1,000 iterations. And I'll put that in this cell.

So I'm ready to run the simulation, except that let me remember to tell Excel and @RISK that fill rate is the

output that I'm particularly interested in. So I'll choose a cell that holds the value of fill rate, go to Output, click

Define. I'll accept that name. If you click on that cell, remember, now it will show you this designation, risk

output. So that lets me know that Excel and @RISK know that fill rate is the output that I'm interested in.

OK, so if I run the simulation now, I will see, for a safety stock, of 10 what kind of fill rate I'm achieving in Buffalo. So let's run the simulation. It will take a moment. OK.

So the results come back. And you see that across 1,000 iterations, the average fill rate is turning out to be 85%. We know that in status quo, everything is set up to achieve 90%. So they must be carrying more safety stock

than 10 units in Buffalo, because with 10 units they would only get 85%.

So our next step is, OK, what should be the safety stock in Buffalo then, so that the field rate is 90%? We have

seen this in a previous module, we can use Goal Seek function. Under the Simulate dropdown menu, choose Goal Seek.

What is your goal? My goal is to set the fill rate output-- which is in cell C13-- to set the fill rate output equal to

90%, 0.9. The mean here tells Excel and @RISK, what about fill rate you're trying to set to 90%? In my case, I'm

trying to set the average fill rate across all these 1,000 versions of what might happen. I'm trying to set that, the

mean field rate, to 90%. So I'm going to choose mean.

The second bottom half of the dialog box is about what can you change to achieve this goal. In our case, what we

can change is the safety stock in cell C7. So choose safety stock. And here, we get to give a lower bound and

upper bound for the safety. So I'll put 0 here for a safety stock. I won't carry a negative safety stock. And for the

maximum, I don't want to pick a huge large value, because if it's huge, it makes life harder for Excel and @RISK. It has to search over a larger range. Maybe I'll put 50. And if that turns out to be too small, I can try again.

So that's all the set up. Click Analyze. It will take a moment to search, and should come back with a safety stock

value that gets us a fill rate very close to 90%. And here's the answer. It says, you wanted a target of 0.9. I was

able to find something that gets you 0.90, something, something. That's very close. Do you want to update C7, the safety stock, to what I have found? I will say yes. And right now, we're getting that we need about a safety

stock of 14 units to achieve our goal of about 90% fill rate.

So this was what must be happening in status quo in Buffalo. So let me go back to my notes. Once we do this for

all 10 dealers, we will obtain the results for safety stock at each dealer. Your results might be slightly different from mine.

In my case, I found, for example, Atlanta needs five units of safety stock. When I ran this previously, I found that Buffalo needed 60 units of safety stock, Denver needed 5, and so on, and so forth. And all of them are, as you

can see, very close to 90% fill rate, which was our goal. And looking at the total amount of safety stock we're

carrying across those 10 dealers, it comes out to 138 units. So this is a picture of what must be happening in

status quo.