Hi everyone, welcome back. Today we're going to learn how to use LP modeling inside Microsoft Excel to decide the right allocation of ads to maximize the number of people that will be reached. If you haven't done so already, please pause the video and read the problem. Try to get it set up. Remember whenever we go through the problem, we always want to think about to ourselves, what is our objective? Are we trying to maximize or we're trying to minimize something? What are the variables? What are the decision variables, the things as managers that we're trying to get the answer to, and then of course, what are our constraints? Ready, let's begin. I'll read it along and maybe you can highlight as we go through what are the important parts here. A candidate for mayor has allocated $40,000 for last minute advertising. Obviously this number is pretty important. That sounds like a constraint. That's going to be our budget constraint. Obviously, with any project you don't want to go over budget. For last-minute advertising in the days preceding the election, two types of ads will be used, radio and television. Imagine you're a consultant or the campaign manager for this particular one, you want to know how many types of ads to purchase. That's going to be the right answer, what is the best possible combination of ads to buy here, radio or television? For our variables, our decision variables, we can start to creating this and typing this away here. Let's think about what we want to do. We need the number of radio ads and we'll be very clear about what we call this. It's okay if they're long names, but I'd rather be clearer than not, and also the number of TV ads. Couple of things here, I get what you mean, but if some people just put radio ads and TV ads, remember Excel doesn't read, Excel doesn't do, I don't know it's English or other languages. It needs a number. I need the number of radio ads, the number of TV ads. The more explanation, the more words, the more clarity you provide in your spreadsheet, the better. We can always make it look prettier later or rewrite things. But at least in the beginning, I've seen a lot of people just say radio ads, but they don't recognize that I need to fill in a number. I'm going to color these decision variable cells green, as is custom. Remember, I like to put in some dummy numbers here just so I can make sure the spreadsheet is working. Let's pretend the answer is 4, and 5. I don't know. Maybe it's not, maybe it is, a good lucky you doubt it, but we'll see. Let's keep going. Each radio ad costs $200 and reaches an estimated 3,000 people. Cost is going to go associated to my budget and that's going to be a constraints, so maybe I'll start building that here. We built a little constraint table down below, and we're always interested in our objective. This will go here. We want to maximize, hopefully you read this and you saw this. We want to maximize the number of people reached. I like to put in parentheses what I'm after here, just as a Max. I have my variables, I have my objective, I have my constraints. Let's start putting in the constraints. Always label your constraints. I don't want to refer like, Oh, the first constraint. That doesn't make sense. Here, I have a budget. The way we're going to set this up in our table is we're going to think about a table that goes three row cells, three columns pass the variables. Here I have two, so I'm going to go all way up to five. My table is going to be, let's see, I don't know how long it's going to be, or I say how many rows is going to be, but it's certainly going to be two for the variables, and then three passed. We always label the cells with the left-hand side, the sign, and the right-hand side. I'll abbreviate LHS for left-hand side and RHS for right-hand side, and signs going to be here. What do I mean by this? How does this work? Think about if the answer is 4, if you purchase four radio ads, this is what we have done, the numbers, we can actually talk about it. It's going to cost you $200. My budget cost, obviously I'm going to have my radio ads and my TV ads. It's going to be $200 times the four radio ads that I purchased. This is just an example here. I don't know if this is four is the right number or five's right number, but I have four here. Then what else do I need to purchase? I'm going to buy five TV ads. How much will those cost? Will they cost $500? It's $500 times 5 TV ads that you bought. Then you calculate whatever that is. You have to ask yourself, is that less than or equal to the actual budget? Did you go over budget? I always make sure that you didn't and you wanted to just check. Is that okay? This is becoming a little bit of a math equation, even an inequality. But we want to multiply the cost 200 by the number of radio ads purchased. Hopefully that makes sense. If I buy four radio ads at 200 bucks, I multiply them together. What you do is again, only put in numbers, Excel likes numbers. Let's put $200 for the cost, the coefficient, and the number of radio ads, and then $500 for the number of TV ads. The left-hand side, this is a calculation. This is a number that's computed. This is a formula. What's it going to be? Now I could sit here and click 200 times 4 plus 500 times 5. But in Excel, there's a faster way to do that. Let's use the sum product formula. The sum product says, I will sum and multiply these as you go, you can highlight the two arrays, 4, and 5 go together and then 200, and 500 go together here, and you hit "Enter." You can check that 200 times 4 plus 500 times 5 is 3,300. Let me remove this. I'll actually put the formula texts in here for the left-hand side, just to show you this is. This is the only formula in our constraint table. This is the only formula we're going to use, everything else here is going to be a number. The sign corresponds to do you want to be less than or equal, or equal even, or greater than, doesn't matter, but we want to be less than or equal. It's okay if we spend all our money, the money is there, but the key is you don't want to spend money you don't have. Our formula for left-hand side is the sum product one more time, how's it working out. Just a shortcut. I also want to, just for good practices, let me put dollar signs here. Let me do my absolute referencing. I'll use the keyboard here to do that. That's Function F4, hit "Enter", and that'll lock it in. Notice it doesn't change the values, it'll just set me up if I want to drag this down, if I have other constraints. Good practice. I don't want to lock in my cells, the 200 and the 500, that can be problematic, because I do want this to scroll down as I go. What we'll do, our formula's with dollar signs. Then the right-hand side is just the budget. What do I want to be less than, 43. Remember I type the numbers in naked, don't put the commas, don't put the dollar signs in, just type the number. If you want to format it to dollar signs, you certainly can, although you don't. That's what this constraint looks like inside of Excel. Notice every cell gets a number, we don't type in equations, we don't type in sentences, Excel doesn't read, can't do. Let's keep going. Each radio ad cost $200, that 200 is now in my spreadsheet, ad reaches an estimated 3,000 people. Now my objective, this is a new one here, I want to maximize this number. What is the thing to max? Maximize the number of people. Each television ad cost 500, and reaches an estimated 7,000 people. What you do here is you put the coefficients for each one underneath. Let's do that. Coefficients for objective. Like what's the point? What are we trying to do here? What do we want to maximize? Where are you put this it doesn't actually matter, but just only put numbers as a reminder. The radio ad reaches 3,000 people. There you go. The number of TV ads reaches 7,000 people. Those are obviously important numbers, they should go somewhere on my spreadsheet. We tend to put them right below the variable they corresponds to. Then the objective, the max, which let's be more clear, the number of people reached. It's okay if you go over, just make the column wider, no big deal. Keep pretty spreadsheet's going along here. This is our objective cell, this is the thing that we want. This is the answer, how do we know we have the best possible way to do this? We usually highlight this gray. The colors may change, but just highlight it something, it makes it easier to find throughout the video series here. We will highlight it gray. Now how do I know how many people it will reach? Remember each radio ad reaches 3,000 people, so what do you do? You multiply that up. Then each TV ad reaches 7,000 people, so what do you do? You multiply that up, and then you add them together. You could do it by hand, but let's get fancy, let's use the sum product formula. I'll take 4 and 5, and then I'll do, 3,000, 7,000. Hit Enter when you're ready, and you get the total number. You format it, make it a number, make it all pretty. Since this is people, we don't need decimals here, maybe I'll add a comma. Now I can really test if this spreadsheet is working. Watch, if I change 4 to a 7 all the numbers should change, and you can of course check this offline just to make sure that this is actually working. Whatever the numbers are going to be, so let me put back my 4. But you can see the spreadsheet was working. If I miss clicked by accident, having these dummy numbers in there will help me find that as I go along. Anything else. Now she has stipulated that at least 10 ads of each type be used, elation, a request, a requirement, that all sounds like a constraint. Let's go to the drawing board for a second before I actually put that in here. I have my at least 10. Now this is actually two constraints, at least 10 TV, and then of course at least 10 radio. What does that mean? The number of radios here, just my dummy variables 4, this should be at least 10. Which one do I want? Do I want less than, or do I want greater than? What's the right one? If I want to be at least 10, be careful, English is a tricky language. If I want to be at least 10, means I need 10, or 11, or 12, I need to be greater than. The one I want is greater than 10, and of course my TV ads here, my TV needs to be greater than equal to 10. Let's think about how we would set that up here. Let's label our constraints, at least 10 radio, at least 10 TV. How am I going to copy? How am I going to count down? I really want to use some product one more time. Whenever you want to just copy the number down, you just want that 4 to appear, like test if it's a 4, you can multiply by 1. You think about it by 1. I've put a 1 here and a 1 here, watch what happens when I multiply them together. I'm just going to drag the formula down, and I will drag the formula text down so you can see it, what's happening. Notice the left-hand side, the thing that I want to test, the 4. How is this calculated? This is the sum product of what? One times 4 plus 0, when it's blank there's a zero, 0 times 5. Convince yourself that 1 times 4 plus 0 times 5 is actually 4. For all the same reasons, 0 times 4 plus 1 times 5 is just 5. Whenever you just want to count, or you just want the number to appear, you just want to check against the number, you put a 1 in there for the coefficient. Here at least would be greater than or equal, greater than or equal. Then we put in our constraints. Notice the sign column and the right-hand side RHS column are just numbers. The formula, the only calculation that has to happen and it's the same calculation every single time comes from the left-hand side. What else? So each television ad is $500 reaches estimated 7,000 people. The campaign mayor would like to reach as many people as possible, but she has stipulated that at least 10 ads of these types be used, we took care of that. Also the number of radio ads must be at least as great as the number of television ads. Oh boy. Another requirement, another stipulation, let's go to the drawing board and try to figure out what that actually means. The number of radio ads must be at least as great as the number of TV ads. Where can I work on that? So let's put this down here. So the radio ads must be at least as great. You have to be greater than or equal to TV ads. I'll use R and T to denote those variables. This is fine, but we're going to do a little bit of rearranging here. Normally on the left-hand side we have a number, you can do it, but we like to be consistent. We want the T to be on the other side. So let's go back to a little bit of basic math here. You can subtract T to both sides and you get R-T is greater than or equal to zero. These are exactly the same constraints. This is saying when you have two numbers and their difference is positive, it's saying that R is bigger than T. Stare at that for a second, think about that, convince yourself that that's true. Now, what do you do here, you grab the coefficients. You grab the coefficients. What are my coefficients on the variables? Well, when I have variable by itself, it's like one and then of course this is minus one. So those are the numbers that are going to go into our constraint table. You always grab the numbers. Do not is very common, but I guess understandable, do not enter letters or sentences or variables in these cells. People put R some people put minus T, that doesn't make sense. So if you want one and minus one, but that's where it's coming from. These numbers, these coefficients on the variables represent the equation. I copied down my formula, copied down my formula one more time. There it is. You can pause the video if you want to see it. This should make sense. I'm always grabbing my variables, the four and five, these are probably not right, just taking a guess here, but I just want to make sure that everything is working. Let's give this constraint a name. So we'll say R greater than or equal to T, you can something better if you want to just had remind you what it is so you can talk about it if you're checking. The inequality here is less than, greater than, or equal one more time, and then here, I actually want this to be zero. I want this to be zero. That comes from rearranging the sentence. So always want to number on the right, he tried to do it with a number on the right. I'll copy the formula text down to show the actual formula in the left-hand side. Now, I think we've gone through. At this point we would read it one more time. We would check it like crazy, make sure we didn't miss a constraint. These constraints come at you in any particular direction, there's no rhyme or reason to them, so just make sure we feel good about it. Okay, I think we're good. Next. Now I want the right answer. Now you can play the game like what is the right answer? If I have one and if I have six, I can reach 45,000 people. Is that good? I don't know, let's play. That one radio ad would cost me $200, six at $500 each. Well, that would be $32,000. That's well under my 40,000, I could probably go higher. So if I go higher, why don't I just do 10 ads here and maybe 20 ads here? So I'm still under budget, but I have some problems here because I'm failing the fourth constraint that a radio ads is not, see this negative 10 here, this would not be greater than or equal to zero. This particular answer wouldn't work, it's not an answer on our feasible region. So we really want to find the best answer. This is where you can see the power of Solver. Solver is going to find the best answer that will maximize this, satisfying all the constraints. We don't really want to sit here and guess and check and say, well, it's the best number that I could find. We'd like to guarantee that it is the best. Here we go. Data Solver and the pop-up box always looks the same. Let's go through, we're just going to go top to bottom here. We set the objective. The spreadsheets are going to get more complicated, they're going to get larger. Color coding really helps with this. It is always the gray cell. Click the cell, for me, it's B5. Then I want to, remember you can always minimize it. You don't want to minimize the number of people reach that'd be pretty bad. We just make sure we have max-min selected here. I remind myself in the spreadsheet that it's a max and the variable cells, those who are green ones. So color-coding helps do that. We highlight the green cells inside of the box. So 1,2,3 things are set. Now subject to the constraints. Inside of here, inside of this box, we hit "Add", and we get those three little boxes pop up. This is going to be our left-hand side, our sign, and our right-hand side. The left-hand side is going to be 1,200. I can change the sign. This is a drop-down, so you can always change it if you need. But here works out quite nicely. Less than or equal at constraint. Don't type anything. Never type anything just click. You can always make a mistake if you type it.. Just click that as you go and then we don't hit, "Okay," it'll actually take you back. You only hit "Okay" at the very end. It's a little confused but you hit "Add" and we'll do another one. We do the left-hand side and now it is greater than or equal to the right-hand side. So it lines up with our table. These are the three columns that go after our variable cells. Let's add another one. Our left-hand side is going to be this 20 that's showing here, greater than or equal to the constraint. Let's add another one, one more last one, we take our left hand side it is greater than or equal to our constraint. We can check this at the end. It's obviously easy to select the wrong box but hit "Okay," when you're all done and there they are. So, 1,2,3,4 corresponds to 1,2,3. Notice your ability to do Solver, to put their parameters, put the constraints in the Solver is only as good as your spreadsheet. If you missed one of the constraints, if you typed in the wrong number on your table, or do something about the solver is going to be wrong and the spreadsheet is going wrong and you're going to get the wrong answer. This is why you have to check along the way as you go. Other things that we could do for you, obviously, if you had a change or delete it, they're all here. If you wanted to just start over, you could reset all. This is a checkbox. It says mega unconstrained variables non-negative. That's saying that can these variables be non-negative. Should they always be zero or above? You can certainly come back and say, I don't want any radio ads, I don't want any TV ads. Perhaps that's the right answer, who knows? But you certainly don't want to come back and say, I want negative for radio ads. I definitely don't want them to be negative. That normally doesn't make any sense to have negative in the work problems. It's by default checked on Solver, and we usually we just leave it checked. It'd be really weird if you came back and said, I want negative 3 radio ads and negative 2 TV ads you might not hold onto that job for too long. So deg and make sure this is checked. Select a solving method. We're doing everything LP, linear programming. Please select "Simplex LP." These other algorithms are not appropriate for this problem. We'll talk about them later in the course. But Simplex LP is the one that you want. When you're ready, you think everything's good you hit "Solve." Depending on your computer, it can be a little slower, a little faster, but here's what you want. Solver found a solution. All constraints are optimally satisfied so we keep the Solver solution. We say, okay, and we hit "Thank you," and we're good to go. Now this is amazing. Appreciate what Solver has done. It's come back and says the best possible answer that you could find is 175 radio ads and 10 TV ads. You can check if you are guessing before as we were, if you want to go back and look, when I plugged in some random numbers, I did not get this answer of 595. My answer, which even wasn't even feasible, wasn't even allowed based on the constraints was much lower. This use of Solver takes away all guess and check work. It is the best possible answer. It removes that gut feeling. It removes that, I think this is the best one and gives you that confidence to say this is the best one. This is a wonderful tool to have to master, to be able to build these spreadsheets, because you can imagine going into a manager or some other colleague with experience and this is how we've always done it. The worst possible answer. I hate hearing that, but you come back with a more analytic approach. You can defend your spreadsheet, you can defend the model, and this will be the best answer. This is really nice. The one thing that I'd like to have my students do is at the very end, I do something what's called a summary sentence. Because it's one thing to find the answer, but then it's another thing to communicate it back. A lot of people don't realize what they actually have, what they're holding. Remember what you're doing here. Let's answer the actual question, the number of radio ads solution purchase 175 radio ads, 10 TV ads, to reach a maximum of 595,000 people. Here's where you would watch your units. Here's where you'd actually present the answer. You can imagine writing an e-mail to your boss and sending the spreadsheet, perhaps them as an attachment as needed, you would never just send someone a spreadsheet without any explanation of what's going on. This summary sentence is extremely important, reminds you of what you found, just helps you to remind yourself the units and does everything makes sense? Did I stay on our budget? I spent all 40,000. Do I have at least 10 radio? I certainly do. Hundred seventy five is larger than 10. Do I have at least 10 TV. Yes, I do. The bare minimum here. Is the number of radio ads larger than number of TVs? Absolutely. Thank you Solver, thank you very much. Here's my answer, 595. Fantastic, so this is a very simple example. I really want you to master this one. The constraints are pretty straightforward. You can imagine how does this get more complicated? We could introduce more complexity and the constraints, the numbers can get larger. We can have more variables. There's lots of ways to go from here. This is the first-level, first foundation. Start here, master this one, build it again from scratch. If you've got something very similar, could you do it? Save this model as a template for linear programming? If you want, just call this like LP template or something from here, all things will grow. We'll have more constraints go down. We'll have more variables going across. In the course the problems, will get a little more complicated as we go. Great job on this example. We'll see you next time.