Hi everyone. Welcome back. Here I have an assignment problem for you with a little bit of a twist. I really like you to pause the video after we go over what the problem actually is and try this one on your own. You'll see anything, even though we create an initial templates, there's always something later that needs to be adjusted, that needs to be taken care of. Here we go. In this example, our job is to assign one umpire crew to each city. We have crews that are currently in Seattle, Arlington, Oakland, and Baltimore, and they need to go to the next game at Kansas City, Chicago, Detroit, or Toronto. Now, you can only take one crew and assign them to one city. You should not send two cruise to the same city, so one crew per city. You also want to minimize the total distance traveled. You want to do this in the most efficient way possible. Now, baseball has a lot of unions and there's a union requirement here that you are not allowed to send any crew from Oakland to Toronto. That is too far to fly, just not allowed, is one of these constraints that you just have to follow. If you want, pause the video now and try to find the optimal solution. Look up your old templates for tensile quiz, pause the video and do it and see if you match me at the end of the video if you can do this. There's a little catch in here. I have the distances in miles between the city that is always given, and of course the X marks the spot here between Oakland and Toronto that is saying that you cannot fly someone from Oakland to Toronto. Pause the video if you haven't done so, then try to work this out. Ready? What did you do? Once we have our cost table, now here notice cost is in distance and miles. We copy the table, the whole thing [inaudible], and we clear out the costs and we turn the cells green. These are going to be the answers. This is going to be our decision variables. We're going to want all these numbers to be 0s and 1s. We can put some dummy numbers in here just to see what happens. One of course means, yes, send the Seattle crew to Kansas City and then blanks or 0s mean no. We must keep track of how many cities these crews were getting sent to, so let's create a total column and sum up the row. We'll drag that formula down, I mean the row. One more time, I'll click for you so you can see what's going on. I want this, remember what is bad here. I don't want it to be 0. That would say that the Seattle crew doesn't get assigned anywhere, and I certainly don't want something like this to happen where I have a 1 in two different spots that says send the Seattle crew to both Kansas City and to Toronto. That can't happen. Wherever this total is, the correct answer, then this number would actually be equal. The equal sign just to remind you, we're going to use a single apostrophe and then type equal. That's Excel's way of saying this is not a formula. You truly do want text. This Number 1 is just a number 1, it is also not a formula. The formula that comes in is in column F. I will drag down the dollar signs and the 1s across the board. Now I want to do the same thing for my totals for my columns of this table. I will sum down the column from B13-B16. This will keep track how many crews are being sent to Kansas City. I'm trying to avoid here is a 1 and then another 1 in the column. Again, what do these numbers mean? It says send the Seattle crew to Kansas City and send the Baltimore crew to Kansas City and now you have two umpire crews that are showed up at the same stadium. Of course, that's just going to mess things up so we don't want. We have our sum across the board, and I drag this across. I drag this across to keep track of all the sums. There's lots of bad is going on here. Obviously, this is just a dummy answers to make sure the spreadsheets working, but it certainly seems like it is. Once again, I really want this to be, I can have it equal to 1 or I could even do less than or equal to 1, and it'll all work out just fine, which one you want less than or equal or equal to 1. This is how we're going to tell Solver to meet our constraint. Hopefully you got pretty close to this while you were working this out. The last thing to include is your objective. Our objective is going to be, we want to minimize the total distance. Again, I like to write up in my objective label what I'm actually doing, do I want to minimize or maximize and what am I actually minimizing? It's not a cost here. It's actually a distance in miles, of course. We will color-code this cell. How do we do this? This is the same as before, so we'll do equals sum product. We highlight all of our costs, comma, and we highlight all of our variable cells. Close the parentheses and hit "Enter". I'll show this formula off on the side using the formula text function, and my distance in miles is 7,750 based on these dummy numbers. If you play around with the dummy numbers, hopefully this should update, and it does. This is where you can try by hand to guess or use your intuition and what the right answer should be. Because I checked things over, I realized I copied 1234. Let's fix that. They should all equal 1 in column H. Once we're happy with our spreadsheet, we head over to solver. As usual, we set our objective to the gray cell, for me it's in B22. We want to minimize the distance traveled in miles and we set our variable cells. These are always the green ones so we highlight the entire box of green cells. We have a couple of constraints here. The first one being that only one crew can be sent to one city. We'll batch upload these. This is me selecting all of F and that should equal 1. You can highlight the 1s or you can just type it. That's okay. Let's add another constraint. I want to make sure that each city only receives one crew. Highlight all the cities, and I can select equal. Then once again, or I can even do less than equal, doesn't matter. But you can select the one or type it. It's all going to work and hit "Okay". Once again you have your supply and demand. Make sure the box is checked to make unconstrained variables non-negative. Select Simplex LP, Solver found a solution. Thank you Solver and we hit "Okay". Now let's take a look at our solution. I was able to do this with 2,590. This is it, this is fantastic. Seattle goes to Chicago, Arlington to Kansas City, Oakland will go to Toronto. Wait a minute, and it's a reminder, Oakland is not allowed to go to Toronto. You found this answer, you fell for a little bit of a trap here. What's happening in this one? Yes, this is the optimal solution. Problem is this X in the given Excel is not treating it as a number, it's treating it as 0, so the cost to send the crew from Oakland to Toronto is 0. That's amazing. There's no cost to do that so of course it's going to be selective when you try to minimize the distance. What you have to do here, there's a couple of ways to do it. Probably the easiest way to do it is treat it as a constraint. I can't send Oakland to Toronto, so why don't we add one more constraint and say, hey, look this cell right here for me, E15, the cell that assigns Oakland to Toronto, well this has to equal 0. There's no way that cells allowed to be 1. I don't care what else you do, but that cell must be 0. That's one way to do it. Now when I run Solver, I have a new solution. Seattle is being sent to Detroit, Arlington to Chicago, Oakland to Kansas City, that's perfectly fine, it's not Toronto. Then Baltimore gets sent to Toronto. Each row has a 1 in it, each column has only 1,1 in it so this is the final assignment and I would write that up for my [inaudible]. The minimal total distance, the best solution meeting this requirement that Oakland cannot go Toronto is 4,580 miles. Watch out for the little X there. That's the trap. If you didn't fall for it, give yourself a little pat on the shoulder. Nice job here. The final answer for us is 4,580. Feel free to go back into Solver and make any adjustments as needed. You can put as many constraints in here as it will allow. Always make sure you meet all constraints. Great job on this example. I will see you next time.
