 Hello everyone, welcome to this course on supply chain digitization which is offered by IIAM Mumbai. This course is jointly being taken by three of the faculty members including myself, Professor Priyanka Varma and my colleagues, Professor Sushmita and Professor Devabrata Das. So far we have covered a lot of concepts on supply chain management and about digitalization and analytics. So this week we are focusing more on analytics particularly in this module how analytics can be used for solving supply chain related problems. In the last session we have started our discussions about facility location, facility selection and supply chain network design related problems. In the previous sessions we have seen that how break-even analysis can be used for selecting a facility when you are knowing that what is the volume that is required to be handled these facilities can help you in completing that requirement in minimum cost. So last session if you remember we have used a very simple analysis like break-even analysis to take up this decision. So in this session we will continue our discussion on analytics in supply chain management and further on supply chain network optimization related topics. The next topic that we are going to talk in detail is about facility location. To understand this topic we will try to look into this through a case. As we know that location of a facility is a strategic decision. It impacts the different problems related to supply chain on a long term basis and that it plays a critical role in deciding the performance of the supply chain. So we will be talking about how can we take facility location related decision using a simple case over here. So before we start let us try to look into our case. This case is related to a manufacturer which is engaged in making a high quality equipments and cooking ranges and currently it is operating a single assembly factory which is located near Mumbai and with this facility alone this manufacturer is trying to serve the entire Indian market. So as you can see from here the existing facility is just one which is a assembly factory and this facility plays a very important role as the same facility or the same manufacturing plant is actually trying to serve the entire market for the given product. Now this product has gained popularity and because of which there is rapid growth in demand is observed and because of this increase in demand the CEO of the company has decided to make another factory so that this increased demand can be catered. However, in this given situation the supply chain manager is playing a critical role as his job is to find out a suitable location for this new factory which can be used for serving this excess demand. Similarly in terms of its parts which are used for making the final products the plants of these parts are situated in Chennai in Kolkata and in Hyderabad and the responsibility of these parts plant is to ensure that the parts are available to this new factory as well. There is already some research has been done and it is found that the new factory is required to serve the markets of Delhi, Bangalore, Mumbai, Pune and Chennai. So we can see that there are three sources or three suppliers which are connected which are required to be connected to this plant and if you observe this plant is required to serve the market requirement for Delhi, Bangalore, Mumbai, Pune and Chennai which means there are total five markets which this new factory will be serving. So there are three suppliers and five customers for particularly for this factory. Now here we have also been given with another set of information which is about the coordinate locations of every market is known to us. The demand not only market as well as these suppliers, the demand are given to us and what is the supply from each parts plant is also known to us. In addition to this the shipping cost for each supply source or market is also provided. So these are the set of information or the parameters which are already given to us about the given case and considering these cost, demand, supply and the coordinate locations we now have to decide where we should make our new factory. So here this is the task that is we have to find out the optimal location. This is very important. We have to find out the optimal location for the new factory in India considering the above given data and other constraints related to supply, demand and so on. So how do we handle this type of situation? This problem can be again solved to a very simple approach called as center of gravity approach which is based on the coordinate location information of the given facilities and considering the demand from these facilities and the distances between them this method can help us in identifying the optimal location of the plant where this factory can be located. Let us try to understand this method of center of gravity. For this we already had discussed that we are required to have the idea about the coordinate location of the markets as well as the supply source also. So in general we are saying that let capital X and capital Y which can be the set of the coordinates of these market locations and the supply locations. Let capital F is again it is a indicates is a variable which is representing the cost of shipping one unit for one mile between the facility and either to the market or to the supply source. So both ways this cost of shipping is considered when we are talking about one unit this one unit can be considered in form of a piece, a pallet, a truck load or a ton depending upon the type of product that you are considering this unit can be decided and accordingly the shipping cost can also be identified. In parallel to that we are also knowing the quantity to be shipped between the facility and market or the supply source which is given as capital D. So if you are trying to find out the location of the new facility where this facility should be created such that the total cost should be minimized. So let us see that how this total cost is calculated. In order to calculate the total cost we first have to find out the distance which is shown as cap small d the distance between the facility at the unknown location because we are trying to find out the location of the new facility and is considered as unknown and is taken as the coordinate X and Y and the existing supply source or market. So this distance is calculated using this formula. So how do we do this? Let me try to demonstrate to you. Suppose this is the location of our new facility and its coordinates are unknown to us that is why the coordinates are given as X and Y whereas you have got 3 supply sources. So their coordinates are given as X1, Y1, X2, Y2, X3, Y3 these are your 3 supply sources S1, S2, S3 and you have got 5 markets whose demands are required to be fulfilled which is M1, M2, M3, M4 and M5 their coordinates are given as X4, Y4, X5, Y5 and so on. So we can see that there are 3 suppliers supply sources and 5 markets. So these supply sources will be supplying to this factory and again this factory will be supplying to the market requirement of these 5 cities. So if you have to calculate the distance between the new facility and any one of these supply sources or markets we can calculate it using this formula which is the distance calculation formula also called as Euclidean distance formula and how do we calculate them? This is given as if you have to calculate the distance between 2 points as whose coordinates are given as X1, Y1 and X2, Y2 then the distance can be calculated as X1 minus X2 whole square plus Y1 minus Y2 whole square. So using this Euclidean distance formula the all the distances between the supply source node and the factory and the node to these markets are calculated and are tabulated in the form of the respective distances. In order to calculate the total cost we can use these distances in our formula very easily. So if we have to find out the total cost this becomes very easy. The shipping cost is given to us as per unit and per mile. So this needs to be multiplied by the number of units that is being transported between the 2 facilities multiplied by the distance between the 2 facilities. So that is why we can see that the total cost formula is given to us as the product of the distance into the quantity into the shipping cost and this is summed over all the combinations of the facilities. So this way we can calculate our total cost but remember that so far we are not knowing the coordinates of our new facilities to be located and that is why this is kept as blank. So how do we solve this problem? In order to solve this problem a very simple approach is shown over here in the form of center of gravity method and we can see from here that we can enter the problem these are the steps which can be done for solving the problem using center of gravity. It starts with the first step as data entry. The second step is about deciding your decision variables. The third step is for the distance calculation. The fourth step is about the total transportation cost calculation and the last step is about using the solver tool which is available in Excel and using this function optimizing the total cost to minimum value and finding out the value of x and y that is the coordinate of the new facility accordingly. So let us try to understand the steps one by one. In step one we will be first entering our data. So this is very simple we have already been given with all the details about the unit shipping cost, unit quantity and the coordinates of the supply sources and the market. So in the first step we will be entering all the data and this again for this problem supporting Excel sheet is provided. So you can also refer to the Excel sheet and you can see that how this data is entered over there. In the second step we will be talking about deciding our decision variables. If you remember the location of the facility, the new facility which is required to be made is considered as x and y and that is why we will define two new cells as x and y where the coordinates of this new factor is unknown to us and this is kept as 0 initially. In the third step we will be calculating all the distances between the potentials for factory location to each source or market. We already have discussed about the formula for calculating the distance which follows the Euclidean distance formula. So using that formula we can perform the calculations of the distance function and here we have also given to you a reference for using the cells for calculating the distances and the same can be followed. This is also given in the Excel sheet, we will try to demonstrate to you in the Excel sheet also. But remember we are using the Euclidean distance formula over here. So once we have got the distance we now have to calculate the total transportation cost. The total transportation cost is the product of all the three values which is your cost, shipping cost, into quantity, into your distances. So we can see from here we are using a function called as sum product which will multiply these three elements which includes your distances, your quantities and your shipping value, shipping cost per unit is also considered. The sum product of these three parameters is calculated which will give us the total cost in C21 cell. So now if you look into this sheet we can see that the values of X and Y as it was initially assumed as 0 and 0, this cost is coming with a very high value and it is not optimized yet. So in the last step we have to calculate we have to actually use this optimization function and try to optimize this total cost to find out the optimal value of X and Y. For this purpose let me shift to the excel sheet and try to demonstrate to you about all the steps that we have done so far. So let us look into our excel sheet once again and you can see that all the previous steps that we have discussed so far are done and our sheet is ready to see the steps related to optimization. The data related to the supply sources are available over here. You have got three supply sources coming from channel Kolkata and Hyderabad, their shipping cost per unit per mile is given to us. The quantity to be delivered that these supply sources can deliver is also given to us over here and then you can also see in terms of the coordinates of X and Y over here which is given as for the three different supply sources. In order to calculate the distances between the supply sources and the X and Y coordinates of the new facility you can see from here the distances are calculated using the Euclidean function that we have already discussed and all the distances are shown over here corresponding to all the supply sources for your new facilities to be located. Similarly, the data related to markets are given over here we have got five markets, their shipping costs are given, their quantities that is their requirements are given and their coordinates are also mentioned over here. So from here the distances are again calculated for the new facility. Now once we have the distances we have simply used some product function which has multiplied the shipping cost into quantity into the distance and this will help us in calculating the total cost. But remember this is not optimized. So what to do for optimizing this cost? We want to minimize this cost in such a way so that the facility or the plant when it is located at this coordinate the cost, the total cost of this transportation is minimized. So in a way we have to find out this coordinate for the new facility which help us in minimizing the overall cost. So for this we will be using this function called as solver. In order to install the solver you can go to this function called as data which is available over here and here we have this function called as solver which is available in excel in freely to everyone. So here in the first step you need to set the objective. Setting the objective means defining that cell value where you have decided about your objective function which you are trying to minimize or maximize. Here our objective is to minimize the total cost. So we have decided cell number B20 to keep over here and because you want to minimize it so that is why it is we have selected the radio button of minimization. By changing variable cells means that which are your decision variables and how changing your decision variables is going to affect your objective function. As we know that here we are trying to change the location variables of the new facility these are our decision variables and that is why we have selected B18 and B19 as the non-linear equation that is why in order to solve this problem we will be using this function called as GA-RG non-linear over here to minimize our cost. So you have different option but we will be going ahead with GA-RG non-linear reason being the cost function turned out to be non-linear. So on solving this problem we can see that the solution has converged to the current solution and all the constraints are satisfied. It means the solution obtained is optimal and let us keep the solver solution. Now you can see from here that our value of x and y which was initially as 0 and 0 has now changed into around 500 and 600. So that means that this is the new coordinate at which you can make your new facility. So how do we take this decision to the next level? If we see very closely we can come back to our slides and let us refer it once again. So on using the solver we have seen the steps of solver which can be done by going through the data tab in that you will see solver option and you can follow the steps that we have discussed so far and enter your data in this option and on optimization you will get your cost which is very minimal and the optimal factory location is found at around 500 and 600 coordinate. So this coordinate is given by the gravity model and if you can go back and check this is also the coordinate of location of delis obviously you can see that one of the market at that site you can locate your factory and it can help you in minimizing the total cost. But suppose due to some constraint you are not able to make this factory at this optimally decided location then what to do? So the solution for this case is the managers can explore some nearby areas which are close to this optimal coordinates and maybe that particular area has got sufficient logistical infrastructure or other type of infrastructure or maybe the work force is easily available at those nearby places and that is a very suitable environment for locating your new factory. So we can say that this is this type of solution can help us in deciding the location of the new facility which can be further used for deciding your next level of decision about final facilities location and if this coordinate is not suitable for any reason the nearby area can be explored which serves your requirement to the best. So with this part we have completed the facility location decision using center of gravity method and this is again a very interesting approach which helps us in deciding the location of a single facility by considering the coordinates of the supply sources and their markets and along with them their supply demand is also considered and the distances are further calculated which is then translated to calculate the total cost and minimizing this total cost will give you the location of the facility where this total cost is optimized with this we will be ending today's session. Thank you everyone have a nice day.