Assignment 6: Mean Variance Optimization Upload Both Q1 and Q2 as Excel Files in Canvas One Set of Files per Team

Q1. Selectthree (3) stocksthat are of interest to you, but select each stock from a different industry. This will insure some degree of nave diversification and a greater potential for portfolio risk (standard deviation of portfolio returns) reduction. Once you select your three stocks,replicate the example in the Three Security Portfolio_ER_SD.xls file using your three stocks. In addition to the weights shown on the spreadsheet, addsix (6) additional rowsto thePortfolio Weightsand calculate the Portfolio E(R), SD and Sharpe Ratio for these new weights:

XOMTAAPL

10.0% 20.0% 70.0% 20.0% 30.0% 50.0% 30.0% 40.0% 30.0% 40.0% 50.0% 10.0% 70.0% 20.0% 10.0% 10.0% 70.0% 20.0%

You will need to calculate monthly total returns from the most recent 24-month period. Calculate monthly total returns based on the month-ending Adj Close* price found under Historical Prices on the www.finance.yahoo.com website.

Total Monthly Return = [Adj Close* Price Month (t) Adj Close* Price Month (t-1)] / Adj Close* Price Month (t-1)

The Adj Close* price is adjusted for dividends and splits, so this price is all that you need to calculate a total return.

Q2.Duplicate (use the data provided, but construct your own Excel spreadsheet) Figures 1 through Figure 4 in the AAII article, Mean Variance Optimization: Multi-Asset Portfolio. The purpose of having you duplicate the figures in this article is so that you can accurately perform mean variance optimization for multiple assets should you wish to use this technique in your own personal investment portfolio(s). There is one typo in the article. You will need to insert an extra ) in the equation for the Portfolio Standard Deviation as shown below

Using the weightings of the individual stocks in the portfolio and the covariance matrix, we can calculate the portfolio standard deviation with this formula in cell

B61: =SQRT(MMULT(MMULT(TRANSPOSE(B49:B58),($B$33:$K$42)),B49:B58))

* Submit Assignment 6 as *.xls file. (2016)