 Hello everyone, welcome to this course on supply chain digitization. This course is offered from I am Mumbai by 3 faculty members including myself, Professor Priyanka Verma, Professor Sushmita Narayana and Professor Deva Pratadas. So far in our previous sessions we have been focusing on analytics in supply chain and this week particularly we are talking about network optimization. If you remember in our last session we have referred to a supply chain network design problem and we have tried to solve that problem using two different heuristics. Let us have a quick review of that problem and we will see that how that problem can be solved in a better way using optimization method. So the problem was all about a company which is using two manufacturing facilities, three warehouses and four retailers to distribute a single product. Both the manufacturing capacity has got a limit which where it is mentioned that manufacturer 1 cannot supply more than 1,50,000 units and manufacturer 2 cannot supply more than 120,000 units. The production cost is assumed to be same for both the manufacturing facility. Similarly, all the three warehouses have got the same handling cost. So these are some strong assumptions with respect to the supply chain network under consideration. The retailers demands were also given to us and it was around 45,000, 35,000, 68,000 and 70,000 units respectively. The shipping cost are also given to us for all these combinations of manufacturer to warehouses and then from warehouses to the retailers and this is given in the form of the stable over here. So this was our supply chain network which we have referred in our previous session also. We know that any manufacturer can supply to any of the warehouse and similarly any warehouse can supply to any of the retailer. So just for demonstration purpose these arrows are drawn over here to indicate the possible movement between every manufacturing plant to every warehouse and similarly from every warehouse to every retailers. Now going forward we have used two different type of heuristics in our previous sessions and we have seen the cost that we have obtained following the two approaches. We will be now trying to solve this problem using optimization method and we will compare the solution of optimization with the previous solution. So our objective if you remember is all about finding out the best strategy for distribution which helps us in deciding the quantity of flow from the manufacturers through warehouses to the retailers and finally to ensure that the retailers demand is fulfilled and the total distribution cost is also minimized. So to solve this problem using optimization method we will be following the linear programming approach. Let us refer to some of the basic requirements of understanding a linear programming. The first thing is we need to decide the decision variables which we are going to use in this particular problem. So let us see what are these decision variables as our supply chain network is all about manufacturers that is two manufacturers, three warehouses and then to the four retailers. We have to see the quantity that will moved between these manufacturers to the warehouse and then to the retailers. So here we are deciding the decision variables as QM1 to W1 this is QM1 to W1 which is nothing but the quantity of product that will move between manufacturer 1 to the warehouse 1. So this is what is represented over here in the form of this decision variable. Similarly all possible combination of product flow variable between the two manufacturing plant and the three warehouses are also shown over here. So you can see that we have got six decision variable related to the product flow between these manufacturing plants to these warehouses. Going forward we also have to decide that how much unit of goods should move between the warehouses to the retailers. So we can see that there are three warehouses and four retailers this will land into 12 combination of possible movements and that is shown to you in form of these 12 decision variable. If you check XW1 R1 is the quantity of product moving between warehouse 1 to retailer 1 and similarly all this 12 combination of movements are assumed and their corresponding decision variables are also taken over are also decided particularly for this problem. So we now know that we have to take a decision that how much quantity of product should move between each manufacturing plant to the warehouse and then from warehouse to the markets. So our objective is always to find out the cost of transportation between these combinations of facilities such that this total cost of transportation is minimized. So when we multiply these quantities with their respective per unit shipping cost we can calculate the total shipping cost very easily and this becomes our equation for calculating the total shipping cost in the given supply chain network and this cost needs to be minimized. This helps in deciding the objective function accordingly. Going forward now in the next stage we have to decide the constraints. Our constraints are very interesting if you have observed we have got two type of constraint. The first constraint is the supply constraints and this is because your two manufacturing plants has got a upper capacity or upper limit beyond which they cannot supply. So this becomes a constraint for you to be taken care. So here we know that the first manufacturing plant has got a capacity of 150,000 and similarly the second manufacturing plant has got the capacity of 120,000. If you remember our network which has got two manufacturing plant three warehouses and four retailers we can see that from manufacturer one to the warehouse one, warehouse two and warehouse three this arrows are indicating the possible flow from manufacturer one to these particularly three warehouses which are QM1W1 plus QM1W2 plus QM1W3. So when we add all these flows from the manufacturer one we know that these three flows cannot exceed the available capacity of manufacturer one and similarly it applies for manufacturer two as well. So together it becomes our supply constraints. Going forward we have got in a similar way the flow balance constraint which ensures that at every warehouse suppose in warehouse one we are getting product from manufacturer one and manufacturer two and this product whatever warehouse one has received will be distributed to these four retailers that starting from retailer one to retailer four. The same is expressed over here in this equation and it is referred as a flow balance constraint which ensures that the total quantity which a warehouse is receiving the same is redistributed to its corresponding retailers and the same expression can be seen over here very easily. This is about warehouse one the similar manner for the other two warehouses also we will be writing the flow balance constraints as well which are shown in these two equations. The last constraint is about the demand constraints and as we refer to our network once again which has got two manufacturing plant three warehouses and four retailers if you remember. So for every retailer this can receive the product from any of these three warehouses warehouse one warehouse two or warehouse three. So if we add these three quantities which is expected to be received from the three warehouses the final objective is these three warehouse should fulfill the requirement of retailer one and that is considered over here in the form of this demand constraint. In a similar manner we will write these demand constraint for all these four retailers. So these four constraints are shown to you in the form of these four equations over here. The question is how to solve this problem? We know that this has turned out as a linear programming problem which can be easily solved using the solver function which is available in Excel. So let us refer to our Excel sheet and try to solve this problem through that. So this was the problem that was given to us and this table we have seen earlier as well. Here you can see the different shipping costs which are given to us and the corresponding supplies from possible from the two manufacturing plants are shown. Similarly the demand of these four retailers are also shown to us. Now assume that so this is the table where we are going to define our decision variables initially it is assumed that all these decision variables are 0 means that we are not aware of how much quantity should be moved from each manufacturing plant to each warehouse and then from each warehouse to each retailer. So initially all these values are taken as 0 and correspondingly the total units of shipped from the manufacturers and to the retailers are also coming as 0. So this is our decision variable we will be using it to write our objective function and also to write our constraints. So here let us first write our objective function. Our objective function is very simple this is nothing but this is the product of the shipping cost and the decision variables that is the quantity that we have to find it out from this problem. So if I simply use this function called as some product we can simply multiply the all the shipping costs with the corresponding decision variable and it will give us the total cost of distribution over here. Going forward let us try to write these constraints. Here we have got three type of constraint one is the supply constraint the second is the flow balance constraint and the third one is the demand constraint. So for writing the these constraints we need to add these decision variables and for more details you can go back to the formulation that we have discussed and accordingly you can calculate the both the sides of the equations to write your constraints accordingly. So here we have added the total units of shift for from the manufacturers and similarly the total quantity of products being supplied to the retailers this will help me in writing my supply and demand based constraints followed by this we also have that flow balance constraint which ensures that the total quantity coming to a particular warehouse is equals to the total quantity going out of that warehouse. So this is accordingly written over here and we will be using this part of cells to write our final formulation. In order to solve this problem using solver we can use a function we can use one special function which is available in excel. You can go to data and you can see that there is a small tab which is mentioning solver which can be used for particularly optimizing this problem. In case this is not available in your excel you can install it by following very simple steps for that you have to go to file option and over here you can go to the last tab which is mentioning options over here you can again go to this next step which is on add-ins and when you are here you can see at the end there is manage excel add-ins. If you go to this part you can see the possible add-ins which you can install in your excel these are freely available as we require solver add-in check that whether it is already installed in your system or not if it is not there just select it and go press go for this it will take small amount of time and this add-in will be available for you in the data tab. So let us go to data again and go to this add-in called a solver we have already installed all the parameter over here which is helping us in setting the objective function telling us what is the nature of the objective function should it be minimized or maximized as we are trying to minimize the total cost this radio button of minimization is selected when we go to this particular tab which is saying by changing variable cells means here you have to write your decision variable cells which are initially unknown to us and on solving them we are trying to find their solution subject to the constraints means here you can add the constraints in the form of the equations that you have calculated you already have prepared your sheet accordingly and thus you can simply use these functions to add your constraints for that you can go to add function and here you can see you need to add the left hand side of the equation followed by the inequality sign that particular constraint is having and the RHS side of the constraint. So in this way you can keep adding your constraint in corresponding to your formulation keep on adding them unless you have added all your constraint once it is done you can go back to your model like this and here you need to ensure that this option of making unconstrained variables non-negative is selected and next step is because this is a simple linear programming model there is no non-linearity over here we can use this solver called a simplex LP and after this just go for this option of solving the problem when you will solve this you can see that the solver has found a solution and also it is giving you a message that all the constraints and optimality conditions are satisfied this is an indication that the obtained solution is the optimal solution. So you can keep solver solution as the final solution and then once you have solved the problem you can see that the variables are have taken up some value and now you can also find out that how much quantity of products are moved from manufacturer 1 to which warehouse and similarly from which warehouse to which retailer all these values are coming over here which you can see and decide about your supply chain network design finally the total cost is also calculated which can be used for comparison with your previous approaches. So if you remember using heuristic one we got the total cost as 961000 using heuristic two we got the solution as 757000 we can see that both these approaches are giving us higher values though heuristic two is giving you a solution better than heuristic one but when we solve the same problem using linear programming we can see that the cost is coming better than heuristic two approach and we can compare the solution with heuristic two which is 757000 and the linear programming solution which is around 689000 we can say that the solution obtained through linear programming approach is the best solution and also it is optimal solution it turns out to be the minimum cost. So here to summarize we can see that this is our final solution by following optimization method the total cost is coming as the minimum compared to the other heuristics and this is the power of optimization which helps us in getting the solution with minimum value compared to the heuristic approach. So this way we have solved our problem of supply chain network design using the two heuristics and using optimization approach this can be further extended to large and complex supply chain network design as well and many problems in different industries related to distributions can be easily solved using this approach hope to see you all soon thank you have a nice day.