And this problem, you will calculate the expected return and standard deviation of three different stocks and portfolio of stocks and choose the best investment opportunities.
Excel 2016 Project Grader Instructions Zutter_Spreadsheet_Chapter_8 Project Description: In this problem, you will calculate the expected return and standard deviation of three different stocks and portfolio of stocks and choose the best investment opportunities. Steps to Perform: Instructions Points Possible Step 3 1 3 2 3 3 Start Excel. Download and open the workbook named: Zutter_Spreadsheet_Chapter_7_Start.xlsx In cells E30, F30 and G30, by using cell references to the given data and the function AVERAGE, calculate the expected return of stocks A, B and C, respectively. In cells E35, F35 and G35, by using cell references to the given data and the function STDEV.S, calculate the standard deviation of stocks A, B and C, respectively. In cells E36, F36 and G36, by using cell references to the given data, calculate the coefficient of variation of stocks A, B and C, respectively. In cell range E41:E47, by using cell references to the given data, calculate the expected return of portfolio AB for years 2012-2018 In cell range F41:F47, by using cell references to the given data, calculate the expected return of portfolio AC for years 2012 2018 In cell range G41:647, by using cell references to the given data, calculate the expected return of portfolio BC for years 2012:2018 7 4 7 5 7 6 3 7 In cells E48, F48 and G48, by using cell references to the given data and the function AVERAGE, calculate the expected return of portfolios AB, AC and BC, respectively. In cells E53, F53 and 53, by using cell references to the given data and the function STDEV.S, calculate the standard deviation of portfolios AB, AC and BC, respectively 3 8 3 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 1 10 1 11 In cell F61, type either more or less depending on your previous answers for stock A and portfolio AB 1 12 In cell C69, type either higher or lower depending on your previous answers for stock Band portfolio BC 1 13 14 1 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 B and portfolio BC. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed 15 0 ted On: 07/05/2019 Zutter_Spreadsheet_Chapter 8 AutoSave CH 1 Sanders_Zutter Spreadsheet_Chapter_8_Start - Read-Only - Excel Formulas Data Review View File Help Page Layout Insert Home == General LG Times New Roman - 14 AA BIU - AA 2 Wrap Text E Merge & Center $ -% Paste 15 Clipboard Font Numbe Alignment A1 f E D G 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 vears 2012 through 2018. The data are given in the following table + 5 6 7 8 9 10 Year 2012 2013 2014 2015 2016 2017 2018 Stock A 10% 139 159% 1496 16% 14% 1296 Stock B 10% 11% 8% 12% Stock 1296 14% 10% 119 10% 996 996 1596 1596 12 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. 14 15 15 12 18 20 To Do Create a spreadsheet similar to Tables 8.6 and 8.7 to answer the following: a. Calculate the average retum for each individual stock 1. Calculate the standard deviation for each individual stock c. Calculate the average retums 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-retum viewpoint. 1. 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 tisk-retum viewpoint. 21 22 24 Chapter 8 Read Type here to search AutoSave Sanders_Zutter_Spreadsheet_Chapter_2_Start - Read-Only Data Review Formulas View Help File Home Insert Page Layout X Times New Roman 14 23 Wrap Text - A A -A-A- Paste BIU SEE Merge & Center Clipboard Alignment F Font A1 . D E AB Solution 25 26 a. Calculate the average retum for each individual stock. 27 28 29 Stock A Stock B Stock C 30 Expected retum 31 32 b. Calculate the standard deviation for each individual stock. 34 Stock A Stock B Stock C 35 36 Standard deviation Coefficient of variation 37 38 c. Calculate the average retums for portfolios AB, AC, and BC 40 Port. AB Port. AC Port. BC 41 43 Year 2012 2013 2014 2015 2016 2017 2018 44 45 45 48 Expected retum d Calentata the candard desitions for nortfolios ARAC and RC Chapter 8 Read Type here to search Sanders_Zutter Spreadsheet_Chapter_e_Start - Read-Only - Excel AutoSave Data Formulas Review View Help File Insert Home Page Layout General X Times New Roman -14 A A BIU - - A.A. Wrap Text Merge & Center $ % 9 Paste Number Font Alignment Clipboard A1 E 50 d. Calculate the stang deviations for portfolios AB, AC, and BC 51 Port AB Port AC Port BC 59 Standard deviation Coefficient of variation 54 15 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-retum viewpoint. 56 57 58 50 61 Stock A has an expected return of 0.0096 with a standard deviation of 0.0096 Investing in the portfolio has a standard deviation of 0.0096 so there is both a amount of risk and return in the portfolio We can see that the CV of the portfolio is than that of stock A alone, so the portfolio of AB should be recommended 1 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-retur viewpoint. 62 63 64 65 BS 67 Stock B has an expected retum of 0.0096 with a standard deviation of 0.0096 Investing in the portfolio comprised of stocks B and delivers a retum of 0.0096 and is associated with a standard deviation of 0.0096 So both the retum and risk of the portfolio are Considering the CV, however, Jane, can determine that is preferable to because the CV is lower 69 70 71 Points 73 Requirements Chapter 8 Read Type here to search o (hp
