In the model in Example 7.9, stock 2 is not in the optimal portfolio. Use SolverTable to see whether it ever enters the optimal portfolio as its correlations with stocks 1 and 3 vary. Specifically, use a two-way SolverTable with two inputs, the correlations between stock 2 and stocks 1 and 3, each allowed to vary from 0.1 to 0.9 in increments of 0.1. Capture as outputs the three changing cells. Discuss the results. (Note: You will have to change the model slightly. For example, if you use cells B10 and C11 as the two SolverTable input cells, you will have to ensure that cells C9 and D10 change accordingly. This is easy. Just put formulas in these latter two cells.)
=SUMPRODUCT($B$6:$H$6,B10:H10)/4
in cell B31 for center 1 and copy it down for the other two centers. (Note that division by 4 is used to obtain a weekly average.) These weekly averages become the targets. Next, calculate the actual hours used at each center each week in the range B37:E39. Unfortunately, there is no way to enter a single formula and then copy it to the rest of the range. However, you can try the following. Enter the formula
=C11-D11 in cell E11 and copying down. Then calculate the sum of squared errors in cell B17 with the formula
=SUMPRODUCT($B$5:$C$5,B9:C9)
=RISKSIMTABLE (D10:H10)
EXAMPLE 7.9 PORTFOLIO SELECTION AT PERLMAN & BROTHERS
Perlman & Brothers, an investment company, intends to invest a given amount of money in three stocks. From past data, the means and standard deviations of annual returns have been estimated as shown in Table 7.7. The correlations among the annual returns on the stocks are listed in Table 7.8. The company wants to find a minimum-variance portfolio that yields an expected annual return of at least 0.12.
Objective To use NLP to find the portfolio of the three stocks that minimizes the risk, measured by portfolio variance, subject to achieving an expected return of at least 0.12.
WHERE DO THE NUMBERS COME FROM? Financial analysts typically estimate the required means, standard deviations, and correlations for stock returns from historical data, as discussed at the beginning of this section. However, you should be aware that there is no guarantee that these estimates, based on historical return data, are relevant for future returns. If analysts have new information about the stocks, they should incorporate this new information into their estimates.