The sample file for this video contains all of the data and summary formulas we need to find the optimal portfolio chosen from a set of investments and for a given level of risk. Now you can create the Solver model itself and generate an answer.
- [Narrator] The sample file for this movie contains all of the data and summary formulas we need to find the optimal portfolio, chosen from a set of investments, and for a given level of risk. Now we can create the solver model itself, and generate an answer. In this movie I will demonstrate how to create the solver model and look at the results. My sample file is the Investments_04 workbook. You can find it in the chapter two folder of the exercise files collection. I have a set of eight funds. I have their risk relative to the market, their average return, and then I also have a set of values for their shares, partial beta, and expected value, and I have a total of one which is what I need for these shares to apportioned correctly.
I want to invest all of my money, and no more money than I have, so that will be one. The partial beta is the amount of risk for the portfolio as a whole, and the expected value is the expected return, and again, that's broken down based on each of the funds in which we have invested. I also have my target beta which is the level of risk I'm willing to accept of 1.3. The maximum share for an individual fund of 30%, or 0.3.
We have to invest in at least four funds, and I have a formula in cell C15 that counts the number of funds in which we have invested. With all of this prelude, we can go ahead and create the solver model. So I'll go to the data tab of the ribbon, go to the analyze group at the far right, and if you don't see solver, then you should see movies elsewhere in this course for how to install it, and click the solver button.
Now in the solver parameters dialog box we need to identify the objective cell, and that will be the expected value. I'll click the collapse dialog button here at the right edge of the set objective box, and I will click cell G11, and I'll click the expand dialog button. I want to maximize the value in that cell. In other words, I want as much return as possible. I want to maximize that value by changing the investment pattern, and that would be the share in each of the eight funds.
So I'll click in the bi changing variable cells box, and select cells E3 through E10. There are a number of rules that I need to follow. Those are my constraints. So I will click the add button in the solver parameters dialog box to begin adding my constraints, and I'll drag the add constraint button toward the top so I can see my entire worksheet. I'll start by saying that I want the fund count in cell C15 to be greater than or equal to the minimum number of funds.
So with the insertion point or cursor flashing in the cell reference box I'll click cell C15, and then I want it to be greater than or equal to the value in C14. So I'll click the operator list box's down arrow and click greater than or equal to, and the constraint as I said is in cell C14. So I need to invest in at least four funds, as enumerated by the formula in cell C15. I'll click add, 'cause I want to create more constraints.
Now I need to ensure that the total investment shares equal one, and that is in cell E11. So with the insertion point flashing in cell reference I'll click cell E11 and yes I do need to click it, even though it was already selected. I'll change the comparison operator to equal, and then constraint I can just type in it's the number one, again representing 100%. More to go, so I'll click add. Now I need to ensure that no fund has an investment greater than the maximum, which I have defined in cell C13 as 0.3, or 30%.
My investment shares are in cells E3 through E10, and I need that to be less than or equal to, so I can stay with the default operator. The constraint, again, this is in cell C13, 0.3, and the reason that I put the constraint in the cell rather than putting it into the solver model directly is that I can change the values in the worksheet without having to change the model. It saves a lot of work. I have one more to go. So I will click add. Now I want my total beta of investments to be less than or equal to the target of 1.3.
I'm going to make the beta less than or equal to 1.3, because I forced solver to find a solution with exactly 1.3 it might not be as good as one at say 1.25 or even 1.2. By telling solver this is the maximum risk I'm willing to accept I give it the power to come back with a solution that might not be as risky. The total beta of the portfolio is in cell F11, and again, that is the sum of all the partial betas, and I want it to be less than or equal to, so I can leave the comparison operator in place, the value in cell C12.
That's it for my constraints. I'll go ahead and click OK, and I'm back in the solver parameters dialog box. I'll make sure that the make unconstrained variables non negative box is checked. What that means is that I couldn't do the equivalent of selling short and put a minus .2 share in one of the funds with a particularly low return. So I can't do that, and the last thing I need to do is to select a solving method. I can't use simplex LP because not all of my formulas are linear.
The one that violates that principle is the fund count, and that is in cell C15. Because I'm counting discrete values as opposed to using a linear equation I have to go non linear. So I will select the down arrow and click GRG non linear and this is the select solving method list box. So I'll click GRG non linear, and everything should be in place. So to recap, before I hit solve, I am trying to maximize the value in G11 which is the expected return, by changing the values in cell E3 through E10 which are the shares apportioned to each of eight funds, and my constraints are as described earlier.
Everything looks good. I'll click solve, and solver comes back and tells me it has found a solution. I'll click OK, and I see that I have a return of basically 5.3%, a beta right at 1.3, and my share is one, so that means that solver has been able to fully assign my investment funds. The specific result are that we have 30% for fund one, which isn't surprising.
It has a return of 6.62 which is higher than everything else in the portfolio. Then 4.19 in fund two gets about 10 and a half percent. 30% to fund three and then fund eight also gets just below 30%, but you can see that it has a high average return and a relatively low beta, so that means that that particular fund has relatively low risk, at least as measured over time. That's the basic solution to the model.
In the next movie, I'll show you what happens if we change some of the constraints.
- Finding target values using Goal Seek
- Finding a solution using Solver
- Tuning investment portfolios
- Organizing worksheets
- Creating objective and control formulas
- Experimenting with different constraints
- Optimizing resource placement
- Defining decision trees