Alright everyone welcome back, we're going to do another LP problem. This one is I think a little simpler than the last one you could tell obviously it's just much shorter in terms of the text on the screen read the text if you haven't done so already. And then keep in mind this table here goes with the problem percentages. One thing just to note on this table, the numbers 5.3, 6.8, 4.9 these are all percentages even though the percent sign is not in the actual value, this is 5.3%. So read the problem if you haven't done so already and as we do so think about what is the objective, what are the constraints, and what are my variables? You'll see this one is a little bit of a play on the last one, more advanced just a little bit though but in a different direction. So pause video, read the question if you have not done so okay. Ready? Here we go, a brokerage firm has been instructed by a client to invest $250,000. Okay right away you always are thinking about this one, what is my constraint, what is my budget? So you are now an investment firm, what are my constraints that we have a budget have 200 grand to do something with? Hold on to that for a second, but we definitely have a budget here. The client requests the firm select whatever stocks and bonds they believe are well rated but with the following guidelines. Municipal bonds constitute at least 20% of the investment, at least 40% of the investment is placed in tech stocks and no more than 50% are the amount invested in many bond to be and high risk. And if you look at the table here we have HDN Stock this is your one high, so my question to you is let's go back for a second, what are my variables? What are the things as a manager that I have to decide on and then what is my objective, what is the thing that I'm after? So here I want to invest some money so clearly I'd like to do what maximize or minimize this rate of return. If someone gives you money, should you maximize or minimize their return? I want to maximize it, so notice that it's not explicitly in the question but clearly that's what they're looking for. So we want our rate of return, all right so let's back into this a little bit, all right, I know the rates of return they're all positive, so keep mind that I'm clearly not giving you all information. This is like an average rate of return obviously stocks can lose money etcetera just given anything, I would throw all my money in HDN right why bother with lower rates of return? But so the question is a little contrived but if you do that you're going to see you don't actually meet the constraints so you're going to have to invest here. What I'm going to do here is I have to decide how much money of the 250 grand to put into each investment and I have 1,2,3,4,5 investments. And this is where this problem grows in complexity last time we had a tricky situation where I had multiple parts going into my net profit right now though I have five count them up 1,2,3,4,5 investments. And so as a manager, I have to decide what am I going to put into the five investments. All right, so let's try that so here we go, so X1, X2, X3,X4, and X5, now, why am I calling it this? I don't want to type out LA Municipal bond, I don't want to type out Thompson electronics I'm just going to call it X. Because X is unknown seems like a good variable and call it I1 these are dummy names, who cares what you call it? They just correspond to it but here's the complexity of this problem that is different from the other ones now, I have five decision variables instead of the two that we've seen prior, so that's our first thing to realize. Now we have to figure out the rate of return and remember the table is a percentage so maybe I'll put in the percentages somewhere else right below it. So how about 5.3 and I'll just two percentages here, so I don't really have to worry about it 5.3%, just make sure I copy these correctly. 6.8% 4.9% 8.4% and 11.8% great. So I'm going to put in some dummy numbers here, dummy numbers just to make sure that my spreadsheet is actually working. So I'll put in hey five bucks across the board clearly that's not the right answer but it's just there to help me out here so what is my objective, what do I want to do? I want to maximize the rate of return, I want to maximize the rate of return, so if I invest $5 in the first one, L A Municipal bonds, well that's going to be a rate of return of 5.3%. And then I would add that to the rate of return for each individual investment, so my rate of return, my objective here, the thing that I actually want to maximize the gray cell is going to be what? We figure this out yet it's the sum product of each investment comma, with their returns interestingly enough here, what is this number? This is not actually my rate of, this isn't a percentage this is the dollar amount that has returned. So if the first investment makes 5.3%, well then I made, and the other one makes 6 20% I've made once you do all of them a $1.86. This is actually a number, this is actually a dollar amount, so I'll put the formula text here okay, so that's our formula, that's what we want to, so now let's go through the budget and the constraints. So let's build out my constraint table so we move some things over because remember how this works. When you build your constraint table out you go as far out as your decision variables and then you go 1,2,3 more and you build it out. So here we are variables, go to F, and then three more gets us to K, so we'll make this table all pretty label your headers, LHs for left hand side, sign, and then right hand side. Now we can do our budget, so the sign obviously for a budget is less than equal and the client only gives us 250,000 only. It's pretty good I think then you want to just stay under it. Obviously the answer here is to invest all of it would be kind of sad if you have these positive returning investments to not invest all of it. But just, to be consistent, will say less than or equal to but the right answer clearly will invest all of it. And now how do I know how much has been invested inside of each one? Inside of this one I just want to add up these numbers and so it's a sort of copy the numbers to use the some product you can certainly do some. But just to be consistent let's do our ones across the board that when we multiply by the values X1, X2, X3, X4, X5, that will multiply five times one which is five then five times one again so you're adding up each term. So a one inside the constraint table just kind of copies the number down, then you wait after you multiply by one which of course does nothing and you add them up, you get basically the sum. So our left hand side is going to be the sum product and I can set this up of our variable cells this way we're consistent every time. Put your dollar signs on, now to do you're referencing, lock this down, I use the keyboard shortcut F4, of course you can certainly come back in and type it in up here in the formula bar. Whatever works for you, then we do our budget and we hit enter and hopefully you agree $5 here for five different investments is certainly 25. I don't need to format that as a dollar sign but I certainly could. All right so there's our first constraint that's coming from the total amount to invest here come the 1,2,3 guidelines that we're going to need. Since there are only three more constraints, I will shrink my constraint table so that my spreadsheet looks pretty to just the other three will label it here. We have the mini bonds consist of 20% of the investment, mini is 20%, will use shortcuts here, not 2% 20% know, at least 40% in tech stocks, so 40% in tech and no more than 50% in should be high risk. So no more than 50% in high risk okay, let's see if we can take these constraints and actually put them in. Let's just do the first one, you'll see a pattern here the municipal bonds consists of at least 20% of the investment. So first off, what, which ones easier the municipal bonds? Well, the only one you got to read the labels here, the only first one is the LA Municipal bond, so this is basically saying mini-bonds or just the amount in X one is at least 20% of the investment. So there's a couple different ways to do this, I'll do it right inside the table here I need the amount in X1. Remember if you just want the amount, you put a one here, Mountain X1, I'll drag this formula down to get that is at least 20% of the investment. All right, be careful this, we saw this before, what does at least mean greater than or equal? This is a little tricky english is a tricky language at least a certain number means greater than or equal. So, the amount in X1 notice that dummy numbers are really helping me see that here, the amount that I put in X1 has to be at least 20% of the investment. That's a calculation right, 20% of the investment and you can type it in or you can link to the other one, does't quite matter, maybe I'll link it just not to copy it. But you can do a calculation on this right hand side and have it be a number and that's okay here. The one thing about this is that the investment, we know the investment is going to be the total amount because every single investment has a positive rate of return. So, clearly, I'm going to put all my money into one of these investments. But I'm going to meet the requirement now that this number has to be at least 20%. So 20% of the investment you can do in excel, you can do it offline, maybe you do in your head is going to be $50,000. So, I need whatever goes in here has to be at least as at least $50,000. That one hopefully is okay, let's think about the other one, the second constraint at least 40% of the investment is placed in tech stocks. Now be careful here, it's not immediately obvious which ones are tech stocks but Thompson electronics, that is a tech stock. So maybe I'll label these here just to be super clear, that's a tech stock, the third one Aerospace, this is a tech stock, and Palmer Tech, I guess that was a little more clear these are also tech stocks. So you need to know how much is going into the 2nd, 3rd and 4th, when you just want the amount you put a one. So we're getting this nice pattern here of ones, so how much is going into tech stocks is really saying how much is going into the 2nd, 3rd and 4th investment. We drag this down It's 15 again in your head 5, 5 and five, these dummy numbers keep being very helpful. We want to do at least we've seen this before, at least is greater than or equal and now we want 40% again, maybe you can do this in your head, but let's do 40% of our investment. You can type out two five zeros, with three zeros or I'll just keep clicking to it the less numbers that I click inside there is perfectly fine. You could do this in another cell, does't matter where you do it, but at least 40%. Last but not least I have to do my high risk, no more than 50% of the amount invested in money bonds should be high risk, this is interesting, no more than 50% of the amount invested should be high risk. Let's think about let's break that down for a second, pause video, see if you can work this one out. A half of this the challenge is not always the excel part, it's just the deciphering part, all right so what's the high risk one? That is X5, that's the only high risk one that's there so this constraint says that whatever we put in X5 it should be no more than 50%. So that's going to be less than or equal, that's the newer more part of 50% of, of means multiply the amount invested in money bonds. What's the amount invested in mini-bonds? That's just X1, how much we put it in mini-bonds, so this looks like this, you can probably put this in, we'd like to put numbers on the right hand side. So what I'm going to do is we've seen this before, I might subtract this off just move it to the left and be less than equal to zero. As a friendly reminder when you work with inequalities equations with less than or equal or greater than everything is normal. You do everything like you would with inequality with one exception if you multiply or divide by a negative the sign switches. Here, I'm just subtracting things to the other side, so the sign is still less than equal to. And so now I can go over back to my constraint table and I can put this in. Just be careful, the coefficient on X1 is negative 50%. And then the coefficient on the X5 term is one, all right, so let's go do that. So just be careful, the coefficient on X5 is one and the coefficient on X1 is negative 50%. Copy the formula down from my left hand side and I will have my sign be less than equal to zero. You absolutely could do it I guess with the original way, and just say like one over here is less than equal to 50% times one of the variables I guess. But it's just not consistent with the way we do it, I'd rather a consistent approach every time, there's certainly more than one way to solve these problems. However, the answer will not change, no matter how you set up your spreadsheets to do this in a way that makes sense to you. But notice once in a while, if you get a tricky constraint, you gotta come off on the side and kind of work it out and that's okay. All right, so we have our budget, we have our three constraints, we have our wonderful five variables. By the way, if you're using Microsoft Excel to do these problems, Microsoft Excel can handle up to 200 decision variables, so here five, it's getting larger is still relatively small. I think we're all set, let's try to do it, let's go to solver here, you can kind of play the the old timey experience boss and see if you can guess the right answer sort of plug in different numbers of time. Let's go right to solver and let's solve or do it. All right here's your solver parameters and your box may look a little different if you have a different computer but everything's still still looking for the same things. What is my objective, what is the thing that I actually want to maximize? So I select the gray cell color coding helps keep things consistent. I want to maximize it, yes, maximize return, don't minimize your clients return it will not be your client for much longer, what are my variable cells? And hey, now I have five of them this is the whole part of this problem, also the financial application, I have five variables. That's okay, highlight all five subject to the constraints, so let's add these in, I can't batch upload things that are not next to each other, so I'll do this sort of one at a time. I add the constraint in the left hand side less than or equal to the right hand side and I hit add now I have two constraints that are both greater than or equal. So I'll batch upload those two, I'll just do those together and then I'll add one more, and I'll do my 2.5 less than or equal to zero. So I click around as we go, I hit okay, and I just give everything a quick check. Do I have my objective, do I have max, do I have my variable cells, do I have my constraints, do I want the variables to be non negative? Yes, you certainly shouldn't put negative dollars into any investment into any allocation. So I select my solving method, just swap that down to simplex LP and I hit solve and I get the ding of happiness solver has found a solution, all constraints are satisfied, so I hit ok. I have these pound signs, hopefully if you haven't seen this before, that just means that your columns are too small, partially coming from the fact that I didn't remove the decimals. So if you ever get those pound signs just expand the columns and here we are. So let's practice reading this, this says that I should invest $50,000 into X1 which is the LA mini-bond. So remember the constraints said that I had to do that X2 and X3, nothing, I like this when it tells you to come back with nothing. It's a little challenging as a consultant to tell someone not to do something. So here it is, so it says don't invest anything in X2 or X3 the Thompson electronics or the United Aerospace and then take most of your money put in X4, the Palmer Tech, which is nice. And then X5 $25,000 goes into the high risk and you can check that you're staying under budget. If you add up all these numbers you get 250,000, the mini the 20% we're putting 50,000 in. So the minimum that we need and the rest is going into tech and high risk, so very nice. So that works out kind of nicely, again, you could write a nice summary sentence here that would satisfy this and I sort of leave that to you to work out. But how would you present this back to the client, how would you defend your assumptions, how do you explain where the numbers came from? This is it, so your best, your actual return is $20,300. Again you can play again, some people just don't appreciate what solvers doing, play the role of sort of the human investor. Take a guess, see if you can beat or get anywhere close and of course you cannot, and if you do get higher. Sometimes students come back and say I found a higher number but then they didn't satisfy the constraints. So not only do you have to get a high number, you got to satisfy all the constraints that are required by your client. All right, so great job on this video we'll see you next time