Hi, everyone, welcome back. Today we're going to do a mixed integer example. This is another type of LP problem or linear programming problem. We've seen all the pieces of this before. This problem, although it's short and simple, is going to sort of put some pieces together and allow you more flexibility when doing these LP problems when using solver to find optimal solutions. If you haven't read the problem, please do so. Pause video and read it. Think about as you go through it, what is the objective? What are you trying to maximize? What are you trying to minimize? Think about what your variable are? If you're the consultant to this cafe manager, what are the answers that you want to come back with? Last but not least, think about your constraints. What are the things that are limiting? And now we've seen some more complicated or subtle problems where the constraints are both listed explicitly, here there's a nice table for us. Or things that are sort of hidden behind the scenes in between the lines that you have to be a little bit more clever about. So pause video, read the problem if you have not done so already and then we can go through it together. Ready, let's begin. Bolsa de Cafe is especially coffee shops selling custom blends of coffee using three types of beans. So I'm a store owner, I have three types of beans, A, B and C as they're called down here on the table. And I sell two products, 20lb bags of blended coffee beans, whole. So these bags, they're beautiful, nice logo, very attractive, I want to sell them. And then individual bags of grinded beans sold by the pound in bulk. These are my two products. The grinded beans can be produced in any quantity. Both bags are composed of three different beans. Again, A, B and C from the table below. 20lb bags sell for $85 and the bags of grinded beans sell for a 1.50/lb. Have to be a little careful here because the first object that I'm selling are just a number of bags. And then the second thing that I'm selling is pounds of beans. So I have two different units going on and maybe just want to keep track of that for my understanding. But let's start to put down our variables here. I have a number of 20lb bags. The 20 in the 20lb is just a label, I don't use it for anything. It might as well be red bags or blue bags or whatever, but I don't use the number 20 for anything. And then I sell pounds of beans, I sell pounds of grinded beans. These are two variables, so I will color code them green. The next thing I want to do is think about, what am I trying to maximize? I definitely want to maximize my profit, so this will be my objective. And I will remind myself that I want to maximize my profit. Definitely do not want to minimize my profit. And now you think about what is the profit formula? What is it actually equal to? How do I make money? For each bag that I sell, it's given in the problem, these sell for $85. And we usually put that number right below the variable cells and we'll format it to dollar signs. For grinded beans, for the pounds, it's 1.50/lb, I'll put in 1.50. And once again, format that to dollar signs. We can put some dummy numbers in our variable cells. I like to put dummy numbers in to make sure that the spreadsheet is working as I go along. If there's an error, I don't want to find it at the end. So I will completely make up numbers here and have about 4 and 5. I'm going to format the pounds of grinded beans to show a couple of decimals here. I want to see because I can sell beans in any quantity, so the problem says. I want to know if the right answer is 5lb or 5.5lb, 5.25lb. So I do want to see the decimals here. Sometimes when you type the number in the default is to show it as an integer. And that could still give me the right answer. But then my formatting would be wrong and I wouldn't actually know the right number. Same here with pounds of bags, we'll put that number in home. All right, so what is our profit function? Let's color code this gray just so that we have it easy to see. This will be a small spreadsheet compared to some other ones that we've done. But let's follow best practices as we go along. So if I sell 4 bags for $85, I would multiply those numbers together. If I sell 5lb of beans for a 1.50/lb, I would multiply those numbers together. And then my total profit would be the sum of all that. Where am I going with this? You guessed it, sum product. Highlight the variable cells, highlight the money that each makes, close the parentheses and hit enter. I will show the formula using the formula, text function. So you have it there and you can play around with this. Make sure your spreadsheet is working, make sure you understand what this thing is doing. You can put 6 in or something like that and the number will update. Whatever your dummy number is, you should hopefully see this thing update as it goes along. Okay, so I have my profit function, everything looks great. Now I just need to put in my constraints. So let's make a constraint table. This is back to LP problems. So we're doing not transportation here, we're not assigning anything. This is back to LP, this is called mixed integer. You'll see why in a second. As a reminder, we've done these before, we go three columns past the variable cells. So I have B, I have C, so I need three more, D, E, F. Off I go, let's put in some borders just to make this readable and we'll make a nice little table. And now think about what this is. So you're producing these bags of coffee or you're selling them grinded as a mix, as a blend. And you have three beans that come in from your supplier, so I have bean A, B and C. I only have as an amount available 2000lbs for A, 800lbs for B and 200lbs for C. That is a limiting resource. I can't use more than that for bean A, B and C. So the number of bags that I make or the pounds that I've grinded beans that I sell, no matter what I do, I still can't use more than 2000. So I label my table left hand side sign and right hand side and hopefully you can see this is all going to be less than or equal to. All three are just limiting resources, 2000 for A, 800 for B and 200 for C. What goes in to the columns of the table is the amount needed for each product. So I need 30lbs for A, 18lbs for B and 2lbs for C and I line all that up with column B which is my number of 20lbs bags. And then I do the same thing for my blends, for my grinded beans in bulk, 0.5, 0.4 and 0.1. With this combination and with these dummy numbers, if I have 4, 20lbs bags and each bag needs 30lbs, how many did I use? Well, 4 times 30 is 120 and I do the same for the pounds of grinded beans. Once again, I multiply and then I need to add to keep track of my total. So we're going to use our favorite function, our sum product, highlight the variable cells and then highlight the coefficient cells. For me, it's B2 and C2, B9 and C9, close the parentheses and there it is. I'll show the left hand side formula using the formula text, this highlights a cell and shows the text. Notice, once again, in the left hand side column, the LHS column. This is the only formula in the entire constraint table. Everything else is typed, everything else is hand entered. Once I have this ready to go, I can drag it down. But I have to be very careful, I need to put dollar signs. If you caught this, give yourself a little pat on the back here. I need to put dollar signs in. You can use the function at four feature or just type the dollar signs in. They always go before the B, before the 2, before the C, before the 2. Without these and I drag, without these, I'd have a problem, I'd get the wrong cells. So you can always check to make sure it's working. If you double click any formula, you'll see the highlighted cells that's used the calculation. Notice as I drag, the B2 and C2 stayed exactly the same, but then of course, B10 and C10 did not. So there we have it, we can drag the formula text down and we have our beautiful constraints. I have my variables, I have my objective read the problem one more time, make sure we're all good. I think I have everything, I didn't copy any numbers in correctly, done that before. So let's go to Solver, Data, Solver. Set your objective to the gray cell, this is the profit that you want to maximize, so make sure max is selected. Changing my variable cells, these will be my green cells, the two things right now that have dummy variables in there. Subject to the following constraints. Now, this is wonderful, they're all less than or equal. So let's batch upload these. I'll highlight all three of my left hand side, less than or equal to. And then I'll upload all three numbers in my right hand side. So we can batch upload those, wonderful, and I hit OK. I make sure the box is checked to make un constrained variables non negative. And then I set my solving method to simplex LP. LP, of course, for linear programming. I hit Solve, Solver found a solution. All constraints and optimality conditions are satisfied. So I hit OK, I say thank you Solver. And Solver came back with the number. Now, here's the difference between computer and a human. The computer will get the number but will never interpret the number and see if it makes sense. I strongly recommend, once you think you have an answer, write out the summary sentence, write out what that email would include your client, what the PowerPoint presentation would say. And here we would recommend producing, let's say here, 44.444. Obviously, it's too many decimals to be useful. We'll just say one or two, bags of whole beans, 20lb bags. And, let me pause here for a second, and hopefully you caught it. If you didn't catch already, stay at this pause, see what the problem is. Why am I pausing here? And it's a key thing that's in this problem and this is what separates this problem from the other ones. It says, you can sell two products that's 20lb bags, whole. You cannot sell 0.444 or whatever reasonable number of decimals you want of this particular item. So you can't go back to do this, you can't do this. And when you have this condition, you actually get 0lb. So the max profit here is 3,777.78 but the answer doesn't make sense, we missed something inside a Solver. And what's the other constraint here? I'm just going to put it on the table that we don't use it. It is that the number of 20lb bags must be an integer, is important. Only one of these variables has to be an integer. In terms of the pounds of grinded beans, that could be any number I want, I can have decimals there. So what I have to do, how do you do this? Well, the wrong way to do it, what a lot of students do is they'll round. They'll say, integer, you just need integer. No problem, 44.44, that turns into 44. And I'll do this and I'm done. And man, this is such a common mistake. Please don't be the person that does this. When you have an integer mistake, sorry, when you have an integer constraint, go to Data, go to Solver and add that constraint. You can always go back and add to your pre existing constraints. I say, look, the cell that I want to be 2, the number of 20lb bags, this has to be an integer, you have to put that on. Select integer from the dropdown and that auto populates the constraint field. And that's how you tell Solver that that number needs to be an integer. Do not round, one thing, you just might want to give a quick check under your options. Sometimes there's a box back here that says, ignore integer constraints. You want to make sure that's unchecked, you certainly don't want to. If I tell it to be an integer, I need it to be an integer. So I hit OK. Everything stays the same. I just add this constraint, again, I do this in Solver and I hit Solve, Solver found a solution. Once again, I say thank you Solver and I hit OK. So now the number of bags is the integer 44. So that was the rounded answer. You say, I could round it. No, not really. Because why? If you remember the answer before said, make 0lb of grinding beans. So you just rounded this answer, you would have got one right, but the other one wrong. So half right is all wrong. So I recommend producing 44, 20lb bags. Now we're at it and 20lb, watch your units, of the grinded bean mix. And this produces a maximum profit of $3,770. If you remember, if you said, what if I just rounded? Remember this number, 3,770. If you put 0 in here and say, look, you're selling a product that you shouldn't, I recommend only selling 20lb bags. You're going to have another issue because now that answer produces a profit of 3,740 which is less than the maximum profit of 3,770. So recommending 20lb of these grinded beans meets all the constraints that you're in, is the best possible mix of your beans A, B and C that produces the best possible profit. Okay, go over this one, again, mixed integer because some other variables or integers. That is okay, here we only have two variables and one is an integer and one is not. If you have 50 variables, you could have one being integer, you can have all 50 be integers or anything in between. That's the whole point. So this now allows you and completely normal that this happens. This allows you to tackle more problems with different meanings of the variables. Again, turn the integer constraint on in Solver. Do not round as you go along. All right, great job on this example. I'll see you next time.