In this video, we come to Monte Carlo simulation. Here, we're going to look at how to use Monte Carlo simulation to help us improve our inventory management decisions. First, I'm going to describe the problem and then also the background information that we have. Then we're going to set up the "model" and then learn how to use the functions within Excel to help us perform a Monte Carlo simulation. Suppose you have a data or the 300 days worth data, histories that you observed. Then based on that data for a product, you observed that the demand is uncertain, of course, but it follows certain probability distribution. You observe that the demand is zero in these 300 days, zero over one. There's no demand for 0 is, happens 15 days, 15 out of 300 days. Then this one demand for 30 days, that would be 300, and then 60 days for two. Then there's three demands for three for 120 days and then demand for four for 45 days. Then demand for five for 30 days. Then you can use this to calculate the probability of these events happening. About five percent of the time there's no demand. Then over 15 percent of the time, there's a demand of four. From this, you can figure out from the data that we have demand probability distribution. Once we figure that out, because demand uncertainty, so these help us to get an idea of demand distribution for our product. Then we come to gather some background information. The current inventory level with this product, and then the lead time. This lead time in days. It usually varies. There's also some randomness to the lead time. Sometimes things can happen, so sometimes here is longer. Sometimes it takes shorter time for us to receive the goods. In the store, our output is 28 days out of the month. There's a fixed order cost, as we mentioned in course 2 on inventory management. There's some sort of order cost and also this holding cost. Then there's also some cost associated with stock outs. Following the inventory management, our module in course 2, the decision variable we want to make is we need to determine the order quantity. We know we're going to use your EOQ to help us determine what the order quantity is and also what the reorder point should be. Now we're trying to minimize our total costs in this case. Then the component has mentioned this. The probabilities listed components are demand, as we just mentioned. Then this also lead time. Lead time also is not constant due to the abilities and unexpected events that happens. The question is, what is a good combination of Q, my optimal order quantity, and also the R, my reorder point that will yield the lowest total cost? Right now, just to refresh us again, we use 300 days worth of data. We observed this demand distribution. We also collected all the time information is also a range because it varies. We collected the current stock level and then the days of my stores' operations per month and then there's cost, fixed order cost, holding cost, and then I have also cost to stockout. What we really wanted to know is what is the combination of Q and R that will yield the lowest total cost. Given this information, we can come and do a calculation. We know the store operates 28 days. I can do a number of one to 28. Now is given that the beginning inventory is seven. I also have, we can receive, and then it becomes initially than my available inventory just beginning plus what I received. I have demand. Then demand here is random, so it follows the probability distribution that we calculate it. That's how you see that this is all different day by day. This is following this distribution is also set up here. We're using the random number generator, grand function in Excel to randomly generate a number between zero and one. Then based on that, you will come to here and look at the distribution. If it's between 0-0.05, then my demand is zero. But if it's between, let's say, 0.9-1, then my demand is 5, for example. That's how it figures out what the number to put here and it's this random. This random number is random, it's hard to simulate the variabilities that we can observe in the real life. Then the demand fill is just what I have available unless my demand minus my ending inventory and this is what I can filter based on whatever was available. I don't have a stock out here because I have my available inventory is more than the demand needs. Then here, I also have a logic, which is, I'm sitting off with this so we can do this simulation here. It's just figuring out if my inventory is less than a certain quantity, do I show any place an order? Then if I order, when will it show up? Is it based on my lead time? You'll figure this out based on lead times and other information, like placing an order, yes. Then you will take the lead time and then so forth. Then from there, I can calculate the holding cost inside the inventory. If I have an inventory, how much it's costing me, and do I have any stockouts, and if am placing order, so like here, I'm placing an order, so placing an order is yes. I have the order cost. So the total costs for day one is 20 dollars and 10 cents. I'm holding inventory and I'm placing the order. Then if you come down here, I'm having an inventory, but I do have stock out cost because I ran out. I cannot fulfill the order that came in. Then I have the stock-out costs here as the total cost for the day. Then we continue to do this, we build this inventory model to simulate what will happen in a month, 28 day time. I continue to simulate this demand and I can calculate the beginning inventory. This by now in day 3, will be equal to the ending inventory from the previous day. As some similar to what we've seen in the previous video, then available inventory the same. This is randomly generated and I can fill this demand because I have enough inventory. This is my ending inventory where it's available minus my demand, and that's my end. Any inventory plus what on order, what's coming, and then that's based on lead time. This is being figured out based on the lead time here. Lead time here is also a number generated randomly using the randbetween function in Excel. This is based on the, so I'm told the lead time is between one to three days. It's randomly picking a number, basically, RANDBETWEEN what it does is it randomly pick a number between one and three because it's uniformly distributed. They have equal chance of that happening. I don't need you to sign a probability similar to the demand. Here, I just need to use the RANDBETWEEN for Excel to randomly pick a number between one and three. These are lead times are also assigned, based on a certain formula. Now you can determine that by base on the lead time setup within an organization. Then the cost, again, just calculated based on what you have available, what stock out, and so forth. Then you can calculate the total cost for the day. By the end of the 28 days, I can calculate the individual like the total holding cost, order cost, and stockout costs, and then the total cost for all 30 days. Once you have something like this setup, you can come to and this is just the same thing from before. Our decision points here, the order quantity and the order points. We wanted to know what this combination should be that will yield the lowest total cost. Then the other information here I just set up, we set it up here so we can use it for calculation on the left. This is just information that we reference to in order to calculate these values. You can see that this is referencing to T15 and then this is referencing to T18 and so forth. To help us, we need to do a simulation. So now we have simulated by manually, by ourselves what may happen in the 28 days in a month, given the variability in demand and then also the variability in lead time. We went ahead and calculate your inventory at the beginning, the end of day and then should I order more, and did I encounter stock-out on a daily basis. I did that. I build this model for 28 days or a month. So what I need to do is I need to set up a table. Here, I need to set up a table. That has my holding cost, that is my stock-out cost, that had my order cost, and that has my total cost. This is basically referencing the cost I have at the end here, under my simulation here so that my total holding cost is referencing cell M30 by the same thing on this darker cost referencing M30, O30 for older cost, and then P30 for the total cost. Let me do this. I'm me take off all the values here. What I need to do now is build a table. This table here is what we would use to do the Monte Carlo simulation. Because what Monte Carlo simulation is, it's just basically repeating. It help you how to automate repeating a model to simulate a model through a countless number of possibilities. Here, we're going to actually run the model 200 times. You can see that at the end of value, you're going to run, and our run value is from one to 200. We're going to simulate what's going to happen. For every run, like this is what happens. Because we only build our model for a month. The simulation number one is equal to what we have, come from the model that we built. This equals to what may happen in a month. But we're going to simulate this 200 times. We can do these simulations. You run through many different variations of demand. Is going to be very various demand variations that we are going to encounter. There's going to be various lead time, the ratio we're gong to encounter. Now what I'm going to do is I'm going to highlight, I'm going to also highlight the run column and then the rest. I'm going to go down highlight this table here up to 200. What I'm going to do is I'm going to go to data and go into what-if analysis. There's a data table. I'm going to leave the row input cell blank. You're going to come down the column input. Here, basically, you just randomly choose a blank cell. You just choose a blank cell and then you hit "Okay". What Excel is doing now is actually repeating this simulation, this model here that you set up, this inventory model that you just set up here 200 times. Every time you repeat, it would generate a random demand for every day that you have in the module and also the lead time. Now it's done. You can see that this simulation, my holding cost varies, it's not the same. Maybe 270 for Month 2 and then up to four or five dollars in Month 5. Similarly, for my stock-out cost, Month 1 [inaudible] dollars. But in Month 6, I don't [inaudible] 16, sorry, Simulation 6, and then in Simulation 7, I only include eight dollars for the month. You can see the variation or you went through this 200 times. With 200 times, I think we can pretty much say we might get a very robust in account for many different possible scenarios that we can face in a real world. Then I have this average, this table here, where it's calculating, basically calculating the average. You're taking the average of all 200 results. On average, by my holding cost is three dollars and eight cents, and stock-out cost is six by 64 dollars in time change, order cost is 129 dollars and 20 cents. Then total cost on average, I think I can say that with high confidence that on average, my total cost would be a $192.32. I have the 200 simulations. Basically, did this for 200 months. Now we have the simulation done for all the different possible demand and lead time variabilities. Now we're going to need to come back and say, well what? Right now, I have here is the order quantity in Q is 12, and then reorder point 7. Now we need to figure out is this is the most optimal? Are there better solutions? We can do with these 200 set of data available now to us. We can come back to the What-If Analysis. We're going to use Scenario Manager. Here, I have pre-build some scenarios. What is this, I will show you. This is the function that you can see the change here. Basically, Q8R5 means that I set my Q to equal to 8 and my R equals to 5. We can see that the number changed when I change this. I know you can now see the whole thing, but you can see some changes with Excel when calculating. I'm going to go down to Q12R5, and I'll just show you is redoing the calculation. The holding cost average also changed. Now my cost is up to $219.19. I'm going to go to the Q12R7 which is what we just did. So that we can do this, we're going to add a scenario here. I'm going to call this scenario, maybe I'm going to do Q14 and I'm keeping the reorder point at 7. Then I'm going to click Okay and it's going to ask you, what number do you want to set for cell T9? T9 is my order quantity Q. Here is, I'm setting it to 14 and I'm keeping the reorder point the same. The way I name, this I just named what Q and set it too and what R I set it too. We can do this. Again, let's do one more. I'm going to set this to maybe to Q14 and then reorder point, let's go back to 5. My order quantity is 14. I think I forgot to change that. If you forgot, click on Edit and then come back here and change this to 5. Let's check to make sure this is 14,5 and this is 14,7. You can continue to do this for however many combination that you wish to see. Once you're done doing this, so they have all the possible scenarios that you wanted to analyze, you can just click Summary. Conceptually, for now, just do a scenario summary. Then you hit Okay. Then you'll see that Excel will actually generate. Now that we have randomly generated 200 sets of simulations by using the data table. Now, it's actually going through based on our decision variable that we needed to sign and then putting these different combination of order quantities and reorder points. Then based on that, all these different combinations is calculating all these costs. This is R26, S26. This is referencing back to here. R26, S26, T26. This is my holding cost and this is a stockout and then for order and then this is my total cost. We can see that you generated based on these 200 set of simulations, 200 month worth of possible demand data, the cost. Then here are the different costs for different combination of my order quantity and reorder point. Then here, you can actually see which combination is the best. Gives you the lowest total cost. You're going to see that even with the same order quantity and the order point, you may get a slightly different cost. Because remember, every time you run this, Excel regenerates the demand variation at a random function, we will generate a new random variable, so that's why these two are different, even though the Q and R are the same. But we can safely say that if 14 and 7 at least based on the scenario that we have available by quantity 14 and then reorder point of seven, seemed to give us the lowest total cost. This is how you use Monte Carlo simulation to help you make a better decision on how to improve your inventory management and by minimizing the total cost and then by deciding the optimal quantity and then reorder points that you should set. You require a bit of setup. But mostly, it's just based on the demand period that you have. You can choose a month. Usually, it has a good timeframe, and then you just go through, get these set up these information, beginning inventory, you may receive available and then the actual demand, which is generated through the random variable. Random function. What you can fill per day, what your ending inventory looks like. Did you incur any stock-outs and how much inventory do you have? My ending plus what's on older and do you need to order for that day. What is the lead time that you are likely to encounter that day? Based on, again, the random function RANDBETWEEN. This is also one. It's going to arrive you ordered today, and it will arrive four days later. Then you calculate the different costs, holding cost, stock-out cost, order cost, and the total cost. Then you set up these. So these are from the problem description. You set up a place to hold the order quantity and reorder point. You can put in random number at the beginning, and you note down the D time your holding costs. These are for calculations reference. Then at the end, you build this table to calculate the average from your simulation. Then the table simulation. You need to set up a table to hold that you reference at least the first one. You reference from the model that you just set up. Then you can highlight all, and you can set out a number of runs that you wish to do. Then you just highlight them and then go to "Data" and then what Eva analysis and generate this data table. Once after that you go to "Scenario Manager" and then to put into the possible combinations that you wish to try and see which one will give you the lowest total cost. What I would suggest is that you can calculate this. We learned in Module 2 or in Course 2 that, EOQ can help us figure out the order quantity. Also our inventory management formula can help us figure out the order point based on variation. I'll try that as a starting point and then you can use this Scenario Manager to do sensitivity analysis to say, maybe assuming that this is my EOQ gives me 14 as a the Q, and then seven as the reorder point. Then you can do sensitivity analysis. My own Q and R, so Q, they say 14, 16, 18, 20, and maybe also some 12 and 10. Then R can vary maybe between 5-10 days, or 15 days or something, that sort of thing. Quantity, so 5-15 in quantity. Then you see that these Scenario Manager will generate this table for you. Write a summary and then from here, you can very easily see which one will give you the lowest total cost.
