Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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%
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started