Hi everyone and welcome back. One of the things that people do and sort of asked me after studying linear programming or integer programming, binary programming, assignment transportation, is they make the realization, Well, not everything is a linear problem. Like not everything is a line. This is all great in theory. But what happens in the real world when you have lots of curves and the beautiful thing about this and you can apply this to non-linear formulas. It's amazing. If you master linear programming, then you can actually get good at all linear examples where things get a little harder, is if you have more variables, you might need more powerful software to solve them, the algorithms and the back getting a little harder. But for small examples, you can see how this works. Then if you ever need it, you can go learn more advanced programming or use more advanced software. Let's do this one. Pause the video, read the example if you haven't done so already. Then thinking about perhaps how you'd set this up. This is a brand new template for us. If you want to give it a try, if not worked through it with me. Here we go. Let's call this non-linear example or non-linear template. One of the first nonlinear formulas you see, especially in statistics, is the formula for variance. Here we're just going to have two variables, x and y. We're going to keep things very simple just to develop a template and then we'll see another example next time. But we have our two variables, X and Y will be the things that we want. We'll color code those green. X will be the proportion of money to invest in a stock and Y is the proportion of money to be another one. Here's the backstory. If you haven't read it already. Ryan, a financial investor for PB and J is evaluating two stocks in a particular industry, wants to minimize the variance. There's your objective right there. Notice your variance formula again, if you've seen this formula before, you know it has x squared, that's a nonlinear term. Something needs to be done about that. You want to minimize the variance of a portfolio consisting of the two stocks, but you still want to have an expected return of 9 percent. After obtaining historical data on past variances and returns, he develops the following nonlinear programming key here being non-linear. X is a percentage or proportion of money you invest in stock one and Y is the proportion of money to invest in stocks too. All funds must be invested. That's going to be constraint that says that whatever your percentages for X and Y are, they have to add 100 percent. All funds must be invested. X plus Y has to be 100. We're still going to have our object. This looks the same. We're still going to have our constraint table. That's all fine. Historical returns on stock one is 11 percent, historical returns on stock two is 8 percent. We're going to use that to get our return on investment, to get an expected return or average return of 9 percent. How do I do this? I definitely want to minimize my variance in finance for those who don't know variances like risk. I want to minimize this. I want to minimize my variance. The formula is 0.16 x squared, 0.12 XY, and 0.9 y-squared. Where's the x squared? Where's the, where's the y? This is the beautiful thing about it. In the linear programming, we had our decision variables, we had our objective and we had our constraints. The thing that we're going to add now to our template, let me slide all this down for a little bit, is our non-linear variables. We had our non-linear variables. What non-linear variables do we need? Well, I need x squared, I need XY and I need y-squared, non-linear. By the way, here, There's lots of ways to create non-linear variables. Linear just means the variable by itself, if you put them together in any way, even X squared or XY or Y squared, that is a non-linear variable. You can certainly do sine of x e to the X, logs all your crazy formulas that you know from math. You can put them in here, that Excel has them all built-in, of course. What you're gonna do here, remember x and y are the things that we're trying to find, but we need X squared, XY and Y squared. What we're going to do here is calculate that these are going to be our formulas. This just says take X and square it. X squared, great. I need XY, I need X times Y. We use the asterisk for times. Then y squared equals Y squared. I'll show the formula tells you, so you build them out. Whatever you need, you go and get them, whatever you need to go and get them the variables that we're still after X and Y, those are still up above these linear decision in linear or objective is then our variance. What I'll do now to set up the formula is the coefficients on these variables can go below. This is a similar template that we have before. We'd like to see these things in cells. I don't want to bury him inside a formula. You can certainly go into variance and just type out the formula, but then you'd lose the numbers on screen. It'd be hard to check if you had a typo or something like that. I do want to see these numbers out front. Again, the point one-six corresponding to the coefficient on X squared, on XY and on Y squared, who had other nonlinear, you would just go off and keep expanding this table to the right. But now we have this is the new thing for this example, non-linear variable table. The variance formula is then the sum product of the variables, the calculator variables, the numbers, the coefficients and I'll show that formula as well. Notice if I put in some dummy numbers, if we just plug in some numbers, let's say 2 and 3. Convince yourself this is working. What is 2 squared, 4, what is 2 times 3, it's 6? What is Y squared? Nine. Not as obvious to say what is 4 times 0.1, 6.6 times point 2, and 9 times 0.9 and add them up. But you can check that it does actually equal 9.94. These are our dummy numbers. I'd love to know what the right answer is to minimize variants, but don't forget, I do have some constraints here. All funds invested. What does that mean as a constraint? Let me expand the column. All funds invested means the percentage, really, this should be percentages. This is going to be something like, well, let me just make these percentages. So 20 percent and maybe like 80 percent or something like that, whatever the combination is, I don't want to come back and say 20 percent and 20 percent, I need all funds to be invested. I'm still going to have three past my variables. Let's build out a constraint table, 3 columns past financing borders. We'll do our left-hand side, we'll do our sign and we'll do a right-hand. All funds invested means that the amount invested, the proportion or percent invested in x plus the proportion invested in y. Well, this has to equal, and we'll do single apostrophe equals just to show the equal sign. This has to equal here 100 percent. We'll do our sum product formula on the variables and the coefficient. This is all the same from before. I'm going a little quickly. Hopefully, you're okay. But I'll show the formula over here for the left-hand side. Everything is the same. Just add up the two percentages. Because it says all funds must be invested, I really want 100 percent invested. I don't want 20 percent in 20 to be consistent. You see here I have 0.4. Let's just make this a percentage and we'll keep everything the same. All funds should be invested. Great. Then I want this other expected return of 9 percent, so expected return of 9 percent. The historical return on fun one is 11 percent. We'll put that in there. Historical return on stock two is 8 percent. We'll take our left-hand side. I'll put it in my dollar signs here. I'll drag this down. I love to have more than 9 percent. We'll do greater than or equal to nine percent. We're working with percentages here and that is totally fine. Percentages are just numbers. There are no other constraints, here again, a simple example really to emphasize the new non-linear aspect of this program. We have our non-linear variables here and notice we use them. Where do they get used? Now the constraints, although you can imagine there could be examples where we need somehow a non-linear constraint. You would just refer back to this table. But really in the variant where they're coming from is being used in the calculation of Eric. We're good. So let's go to data. Let's go to solver and there's one small change here as well. Set your objective. This is your gray cell. For me, it's b 11. I do want to minimize variance. Again, variance is your risk. By changing the variable cells. The two variable cells are not the non-linear ones. Remember, these are just calculated. What you're actually changing is coming from b2 and c2, the green variable cells, subject to the following constraints. Let's see here I have that all funds must be invested. My left-hand side has to equal my right-hand side 100 percent. Let's add another one. I want my expected return to be greater than or equal to 9 percent. That is okay. These variables here are not integers, percentages are not integers, they're not binary. I do want a number, it's not a yes or no question, but again, you can think about it, and then you're okay. I do want these things to be non-negative. It would be weird to come back and say invest in negative 20 percent. Here's the only thing that's new. Before I kept using Simplex LP for linear programming. This is very specifically non-linear. These variables in here, the calculations in here are Nonlinear. Solver has a built-in Nonlinear Solver, GRG nonlinear. Let's select that and hit Solver found a solution all constraints and optimality conditions are satisfied. You will keep solver solution. I have a 93 percent to invest in x. They don't tell me the number or the budget, but just 93 of my budget in x and 7 percent and y and my variance. This usually is reported as a decimal, although you could also do it as a percentage. Get back a reasonable number. It drives me crazy when students give me back nine decimals or somebody of that pick a reasonable number to 3 or 4 is fine as your variance and then you'd write all this up in your summary. Just don't forget to include your summary sentence here. There's no tricks about units or anything else. Just a new template that we're going to build off of to wrap up the content of this course. Hopefully, you see how this can expand. Go over this one, make sure you understand it. Great job on this example. I'll see you next.
