PORTFOLIO OPTIMIZATION - Dynamic
Chart
If you increased the expected return of a risky asset, what would happen to the efficient frontier curve or the efficient trade-off line (tangent line)? What would happen if you increased the standard deviation of a risky asset? What would happen if you increased the correlation between risky assets? You can answer these questions and more by creating an Dynamic Chart using “spinners.” Spinners are up-arrow / down-arrow buttons that allow you to easily change the inputs to the model with the click of a mouse. Then the spreadsheet recalculates the model and instantly redraws the model outputs on the graph.
FIGURE 1. Spreadsheet for Portfolio Optimization - Dynamic Chart.
How To Build This Spreadsheet Model.
1. Start with the Many Assets Spreadsheet, Add Rows, and
Rearrange A Bit. Open the spreadsheet
that you created for Portfolio Optimization - Many Assets and immediately save
the spreadsheet under a new name using the File Save As
command. Move both of the
graphs out of the way. Select each graph and drag them below row 22
and to the right of column H. Add
four rows by selecting the range A19:A22,
clicking on Insert,
Rows.
Select the range E2:F10 and drag
the range (hover the cursor over the lower highlighted line, click on the left
mouse button, and hold it down while you move it) to cell H26.
2. Increase Row Height for the Spinners. Select
the range A5:A10.
Then click on Format Row Height from the main menu. Enter a
height of 30 and click on OK. Select
the range A19:A22.
Then click on Format Row Height from the main menu. Enter a
height of 30 and click on OK.
3. Display the Forms Toolbar. Select View Toolbars Forms from the main menu.
4. Create the Spinners. Look for the up-arrow / down-arrow button on
the Forms toolbar (which will display the word “Spinner” if you
hover the cursor over it) and click on it. Then draw the box for a spinner from
the upper left corner of cell D5 down to the lower right corner of the cell.
Then a spinner appears in the cell D5. Right click on the spinner (press the right
mouse button while the cursor is above the spinner) and a small menu pops up.
Click on Copy. Then select the
cell D6
and click on Paste. This creates
an identical spinner in the cell D6. Repeat the process
four times more in cells D7, D8, D9,
and D10. Repeat the process
five times more in cells G6, G7, G8, G9, and
G10. Click on the
spinner button on
the Forms toolbar and draw the box for a spinner covering the left
half of cell B19.
Click on Copy. Then select the
cell B20
and click on Paste. Repeat the process
eight times more in cells B21, B22, C20, C21, C22, D21, D22, and E22.
5. Create The Cell Links. Right click on the first spinner in the cell
D5
and a small menu pops up. Click on Format Control and a dialog box pops up. Click on the Control tab, then enter the cell link E5 in the Cell
link edit box and click on OK.
Repeat this procedure for the spinners in the range D6:D10
and in the range G6:G10
to link each one to the cell immediately to the right of each spinner. Right click on the
spinner in the cell B19
and a small menu pops up. Click on Format Control and a dialog box pops up. Click on the Control tab, then enter the same cell
B19 as the cell link in the Cell
link edit box and click on OK.
Repeat this same cell procedure for the spinners in the range B20:B22
and in the triangle C20
to C22 to
E22
(linking each one to the same cell as the spinner). Test your spinners by clicking on the
up-arrows and down-arrows of the spinners to see how they change the values in
the linked cells.
6. Create Scaled Inputs. The values in the linked cells are always
integers, but they can be scaled appropriately to the problem at hand. In cell C5,
enter =E5/500 and copy this cell to the
range C6:C10
and to the range F6:F10.
In cell B15,
enter =B19/10-1 and copy the cell to the
range B20:B22
and to the triangle C20
to C22 to
E22.
7. Relocate The Graphs. Move both of the graphs back into place. Move the Efficient Frontier graph into the range G11:K22. Move the Portfolio Weights graph into the range I5:K10.
Your Dynamic Chart allows you to change Portfolio Optimization inputs and instantly see the impact on a graphs of the efficient frontier and portfolio weights. This allows you to perform instant experiments on the efficient frontier and portfolio weights. The portfolio weights of the optimal (tangent) portfolio are a trade-off between putting more in assets with higher expected returns vs. spreading investment out evenly to lower portfolio risk by diversification. You can see how this trade-off works by doing some of the experiments listed below: