All right, everyone, welcome back. Unlike other examples where I usually have a problem pasted here, walk you through an example of a transportation problem. Now, remember what we're doing here in this course. So we're doing linear programming, right, or LP problems. So LP for short and then what we're going to look at now is a subset or a special type of LP problems called transportation problems. Every this is going to look and feel the same. We still have a single objective. We have constraints. We have multiple decision variables to figure out. So it's all going to be part of the LP template, but these are very specific examples that have a very sort of specific and slightly different template that we're going to use. And once you recognize that you have a transportation problem, you can use this template, fill in the information and then we're going to use solver to find the best optimal solution. This transportation problem which is a special type of linear programming problem, it tries to find the best which could be money, time or distance way to distribute goods from sources called factories to multiple destinations. These can be retail centers or even customer. You can imagine big companies like Amazon or UPS or Fedex of people that have multiple things that are going to and from they have to transport lots of goods, they are very good at these type of problems to minimize their cost, to minimize time to become the best and leading experts in the business. So let's just do a small example to get started and follow along with Excel, let's get started. So we have three factories, Des Moines, Evanston and Fort Lauderdale. These are all cities in the US. Stores are in Albany, Boston and Cleveland purposely picking these. So we have A, B, C and D, E, F and you are in charge of shipping, okay? You have items in your factories and they have to go to the stores. Now, there's a lot of information here. So the one thing you're going to notice about these transportation problems is that there a lot longer to read. There's a lot more given, so you really have to organize this well, okay? So a couple things, we have shipping costs. So this could be presented as a chart, as a table. But if you're shipping things from Des Moines, well, you can ship things to three different places. We can go to Albany to Bbston or to Cleveland and the cost will be $5, $4, $3 for each item. If you're shipping from your factory in Evanston's, my old hometown, then you're going to ship it again to possibly A, B or C, Albany, Boston or Cleveland. This would be $8, $4 and $3. You can imagine I'm picking nice numbers here just so it all works out, but these costs are known beforehand. And if you're shipping from your last and final factory in Fort Lauderdale to either A, B or C, well, then those costs are $9, $7 and $5. So for now, just watch and pay attention. You can imagine in some word problem to follow, this will all be given. So notice we have three sources and we have three destinations. Right now, they match. They do not have to match in general. You can have a mismatch between your sources and your factories. Now our goal here, our objective, it wasn't clear by now. I want to minimize the cost. I want to ship what I need to these places. I haven't given you the demands yet, but I want to minimize my cost. Do that, I need to tell you what is demanded. Well, each store, each factory. So let's put D, E and F here. These would be what your outputs are. Each store can produce, let's say, 100 items from Des Moines, 300 items from Evansville and 300 from Fort Lauderdale. Now that's what can be produced, what are your demands? What are the requirements from the managers of these individual stores in Albany, Boston and Cleveland in A, B, C? Boston needs 300 items and Cleveland needs 200 items. So your job is to find the best way to ship these items from their sources, from the factories to the stores, but here's all the given information. You can imagine this could be a very lengthy word problem. This is all given and what do you as the manager, what do you as the person in charge need to figure out, how do we minimize the total cost? And of course here, the constraints would be I can't ship more than what is out. Put it in my factories. I also don't want to not meet the demand. I have to meet the demand of my stores. All right, so here's how we build this template out when we have this type of transportation problem. Again, this is just our basic template. We will expand on this in future examples. But for now, if you want to say this is our transportation example. So first thing we do is we create what's called a data table. This is where we take all the given information no matter which way it comes in and we put it nicely into a table. So what you do is you put your sources on the left as many as you need, Des Moines. We put Evanston, we put Fort Lauderdale and we always list our sources on the left. Expand the column as needed, so that it is readable as always and then we list our stores or destinations on the top. So we have three destinations here. If we had more, we would just add them accordingly and we create a nice little table with all of our costs. Right now, these costs are actual dollar costs. You can imagine this could be miles for distance or even time between the cities, as well. So we put all the numbers in and we center the numbers to make it nice. And now, we still have other givens that we have to put in. For example, I need to know what is output it by each factory, what source and I also need to know what is the demand. So to clarify that since it's not obvious in our table which is what, let's merge and center these three cells and we'll remind ourselves that these are sources. So I'll center this. If you want to get even fancy, we can change the orientation. There's our sources and then in the columns that are above, merge and center and remind ourselves that these are our destination. These are our stores. Let's find a nice way to put our output and demand numbers on this table. We call these a wrapper column. So right here, we're going to put our supply or output and I want to know how much can Des Moines with an S on it, there it is. How much can Des Moines produce? What is the maximum production? So I have 100 for Des Moines, 300 for Evanston and 300 for Fort Lauderdale. That is our output. Notice these numbers even though they go down the column, they correspond to the source going across, then I put my demand. Now, my demand is going to be a column. But just like their output, they correspond to the destinations demand. So how much does Albany demand? Well, Albany demand, again, this is given 300 units and Boston demands 300 units. Cleveland demands 200 units. So I create these wrapping columns and rows. You notice I have taken all the information that's written on the page and put it in a nice little table. Fantastic, once you have that, copy it. Take the whole thing once it's all beautiful and formatted, and copy it right below. Clear out the numbers in the middle, okay, and clear out the output numbers. Clear out all the givens. We're just really copying the formatting of the table and the inside of the table becomes our decision variables. This is going to be the right amount of things to send. Now it's no longer a data table, it is now going to be our shipment table. This is going to be the actual answer of what I want. So for example, in the first cell here, I want to know how many from Des Moines do I shipped to Albany. How many do I ship? So this is what solver will find us the output numbers. So let's put in some dummy numbers right now. So again, I don't know what the number is. I know that Des Moines, I'm just going to make the numbers here. How about 20? I'm going to put 20 across the board. 20 and 30 for all of them in Evanston. Again, these are all dummy numbers. These are all fake. This is clearly not the right answer. Nothing matches, but I'm just putting in dummy number. Now, I need to keep track of how many are getting sent out from Des Moines. Read this table, make sure you can read this table. For example, this says that De Moines is going to send 20 units to Albany, 20 units to Boston, 20 to Cleveland. So I have to keep track of how many Des Moines is sending out. To do that, I'm going to sum the entire row across. 20 plus 20 plus 20 does give me 60. Move over here and I will show the formula here. So I am summing C12 to E12 just how much is Des Moines putting out. So this is a calculated number and I drag this down. Evanston with these dummy numbers is putting in 30, 30, 30. So 90 totals being shipped from Evanston. And Fort Lauderdale, of course, as well, 40, 40, 40. Now the demand, how many has Albany received? How many units has Boston received? To do this, I will sum down the column. This is also just a formula and I sum the entire column. If I had more destinations and more resources, this table would just grow accordingly. But for now, it's a nice three by three table and I some across. So each one of these is formula on my wrapping column. The green cells, again, these are dummy variables there just put in to make sure that the spreadsheet is working. The one thing I am still missing is my objective. As a reminder, we want to minimize the total cost. Now, let's think about this for a second. This is my objective, so I'm going to color coded gray. If I send 20 units from Des Moines to Albany, how much does it cost me? Well, the cost up here is $5 per unit. So the total cost of 20 items from Des Moines to Albany will be 20 times 5, I multiply that as I go. Boston is $4 a unit, I'm shipping 20. So the total there would be 20 times 4 and then I would add it together with the total cost. Each one of these two blocks, these two tables are multiplied together. So we want to do some product one more time. So this feels familiar and I highlight the entire table. Some product does not care, that is not a single row. It can take any shape or size as long as it matches. So we'll do the three by three table and then the three by three table. Again, the order on which you input these does not matter, either for some product, you can mix it up. Close the parentheses and hit Enter, and I'll show the formula text for this, and you can check. If you multiply these numbers together, if you want put some zeros around us to make the math a little easier. But if you multiply these together, you do, in fact, get the so some product of all these numbers. You can see as you change the dummy cells that the spreadsheet is working, all the numbers are changing and this works out quite nicely. So this is the model, this is it. We're ready to use solver. Let me just pause for a second to make sure everyone's caught up. But in all the given information, again, this can come at you in a table. This come at you in a chart. This come at you with arrows. Word problem doesn't matter, I create my shipment table. The wrapping cells are now formulas. This one output measures across, demand measures down. Just add those up and then I do some product formula one more time for my objective. And so let me clear out my decision variables. I'm also going to make one change. I'm looking at my notes here and I realized I copied the number wrong. Please make this change in your notes on your spreadsheet. Instead of 300 as the demand for Boston, please set this to be 200. Sorry about that. Please change it to be 200, okay? Now, let's get over to solver and actually solve this problem. So I go to data, I go to solver. I said my objective cell, this is the gray cell that I color, this is the total cost. I set it to be a minimum and I want to change the variable cells, which will be all my green ones. It's okay that they're not in a single row, you can highlight whatever you want as long as they are connected. Here I have a nice three by three table, highlight the entire thing. Now, subject to what constraints. Let's have some constraints four, I had an entire constraint table. I don't have that anymore, but the constraints are there. They're just in different places. So what I want to do? I have really to constrain. I can't send more than the factory can produce. I have to be careful my output. Remember, each factory has a limit in terms of what it can output. So the limit that's calculated are these cells in the shipment table. Remember those are all added up and I want to make sure that, that's less than or equal to the given. Hopefully, that makes sense. I can't send anymore that is produced and I'm going to batch upload these by highlighting all three. Calculated numbers go first, less than or equal to the givens, the upper limit. Let's add another one. I also have to make sure that demand is met. So what is being shipped to Albany, Boston and Cleveland, the calculated numbers in my shipment table has to equal let's equal demand. It could also be greater than or equal, I guess if you want her to send more rather send more or less, but the key is you can't be under. If Albany demands 300, then you have to send 300. So we'll just use equality to get it. So let's say, equals and then we put in the givens. So we highlight the 3 givens 200. Again, make sure your says 200 and then hit OK and that's it. This is the very first example, so just has these two constraints. You can imagine this gets a little more complicated as things go on. But for now, don't send any more than the factories can produce and make sure that the stores receive what they have requested. Select the box to say make unconstrained variables non-negative and select simplex LP for these transportation problems. Remember, LP stands for linear programming. We hit solve, solver found a solution. Wonderful, so we hit OK. So thank you, solver and we have these numbers here. And now just make sure you understand how this works, this is saying the best possible way to do this. Again, you can try by hand to see if you can break it. But I promise you, you will not be able to. The best possible solution is to send 100 units from Des Moines to Albany, right? Now, that maximizes Des Moines output. Evanston's can send 300 units. So we're going to split that to be by 200 to Boston and 100 Cleveland, and then Fort Lauderdale with the 300 unit capacity gets split between 200 Albany, and 100 to Cleveland. So this is our answer and you would put all this, of course, in a summary sentence. So we would ship 100 units from Des Moines to Albany, 200 units from Evanston to Boston, 100 from Evanston to Cleveland, etc. And you'd write this out, but that's it. Again, try to see if you can beat it. The key here is that the minimum total cost, your objective is $3,900. You have found the best possible way using this model and using solver to get the goods where they need to go without more than each factory can produce. Again, appreciate the power of this and hopefully share this for a minute and just realize this is an LP. We have a single objective. We have decision variables, how many from each source am I sending to each destination and I have my constraints. So a slightly different flavor and then we're going to add on this. Again, these are called transportation problems and they are a subset of LP problems. All right, great job on this example. I'll see you next time.