Spreadsheet Exercise: Chapter 8 Jane is considering investing in three different stocks or creating three distinct twostock portfolios. Jane views herself as a rather conservative investor. She is able to obtain historical returns for the three securities for the years 2012 through 2018. The data are given in the following table. Stock B 10% 11% Year 2012 2013 2014 2015 2016 2017 2018 Stock A 10% 13% 15% 14% 16% 14% 12% 8% 12% 10% 15% 15% Stock C 12% 14% 10% 11% 9% 9% 10% In any of the possible two-stock portfolios, the weight of each stock in the portfolio will be 50%. The three possible portfolio combinations are AB, AC, and BC. To Do Create a spreadsheet similar to Tables 8.6 and 8.7 to answer the following: a. Calculate the average return for each individual stock. b. Calculate the standard deviation for each individual stock. c. Calculate the average returns for portfolios AB, AC, and BC. d. Calculate the standard deviations for portfolios AB, AC, and BC. e. Would you recommend that Jane invest in the single stock A or the portfolio consisting of stocks A and B? Explain your answer from a risk-retuin viewpoint. f. Would you recommend that Jane invest in the single stock B or the portfolio consisting of stocks B and C? Explain your answer from a riskreturn viewpoint. Activate Windows A1 X f A B D F G H J K L M 24 25 Solution 26 27 a. Calculate the average return for each individual stock. 28 29 Stock A Stock B Stock C 30 Expected return 31 32 b. Calculate the standard deviation for each individual stock. 33 34 Stock A Stock B Stock C 35 Standard deviation Coefficient of variation 36 37 38 c. Calculate the average returns for portfolios AB, AC, and BC. 39 40 Port. AB Port. AC Port. BC 41 Year 2012 2013 2014 42 43 44 45 2015 2016 2017 2018 46 47 48 Expected return 49 50 d. Calculate the standard deviations for portfolios AB, AC, and BC. 51 Activate Wind A B E G H Expected return 8 9 0 d. Calculate the standard deviations for portfolios AB, AC, and BC. 1 2 Port. AB Port. AC Port. BC 3 Standard deviation Coefficient of variation 84 5 e. Would you recommend that Jane invest in the single stock A or the portfolio consisting of stocks A and B? Explain your answer from a risk-return viewpoint. 6 7 8 with a standard deviation of 0.00% 0.00% so there is both a 9 50 Stock A has an expected return of 0.00% Investing in the portfolio has a standard deviation of amount of risk and return in the portfolio. We can see that the CV of the portfolio is should be recommended. 51 than that of stock A alone, so the portfolio of AB 2 3 f. Would you recommend that Jane invest in the single stock B or the portfolio consisting of stocks B and C? Explain your answer from a risk-return viewpoint. 64 55 6 57 8 Stock B has an expected return of 0.00% with a standard deviation of 0.00% Investing in the portfolio comprised of stocks B and C delivers a return of 0.00% and is associated with a standard deviation of 0.00% So both the return and risk of the portfolio are Considering the CV, however, Jane, can determine that is preferable to because the CV is lower. 59 FO -1 -2 Points 3 3 3 7 7 7 3 73 Requirements 1 In cells E30, F30 and G30, by using cell references to the given data and the function 74 AVERAGE, calculate the expected return of stocks A, B and C, respectively. 2 In cells E35, F35 and G35, by using cell references to the given data and the function 75 STDEV.S, calculate the standard deviation of stocks A, B and C, respectively. 3 In cells E36, F36 and G36, by using cell references to the given data, calculate the coefficient of 76 variation of stocks A, B and C, respectively. 4 In cell range E41:E47, by using cell references to the given data, calculate the expected return of 77 portfolio AB for years 2015:2017. 5 In cell range F41:F47, by using cell references to the given data, calculate the expected return of 78 portfolio AC for years 2015:2017. 6 In cell range G41:G47, by using cell references to the given data, calculate the expected return of 79 portfolio BC for years 2015:2017. 7 In cells E48, F48 and G48, by using cell references to the given data and the function 80 AVERAGE, calculate the expected return of portfolios AB, AC and BC, respectively. 8 In cells E53, F53 and G53, by using cell references to the given data and the function 81 STDEV.S, calculate the standard deviation of portfolios AB, AC and BC, respectively. 9 In cells E54, F54 and G54, by using cell references to the given data, calculate the coefficient of variation of portfolios AB, AC and BC, respectively. # In cell J59, type either higher or lower depending on your previous answers for stock A and portfolio AB # In cell F61, type either more or less depending on your previous answers for stock A and portfolio AB. # In cell C69, type either higher or lower depending on your previous answers for stock B and portfolio BC. # In cell H69, type either stock B or portfolio BC depending on your previous answers for stock B and portfolio BC. # In cell C70, type either stock B or portfolio BC depending on your previous answers for stock 87 B and portfolio BC. 3 3 82 1 83 1 84 1 85 1 86 1 Activate Go to Settir