So in the reading, we have talked about how the optimization or the type of optimization that's out there in the in the supply chain world. In this video we're going to look at how the optimization is used in supply chain. So for this first video we're going to look at how the linear programming, which is type of optimization that we can do easily set up in Excel to help us decide where to produce our goods. So as mentioned in the reading, in any optimization, there are two things that we need to pay attention to. So one is the objective, what is our objective for performance in this optimization and the second is the constraints. What constraints do I have in my supply chain? So we need to set this up in the Excel as a model and then we can use the Excel software function to help us figure out where is the best location to produce our goods. So for this example we have six countries. So we have the six countries and then the cost. So the cost here is the landed cost which includes my production cost, my transportation costs and also my import tax per tons of goods. So here the table on the top is that you have, if you're producing country one and you ship the country one, then $4,840 is your cost is your limit cost. But if you produce in country one and it gets shipped to country two that is 4900. So and so forth from country one, the country three is 5484. And then from country one to country four is 5501. So that we have computed a table like this. Okay, this is the the cost, the clinic costs in this supply chain which includes production costs, transportation and then the import tax and then in any supply chain, we can also find out the production parameters so for every country. So assuming there's a country 126 here, right? We can decide are they operating right? Are they, can we produce and then the capacity that's available initially countries and I also have for every operation I have, I have the fixed cost. Okay, so fixed cars is just the my if it's operating times the it takes this time a number that is given right in real world you can gather this from the organization or from your own company on what this fixed operating costs is same as the landed cost. You have to do some investigation to find out what these costs are and then you can put it in this format. So then after you have all these costs then you have, you need to add them up. Right? So I have my sum. This total cost here is my sum of the fixed operating cost plus the sum of the landed cost. Okay, so it's basically the sum here and plus the total sum here on this table. And this becomes my objective function typically to design on where they produce. One of the objective is that I want to minimize my cost as a way to optimize supply chain or you can set this objective differently also it doesn't have to be cost per say, but in this example is cost and then I have this table here. Right? So this sorry, the total cost then is my objective function. So then the table down to below this pledging matrix. So this is where our decision variable will go by, where are we going to produce? And how much from which country to which country and how much are we going to produce based on the landing cost and also based on the fixed upgrading cost and then the production total is just a summation of whatever these values are. And then the supply, the market is the same is a summation of what is being shipped to country one country two country three and so forth. And there's a demand so every country there's a demand of how much they need right per country. So this is also noted down below. So once you have all these table, your objective which is that in this case is my total cost and then my constraints. Right? So my constraint one of them is the demanding tons right from every country. So I can use the the function in the solver function in Excel which is on the data and under solver. And if you don't have this you can install this. Okay. You can look up a youtube video to see how you can set this up. So let me just restart this again. So you have in a solver you have the so the first thing you need to said is my objective. Okay, so objective here is in this case I am trying to minimize my cost. Right? So then I choose this field which is named total cost. And then I want to minimize so I choose to minimization from option and you can of course also maximize or you can do seek right? You can seek to certain value but typically you do either maximization or minimization. Okay. And then you can do but perhaps you're looking for the most you're the max output, right? So maybe you can do max if you set up your objective as such. But in this case we're trying to minimize total cost and then by changing, right? So this cell here variable cells, right? So this is the supporting matrix. So this is the cell values here from from E14 all the way down to this is L19. Okay, so this is my matrix here. This is what goes into here this box here by changing variable cell and then I have all these constraints. Okay, so this is where you set up your constraints, that you will know that you'll get that from your supply chain. The first one is my operation, right? My operation is a binary number so less than equal to one. You got some sort of binary number and this is indicate whether it's producing or not. I need to know whether it's in production is producing something or it's not producing something and my production total what I produce, it has to be less than the capacity. So whatever I produce here, I add up in this cell here M. So from M14 to M19, you know what I produced per country has been less than the capacity the country has, which is noted here under the capacity column and then my supply chain matrix, it has to be less than the what I can supply and then the supply total like what I supply the market, what I add up here, supply the market, it has to be less than or equal to my demand. Okay, I exceed my demand. Okay, so then once you have this set up, what you want to do is we usually want to use the simplex lp, okay, so when you choose the solving method, usually in excel that three of them and usually we are going to use the simplex method. Okay, so here again in the solver in the solver feature you want to choose. I said your objective in this case I want to minimize the total cost. I choose the total cost field. And by changing my matrix, my variables. Okay. And is subject to four constraints. So one is whether is the plant, my country or country is operating or not, the production facility that is operating or not. And then my production total what I produce per country has to be less than the capacity that country has and what I have in total has to be less than the maximum supply matrix, okay, so what is the maximum supply I have as a whole and then what I supply to each market, where I supply to each country, it has been less than the demand or equal to the demand that country has. Okay, so then once I have done this I can just hit solve. So right now pay attention that right now the matrix supply chain matrix is empty is blank. Okay, so when I once I hit solve iit will generate all these numbers. Right. Basically we went through iteration, you can see that there's a pause, right? And then depending on the how you knew a computer is then there is usually a pause. So they basically excel the optimization engine in the background during all the checking all the possible combinations to see which one is the most optimal one that minimized my total cost. So now here has generated this table which has the production per country and from into right. And then that will minimize my total cost. So then once you find this right, it will tell you that solver has found into your right solution. Okay. With intolerance or something. Something will tell you that it has found an optimal solution. Otherwise if you there's no optimal solution will tell you that there are alternate solutions. Do you want, what do you want to do? So in this case we have our optimal solution. So you can just say keep right unless you wanted to redo something again you want to change and usually just hit keep the software solution and you hit. Okay. Okay. So this is a very typical case, right? In terms of where you think about where there's source, where they perch produce, what is shipped to write based on your demand and then your production countries. So once you do this there's actually another report. Right? So this is another example that a very a simpler one that I want to produce the two items I want to produce. Right? So the decision is R and C are my two items in the case of my two items the profit that issue one generates. Right? So in this case I shouldn't I wouldn't know this to begin with. And then the decision is to decide on how much to produce, right? Based on all these constraints. Right? I have these are different manufacturing steps and then these are the capacities for each of the steps, capacity in hours. So then for each this step for this item you will take one hour at cutting and sewing and 1.5 this c right operation and then for finishing the same for shipping pack is here. So it depends on how much I produced by each of these items. My total hours, my capacity here cannot. So this is basically just an addition of the total hours for these two items cannot exceed 900. And then same thing for finishing cannot exceed 300. And then shipping a pack and I see 100. So once you run this the same thing, my objective function here is actually to maximize right, my profit. This is I'm trying to generate profit right? How much should I produce before each of this item? So based on the probability and also based on my constraint here, I have all these production constraints, which is what is set up down here by changing the cell, right? C2 and D2 right, these are my decisions how much of each one I need to make. So this one you see that this is a maximization and maximizing profit. Then you can solve. And then gives you a number, right? So now you can see that solver has found a solution all constraints and optimalities conditions are satisfied. So then that's great. I can hit and okay here. But before you do that right, I wanted to also to know that you can look at the sensitivity report so I already have it here, so I'm not going to hit it here. But this is something that you can choose to further analyze whether can you improve, what can you change right further without changing the optimal object results? Okay, so I'm going to hit Ok. And then I'm going to turn over to the sensitivity report. So sensitivity report has two sides. Okay, one is the variables right? This is all the the how much I need to produce per each of these item. So this is not the variables I was changing for the optimization engine was changing so that I can maximize my profit. And also there are constraints okay, what what is my final value? Right? For the how much hours I consume. Right for each of these operations, these are my constraints. So remember that back here, I cannot exceed right? My constraints can I see the the capacity I have. So my capacity is basically my constraints across these three operations. And what we want to know is that for the variables so you have this allowable increase and allowable decrease and this is basically telling you right, how much you can go up or down, you can change the coefficient, you can change this value without affecting the optimal solution which means the optimal solution in this case is 3700. Okay, that is the I'm trying to maximize my profit. So you can change right? You can change r can go up by seven or go down by one and see where you can go down by, go up by two and then go down by about 4.66667 without changing the optimal solution. And then the other one to pay attention to is the reduced cost. So this is the it tells you how much the decision variable 550, must be improved before the final value right is positive and in this case because they're positive already. So the reduced cost is zero. So we don't need to do anything. But if there are other values that maybe you have to forget how you know what you need to change in order to improve this. So once you change something you have to rerun solver so then you will recalculate re optimize, so same thing on the constraint side that you have the allowable increase and also the allowable decrease. Okay, so then this tells you that the right hand side value which is my constraint, how much can they go up or go down by and when you see this this infinite number, that means you can grow up infinitely. And that's because there's no upper bound to this to this constraint, okay you can only be increased by 175 and then here you can go up by 100 and 166 and then 35 and 25 for shipping pack. So whenever you adjust if you go up by one, for example here the other thing to pay attention to is the shadow price or if you go up by one right? The value the final value here, the shadow price tells you how much the final value right here your coefficient will go up by. So if you go here increased by one on the ship impact then the your right hand side right will go from 300 to 303. Okay, for each increase on the right hand side, your final value will go up or by this value, this is what the Shadow Price tells you. So same thing on the ship impact, if I go up by one then you'll go from 100 to 128
