There is a useful optimization tool available with Microsoft
Excel. Many users are not aware of the existence or of the utility of this
Excel Add-in feature, Solver (tips for setting up Solver are given at the end
of this post).
Here is an example of a typical optimization problem. A
financial advisor uses a questionnaire to determine the appropriate risk level
for a client. Based on the client’s answers, age, and other factors, a risk
level of 1 to 10 is assigned to the client. Each investment offered by the
advisor has a potential rate of return and is assigned a risk score based on its
volatility. The advisor’s firm may also have various rules such as: no more
than 10% of the portfolio may be in one investment; no more than 30% of the
portfolio may be in one sector, etc.
Now the question becomes what is the best mix of investments to maximize the potential
return while meeting not exceeding the risk tolerance of the client and meeting
the other investment guidelines of the advisor’s firm?
Maximizing the potential return is called the objective
function of the problem. The total investment amount, the risk tolerance, and
the investment guidelines are called the constraints of the problem. The
amounts to place in each investment are called the decision variables. It is
these decision variables that we are trying to determine to reach the optimal
solution to the problem.
Without good analytical tools, a solution could be found by
trial-and-error (not very efficient) or by setting up a matrix of linear
equations and solving for an optimal solution (tedious and difficult). However,
using Excel, one just needs to be able to express objective function and constraints
as functions of the decision variables.
Here is a specific example:
Total amount to invest: $1,000,000
Client’s Risk Tolerance: 5
Available Investment Choices (with potential return and risk
scores)
Investment Symbol Potential Return Risk Score
Cash C 1% 1
MuniBond M 3% 2
TechStock1 TS1 10% 9
TechStock2 TS2 7% 8
Utility1 U1 5% 4
Utility2 U2 5% 6
(Realistically, there would be many more investment choices.
However, the same method works for any number of choices.)
The objective function becomes:
Maximize Return = .01C + .03M + .10TS1 + .07TS2 + .05U1 + .05U2
The Total Investment Constraint is:
1C + 1M + 1TS1 + 1TS2 + 1U1 + 1U2 = 1,000,000
The Risk Constraint is:
1C + 2M + 9TS1 + 8TS2 + 4U1 + 6U1 <= 5,000,000 (the limit is the client’s
risk tolerance x total inv)
The constraints for the maximum allowed in each investment are (we only have a
choice of 6 investments, so use a max of 30% for any one investment – this is
higher than normal investment advice, but that is when you have a much larger
selection of possible investments).
C <= 300,000 (30% of 1,000,000)
M<= 300,000
TS1 <= 300,000
TS2 <= 300,000
U1 <= 300,000
U2 <= 300,000
The constraints for the maximum invested in each sector are
(this example uses 50%. Again, this is higher than normal conventions, but we
only have four sectors in our example – cash, bonds, tech, utilities).
C <= 500,000 (50% of 1,000,000)
M <= 500,000
TS1+TS2 < 500,000
U1 + U2 < 500,000
The complete formulation of the problem now has one
objective function, six decision variables, and twelve constraints.
In Excel, the problem is set up as shown below:
To complete the solution of the problem, pull up the Solver dialogue box. The Total Potential Return in cell H13 is the objective function. The functions for the constraints are given in column G and the limits of the constraints are given in column H. Each constraint uses the sumproduct(array1,array2) function, where array 1 is the group of cells A10:F10 (the decision variables) and array 2 is group of constraint coefficients in the rows 18 through 32. The completed dialogue box for Solver is:
Clicking the Solve box and the spreadsheet is updated with the optimal solution:
The Solver solution says to invest $42,587 in Cash, $300,000
in Muni Bond, $300,00 in Tech Stock 1, $57,143 in Tech Stock 2, $300,000 in
Utility 1 and $0 in Utility 2. The potential annual return is projected to be
$58,429.
Viewing column G, we see all the constraints are met.
Once a user has completed a modest size problem as the above, it is just a matter of adding additional decision variables (columns) and constraints (rows) to solve much larger problems.
Solver Set-up:
In Excel, from the top ribbon click on Data then look for Solver. If it is not
there, go to File and click on Options. Select Add-ins then click on Go. In the
dialogue box, click Solver then OK. Now, when you return to Data on the ribbon,
Solver should be there. For your version of Excel, the steps may be different,
so review this link from Frontline for your version of Excel: Search | solver.
For another example of using Solver see: A Geometry Puzzle Solved with Solver.
.png)