Please help me with the yellow highlighted questions. thank you so much you guys are a lifesaver!

A1 Xfx Build a Model A B D D E F 6 a. Use the data given to calculate annual returns for Goodman, Landry, and the Market Index, and then 7 calculate average returns over the five-year period. (Hint: Remember, returns are calculated by subtracting 8 the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital 9 gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the 10 Index. Also, you cannot calculate the rate of return for 2014 because you do not have 2013 data.) 11 12 Data as given in the problem are shown below: 13 Goodman Industries Landry Incorporated Market Index 14 Year Stock Price Dividend Stock Price Dividend Includes Divs. 15 2019 $25.88 $1.73 $73.13 $4.50 17,495.97 16 2018 $22.13 $1.59 $78.45 $4.35 13,178.55 17 2017 $24.75 $1.50 $73.13 $4.13 13,019.97 18 2016 $16.13 $1.43 $85.88 $3.75 9,651.05 19 2015 $17.06 $1.35 $90.00 $3.38 8,403.42 20 2014 $11.44 $1.28 $83.63 $3.00 7,058.96 21 22 We now calculate the rates of retum for the two companies and the index: 23 24 Goodman Landry Index 25 2019 26 2018 27 2017 28 2016 29 2015 30 31 Average 32 33 Note: To get the average, you could get the column sum and divide by 5, but you could also use the function 34 wizard, fx. Click fx, then statistical, then Average, and then use the mouse to select the proper range. Do this for 35 Goodman and then copy the cell for the other items. 36 37 b. Calculate the standard deviation of the returns for Goodman, Landry, and the Market Index. (Hint: Use the 38 sample standard deviation formula given in the chapter, which corresponds to the STDEV function in Excel.) 39 40 Use the function wizard to calculate the standard deviations. 41 42 Goodman Landry Index 43 Standard deviation of retums 44 45 46 c. Construct a scatter diagram graph that shows Goodman's and Landry' returns on the vertical axis and the 48 Market Index's returns on the horizontal axis. 49 50 It is easiest to make scatter diagrams with a data set that has the X-axis variable in the left column, 51 so we reformat the retums data calculated above and show it just below. 52 53 Year Index Goodman Landry 54 2019 0.0% 0.0% 0.0% 55 2018 0.0% 0.0% 0.0% 56 2017 0.0% 0.0% 0.0% 57 2016 0.0% 0.0% 0.0% 58 2015 0.0% 0.0% 0.0% 59 60 61 62 63 54 A1 > fx fe Build a Model A B B D E F 50 It is easiest to make scatter diagrams with a data set that has the X-axis variable in the left column, 51 so we reformat the retums data calculated above and show it just below. 52 53 Year Index Goodman Landry 54 2019 0.0% 0.0% 0.0% 55 2018 0.0% 0.0% 0.0% 56 2017 0.0% 0.0% 0.0% 57 2016 0.0% 0.0% 0.0% 58 2015 0.0% 0.0% 0.0% 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 To make the graph, we first selected the range with the retums and the column heads, then clicked the chart wizard, 77 then choose the scatter diagram without connected lines. That gave us the data points. We then used the drawing 78 toolbar to make free-hand (by eye") regression lines, and changed the lines color and weights to match the dots. 79 80 81 82 83 d. Estimate Goodman's and Landry's botas as the slopes of regression lines with stock returns on the 84 vertical axis (y-axis) and market return on the horizontal axis (x-axis). (Hint: use Excel's SLOPE function.) 85 Are these betas consistent with your graph? 86 87 Goodman's beta B8 89 Landry' beta- 90 91 92 93 94 95 e. The risk-free rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the 96 expected return on the market? Now use the SML equation to calculate the two companies' required returns. 97 98 Market risk premium (RPM) - 5.000% 99 Risk-free rate = 6.040% 100 101 Expected retum on markets Ruisk-free rate Market rak premium 102 6.040% 5.000% 103 11.040% 104 105 Required retum 106 107 Goodman: 108 Regulad motum 3.no Build a Model + = + + 94 95 . The risk-free rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the 96 expected return on the market? Now use the SML equation to calculate the two companies' required returns. 97 98 Market risk premium (RPM) = 5.000% 99 Risk-free rate 6.040% 100 101 Expected retum on market = Risk-free rate Market risk premium 102 6.040% 5.000% 103 11.040% 104 105 Required retum 106 107 Goodman: 108 Required retum 109 110 111 Landry: 112 Required retum 113 = 1114 115 116 117 118 119. If you formed a portfolio that consisted of 50% Goodman stock and 50% Landry stock, what would be its 120 beta and its required return? 121 122 The beta of a portfolio is simply a weighted average of the betas of the stocks in the portfolio, so this portfollo's beta 123 would be: 124 125 Portfolio beta = 126 127 3. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and C 128 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 129 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 130 Btock C. 131 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 40% 137 Stock C 1.423 20% 138 100% 139 Portfolio Beta = 140 141 Required retum on portfolio: Risk-free rate + Market Risk Premium Beta 142 143 144 145 1106 ### A1 Xfx Build a Model A B D D E F 6 a. Use the data given to calculate annual returns for Goodman, Landry, and the Market Index, and then 7 calculate average returns over the five-year period. (Hint: Remember, returns are calculated by subtracting 8 the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital 9 gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the 10 Index. Also, you cannot calculate the rate of return for 2014 because you do not have 2013 data.) 11 12 Data as given in the problem are shown below: 13 Goodman Industries Landry Incorporated Market Index 14 Year Stock Price Dividend Stock Price Dividend Includes Divs. 15 2019 $25.88 $1.73 $73.13 $4.50 17,495.97 16 2018 $22.13 $1.59 $78.45 $4.35 13,178.55 17 2017 $24.75 $1.50 $73.13 $4.13 13,019.97 18 2016 $16.13 $1.43 $85.88 $3.75 9,651.05 19 2015 $17.06 $1.35 $90.00 $3.38 8,403.42 20 2014 $11.44 $1.28 $83.63 $3.00 7,058.96 21 22 We now calculate the rates of retum for the two companies and the index: 23 24 Goodman Landry Index 25 2019 26 2018 27 2017 28 2016 29 2015 30 31 Average 32 33 Note: To get the average, you could get the column sum and divide by 5, but you could also use the function 34 wizard, fx. Click fx, then statistical, then Average, and then use the mouse to select the proper range. Do this for 35 Goodman and then copy the cell for the other items. 36 37 b. Calculate the standard deviation of the returns for Goodman, Landry, and the Market Index. (Hint: Use the 38 sample standard deviation formula given in the chapter, which corresponds to the STDEV function in Excel.) 39 40 Use the function wizard to calculate the standard deviations. 41 42 Goodman Landry Index 43 Standard deviation of retums 44 45 46 c. Construct a scatter diagram graph that shows Goodman's and Landry' returns on the vertical axis and the 48 Market Index's returns on the horizontal axis. 49 50 It is easiest to make scatter diagrams with a data set that has the X-axis variable in the left column, 51 so we reformat the retums data calculated above and show it just below. 52 53 Year Index Goodman Landry 54 2019 0.0% 0.0% 0.0% 55 2018 0.0% 0.0% 0.0% 56 2017 0.0% 0.0% 0.0% 57 2016 0.0% 0.0% 0.0% 58 2015 0.0% 0.0% 0.0% 59 60 61 62 63 54 A1 > fx fe Build a Model A B B D E F 50 It is easiest to make scatter diagrams with a data set that has the X-axis variable in the left column, 51 so we reformat the retums data calculated above and show it just below. 52 53 Year Index Goodman Landry 54 2019 0.0% 0.0% 0.0% 55 2018 0.0% 0.0% 0.0% 56 2017 0.0% 0.0% 0.0% 57 2016 0.0% 0.0% 0.0% 58 2015 0.0% 0.0% 0.0% 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 To make the graph, we first selected the range with the retums and the column heads, then clicked the chart wizard, 77 then choose the scatter diagram without connected lines. That gave us the data points. We then used the drawing 78 toolbar to make free-hand (by eye") regression lines, and changed the lines color and weights to match the dots. 79 80 81 82 83 d. Estimate Goodman's and Landry's botas as the slopes of regression lines with stock returns on the 84 vertical axis (y-axis) and market return on the horizontal axis (x-axis). (Hint: use Excel's SLOPE function.) 85 Are these betas consistent with your graph? 86 87 Goodman's beta B8 89 Landry' beta- 90 91 92 93 94 95 e. The risk-free rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the 96 expected return on the market? Now use the SML equation to calculate the two companies' required returns. 97 98 Market risk premium (RPM) - 5.000% 99 Risk-free rate = 6.040% 100 101 Expected retum on markets Ruisk-free rate Market rak premium 102 6.040% 5.000% 103 11.040% 104 105 Required retum 106 107 Goodman: 108 Regulad motum 3.no Build a Model + = + + 94 95 . The risk-free rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the 96 expected return on the market? Now use the SML equation to calculate the two companies' required returns. 97 98 Market risk premium (RPM) = 5.000% 99 Risk-free rate 6.040% 100 101 Expected retum on market = Risk-free rate Market risk premium 102 6.040% 5.000% 103 11.040% 104 105 Required retum 106 107 Goodman: 108 Required retum 109 110 111 Landry: 112 Required retum 113 = 1114 115 116 117 118 119. If you formed a portfolio that consisted of 50% Goodman stock and 50% Landry stock, what would be its 120 beta and its required return? 121 122 The beta of a portfolio is simply a weighted average of the betas of the stocks in the portfolio, so this portfollo's beta 123 would be: 124 125 Portfolio beta = 126 127 3. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and C 128 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 129 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 130 Btock C. 131 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 40% 137 Stock C 1.423 20% 138 100% 139 Portfolio Beta = 140 141 Required retum on portfolio: Risk-free rate + Market Risk Premium Beta 142 143 144 145 1106 ###