Hi everyone. Welcome back. We're going to do another specific type of LP problem today called a binary example. We're going to use it to make an investment decision. This is mildly amazing. I love this because you can get decision-making down to a mathematical formula. You can let Solver make decisions for you. I think most people know this, but just in case not binary variables means zero or one. A lot of people have heard this with binary numbers, but binary means zero or one. For decision analysis, how we're going to use a spreadsheet to make decisions. For us a zero is going to correspond to a no. A one is going to correspond to a yes. Now, there are lots of decisions that are binary. Should I hire someone, should I not? Should I approve this project, should I not? There are also lots of decisions that are not binary, where the answer might be sometimes or maybe, or a percentage so not every decision is going to be modeled by a binary decision variable. But sometimes you can. Here, read the problem if you haven't done so we're going to decide if we should invest by some, all, or none of these seven investment opportunities. This is a yes-no decision. Do I buy the block of shares for Texas-Trans Oil at this cost? Yes or no? Do I buy the block of shares for BP at this cost? Yes or no? We're not asking how much money do I put towards it? We're not asking what percentage of an investment do I put towards it? This is just yes or no. When you have a problem like this, then you can model it and solve it, optimizing on something using Solver so you can get decisions down to mathematical equations. Read the problem if you haven't done so already and let's think about how we're going to model that. This particular example is going to be a yes-no on finding the right investment to maximize the return. Now, just so you know, we're looking at here, we have a bunch of constraints up here by the client. That's fine. We have seven stocks that can be bought in block. Now watch the units here. These are in thousands, so one block is 480,000. We'll leave the numbers small for Excel. But then when we write our summary sentence, we have to watch our units. The return here in thousands of dollars, the expected annual return is all positive. We have up to three million for investing. In a perfect world, if I have the choice where I know everything is going to have a positive return. Obviously, I pick the highest one here, Houston Drilling and I look at and say, well, this is going to be the highest return, but I have these constraints that are just going to prevent me from buying only one thing. What are the decisions that I have to make? I have to decide yes or no to these seven stocks. That is seven separate decisions. Our variables here, which are binary now, and I'll just call them X1, X2. I don't have a good name for it. It's like yes or no, do I buy it? I don't want to list out all seven stocks. So X1, X2, X3, X4, X5, X6, and X7 and I'll shrink the columns a little bit as well, just so we have nicer formatting. Maybe I'll move this over here and it's important to realize what this represents. This is the first time in any problem that we've done, we're not representing the variables with a dollar amount. We're not representing the variables with how many bags of coffee beans or something. It's a yes or a no. It's almost like text. We're going to represent them, of course, with numbers zero or one because of course Excel doesn't like texts, but we have seven variables. We've seen problems with seven variables. The number seven shouldn't be too scary. This isn't the thing that should concern us. It's the use of the variables, the fact that it's going to be zero or one to decide, should I buy the stock or should I not? You can imagine just like last time when we had mixed integer problems so some variables are binary, same are not. This is a particular type of integer, but instead of any integer, it's zero or one. It's a binary integer problem. Now, think about what I want to do here. I want to optimize my return so my objective as a financial investment firm would be to maximize. Maximize what? Maximize the return on this. This would be my gray cell and this one here, I might need to make a little bigger. Instead of me expanding the column out, what I'll do is I'll merge it and let's think about each investment. What does it return so this is where again, the units are in thousands so maybe I'll put my units here on the side just to remind myself that the units are thousands. If I buy the first stock, my expected return, of course, is 50. The second stock, its returned is 80, 90, 120, 110, 40 and 75. Here's how this is going to work. I'm going to ask Solver to find me the best results here. Solver is going to come back with a bunch of zeros and a bunch of ones. Let's just pick ones across. It can't give me any other number. I'll do 1, 1, 1, 1 maybe a zero. One means yes, purchase the block of shares for Texas Oil. A one in X2 means yes, purchase also the block of shares. This is yes or no. It's either buy it or don't buy it. Not puts this much money towards it, but yes or no. If a zero comes back, then that answer is no, don't buy this one. Buy it, don't buy it. Yes or no. As a reminder, zero is no and one is yes. Now, what is my expected return? How can I do a formulation on this? The block of shares, if I buy the Trans-Texas oil block for $480, that'll be my cost. That'll be a constraint that I'll build in to the three million for investing, then my return is 50. These are my returns in thousands of dollars. Just remind myself those units over there, my return for this block of shares is 50. My return for this block of shares is 80. What's nice about this is multiply this together, you still get 50. If you multiply the one in the 80, you still get 80. When you multiply the zero and the 75, zero being no cancels the return. If you do a sum product, which is exactly what we're going to do, again, this is still an LP program, on the decision variables, all seven of them comma their expected returns, line up the two blocks of cells, close the parentheses and hit "Enter". Then I'll show that formula here so you have it. You can see that the sum product is doing exactly what you want. When there's a one, it adds the expected return. When there's a zero, it adds zero times 75, which is saying nothing, zero times 75 is zero. If I turn all of these off, if the solver comes back, they won't do this, of course, but I'll say, what if you turn all of them off, then the return is zero, zero times 50 plus zero times 80 plus zero times 90 is always going to give you zero. Play around with this. Again, dummy numbers here are key. If I did every other one, you can check 50 plus 90 plus 110 plus 75 is exactly 325. This will be in dollars and this will be in thousands of dollars. Had to watch my units here, but it's doing exactly what you want. It's adding up the ones that are turned on, and it's turning off the ones that are turned off. This is the power of zero and one, and how you can use this in decisions. Now, I have these 1, 2, 3, 4 constraints that are on here. Let's start to build our constraint table and move the text over a little bit. As always, even though we have seven decision variables, we're going to go 1, 2, 3 over. I need columns i, j, and k here. We're going to put some constraints down. I have about four. We'll build a nice table. We're still going to use our left-hand side, our sign, and our right hand side. Now let's think about what this means in terms of the constraints. The first one says, at least two Texas oil firms must be in the portfolio. Someone loves Texas oil. Texas oil, which ones are my Texas oil firms here? You may not be as familiar with some of the cities in Texas, but just in case you are not, the Texas one here is well, obviously the Trans-Texas oil is a Texas firm. Houston is a big city in Texas. I'll copy that as well just to remind you. Then there's one other called Texas Petroleum. These are all fake of course, but Texas nonetheless. The first one, the fourth one, and the fifth one are all your firms from Texas. The client is demanded that at least two of them must be in the portfolio. Let's label this constraint at least two Texas. Which ones we say those words. It was the first one, the fourth one, and the fifth one. Using a one here again, one means turn it on yes, to purchasing that one. Now how do I build this in as a constraint? If you want, this is a good place to pause the video and think about how you would do that. What's the sign? I need at least two. This is the tricky one. At least is the one that is greater than or equal to. It's a little bit counter intuitive. Greater than or equal to two, then the left hand side is no change. It's still our good old sum product. What does it have? The variable cells comma, and then these numbers here in the constraint table. I'll stay on that so you can see the formula. It's the same formula we've always done. But realize what's going on here. It counting how many of these firms there are. This two is coming from what? One times one, so X1 is on, Trans-Texas is on, X4 is off, so that's zero, it's contributing zero to the sum and then one of these is on. I put a one in here to almost count it. We've seen this before when you want to count what the variable is, but then becomes a one. If I just happen to have some combination where, let's say the Texas ones are off, so X1, X4, and X5 are off. This will be one plus zero plus zero. That's what's happening here when I'm adding up these numbers and I get that the left hand side is a one. This combination would not be in my feasible region. It would fail the constraint and this would never be returned as a solution by solver. That's the first one. If you want, pause the video and see if you can get two, three, and four for your constraints as we go. Constraint number 2, no more than one investment can be made in foreign oil. No more than one foreign. Now we have to think to ourselves which ones are foreign oils? There's two of them here, British Petroleum and Dutch Shells. These are your two foreign oils. We'll keep track of if X_2 and X_3 variables are turned on, and it says no more than one. My left-hand side, let's actually put the dollar signs in before I forget. Put dollar sign B, dollar sign eight, dollar sign H, dollar sign eight. You just drag this formula down. No more than, that is less than or equal and it says one. So no more than good old one, great. Third constraint. One of the two California stocks must be purchased. You can have one but not both. California constraint here. Which ones are my California again, if you know some cities in California great, if you don't I'll tell you here. The California would be San Diego. It's a wonderful city in California. California Petro, obviously that's California as well. So I have X_6 or X_7 that I really have to worry about. Let's think about this for a second. How can I say one of the two stocks must be purchased? I definitely need to keep track of X_6 and X_7. My left-hand side formula is not going to change, that just gets dragged down. My right-hand side says, one of two stocks must be purchased. What's happening right now? If I sum product this, I get 0*1 which is 0. This is counting how many are turned on. What is the thing that goes in sign here? If I do less than or equal then having no California stocks would be okay. If I do greater than or equal then having two would be okay and that's a problem as well. I'm only allowed one so what's my sign here? Equals. If you remember in the drop-down list for solver equals is a choice. Here's one where we actually want equals. I just want this to equal one. That'll be okay. Notice if I turn both on, then the sum turns into a two and that's not allowed, so this won't pass. If I turn both off then I get a zero back and that's no good because I need one of the two so really what I'm allowed for is just one of these two. Equal will be my sign here. That's a good one. Then last but not least, of course, this is my budget and it says up to three million for investing to purchase a single block per the table below. If I buy the first one, what's the cost? Now here's where I use these numbers in the table. If I buy X_1, what's my budget? Budget is good, old-fashioned, less than or equal and now you have to be careful here. It's three million, but all my numbers are in thousands so you have to be very careful here. I want to put three zeros, zeros, zeros. I don't want the extra three because that's included in the units all over the place so I really want 3,000 here. Watch out for that. That's a little tricky. You can really mess, you can have everything perfect and mess that up with a budget. If I buy the first one, what's it going to cost me? That's going to cost me 480. If I buy the second one, what's that going to cost me? 540 and you can work your way across the table. So 680, just copy it correctly; 1,000, 700, 510 and 900. Now when I do some product for all the ones that are turned on, it will sum their cost to my total and this cost should be under 3,000 or three million. I think everything is good to go. Let's go ahead and just give it one quick check. Make sure you agree. Data, solver, the box pops up. Let's set our objective. That is the gray cell. For me it's in the merge B12. I hope it works with emerge, we'll find out. I want to maximize this cell. This is my return. My variable cells are the seven variable cells for me in Row 8 subject to the following constraint. I want to add a couple of constraints here. Now nothing is matchable, I can't do it so I just have to be careful here. Let's do the first one for the Texas constraint. The first one is greater than or equal to two. Let's add another one. This is my no more than one foreign. My left-hand side is less than or equal to one. Let's add another one. My California constraint is equal to one, use of equal there and then last but not least is my budget. I want this number to be less than or equal to this number. Left-hand side less than or equal to the right-hand side. Here's the new one. Now it's going to hit ''Solver'' and solve it but let's just go right to it. Let's do one more and add. Just like before when I had to tell Solver that the variables have to be integers. Remember binary is a specific type of integer problem. I still have to tell Solver that all seven decision variables, at least all seven in this case, you can imagine cases where some of them are binary some are not and that's right here. It's the drop-down after integer is the next one on the list. It is a binary decision variable. As usual, it populates the binary constraint. You hit "Okay" and it gives you the box back. Just make sure you agree objective, max, variable cells. There's the binary constraint. Select the box to make unconstrained variables non-negative. Select Simplex LP and then you can hit "Solve." Solver found an integer solution so thank you solver, and then come back and hit" Okay" and here it is. Solver is coming back and say, I want you to purchase X_3, which is your Dutch Shell, X_4 Houston drilling, X_5 and X_6. My return here is $360,000. Convince yourself that this purchase of X_3 through X_6 of the third through sixth option actually meets all of them. Do I have at least two Texas oil firms? Sure I do. I have Houston drilling and Texas Petroleum. Do I have no more than one investment in foreign oil? That's right. I just have Dutch I don't have British. Do I have one of the two California's purchase? Yes, I do. I have X_6 which is San Diego, but not X_7. Lastly then I stay under budget. It turns out my budget here is 2,890 which is under my restriction of 3,000. That would be my summary sentence. The only thing that I just would caution about in the summary sentence you can have this here, so you purchase and you can list these out. I'll just say X_3 through X_7 in the interests of time for a return of now just be careful here is 360. It's not dollars it's thousand. So just watch the units. That is the maximum return. Again, play around with this if you want to. Turn the zeros and ones and flip around, see if you can beat solver. You won't be able to, 360 is the absolute max where all the constraints are satisfied. You might be able to find better answers, but you're going to break some of these constraints, which means you lost a trillion dollar client. That's never a good thing. This binary analysis, it takes making decisions down to a spreadsheet, which is extremely powerful. Go over this one, make sure you understand it. Great job on this one. We'll see you next time.