 Dear participants, welcome to the course on supply chain digitization. It is jointly being taught by Professor Priyanka Burma, Professor Sosmetha Narayana, and Professor Devapratadash from IM Mumbai. In today's lecture, we will continue the discussion on green field analysis. And specifically, we will focus on how to do green field analysis using Excel solver, and we will also see how can we do the similar analysis using any logistic software. So, before we proceed, let us quickly summarize what we discussed in last two lectures. First we focused on what is digital twin. Then we also discussed various types of digital twin in supply chain and operations management, product level digital twin, process level digital twin, company level digital twin that is enterprise level. Then we also discussed supply chain network level digital twin and also network of networks level digital twin. Then we discussed the digital twin framework from supply chain point of view. Then we focused on a case study to perform green field analysis. And specifically, we developed an optimization model to determine the optimal location of distribution center, so that transportation cost is minimized. We also discussed the results of the optimization model specifically the Excel solver output. We also have shown the output of the green field analysis done using any logistic software. So, in today's lecture, we will explain the Excel solver like how green field analysis can be done using the optimization method in Excel. In the previous class, we only shown you the output. In today's class, we will do a hands on and discuss how can you do the green field analysis and get the exact same output as we have shown in the last class. We also will perform the green field analysis using any logistic software. In the last class, we showed you the output. So, in today's class, we will show you how you can do the green field analysis using any logistic software, so that you can also reproduce the similar results. So, with this, let us relook at the case study, which we discussed in the last class. The senior management of a pharmaceutical company is planning to set up a distribution center to cater the demand of western region of India. And if you see, we have four markets in western region, Pune, Mumbai, Ahmedabad and Surat. We have their yearly demand. We also have the exact location of this market that is latitude and longitude of each of these four places. Our objective is to find the optimal location of the distribution center. I have four customers, customer one is located in Pune, customer two located is in Mumbai, customer three located is in Ahmedabad and customer four located is in Surat. And we have yearly demand at each of these customers location. So, we need to meet this demand. So, where can I locate the distribution center? So, that my total transportation cost is minimized. So, let us say if we locate it here, then I need to transport products from DC to Mumbai, from DC to Pune, from DC to Surat, from DC to Ahmedabad and these are my distance. So, I need to minimize the transportation cost that is minimize the distance multiplied by how many units I am sending to this customers location. So, that I need to minimize. So, with that objective in mind, we developed an optimization model in the last class. And if you see x i and y i are coordinates of market i. So, x i is x coordinate that is latitude of market i, y i is the y coordinate that is longitude of market i. Then we also have demand i that is demand of each of this market. Then x dc represents the location of distribution center which is a decision variable. I do not know the value yet, but it represents what would be the optimal latitude of the distribution center. y dc represents the y coordinate of optimal location of dc. That means what would be the optimal longitude of the distribution center. So, these two value right now I do not know this will be decided based on the optimization model. Then transportation cost per kilometer per unit is represented by dc and z is the total transportation cost. So, we have distance between each market from the optimal location of dc. I need to multiply it with the demand because these many units I need to transport from dc to demand location. So, if I sum it up from 1 to 4, I will get it for 4 markets and then that is value should be multiplied by transportation cost per kilometer per unit. Then I will get total cost of transporting products from dc to customers locations. So, these value I need to minimize. So, I have to minimize z. In the last class we showed you the excel solver output. We also showed you the output of any logistics model. And if you see the output says that my distribution center should be located here. And optimal latitude is 19.09 and optimal longitude is 72.87. So, this is my optimal location of dc with latitude 19.09 and longitude is 72.87. Now, this is now in today's session we will try to reproduce this result using excel solver as well as using any logistics. So, to do that first we need to open the excel file greenfieldanalysis.xlsx then make sure that you have solver add-ins is installed and then follow the steps which I am showing it to you. So, for that I will now go to excel solver. This is the data. So, let us this is the optimal value, but I do not want to show the optimal value to you right away. I want excel solver to give you the optimum value. So, now let us look into this excel file. So, initially this is my initial value. So, latitude of dc, longitude of dc this is my decision variable. I do not know this value yet. So, let us assume that initial value is 20 and initial value is 72 for longitude. Now, if that is the longitude and latitude of dc then what would be my distance. So, I have this data given in the case Pune, Mumbai, Amandabad, Surat these are four markets. They are demand their exact latitude longitude and also cost per kilometer per unit which we have assumed 1 dollar. Now, we have to find out the distance from Pune to this dc, Mumbai to this dc, Amandabad to this dc, Surat to this dc and this is the distance which is coming up 254.54 kilometer from Pune to this dc if latitude is 20 and longitude is 72. Now, how did you get this value? So, for that we need to look into this formula which we have discussed in the last class. So, now I need to first calculate d long that is deviation in longitude value which is nothing but y dc minus y i, y dc is this value. So, longitude of dc, y i is a longitude of ith market that is Pune. So, that is how d long is calculated which is in column h. So, if I click it over here you will see d long equal to radians b 8 minus e 2. So, b 8 is longitude of dc. So, this is the longitude of dc that is y dc and e 2 is longitude of Pune that is market 1 that is y 1 and we have also applied radians because radian is the standard unit of angular measure. So, we will get the value of d long. Similarly, I will also get the value of d long for Mumbai, Amandabad and Surat. The same formula I have applied. Now, we need to calculate d lat. So, d lat is deviation in latitude. So, for that first I need to get the value of x dc that is latitude of the dc minus latitude of ith market. So, if you look into this formula radians b 7 minus d 2, b 7 is latitude of dc minus d 2 is latitude of first market because I equal to 1 here that is Pune and we have again applied radians over here. The radian is the standard unit of angular measure. Then same thing we have calculated for Mumbai, same thing we have calculated for Amandabad and same thing we have calculated for Surat. So, we got d long as well as d lat value for Pune, Mumbai, Amandabad and Surat. Then I need to calculate A. What is A? Sine square d lat by 2 I have d lat value then cos into x i into cos x dc into sine square d long by 2 I also have the value of d long. I also know x i and x dc. So, what is x i? x i is nothing but latitude of ith market and x dc is nothing but latitude of the dc. So, if we use this formula we will see sine g 2 by 2. So, g 2 is nothing but d lat by 2 then square. So, that is sine square d lat by 2 plus cos of radians of d 2 that means cos of radians of d 2 means latitude of Pune. So, that is x 1 then cos of radians of b 7. So, cos of radians of b 7 is latitude of the dc multiplied by sine h 2 by 2 sine d long by 2 then square. So, we will get the value of A same way you can get the value of A for Mumbai, Amandabad and Surat. Then we need to get the value of C what is C? C is nothing but 2 times A tan 2 then square root of 1 minus A comma square root of A. So, in excel we have used this value and if you enter you will get the value of 0.03995. So, I can get the value of C for Pune, Mumbai, Amandabad and Surat. Using the formula now I got C, I got A, I got d long, I got d lat, I need to calculate distance. So, distance between the market and distribution center is r into C. So, what is r here? r is 6 371 which is a constant value I need to multiply it with C which you already derived. So, if you do this we will get the distance from Pune to this dc for which latitude is 20 longitude is 72. The same way I will be able to get the distance of Mumbai from the dc distance of Amandabad from dc distance of Surat from dc. So, now whole sheet has been explained to you. Now, what is the cost? Cost is some product of k 2 to k 5 that is distance I have to multiply this distance with how many units I am sending. So, from Pune from dc to Pune I will be sending 157 680 units and how many kilometer I have to travel 254.54 and the cost per kilometer per unit is 1. So, therefore, I am multiplying 1 into this into 254. So, if we do that I will get it for one market. I need to again sum it up with 1 into this into this 1 into this into this 1 into this into this. So, all of this can be easily done using formula sum product. So, we will get the cost value this. Now, this is not the optimal cost because we have not applied any optimization yet. This is the cost if latitude of dc is 20, longitude of dc is 70, but is this the optimum value that is what we have to find out. So, for that we will go to data and then solver make sure you have solver installed. Once we go here I need to set the objective value. The objective is to minimize b 9 minimize this cost this is b 9 by changing variable b 7 to b 8. So, these are my decision variable which are in yellow color I need to change this. So, optimization model will tell me what is the optimal value this value will be changed and then I am applying non-linear method because in this case if you see the distance formula is non-linear. So, therefore, I need to apply non-linear method. If you press solve you will say solver has converged to the current solution all constants are satisfied. Although there is no constant here, but solver has found an optimal solution and optimal value of latitude is 19.09, optimal value of longitude of dc is 72.87 and if you see this is exactly same as Mumbai location and that is why from Mumbai to this is distance has become 0 also. So, if I locate dc at this location 19.09 comma 72.87 I will get the optimal location of the distribution center and the total cost of transporting products from dc to demand points will be minimized. So, I am sure all of you understood how excel solver works. Now, for a small problem this is very easy because I have only one dc to determine and I have 4 markets, but imagine if you have 4000 markets located across the countries then where should I have dc? Can I do it using excel solver? I cannot do it because excel has limitation. So, therefore what should I do? So, is there any method? Is there any software which will help me to do this? Now, if I have 4500 markets located across the countries and I need to open let us say 3 dc, 4 dc, 5 dc. So, then excel solver would be difficult to solve. So, I need to take help of some other software which has these facilities, but excel solver is good for a small scale problem. So, we have done this step already and we got this result using excel solver. Now, let us make this problem little advanced. So, if you see the data we have taken 4 SKUs. So, in the previous case which was very simple we have only one product at each market Pune, Mumbai, Surat, Ahmedabad. Now, I have 4 different SKUs, SK1, SK2, SK3, SK4 and we also have daily demand. So, in place of yearly demand we have daily demand. So, imagine the complexity has increased. So, earlier I had only one product, now I have 4 different SKUs and imagine if these SKUs increases and typically company has multiple SKUs. Let us you have 20 SKUs and 40 100 markets located across the country. So, then how do you find out the optimal location of DC and the demand is also daily. So, it will be difficult to solve in excel because excel has limitation, it cannot solve a optimization model which has more than 200 decision variables. So, therefore, we need to take help some other software. So, any logistics is one such software which helps us to do green field analysis and get the results in seconds. So, if you want to read more you can go to this website anylogistics.com and you will be able to find out the benefits of any logistics specifically if you want to do green field analysis. So, now let us see how we can do green field analysis, how we can develop a supply chain digital twin using any logistics as per as green field analysis is concerned. So, this is the output of the green field analysis which we have done and I am showing it over here. So, before I go and do the hands on this is how the screen of any logistics looks like. So, this is version 3 any logistics version 3 PLE means personal learning addition. So, if you are a student you can use this addition and it has 3 module one is green field analysis, second one is network optimization and third one is simulation. Let us say in our case we are focusing on green field analysis. So, we will go into the module of green field analysis. When we will do network optimization we will see the module of network optimization and in this course we are not doing simulation, but however you can perform simulation using any logistics. So, let us first open the any logistics software and see how we are getting this output. I already have the any logistics installed, but if you do not have this install please go to this website www.anylogistics.com then download any logistics. Personal learning addition you can download enter the relevant data in logistics in the green field analysis module run the analysis. So, if you are more interested to do it on your own then I would request that you go to this website www.anylogistics.com then under that there is an academic tab under that ALX educational toolkit. So, you will get all help over there. They have explained step by step how to do green field analysis they have also given examples data everything is given you just have to change your data set and run the model. So, it is very easy for you to do it I have already done it myself I have changed the data and put an Indian context you can also do depending upon your context depending upon your data you need to change the input value and input parameters. So, now let us go to the any logistics software this is how it will look. So, now I already have the data enter. So, I will show how the data will look like. So, this is network optimization so I need to go to green field analysis. So, this is the green field analysis I will show you how the data look like. So, the customers I have 4 customers Pune Mumbai Amdabad and Surat. These are all customers then I have disease and factories right now I do not have anything you can see here nothing is there then demand I have. So, as you have seen I have demand at 4 different locations Pune Mumbai Amdabad and Surat and each location I have 4 different products. So, if you see at Pune I have SQ 1 SQ 2 SQ 3 SQ 4 I have in Mumbai Surat Amdabad and my demand is periodic demand and if you see the click over here you will see the demand in Pune per day is 130 units. So, you have to enter this value demand at Pune for SQ 2 is 90 units for SQ 3 demand at Pune is 95 units and so on. So, similar way you can enter if you have in place of 4 markets if you have 5 markets you add one more market and enter the demand per day. Then we have their locations of if you see Pune location Mumbai Amdabad and Surat the Pune location has latitude 18.52 comma 73.85 Mumbai location has latitude this longitude 72.87 Amdabad latitude 23.02 longitude 72.57 Surat latitude 21.18 longitude 72.83. So, I have 4 products you can see SQ 1 SQ 2 SQ 3 and the units are in pieces. This way you can enter the values and one thing I want to mention right now I have entered the latitude and longitude manually, but you also have auto field coordinates. Suppose you enter the name correctly let us say I want to enter the name of Akola. If I enter Akola automatically the coordinates will be auto field. So, now once all the data are entered correctly then I will go here and run this GFA experiment if I run this I will get the output and if you see my output is here. So, it is telling me where my DCC. Earlier this GFA DCC was not mentioned, but now after running it is telling it has to be exactly at same location as Mumbai and I can also use this connector then it will also tell me how these locations are connected. I can also use label then it will give me the name. So, I have now name Pune Mumbai Surat Amdabad. So, all this information I will get it on the map. So, if I use any logistic software I can see that this is my advantage and not only able to see the location of the distribution centers. I am also able to see where my customers are located how far it is from the distribution center I can visualize it in a better way. This is for a small problem with 4 customers. If I have more customers then any logistics would be much more useful the visualization will be much more better and it will be needed also. For the decision maker it will be very good to visualize it properly. So, now let us see the results. So, if you see the results new site GFA DC the location is latitude 19.096 longitude 72.87. So, the same value which we got in excel also 19.0972.87. So, the same value has been given by new logistics then I can also see the flows. That means, from DC to various demand points what is the flow. So, DC to Amdabad for SQ 1 I am sending how many 43800 the distance is 437.988 and this is the flow cost. So, same way I can see from distance from distribution center Amdabad for SQ 2 how much units are sending 34675 and then distance in kilometer is 437.988 and flow cost is this. So, you will be also able to see not only the new site and its location you also able to see the flow from DC to this various demand points how many products are flowing. I can also see demand by distance I can also see distance by demand. So, these are all possibilities are there and the visualization will be much more better if I have in logistics software with me I will get a much better visibility I will be also able to see where are they located and how the flow of products are happening from DC to various demand location. So, again I urge all of you to go to this website download this educational toolkit and I am very sure you would be also able to do the same analysis on your own. Only thing you have to do you have to change your data set because they have given the example from European point of view you have to change it based on your requirement based on your company based on your country. So, latitude longitude has to be changed demand data has to be changed then you can also perform the same analysis. So, till now we have seen how infill analysis can be done using excel we also have discussed how it could be carried out using analytics. We have discussed the advantages of analytics in this class. So, now what we will do in the next class we will see how network optimization can be done using analytics in detail. So, thank you so much look forward to seeing you in the next class.