1 00:00:01,120 --> 00:00:07,350 Hey everyone in this video we will see another use case of Excel. 2 00:00:07,350 --> 00:00:14,610 This time we will solve the transportation problem a transportation problem is a common optimization 3 00:00:14,610 --> 00:00:15,040 problem. 4 00:00:15,720 --> 00:00:21,810 Well we have one set of suppliers and one set of destinations where there is demand of a particular 5 00:00:21,810 --> 00:00:24,000 commodity. 6 00:00:24,000 --> 00:00:29,550 Now the problem is that there is different cost of transportation per unit on different routes. 7 00:00:30,720 --> 00:00:39,870 So transferring one unit from this factory to this show cost two point nine units of money. 8 00:00:39,870 --> 00:00:46,900 And our agenda is to find out which factory will supply how much to which shop. 9 00:00:47,130 --> 00:00:51,710 So that the total cost of transportation in code is leased. 10 00:00:51,720 --> 00:00:55,980 Let me show you this example situation. 11 00:00:55,980 --> 00:01:02,670 Suppose you are required to satisfy the demand of certain commodity at three different shops as one 12 00:01:03,690 --> 00:01:15,480 as two industry with what is available at two different factories F1 and F2 the quantities available 13 00:01:15,480 --> 00:01:23,580 at F1 and F2 are three units and six units respectively. 14 00:01:23,580 --> 00:01:33,300 The demands add as one as two an extra year to Unit three unit and four units respectively as the total 15 00:01:33,300 --> 00:01:36,060 supply is the same as the total demand. 16 00:01:36,060 --> 00:01:41,450 Obviously there is at least one feasible solution to this problem. 17 00:01:41,790 --> 00:01:48,120 Also as the number of factories is less than the number of shops at least one of the factories is applying 18 00:01:48,360 --> 00:01:51,240 to more than one shop. 19 00:01:51,570 --> 00:01:58,610 So let us first put this problem into a tablet format. 20 00:01:58,860 --> 00:02:04,230 On the left I have just listed down all the constraint that I mentioned on the right. 21 00:02:04,230 --> 00:02:08,130 There is a table in this table on the left. 22 00:02:08,280 --> 00:02:15,500 We have factories F1 and F2 and on the top you can see shops s1 s2 and history. 23 00:02:15,780 --> 00:02:24,290 The first value in this table will tell us how much unit will go from factory one to shop one. 24 00:02:24,630 --> 00:02:29,410 The second value will tell us how many unit from factory one will go to shop too. 25 00:02:30,240 --> 00:02:32,980 And this is the meaning of this table. 26 00:02:33,120 --> 00:02:43,170 Each side is telling us which factory is sending how many units of commodity to which shop on the date 27 00:02:43,200 --> 00:02:50,340 I am right written the constraint the factory one has a constraint of supplying maximum 3 unit factory 28 00:02:50,370 --> 00:03:00,500 2 has a constraint of secured shop one is demanding to unit shop 2 is demanding 3 unit shop 3 is demanding 29 00:03:00,510 --> 00:03:01,660 4 units. 30 00:03:01,680 --> 00:03:06,070 So basically the sum of these two values should be two. 31 00:03:06,360 --> 00:03:11,760 These two values should be three next to value should be four and if you go horizontally these three 32 00:03:11,760 --> 00:03:12,980 value should be three. 33 00:03:13,200 --> 00:03:17,210 And these two value should be six below. 34 00:03:17,220 --> 00:03:19,500 I have made another table. 35 00:03:19,500 --> 00:03:24,960 This table is giving us the transportation cost from each factory to each shop. 36 00:03:25,530 --> 00:03:33,390 So if you want to transport one unit from F1 to S1 you'll be charged two point nine unit of money. 37 00:03:35,280 --> 00:03:42,270 If you are going from EV to do S1 you'll be charged to 1 3 units of money per unit of commodity. 38 00:03:42,270 --> 00:03:50,820 So the total cost of transportation will be the product of number of units you're sending from one factory 39 00:03:50,820 --> 00:03:59,370 to their job multiplied by the cost of transportation per unit plus the units that you send from that 40 00:03:59,370 --> 00:04:04,890 factory to another shop multiplied by the cost of transportation there and so on. 41 00:04:04,890 --> 00:04:11,100 You add all these individual costs to get the total cost to get the total cost. 42 00:04:11,090 --> 00:04:13,810 We have used a single formula called sum product. 43 00:04:14,130 --> 00:04:19,830 This formula multiplies elements of two different mattresses and adds them simultaneously. 44 00:04:19,920 --> 00:04:26,330 If you want to know more about such formulas there is a separate course on Microsoft Excel that we have 45 00:04:26,330 --> 00:04:30,390 hosted on udemy the link for which you can find in the description below. 46 00:04:31,350 --> 00:04:39,940 Now from these tables you can see our agenda is to minimise this cost and the sales in green color are 47 00:04:40,070 --> 00:04:41,260 variable tools. 48 00:04:41,310 --> 00:04:48,930 We want to change values in these sales so that the demand of all these shops is satisfied and we get 49 00:04:48,930 --> 00:04:53,660 the minimum cost just to prove that there are multiple solutions. 50 00:04:53,670 --> 00:04:56,690 Let us just by observation solve this problem. 51 00:04:58,140 --> 00:05:03,990 Let us try to put values here to fulfill the demand of shops for disposal. 52 00:05:04,320 --> 00:05:09,060 We will say to unit so that the demand of shop one is fulfilled. 53 00:05:09,060 --> 00:05:15,000 Now if one has only one unit left because it has a limit of three unit and we have already sent two 54 00:05:15,000 --> 00:05:18,340 units to shop one so it can only send one unit. 55 00:05:18,460 --> 00:05:22,910 So we are sending one year to shop to now to fulfil shop to demand. 56 00:05:23,190 --> 00:05:30,720 We have to send two units from factory two and we head will have to send for units from factory to to 57 00:05:30,900 --> 00:05:33,300 shop three. 58 00:05:33,400 --> 00:05:38,490 This is one solution and you can see since I have already applied the formula here it is giving us the 59 00:05:38,490 --> 00:05:43,040 value that the total cost I really got is fifty four point four. 60 00:05:43,710 --> 00:05:50,880 Now instead of sending two units from factory one if we decided to send only one unit from factory one. 61 00:05:51,680 --> 00:05:55,620 So we have one unit from factory one and one from factory two. 62 00:05:55,980 --> 00:06:03,780 Again for short two we are sending one unit from factory 1 and 2 from factory to for shop 3 also we 63 00:06:03,780 --> 00:06:10,120 are sending one unit from factory one and w 3 from factory 2. 64 00:06:10,350 --> 00:06:13,950 You can see still the total cost is forty seven point five. 65 00:06:14,820 --> 00:06:22,910 Is there any solution for which I am sure that it is the minimum cost to solve such problems in excel. 66 00:06:22,910 --> 00:06:32,590 We use goal seek goal take is available in the data menu and the solver I have solved here because I 67 00:06:32,590 --> 00:06:35,150 have added it from the add into the back. 68 00:06:35,850 --> 00:06:48,500 If you are not seeing this you go to defy option it go to options and go to the add an option manage 69 00:06:48,500 --> 00:06:51,020 excel at it go. 70 00:06:51,770 --> 00:06:57,740 Here you have to take this all over again if it is ending you will not be able to see this all over 71 00:06:57,740 --> 00:06:59,730 option here take this. 72 00:06:59,780 --> 00:07:07,010 Click on okay and you will get followed option to now let us go to the SSA sheet and solve this problem. 73 00:07:07,230 --> 00:07:14,750 First thing I have done is we have added to sales it these sales will actually add the values in these 74 00:07:15,050 --> 00:07:16,760 three sales. 75 00:07:16,940 --> 00:07:22,310 This will add the values in these three sales and the next sale will add the values of these results 76 00:07:22,970 --> 00:07:28,940 and the constraint that will give our solve it is that this value should actually be equal to the supply 77 00:07:28,970 --> 00:07:32,000 that this factory can actually do. 78 00:07:32,030 --> 00:07:39,110 Similarly for the shops we will add these two sales and to this bottom sell and will give the constraint 79 00:07:39,110 --> 00:07:45,170 that this value the sum of those two should be equal to this constraint. 80 00:07:45,170 --> 00:07:47,930 Now let's start typing is equal to some 81 00:07:51,370 --> 00:07:52,150 of these three 82 00:07:55,520 --> 00:08:00,310 know some from J seven to eleven 83 00:08:05,530 --> 00:08:09,620 and this sale will have some of these six and seven 84 00:08:13,360 --> 00:08:16,370 we can drag this also or we can continue to write it 85 00:08:19,350 --> 00:08:28,940 let's drag it and this boredom sail is already containing this form love some product to apply some 86 00:08:28,940 --> 00:08:36,020 product we laid is equal to some product and within brackets we first give the first metrics then after 87 00:08:36,050 --> 00:08:39,160 a comma we will select the second metrics. 88 00:08:39,290 --> 00:08:43,020 So now we will click solver option. 89 00:08:43,850 --> 00:08:47,550 The objectives L is SDL that we want to optimize. 90 00:08:47,590 --> 00:08:54,240 So the sale is J 17. 91 00:08:54,300 --> 00:08:57,720 Now we want to minimize this because this is cost to us. 92 00:08:57,780 --> 00:09:01,950 So we will select minimize by changing variable sales. 93 00:09:01,950 --> 00:09:04,200 These are these cells that are marked in green. 94 00:09:04,230 --> 00:09:06,690 These are the cells that lose value. 95 00:09:06,690 --> 00:09:08,180 We can change. 96 00:09:08,310 --> 00:09:09,710 Now we have to go to. 97 00:09:09,980 --> 00:09:21,410 Now we have to add constraint to look at the first constraint as this value should be equal to J name. 98 00:09:21,420 --> 00:09:24,440 You can see this first value cell reference. 99 00:09:24,450 --> 00:09:26,640 This is the calculated value. 100 00:09:26,640 --> 00:09:29,790 This is where you at apply these sum formula. 101 00:09:30,120 --> 00:09:37,320 And this second box is deep constraint and this is the fixed constraint that we have received in the 102 00:09:37,320 --> 00:09:38,930 problem statement. 103 00:09:39,100 --> 00:09:45,420 So we'll click add here next we will select k 8. 104 00:09:45,440 --> 00:09:51,030 It will take a 9 l 8 equal to a line 105 00:09:54,480 --> 00:09:56,970 M6 equal to M7 106 00:09:59,690 --> 00:10:01,670 index at 107 00:10:06,230 --> 00:10:09,150 M7 is equal to entering 108 00:10:13,060 --> 00:10:17,820 and at cost set of constraint is that all these values should be positive. 109 00:10:18,450 --> 00:10:24,340 So we will select all these and will delight to be greater than equal to zero. 110 00:10:24,450 --> 00:10:28,770 Another set of constraint is that all these values should be integral. 111 00:10:29,700 --> 00:10:33,960 So it cannot have values like one point five or two one main. 112 00:10:34,110 --> 00:10:42,510 So these should be in danger so religiously that these are integers and I think these are all these 113 00:10:42,510 --> 00:10:53,070 sorts of constraint so you can't allow so you can see the objective is set to J 17 till we have to minimize 114 00:10:53,070 --> 00:10:56,320 the sale by changing variable tables. 115 00:10:56,470 --> 00:11:00,010 J six to seven. 116 00:11:00,420 --> 00:11:09,600 And these are the list of conditions that we have now we have to select us all we matter because the 117 00:11:09,660 --> 00:11:14,070 variables in this problem have a linear relationship amongst them. 118 00:11:14,070 --> 00:11:22,830 That is why we'll be using these simplex linear programming solving method so let's click solve no. 119 00:11:23,770 --> 00:11:28,760 You want to keep this all your solution so click on okay. 120 00:11:29,200 --> 00:11:36,860 Now these values are actually failing by this all what we initially had some other values. 121 00:11:37,080 --> 00:11:41,410 Now these values are given by a solver and we have a total cost of thirty eight point one. 122 00:11:41,830 --> 00:11:47,590 If you remember initially we had something around 50 now this studied 1 one and this solution is the 123 00:11:47,590 --> 00:11:50,240 optimal solution. 124 00:11:50,300 --> 00:11:56,890 So this is how this all minimization and maximization problems using Excel and particularly using Solver 125 00:11:56,890 --> 00:11:59,630 in the exit. 126 00:11:59,680 --> 00:12:05,560 If you want to learn basics of Excel and some cool new tricks and graphs and excel you have a short 127 00:12:05,560 --> 00:12:09,370 course on udemy the link for which you can find in the description below. 128 00:12:09,850 --> 00:12:14,200 If you click this link you will get the course at a discounted price. 129 00:12:14,200 --> 00:12:17,380 Also if you like this video don't forget to like Sharon subscribe.