Uraw Page Layout Formulas Data Review View Tell me X Arial - 12 A A 20 General Pasta BU Av {"4800 $% 23 sent Delete It Format Conditional Format Cell Formatting as Table Styles Z Sort A1 fx Build a Model - Filter Find & Selest A N O 95 The risk tree rate on long term Treasury brands is 6,045. Assume that the marketiak premium. What is the pected return on the market? Now use the SML quation to calculate the two companies' required return Marketprom 5.000 Home - 8.0405 100 101 Expected tomon 102 604 103 11040% 5000% 104 105 Required 104 107 Goodman 108 Required folum 100 190 111 Landy 112 Medrum 193 114 115 110 117 119 110. Wyou formed a portfolio that of Son Goodman stock and Sos Landry stock, what would be 120 Beta and its required nun? 121 123 The best of a portionly weighted average of the best of the stock in the pools, so the postela 124 125 Poets 127 Buppose an investor want to include Goodman Industries stock in his or her role Stocks A and 120 are currently in the portfolio and the beas are 00.305, and 1.423.revety Calculate the new 150 portfolio quired return it consists of 25% of Godman, 15% of Stock A, 40% of back and 20% of Stock 191 132 133 Bata Portfolio Weight 134 Good 25 135 Stock 0.789 15% 136 Stock 40% 31 Build a Model ty MacBook Pro 03 0 A1 Xvfx Build a Model UN Format B E G H 109 110 111 Landry: 112 Required retum 113 114 115 116 117 118 119t. 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 portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and C 128 129 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of Stock 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% 100% 139 Portfolio Beta - 140 141 Required retum on portfolio Risk-free rate + Market Risk Premium Beta 142 138 143 145 146 147 148 149 150 151 $ % .000 LUITUILUIL Formattin A92 B H + + 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 (RP) = 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 114 115 116 117 118 119t. 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 bota of a portfolio is simply a weighted average of the beas of the stocks in the portfolio, so this portfolio's beta 123 would be 124 125 Portfolio beta = 120 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio Stocks A, B, and C 129 are currently in the portfolio, and their bets are 0.769, 0.985, and 1.423, respectively. Calculate the new 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 131 Stock 132 133 Beta 134 Portfolio Weight Goodman 135 Stock A 25% 0.769 136 Stock B 15% 0.985 40% PU Formatting as Tabla K 131 A92 fx . 122 The beta of a portfolio is simply a weighted average of the betas of the stocks in the portfolio, so this portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 9. 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 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of Stock C 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 137 40% Stock C 1.423 20% 138 139 Portfolio Beta 1001 140 141 Required retum on portfolio: Risk-free rate + Market Risk Premium 142 Beta 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 - Build a Model 10 VA A General B IV IS E $ % 9 Conditional Format Formatting as Table A92 H B D 6. 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 14 Market Index Year Stock Price Dividend Stock Rice Dividend includes Divs 15 2019 $25.88 $1.73 $7313 $4.50 16 17,495,97 2018 $22.13 $1.59 $78.45 17 $4.35 2017 13,178.55 $24.75 $1.50 $73.13 $4.13 18 2016 13,019.97 $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 24.89 -1.0% 32.8% 26 2018 4.2% 13.2% 1.2% 27 2017 62.7% -10.0% 34.9% 28 2016 2.9% -0.4% 14.8% 29 2015 60.9% 11.7% 19.0% 30 31 Average 29.4% 2.7% 20.6% 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 cel 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 31.4% 9.7% 43 Standard deviation of retums 13.8% 44 45 46 47 c. Construct a scatter diagram graph that shows Goodman's and Landry' returns on the vertical axis and the Paste LU General A92 Conditional Format Cell Formatting as Table Styles . D 46 47 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 so it is easiest to make scatter diagrams with a data set that has the X-as variable in the left column 51 so we reformat the retums data calculated above and show just below 52 53 Year Index Goodman 54 2019 32 8% Landry 24.8% 55 2018 -1,0% 1.2% 4.2% 56 2017 15% 34,9% 62.7% 57 -100% 2016 14.8% 2.9% -0,4% 58 2015 19.0% 60.9% 59 11.7% 60 61 62 63 64 GS 66 67 68 69 70 74 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 ines. That gave us the data points. We then used the drawing 78 toolbar to make free-hand (by ayo") regression lines, and changed the lines color and weights to match the dots. 79 80 its clear that Goodman moves with the market and Landry moves counter to the market. So goodman has a postve beta 81 and Laundry a negative one. 82 83 d. Estimate Goodman's and Landry's betas as the slopes of regression lines with stock returns on the 84 vertical axis -axis) and market return on the horizontal axis ix-axis)(Hint: use Excel's SLOPE function) 85 Are these betas consistent with your graph? 86 87 Goodman's beta 1.54 BB Build a Model + A92 % Conditional Format Formatting as Table B 1.56 H 89 Landry' beta = 90 91 Landris bata suggests that it is less risky than average in a CAPM sense whereas goodman is more risky than average 92 93 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 (RP) = 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 114 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 beas of the stocks in the portfolio, so this portfolio's beta 123 would be: 124 125 Portfolio beta 126 127 9. 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 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 130 Stock C. 131 129 Build a Model A92 G H B C D TZT 122 The beta of a portfolio is simply a weighted average of the botas of the stocks in the portfolio, so this portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and G 129 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new portfolio's required return if it consists of 25% of Goodman, 15% of rock A, 40% of Stock B, and 20% of 130 Stock C. Beta 0.769 0.985 1.423 Portfolio Weight 25% 15% 40% 20% 100% Risk-free rate + Market Risk Premium Beta = 131 132 133 134 Goodman 135 Stock A 136 Stock B 137 Stock C 138 139 Portfolio Beta - 140 141 Required retum on portfolio: 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 Excel VIO AutoSave ON AR Chapter 6 - Las Home Insert Draw Page Layout Formulas Data Review View Tell me Arial 10 Gene ' A Paste BIU A92 X A 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 (RP) = 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 114 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 portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A B, and C 129 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of Stock C 131 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 40% Build a Model + View Excel File Edit Insert Format Tools AutoSave ON Data Window Help AR Chapter 6 - Last Me Review View Tell me Home Insert Draw Data Page Layout Formulas = A Arial v 10 General Paste v BIU hl A92 fx D 117 118 119t. 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 portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and C are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 129 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 131 Stock C 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 40% 137 Stock 1.423 20% 138 100% 139 Portfolio Beta - 140 141 Required rotum on portfolio Risk-free rate + Market Risk Premium 142 Beta 143 144 145 146 147 148 149 150 151 152 153 154 156 156 157 156 159 Build a Model 10 AutoSave ON ABSU- AR Chapter 6 - Last Mod Home Draw Formulas Insert Page Layout Data View Tell me Review JU V Arial ' 10 X [D General Y ili lil Paste BIU A ~ lili HI - $ % A92 D G H 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 80s clear that Goodman moves with the market and Landry moves counter to the market. So goodman has a positive beta 81 and Laundry a negative one. 82 33 d. Estimate Goodman's and Landry's betas as the slopes of regression lines with stock returns on the 134 vertical axis by-axis) and market return on the horizontal axis (x-axis). (Hint use Excel's SLOPE function.) 85 Are these betas consistent with your graph? 85 37 Goodman's beta 1.54 139 Landry' beta- -0.56 90 91 Landrys beta suggests that it is less risky than average in a CAPM sense whereas goodman is more risky than average 92 94 195. The risk tree rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the 38 expected return on the market? Now use the SML equation to calculate the two companies' required returns. 97 98 Market risk premium (RP) 5.000% 99 Risk tree rate 6.040% 100 01 Expected retum on market Risk-free rate Marketisk premium 02 6.040% 5.000% 03 11.040% 04 105 Required retum 06 107 Goodman 108 Required retum 109 110 111 Landry 112 Required retum 113 114 115 116 Build a Model 10 Excel File tail View AutoSave ON ASSU: AR Chapter 6 Insert Home Draw Page Layout Formulas Data Review View Tell me Arial = V 10 Paste B y y y IU lili G A92 B D F 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, tx. Click tx, 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 31.4% 9.7% 13.8% 44 45 46 47. 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 32.8% 24.8% -1.0% 55 2018 1.2% 4.2% 13.2% 56 2017 34.9% 62.7% -10.0% 57 2016 14.8% 2.9% -0.4% 58 2015 19.0% 60.9% 11.7% 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 Build a Model AutoSave ON AR Chapter 6 - Las View Tell me Home Insert Draw Page Layout Formulas Data Review 10 Arial V 10 V [0 A Gen Paste BIU v V y lili il I'I y A92 B D E F G 11/26/18 1 Build a Model 2 Chapter: 3 Problem: 6 15 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 24.8% -1.0% 32.8% 26 2018 4.2% 13.2% 1.2% 27 2017 62.7% -10.0% 28 34.9% 2016 2.9% -0.4% 29 14.8% 2015 80.9% 11.7% 30 19.0% 29.4% 2.7% 20.6% 32 31 Average 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, tx. Click tx, 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 Build a Model OCT Uraw Page Layout Formulas Data Review View Tell me X Arial - 12 A A 20 General Pasta BU Av {"4800 $% 23 sent Delete It Format Conditional Format Cell Formatting as Table Styles Z Sort A1 fx Build a Model - Filter Find & Selest A N O 95 The risk tree rate on long term Treasury brands is 6,045. Assume that the marketiak premium. What is the pected return on the market? Now use the SML quation to calculate the two companies' required return Marketprom 5.000 Home - 8.0405 100 101 Expected tomon 102 604 103 11040% 5000% 104 105 Required 104 107 Goodman 108 Required folum 100 190 111 Landy 112 Medrum 193 114 115 110 117 119 110. Wyou formed a portfolio that of Son Goodman stock and Sos Landry stock, what would be 120 Beta and its required nun? 121 123 The best of a portionly weighted average of the best of the stock in the pools, so the postela 124 125 Poets 127 Buppose an investor want to include Goodman Industries stock in his or her role Stocks A and 120 are currently in the portfolio and the beas are 00.305, and 1.423.revety Calculate the new 150 portfolio quired return it consists of 25% of Godman, 15% of Stock A, 40% of back and 20% of Stock 191 132 133 Bata Portfolio Weight 134 Good 25 135 Stock 0.789 15% 136 Stock 40% 31 Build a Model ty MacBook Pro 03 0 A1 Xvfx Build a Model UN Format B E G H 109 110 111 Landry: 112 Required retum 113 114 115 116 117 118 119t. 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 portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and C 128 129 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of Stock 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% 100% 139 Portfolio Beta - 140 141 Required retum on portfolio Risk-free rate + Market Risk Premium Beta 142 138 143 145 146 147 148 149 150 151 $ % .000 LUITUILUIL Formattin A92 B H + + 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 (RP) = 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 114 115 116 117 118 119t. 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 bota of a portfolio is simply a weighted average of the beas of the stocks in the portfolio, so this portfolio's beta 123 would be 124 125 Portfolio beta = 120 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio Stocks A, B, and C 129 are currently in the portfolio, and their bets are 0.769, 0.985, and 1.423, respectively. Calculate the new 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 131 Stock 132 133 Beta 134 Portfolio Weight Goodman 135 Stock A 25% 0.769 136 Stock B 15% 0.985 40% PU Formatting as Tabla K 131 A92 fx . 122 The beta of a portfolio is simply a weighted average of the betas of the stocks in the portfolio, so this portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 9. 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 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of Stock C 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 137 40% Stock C 1.423 20% 138 139 Portfolio Beta 1001 140 141 Required retum on portfolio: Risk-free rate + Market Risk Premium 142 Beta 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 - Build a Model 10 VA A General B IV IS E $ % 9 Conditional Format Formatting as Table A92 H B D 6. 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 14 Market Index Year Stock Price Dividend Stock Rice Dividend includes Divs 15 2019 $25.88 $1.73 $7313 $4.50 16 17,495,97 2018 $22.13 $1.59 $78.45 17 $4.35 2017 13,178.55 $24.75 $1.50 $73.13 $4.13 18 2016 13,019.97 $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 24.89 -1.0% 32.8% 26 2018 4.2% 13.2% 1.2% 27 2017 62.7% -10.0% 34.9% 28 2016 2.9% -0.4% 14.8% 29 2015 60.9% 11.7% 19.0% 30 31 Average 29.4% 2.7% 20.6% 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 cel 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 31.4% 9.7% 43 Standard deviation of retums 13.8% 44 45 46 47 c. Construct a scatter diagram graph that shows Goodman's and Landry' returns on the vertical axis and the Paste LU General A92 Conditional Format Cell Formatting as Table Styles . D 46 47 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 so it is easiest to make scatter diagrams with a data set that has the X-as variable in the left column 51 so we reformat the retums data calculated above and show just below 52 53 Year Index Goodman 54 2019 32 8% Landry 24.8% 55 2018 -1,0% 1.2% 4.2% 56 2017 15% 34,9% 62.7% 57 -100% 2016 14.8% 2.9% -0,4% 58 2015 19.0% 60.9% 59 11.7% 60 61 62 63 64 GS 66 67 68 69 70 74 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 ines. That gave us the data points. We then used the drawing 78 toolbar to make free-hand (by ayo") regression lines, and changed the lines color and weights to match the dots. 79 80 its clear that Goodman moves with the market and Landry moves counter to the market. So goodman has a postve beta 81 and Laundry a negative one. 82 83 d. Estimate Goodman's and Landry's betas as the slopes of regression lines with stock returns on the 84 vertical axis -axis) and market return on the horizontal axis ix-axis)(Hint: use Excel's SLOPE function) 85 Are these betas consistent with your graph? 86 87 Goodman's beta 1.54 BB Build a Model + A92 % Conditional Format Formatting as Table B 1.56 H 89 Landry' beta = 90 91 Landris bata suggests that it is less risky than average in a CAPM sense whereas goodman is more risky than average 92 93 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 (RP) = 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 114 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 beas of the stocks in the portfolio, so this portfolio's beta 123 would be: 124 125 Portfolio beta 126 127 9. 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 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 130 Stock C. 131 129 Build a Model A92 G H B C D TZT 122 The beta of a portfolio is simply a weighted average of the botas of the stocks in the portfolio, so this portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and G 129 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new portfolio's required return if it consists of 25% of Goodman, 15% of rock A, 40% of Stock B, and 20% of 130 Stock C. Beta 0.769 0.985 1.423 Portfolio Weight 25% 15% 40% 20% 100% Risk-free rate + Market Risk Premium Beta = 131 132 133 134 Goodman 135 Stock A 136 Stock B 137 Stock C 138 139 Portfolio Beta - 140 141 Required retum on portfolio: 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 Excel VIO AutoSave ON AR Chapter 6 - Las Home Insert Draw Page Layout Formulas Data Review View Tell me Arial 10 Gene ' A Paste BIU A92 X A 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 (RP) = 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 114 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 portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A B, and C 129 are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of Stock C 131 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 40% Build a Model + View Excel File Edit Insert Format Tools AutoSave ON Data Window Help AR Chapter 6 - Last Me Review View Tell me Home Insert Draw Data Page Layout Formulas = A Arial v 10 General Paste v BIU hl A92 fx D 117 118 119t. 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 portfolio's beta 123 would be: 124 125 Portfolio beta- 126 127 128 9. Suppose an investor wants to include Goodman Industries' stock in his or her portfolio. Stocks A, B, and C are currently in the portfolio, and their betas are 0.769, 0.985, and 1.423, respectively. Calculate the new 129 130 portfolio's required return if it consists of 25% of Goodman, 15% of Stock A, 40% of Stock B, and 20% of 131 Stock C 132 133 Beta Portfolio Weight 134 Goodman 25% 135 Stock A 0.769 15% 136 Stock B 0.985 40% 137 Stock 1.423 20% 138 100% 139 Portfolio Beta - 140 141 Required rotum on portfolio Risk-free rate + Market Risk Premium 142 Beta 143 144 145 146 147 148 149 150 151 152 153 154 156 156 157 156 159 Build a Model 10 AutoSave ON ABSU- AR Chapter 6 - Last Mod Home Draw Formulas Insert Page Layout Data View Tell me Review JU V Arial ' 10 X [D General Y ili lil Paste BIU A ~ lili HI - $ % A92 D G H 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 80s clear that Goodman moves with the market and Landry moves counter to the market. So goodman has a positive beta 81 and Laundry a negative one. 82 33 d. Estimate Goodman's and Landry's betas as the slopes of regression lines with stock returns on the 134 vertical axis by-axis) and market return on the horizontal axis (x-axis). (Hint use Excel's SLOPE function.) 85 Are these betas consistent with your graph? 85 37 Goodman's beta 1.54 139 Landry' beta- -0.56 90 91 Landrys beta suggests that it is less risky than average in a CAPM sense whereas goodman is more risky than average 92 94 195. The risk tree rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the 38 expected return on the market? Now use the SML equation to calculate the two companies' required returns. 97 98 Market risk premium (RP) 5.000% 99 Risk tree rate 6.040% 100 01 Expected retum on market Risk-free rate Marketisk premium 02 6.040% 5.000% 03 11.040% 04 105 Required retum 06 107 Goodman 108 Required retum 109 110 111 Landry 112 Required retum 113 114 115 116 Build a Model 10 Excel File tail View AutoSave ON ASSU: AR Chapter 6 Insert Home Draw Page Layout Formulas Data Review View Tell me Arial = V 10 Paste B y y y IU lili G A92 B D F 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, tx. Click tx, 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 31.4% 9.7% 13.8% 44 45 46 47. 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 32.8% 24.8% -1.0% 55 2018 1.2% 4.2% 13.2% 56 2017 34.9% 62.7% -10.0% 57 2016 14.8% 2.9% -0.4% 58 2015 19.0% 60.9% 11.7% 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 Build a Model AutoSave ON AR Chapter 6 - Las View Tell me Home Insert Draw Page Layout Formulas Data Review 10 Arial V 10 V [0 A Gen Paste BIU v V y lili il I'I y A92 B D E F G 11/26/18 1 Build a Model 2 Chapter: 3 Problem: 6 15 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 24.8% -1.0% 32.8% 26 2018 4.2% 13.2% 1.2% 27 2017 62.7% -10.0% 28 34.9% 2016 2.9% -0.4% 29 14.8% 2015 80.9% 11.7% 30 19.0% 29.4% 2.7% 20.6% 32 31 Average 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, tx. Click tx, 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 Build a Model OCT