Excel exercises 4. Download the Excel file "PS2 Data" from Aula Global. It contains the actual) annual returns on Coca-Cola (KO) and Walmart (WMT) over the past 31 years (1980-2010). a. Calculate of the arithmetic average return (function -AVERAGE) and the volatility of the return (function STDEV) for the 2 stocks, and the correlation between them (function -CORREL). sing the numbers above as estimates of the expected return, standard deviation, and correlation, plot the investment opportunity set using the two stocks for weights in Coca- Cola between 0% and 150% (0 to 1.5). c. What are the approximate weights to the nearest 1%) in the minimum variance portfolio? (Trial and error is a viable strategy. There is an analytical solution, but it requires the application of some calculus. Again, you can also use the Solver tool in Excel.) d. Assuming an annual risk-free rate of 5%, what are the approximate weights (to the nearest 1%) in the portfolio with the maximum Sharpe ratio? (Trial and error is a viable strategy. Again, there is an analytical solution, but it is very complex so probably not worth the effort. In this case, Solver is clearly the preferred methodology.) Sheet1 Sheet2 Sheet3 Returns KO WMT 3.49% 75 82% 11.15% 41.46% 50.77% 136, 12% 8.2545 57.00% 1906 1987 69 21% 382 12 26% 21.324 75.36 95.14% 5.82% 10% 8.285 21 51% 17 43% -14 41% 46.125 54% 1900 2002 27.88% 74 74% 12.11% 5.915 0.42 2280 2007 -5.50 -11.74% 2009 2010 - 16.09% -06115 0.51% -10 27% 30 .10% 2012 30.40% 4.89% 2014 2015 30.08% 19.04% 2.62% 3.25% Excel exercises 4. Download the Excel file "PS2 Data" from Aula Global. It contains the actual) annual returns on Coca-Cola (KO) and Walmart (WMT) over the past 31 years (1980-2010). a. Calculate of the arithmetic average return (function -AVERAGE) and the volatility of the return (function STDEV) for the 2 stocks, and the correlation between them (function -CORREL). sing the numbers above as estimates of the expected return, standard deviation, and correlation, plot the investment opportunity set using the two stocks for weights in Coca- Cola between 0% and 150% (0 to 1.5). c. What are the approximate weights to the nearest 1%) in the minimum variance portfolio? (Trial and error is a viable strategy. There is an analytical solution, but it requires the application of some calculus. Again, you can also use the Solver tool in Excel.) d. Assuming an annual risk-free rate of 5%, what are the approximate weights (to the nearest 1%) in the portfolio with the maximum Sharpe ratio? (Trial and error is a viable strategy. Again, there is an analytical solution, but it is very complex so probably not worth the effort. In this case, Solver is clearly the preferred methodology.) Sheet1 Sheet2 Sheet3 Returns KO WMT 3.49% 75 82% 11.15% 41.46% 50.77% 136, 12% 8.2545 57.00% 1906 1987 69 21% 382 12 26% 21.324 75.36 95.14% 5.82% 10% 8.285 21 51% 17 43% -14 41% 46.125 54% 1900 2002 27.88% 74 74% 12.11% 5.915 0.42 2280 2007 -5.50 -11.74% 2009 2010 - 16.09% -06115 0.51% -10 27% 30 .10% 2012 30.40% 4.89% 2014 2015 30.08% 19.04% 2.62% 3.25%
