In this video, we're going to talk about, reorder point and how to do that in excel. Before we do that, we're going to review, a little bit of the inventory demand pattern that we mentioned in the meeting. So graphically, we can take note, or we can represent our demand as such. And just reminded that in this module, we are looking at only the demand in steady state. So, we are not considering or we're not worried about the variability in our demand just yet. So with that, our inventory level can be represented as such, along with demand rate, over time. So demand is constant. So it depletes, at a constant rate as denoted by the diminishing lines here. The rate, the slope, if you calculate ,they are the same. So these are, the lines that it comes down. And if I don't have any time in my supply chain, then I get replenished instantaneously. So that's what the first graph it depicts. So my demands constant, there's no variability and my there's no lead time in the system. So I get my inventory gets replenished instantaneously over time. But this may not always be the case. Even we have a steady demand. That demand rate doesn't change. We still have, lead time in our system, right? So, we may place an order, at a certain point in time, but then, it doesn't arrive until, a few days or maybe a few weeks later in the future. So there's a lead time that's required, that we need to take into account, right? That's required for shipping, for manufacturing and so forth. We need to take into account what that is. And we can order enough, we're going to have enough time we order ahead of time. So in an ideal situation, that these whatever you order this quantity Q. It will shoot up, Just right at the time that our inventory reaches inventory position which is zero. So the way we're going to encounter or we're going to count for that, is, by calculating that we order point to figure out what this is. Right? So we know, when do we need to know how many days? Or how many weeks? Do we need to order ahead of time in order to buffer for the demand, that will occur during the lead time? So we have enough inventory in the system and then we have enough coming just at a time that we reach our inventory position which is zero. And we get replenished and we have more, we can continue to sell without having to impact our business. So with that, we're going to now look at the quantitatively how to calculate this. So as in the previous worksheet, we alluded to a concept Q, right? What this Q, this quantity is, like what's my order quantity? And the one way we can calculate that, is actually using the economic order quantity formula, or short for EOQ. And the format for EOQ, which is represented by Q, subsequent optimal, OPT. Is equal to the square root, of 2, times my annual demand and times, the order or set up cost and then divided by the holding cost. So we can figure out right from our data, for more demand data from the company, right? Whether you recorded by day, by week or by month, right? At the minimum you I think you were recorded by day, the sales. So you have that, you can aggregate that, you can sum that up, into annual, on an annual basis. So you have annual demand, times the order or set up cost and there is a cost that is usually associated, to a, to an order. So this is typically, your processing like manpower, right? That's required, to make that order, or there's some other transaction cost that's within your supply chain that you need to capture. And this varies by company. Right? So even if you, have a 40 automated system, if there is a review that takes place, if someone needs to review the order before you actually gets a cent to your supplier, you need to account for that. So this will vary the disorder cost, or set up a very bad organization. And that is something that you will have to do a bit of a research, to understand what that is, for your organization. And they were divided by age, which is the holding cost. And every inventory has holding costs, because it sits in a warehouse, it takes up space. Right? So, we need to calculate this. So the Q, is the Q optimal. Once we figure out, once we know these are variables, like the value of these variables, then we can calculate what a Q optimal is. So let's assume, that you did this work, you went through before this one product, you actually aggregated the demand, to the annual basis, as 10,000 units, annually. And then you also figured out right based on the individual item costs and then the interest rate or the holding cost, the value that you assigned to compute holding cost. You figured out it cost $5 per year, per item per year, to hold this item. And then, you also figured out, right? Cost $10 per order, every time generate a PO, it cost the company $10. So then in my in your system, in your supply chain is a two week lead time, right? But from the time you ordered, two weeks later, you were showed up, by your doorstep and ready for you to receive and consume. So then the first step, we need to do is calculate this Q optimal. Okay, so it's basically in excel we can use, the function, right, square root is SQRT. And then I'm going to use, just for older operations sake. So 2 times, following the formula above. 2 times, that annual demand, per year and then times my set of costs. So which is S, okay. And then so that completes in numerator and now I divided by, my denominator, which is my annual holding cost of $5. As I close all the parentheses, okay, enter. Anyway this shouldn't be here. And then, so I calculated the Q optimal, which is 200. Right? So every time I order, I order 200 for every that's my optimal order quantity. So now we can come down and we figure out my reorder point, right? At what point should I reorder my inventory? So to do that, there's one more thing we need to figure out. We need to figure out, the time between order. So I have my Q, right now is my Q optimal. And then I also know my annual demand. So if I take Q, divided by D, right? The quantity, per order, divided by the annual demand. I can figure out the time between order. So this gives a very small 0.02 number. And that's because, our demand right now, is on an annual basis. And then if I, so for example if I take this and have time by 365, I converted this to days. So time between order is about 7.3 days. Maybe 7.3 days, I need to place an order of 200 quantity 200 to my supplier. Or I can convert this to weeks, time by 52. This is about every week or so. I need to make an order to my supplier. Okay, that's because I think $10 really cheap, relatively cheap, so I can do that more frequently. So, I let me stay in so I'm going to stay in weeks. So right now, the way to calculate the order point, is, actually taking my demand. Okay? And times the lead time, I'm going to use a small case d. Times lead time. Okay, so this small case d, is actually the demand over lead time. Okay, so you need to don't don't confuse this with capital letter D. So capital letter D, is usually used to denote annual. Okay annual demand. And in the small case d, is for other like for example, demand over lead time. So here, we need to be mindful that, oh,sorry, one more thing is, the reason we calculate time between order, is that we need to make sure this value the time between order is less than hourly time. Okay, so if it more typically this would be less, right? If it's more, we need to do something else, but typically in general it will be less. So this is just a check that we need to do, before we can proceed. So now we know that, so we can calculate this in a normal way, which is the demand over lead time, is the way is the equation to calculate the order point. So now I know my demand, actually I need to convert. Sorry, I know my annual demand is 10,000. Okay, so I have 10,000 units, per year. But because my lead time is in weeks, so I need to convert this to weeks, so that's calculated by 52 and then times, by lead time. Okay? So my reorder points every time my inventory is at about 300, let's say 385. Okay let's round it up. Then I need to place an order of 200, 42 for you to come. So, this, or you can do it the other way is, right. I can take 10,000 and then you can times right? So you can just convert the weeks into year. Then that will give you the same thing. So I'm not going to do that but you can do it you know one or the other way. Sophia just the thing to make sure is that, your units. Like between the time and your demand and then the calculate reorder points. Make sure ensure that the unit that you're using unit of measure that you're using is consistent. Then the number won't look so funny. Okay so this is how you calculate the reorder point, in when you don't have any variabilities in your supply chain.
