All right, everyone, welcome back. In the last video, we did our first example of a transportation problem using the so-called table approach, but I'd like to do now is show you a different layout than the one that we have and this layout is a little bit nicer. A little more transparent, I think for smaller examples, it doesn't work as well for large. But again, for small examples, I think this is great and just gives you another way to sort of attack a problem if you get one of these transportation problems to model it inside of itself. So let's just remind ourselves of the data, the inputs from the last question. We have three factories that are supplying products Des Moines, Evanston and Fort Lauderdale. We have stores in Albany, Boston and Cleveland, okay? We're going to abbreviate, this is A, B, C and D, E, F. As a friendly reminder, all the shipping costs are provided. So they're here in the image that I screenshot from the last video. We have D to A, B and C, 5, 4 and $3. Evanston to A, B and C. It's 8, 4 and $3. And then from Fort Lauderdale to A, B and C, nine, seven and five. Those are all given, those will always be given. Our outputs the ability of our factories to produce certain items is always limited, right? You can produce an infinite amount of items. So we have 100, 300 and 300. And then our demands, what's being demanded by our stores, yet the call says, please show me the stuff immediately. A, B and C is 300 of 200 and 200. In the prior video, I start off with 300. But hopefully, fix that as we went along. And so our goal here is to minimize the total cost, that's our objective. And of course, our constraints are you can't ship more than what the factories can produce and you can't send less than what's demanded. You want to meet demand. So let's create an alternative transportation problem. This one's just a bit more nicer, I think to layout. So we have our origins or or factories, whatever you want to call them, just where things are coming from. And here, you'd list it out. And so since like in the past and the last one, we had Des Moines and we listed it once and it was nice. It gave us a small spreadsheet. But really, de Moines can ship to three places. So what you do now is you listed three times and then you have Evanston, and last time we listed it once. But now since you can shift to three places, we listed three times. If you can shift to four, you listed four. If it's shipped to five, five and so on. And then of course, you have Fort Lauderdale who struggle to say that word, Fort Lauderdale. And again, since it can ship to three places, you listed out. So you just give a big list. And again, this is why this doesn't scale necessarily. Although Excel has lots and lots of rows that you can fill in, so you can certainly use this for many, many problems. And you say, okay, so I have all these origins and where can they get shipped to? So we put our destinations, our stores, wherever they're going, customers and you list out each single one. So we have Albany. We have Boston. We have Cleveland, that's a longer spreadsheet. It's not as concise. It's not as pretty, but you listen. And now, you have every possible combination that you have. You have three choices for the first, three choices for the second multiplied together and you get nine. Fantastic, all right, what else do you want to keep track of? Again, what's the data what's given? We have our unit costs. We take our unit cost and these are all given, and you type them in. So five, four, three, eight, four, three, nine, seven, five. And the last thing you want to know call our shipments, these will be our decision variables. What do we want to send from each origin to each factory and we will color code these green, then we'll make this a nice pretty table, okay? Everything here is givens, so the next thing we need to do is put in our supply constraints. This would be our factories, what are we able to supply? We have Des Moines. We have Evan Stone. We have Fort Lauderdale and you want to think about what they're able to to produce? What is being, what we call this outflow, what is actually produced now? This we have to keep track of. Let's put in some dummy numbers here just because. So let's do two, two, two, three, three, three, four, four. These are the wrong answers. These are just dummy numbers, but let's count how much is actually going out. And remember, we have our sign and then we have our capacity and you have to stay under what actually is going out has to be less than or equal to the capacity of the plants. So I have 100 and 300, and 300 again. So I need to count, we need to find a way to keep track of what's being sent out. Okay, we can do that. This is a new formula for us. If I asked you to take this table and sort of count, there's two ways you can do it. The long way to do it, then not so pretty way is to say, well, let's add up de Moines. Going to Cleveland, add up De Moines going to Boston. You can do it this way like sort of select each one or maybe you can get clever and do some happen to be in order, but I'd just like to show you another way to do it. This is probably the more, I guess advanced way to do it, but you can use what's called the some if formula. And this says, tell me what I'm summing, what's my range? What is my criterion? And then well, what should I sum? So the range here, since we're looking for Des Moines is we say, go look for the table for Des Moines. What is my criteria? Well, I'd like it to match to search this entire column, find Des Moines and then let's range. What should I add up? Add these here and it knows to match the room. When you do that, close parenthesis and Enter, it correctly gives six. I notice if I update something say here to three, this is great. I want to drag this formula down. This would scale up with my model, nice little hack here. So let's put dollar signs on it. Both A4 and A12 to D40, 12. Hit Enter and we'll drag this down. So that's a nice little formula. I'll put the formula next on that, so you can see it. The easiest way to do it that we just kill a mosquito with the cannon probably, but it's a nice way to scale up. Add now our demand constraints. So we're going to make sure and solver that are outflow stays under our capacity. And then for our demand constraints, let's look at our factories one more time. I guess this will be our stores this time. We have our stores, we have our inflow. And here, we really want to be equals or even greater than equals. Let's do greater than or equal and this is given. You can always send more, I guess, but it's going to be not very cost efficient to keeps anymore. The demand here is 300. Again, 200 and 200 and our stores will list them off as Albany, Boston and Cleveland. We'll copy and paste that from above. And now, we have our tables. Let's do the same in flow formula. Let's use the some if formula. So it says range, where am I looking? So let's look under our stores. Now, what's the criteria? What am I checking for? Well, let's go actually get the store name and then what am I adding up? We'll add up what's being shipped in. So now, I can actually track of what's going to Albany. Again, I could have clicked the Albany, Albany and Albany and you can see four and three and two. It does add up correctly, but I want something I can scale up and drag down. There's no reason why these have to be sorted the way they are. So this is just more advanced. Put dollar signs on everything, so we can do, drag it down. Hit Enter when you're ready and drag to get. Put some borders on the tables just to make them a little more readable. We like pretty spreadsheets, we'll center everything for readability and there we have it. So this captures all the same information as the other spreadsheet. It's just a different way to see it. And again, because this is a relatively small example, we're able to do this template. The one thing and the last thing we need is our objective function. I'm running a little bit out of room, so let me zoom to make things smaller. I'll put it right here at the bottom. Our objective function as a reminder we are trying to minimize the cost. Now, this is a little easier to do. We color code this gray. If you want, pause the video here and see if you can come up with a formula that would find the total cost based on this spreadsheet, ready? What do you want to do? I'm going to ship this many things, my variables at these costs. So two things from De Moines to Albany will cost $5 each. Two things from De Moines to Boston, $4 each. Well, that is just multiplying and summing as we go. So of course, we're going to use our some product formula with all the unit costs and then all the corresponding shipments. The order on which you highlight doesn't matter. Hit Enter when you're ready and you get, at least with these dummy numbers, 1, 56. We're going to get hopefully the same exact answer as we got last time. But of course, we need solver to find that for us. So once your spreadsheet model is correct and you understand it, again, don't just copy me as I go through and make sure you understand it. Let's head over to data to solver and let's go ahead and fill in all the information, that's our objective function. Again, if it's color coded correctly, it's the gray cell helps us find it quickly. We want to make sure we're selecting min and we want to change our green variable cells. So one, two, three, make sure those are all set. Let's add some constraints here. And now, you can also see the benefit of this template. It matches what we're used to with our constraint table. So I have my outflows, let's do batch upload here. All of my outflows need to be less than or equal to all my constraints and then we'll add another one, and then all of my inflows need to be greater than or equal to my demand. To put equals here as well, you get the same answer and you hit OK. So we have our two demands, our capacity demand from supply and then from the stores with their select the box to make sure it's selected to make unconstrained variables non-negative and select simplex LP solver, found a solution. All constraints and optimal conditions are satisfied it. Hit OK and we're good. And if you remember, the 3,900 was exactly the same number that we got last time. So nothing's changed at all just the layout, just the organization of the spreadsheet. And from here, of course, we'd write a summary sentence. Maybe put some units, put some dollar signs on these things and then present it off to the next person on the team. All right, so just another way to do it. Pick the one that you like the best, pick the one that you understand. Try both the beginning until you find one that you really like as you work through the other problems in this corner. All right, great job on this example. I'll see you next time.
