Year Excel Activity: Evaluating Risk and Return Bartman Industries's and Reynolds toe's stock prices and dividends, along with the Winstow 5000 Index, we shown here for the period 2015-2020. The Winslow 5000 data are adjusted to include dividends. Bartman Industries Reynolds Inc. Window 5000 Stock Price Dividend Stock Price Dividend Includes Dividends 2020 516,85 51.09 $50.50 $2.75 511.759.31 2019 14.50 1,02 2.65 1,990.49 2018 16.25 0.95 50.50 2.50 8,878.85 2017 10.70 0.00 50.10 2.20 6,49231 2016 11.12 0.85 63.55 205 5,677.00 2015 7.57 0.00 4,71793 The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Use a minus sign to enter negative values, if any. X Bonnland seadabant.Eynunting Binh, and Return: 3. Use the data to calculate annual rates of return for Batman, Reynolds, and the Winslow 5000 Index. Then calculate cach entity's average return over the year period. (Hint: Remember returns are calculated by subtracting the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the inder. Also, you cannot calculate the rate of return for 2015 because you do not have 2014 data) Round your answers to two decimal places Lartman Industries Reynolds Inc. Winstow 5000 Year 2020 2019 2018 2017 2010 Average b. Calculate the standard deviation of the returns for Bartran, Brynolds, and the Winslow 5000. (one the the sample standard deviation formula, which corresponds to the STDEVS function in Excel) Round your swers to two decimal places Hartman Industries Reynolds Inc. Winslow 5000 Standard deviation 6. Calculate the coeficients of variation for Gorman, Reynolds, and the Window 5000. Round your ariswers to two decal places Bartman Industries Reynolds Inc Window 5000 Coefficient of variation d. Assume the risk tree route during this time we 3. Calculate the sharperation for Batman Beynolds, and the index over this period using the verage returns Round your answers to four decimal places Hartman Industries Reynolds Inc. Winslow 5000 Sharpe ratio e. Construct a scatter diagram that shows Bartman's and Reynolds's returns on the vertical axis and the Winslow 5000 Index's returns on the horizontal Choose the correct graph The correct graphis Stock Returns vs Index's Returns Stock Retum Index Ratu 70% A 104 604 60 50% 501 404 Stock Rom 20 30% 30N 2014 Stock 204 109 104 09 09 1016 30 40% -109 10% 104 304 209 Index's Retur Batman Industri Raynoldo Inc Index's Returns Batmands Remodele Stoder Betume wa Indexes Batur D Stock Rom Indo 704 Son 2045 Stock Rom 304 30% 104 104 09 109 104 ON 2016 Index minuni yote Bree Estimate Bartman's and Reynolds betos by running regressions of the returns against the wider's returns. Round your towers to four decorat aces Bartman's beta Reynolds's beta: Are these be consistent with your graph These betas consistent with the scatter diagrams Assume that the free rate on long term treasury band 55 so that the manual return on the Window 5000 toned estimated the market's required retom-it is too high Souse 10% as the expected return on the market. Use the SML to calculate the two companies required retum Sound your answers to two decimal places Batman's required return Heynolds's required retum h. If you formed portfolie that conutted of 50% tortman and 50% Rayo, what would the portfolio's hot and required retum be? Round your swer for the portfolio's been to four decimal places and for the portfolio's required return to two decomal places Portfolios beta Portfolio's required return Suppose an investor want to include Bartman Industre's stock is portfold. Stock A, B, and are currently in the portfolio, and there are 0.76), 0.928, and 1.241, respectively Calculate the new portfolio's red return it costs of 30of Batman 10 A 45 of 15% of Stock Round your answer to two decimal places Year Excel Activity: Evaluating Risk and Return Bartman Industries's and Reynolds toe's stock prices and dividends, along with the Winstow 5000 Index, we shown here for the period 2015-2020. The Winslow 5000 data are adjusted to include dividends. Bartman Industries Reynolds Inc. Window 5000 Stock Price Dividend Stock Price Dividend Includes Dividends 2020 516,85 51.09 $50.50 $2.75 511.759.31 2019 14.50 1,02 2.65 1,990.49 2018 16.25 0.95 50.50 2.50 8,878.85 2017 10.70 0.00 50.10 2.20 6,49231 2016 11.12 0.85 63.55 205 5,677.00 2015 7.57 0.00 4,71793 The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Use a minus sign to enter negative values, if any. X Bonnland seadabant.Eynunting Binh, and Return: 3. Use the data to calculate annual rates of return for Batman, Reynolds, and the Winslow 5000 Index. Then calculate cach entity's average return over the year period. (Hint: Remember returns are calculated by subtracting the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the inder. Also, you cannot calculate the rate of return for 2015 because you do not have 2014 data) Round your answers to two decimal places Lartman Industries Reynolds Inc. Winstow 5000 Year 2020 2019 2018 2017 2010 Average b. Calculate the standard deviation of the returns for Bartran, Brynolds, and the Winslow 5000. (one the the sample standard deviation formula, which corresponds to the STDEVS function in Excel) Round your swers to two decimal places Hartman Industries Reynolds Inc. Winslow 5000 Standard deviation 6. Calculate the coeficients of variation for Gorman, Reynolds, and the Window 5000. Round your ariswers to two decal places Bartman Industries Reynolds Inc Window 5000 Coefficient of variation d. Assume the risk tree route during this time we 3. Calculate the sharperation for Batman Beynolds, and the index over this period using the verage returns Round your answers to four decimal places Hartman Industries Reynolds Inc. Winslow 5000 Sharpe ratio e. Construct a scatter diagram that shows Bartman's and Reynolds's returns on the vertical axis and the Winslow 5000 Index's returns on the horizontal Choose the correct graph The correct graphis Stock Returns vs Index's Returns Stock Retum Index Ratu 70% A 104 604 60 50% 501 404 Stock Rom 20 30% 30N 2014 Stock 204 109 104 09 09 1016 30 40% -109 10% 104 304 209 Index's Retur Batman Industri Raynoldo Inc Index's Returns Batmands Remodele Stoder Betume wa Indexes Batur D Stock Rom Indo 704 Son 2045 Stock Rom 304 30% 104 104 09 109 104 ON 2016 Index minuni yote Bree Estimate Bartman's and Reynolds betos by running regressions of the returns against the wider's returns. Round your towers to four decorat aces Bartman's beta Reynolds's beta: Are these be consistent with your graph These betas consistent with the scatter diagrams Assume that the free rate on long term treasury band 55 so that the manual return on the Window 5000 toned estimated the market's required retom-it is too high Souse 10% as the expected return on the market. Use the SML to calculate the two companies required retum Sound your answers to two decimal places Batman's required return Heynolds's required retum h. If you formed portfolie that conutted of 50% tortman and 50% Rayo, what would the portfolio's hot and required retum be? Round your swer for the portfolio's been to four decimal places and for the portfolio's required return to two decomal places Portfolios beta Portfolio's required return Suppose an investor want to include Bartman Industre's stock is portfold. Stock A, B, and are currently in the portfolio, and there are 0.76), 0.928, and 1.241, respectively Calculate the new portfolio's red return it costs of 30of Batman 10 A 45 of 15% of Stock Round your answer to two decimal places