Hi everyone. Welcome back. In this example, we're going to now do a special type of transportation problem. Remember just where we are in the grand scheme of things, we have LP or linear programming problems. We then did a special type of linear programming problem called transportation problem. Now we're going to do a special type of transportation problem called an assignment problem. Assignment problem is exactly what it sounds like. We'd like to assign something to some other thing. Now in this example here on the screen, we're going to assign workers to offices, but you can also imagine assigning computers to jobs, or people to task. But for any assignment, it turns out you can use the transportation template to do that. Pause the video for a second and read the little problem on the screen just to get a sense of where we're going, and have a spreadsheet to work through this with me as we go along. We're going to rebuild our transportation template instead of just copying for the old one again, repetition. To least understanding, it's better to build it a bunch of times so you understand where all the pieces go. But here's the situation. Imagine you're in HR right now. A company has three new hires: Jones, Smith, and Wilson, and you can send them to any office in Omaha, Dallas, New York, or Miami. You brought in three new recruits. Offices have a need. You can send them anywhere. You can only do one candidate per office. You can't send a candidate to two different offices. The costs for the relocation only way you get a new job, they pay for you to move a little bit. The relocation cost provided by the firm are here. Jones to Omaha, for example, would be $800. In your job what's the goal here is you want to minimize the cost. You want to minimize the total cost. Compare this if you have the last example open, you can pair it what we did last time. Once we had our shipping cost table, we then created a wrapper row and a wrapper column with the supply and with the demand. Pause the video for a second. Think about what the supply would be, and think about what the demand would be from our local factories or our stores. What will go in these wrapper columns? Ready? Well, it turns out, Jones can only supply one Jones. The supply here would actually just be 1, and that's true for Smith and that's true for Wilson. This is just one person, not actually one dollar. Let me fix that is just the number here. Jones can only send one Jones elsewhere. You can't send Jones, split them and send them to different places. Same thing for the offices. There is a restriction here only one candidate per office. The number of people that we're sending to Omaha, demand would be 1, 1, 1, and 1. This is what makes an assignment problem different than a general transportation problem. This is always true. That's difference. In an assignment problem, there are no wrapper columns on the cost table. It's assumed to be one and since they're always one, we don't write them. We actually leave as completely blank. We leave it off. We've been turned off all the formatting as we go through. You're never going to see a wrapper column in an assignment problem. It's implied to be one. Everything else is then the same. We take our cost table, and we copy it and this will now become our assignment table. Use we're shipping table how many we're going to do, our assignment table. We have our offices at the top, we have our workers at the bottom, and now we still do, we clear out the middle, and these become our decision variables. Whose going to get sent where? What could happen? You can imagine something like a couple of ones could appear. Well, let me turn off the dollar formatting. Let's think about what this means. If you have some row with two ones, it means you're sending Jones to both Omaha and to Dallas. One is going to be like a yes like we send one Jones to Omaha or the Dallas, and zero or blank is like a no. This obviously is a problem. I can't send Jones to Omaha and Dallas. How do I prevent this from happening? Well first off, just like before I need to keep track of the sum of the row. This is important now, so my sign, it doesn't need to be for this to actually work. I can't have zero. That would mean that Jones doesn't get assigned. I can't have two, that means Jones is getting assigned two offices. What can I have, two single apostrophe equal sign here to get the equal sign, it has to get to one. Where's that coming from? Each candidate gets assigned an office. You can't just say, welcome to firm Jones, we don't have a home for you. Doesn't work that way. This is what you're after. This is going to be your constraint. Notice how we're setting it up over here on the right, and that's fine. Solver does not care where things go, only you should care where it matches. This is obviously no good. Just to keep things as dummy numbers that make it a little bit of sense. We can put in some dummy numbers just to make sure the spreadsheets working. This idea of adding up across, and these are formulas inside of F11, F12, and F13, I want them all to equal, and then exactly one. You don't need to put the equals one, we could type this right into solver but in the interests of transparency, we're going to write this out. Then, of course, we're going to keep track of our total across the bottom row. Now, what does that going to keep track of? Well, I add up everyone go to Omaha. This is the constraint now that only one candidate per office. I really want this number. It could be 0. I have three candidates and four offices. That can happen. Someone's not going to get a candidate. But I just want to make sure that this number is less than or equal to 1. Should never have a column with multiple ones in it. What does that mean? It means I send Jones to Omaha, and I send Wilson to Omaha. That's not allowed only one candidate per office. Sign is always going to be less than or equal, and then I'm always just going to type 1. Center it and make it pretty. The only formula in this table are the usual formulas that we use for the transportation problem, or I sum each column and then I sum each row inside. Last but not least, add your objective. Here we want to minimize our relocation costs. We will color-code this cell gray, and what's this going to be? How do I know how much is going to cost? Well, think about it. If I send Jones to Omaha, it's going to cost me $800. To keep track of that I would multiply $800 by wherever Jones gets sent. If he get sent to Miami let's say it was 0 under Omaha and 1, then I will want to include the $1,100 relocation costs. Hopefully, you see where this is going. This is once again a big sum product of all the costs and the decision variable. In the text so you can see the formula, but it's once again a good old some product. This is a true cost in dollars so we can format it as such. Now the question is, what's the best way to assign people to these offices minimizing the total relocation? The spreadsheet model is ready, head over to data, head over to solver. We're going to set our objective cell, which for me is in B19. We're going to minimize this one. This will maximize the relocation costs. We're going to have a whole bunch of variable cells here. Now, this is a four-by-three table, and we're going to subject it to the usual constraints. Let's batch-upload these things. I want the candidates to be assigned one office. Now, this is where really need the one here because I certainly come in here and just type one, although you could also just link it. Either one works, but I want the candidate equal 1, so F11, F13, and then I want to add another one, or I take how many candidates are being send to each office, total row now instead of a column, and I say I want that to be less than or equal to. Once again I can type one or in this case just to be different, a highlighted cross, and I hit "Okay". This should feel familiar. This is all the same. Our setup for the objective function, putting it in solver, check the box, that says the variables can be non-negative, select simplex LP, and it's solved. Solver found a solution. Thank you, solver. You hit "Okay". Now we have the solution. Once again the summary sentence is extremely important. Again, Excel the computer will be very good at finding the answer, but it will be very terrible at communicating or explaining model back to your boss or to a client. Our summary sentence here what does it mean? Remember, zero means no and one means yes. We would send Jones, Smith to New York, and Wilson to Omaha. For a total relocation of $2,400. Again, I love doing this because I see people still watch it. They say, there's no way that can be the best answer. I say go find me a better one. They sit there and play and they tried to do it and of course, they can't, 2,400 is the absolute minimum, the best answer for this relocation. Nice little assignment problem. Again, the only big difference, what's missing here are the wrapper columns in the cost table, and you have an assignment problem you don't need those, you leave those blank, really do miss them. You can certainly put ones in there but it's best practice to leave it off. Great job on this example, I'll see you next time.
