The HW1-3.xlsx spreadsheet contains monthly returns from 1991 to 2016 on Exxon (XOM),Coca-Cola (COKE), IBM, Procter & Gamble (PG), Sony (SNE), Walmart (WMT), andFedEx (FDX), along with the S&P 500 market portfolio and the one-month risk-free rate.You may use the Excel-based Markowitz portfolio optimizer that comes with the Bodie, Kane, and Marcus textbook for answering the questions below. You can also use the alternativeExcel-based Markowitz portfolio optimizer that I have posted on the class website or anyother software package.
Hints on using the Excel-based portfolio optimizer. Open the spreadsheet and click enable the macro. Input the mean, sd, and corr, together with a set of random, but non-zero, startingvalues for the portfolio weights. Open solver, and set the objective (choose “maximize”), setthe choice variables (the variables to vary in order to optimize the objective), and set anyconstraints (are there any constraints in our optimization?). Finally, click “solve”.
a. Compute the average returns, sample standard deviations, and correlations for each ofthe seven stocks.
b. Calculate the optimal risky portfolio comprised of the seven stocks, assuming no short-sellconstraints and unlimited lending and borrowing at the average one-month risk-free rate.What is the Sharpe Ratio of this optimal risky portfolio? How does it compare to theSharpe Ratios for each of the individual stocks? Why? What do you think about thecomposition of this optimal risky portfolio? Does it involve short positions?
c. Now assume that the single-index model is true. Calculate the betas and the correlationmatrix for the seven stocks under this assumption. How do the single-index model correlations compare to the sample correlations that you calculated before? Why are theydifferent?
3
d. Using the correlation matrix from the single-index model, calculate the optimal riskyportfolio. How does this portfolio compare to the one that you calculated before?
e. Now suppose that the CAPM holds true. Using the CAPM expected returns in place ofthe sample means, together with the original sample variances and correlations, calculatethe optimal risky portfolio. What do you think about the composition of this portfolio?Why does it look so different from the two portfolios that you calculated before?
f. The average historical return on FDX was substantially higher than the expected returnimplied by the CAPM over this period. Standing in December 2016, suppose that youbelieve that this historically superior performance of FDX is likely to continue in the“future.” Specifically, suppose that your estimate of alpha for FDX relative to the CAPMis 1% per month. Note, this is a very “aggressive” alpha. Also, suppose you believe thatall of the other stocks are fairly priced according to the CAPM. How do the weights forthe optimal risky portfolio constructed under these assumptions compare to the weightsthat you calculated under the assumption that the CAPM is true for all of the stocks?
The Black-Litterman model that we briefly discussed in class provides a more sophisticated approach for incorporating supposedly superior information, by allowing an investorto express his/her “view” that the future return on FDX will exceed the return predictedby the CAPM, along with a degree of confidence in that “view.”

Answer rating: 100% (QA)

aThe average returns are 00062 00075 00057 00128 00082 00131 and 00189 respectively The sample standard deviations are 00209 00172 00234 00270 00244 00195 and 00237 respectively The correlations are 0... View the full answer