Excel Activity: Evaluating Risk and Return

Exc Spread Sheet:

a. Use the data to calculate annual rates of return for Bartman, Reynolds, and the Winsfow 5000 Index, Then caiculate each entity's average return over the s-year period. (Hint: Remember, returns are calculated by subtracting the beginning price from the ending price to get the capiral pain or loss, adding the dlvidend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are arready included in the index, Also, vou cannot calculate the rate of return for 2015 because your do not have 2014 data.) Round vour answers to twe dfecimal places. f. Estimate Bartman's and Reynolds's betas by running regressions of their returns against the index's returns. Round your answers to four decimal places. Battman's beta: Reynolds's beta: Are these betas conisistent with your graph? There betas consistent with the scatter diograms. 5000 is hot a good estimate of the markets required return-lit too hiph. 50 use 109 as the expected retum on the market. Une the SML equation to calculate the two companies' required retums, Round your answers to two decimal blacel. Bartman's requited ieturni a. Calculatina the twn comnanine'. h. Calculating the portfolio's beta and reauired retum i. Calculating the new portfolio's required return Excel Activity: Evaluating Rtuk and Return Burtman Industries's and Reynolds inc's stock prices and dividends, alono with the Winslow 5000 index, are shown here for the period 20152020. The Winsiow 5000 data are adjusted to lnclude dividends. The data has been callected in the Microsaft Excel flie below. Dowrload the spreadtheet and perform the requited analysis to answer the Questions below. Do not round intermediate calcufations, use a itinus sfon to enter negative values, if amp. Bartman's and Reynolds's returns on the vertical axis and the Winslow 5000 Index's returns ct araph. b. Calculate the standard deviations of the retums for Bartman, Reynolds, and the Winslow 5000 . (Hint: Use the sample standard deviation formula, which corresponds to the stoevs function in Excel.) Round vour answers to two decimal olaces. c. Calculate the coefficients of variation for Bartman, furnolds, and the Winslow 5000 . Round rour ansmers to two decimal places. Bartman Indertertes Rempolets the whatow snoe Coefficient of variation Barman industries Meynolds the. Winsfow 5000 Sharbe ratio The correct graph is Reynolds's Calculations After reading these instructions, delete all text in this shaded area. On the Data tab, in the Analysis group, click the Data Analysis button. Select Regression and click OK. After deleting all text in this shaded area, set the output range to the top left cell of this area (A87). Your Regression analysis output should fit into this shaded area. Bartman's beta Formulas Reynolds's beta \begin{tabular}{|l} Formulas \\ AN/A \\ HN/A \end{tabular} 12. a. Calculatina the two companies' required returns e. Construct a scatter diagram that shows Bartman's and Reynolds's returns on the vertical axis and the Winstow so00 Index's returns: on the horizontal axis, Choose the correct araph. The correct oraph is c. - Bartman fnduatries e Rejaolda fisa D. - B ms D. tolds Inc. Bartman Industries - Rejzolds Inc Evaluating Riak and Retum 2 a. Calculating the annual rates of return for Bartman, Reynolds, and the Winslow 5000 Index Formulas Calculating each entity's avetage return over the 5-yoar period Bartunan Reynolds Winslow Averane tetum b. Calculating the standard deviations of the cetarns for Bartman, Reynolds, and the Wiinslow 5000 Standand devation 21 e. Calculatinn the coetricients of variation for Bartman, Revnolds, and the Wilnslow 5000 Shent1 0. Assume that the risk-free rate on long-term Treasury bonds is 5.5%. Assume also that the average annual return on the Winslow 5000 is not a good estimate of the market's required return - it is too high. 50 use 10% as the expected return on the market. Use the Sill equation to calculate the two companies' required returns. Round vour answers to two decimal places. Bartman's required return) stovnotacis reatired retiorn: h. If vou formed a portfolo that consisted of 50%. Earman and 50%. Revnolds, what would the portfolios beta and required return be? Rouns your answer for the portfolio s beta to four decimal places and for the portfollo's required refurn to two declimal places. Dertolio'sets: their betas are 0.771 .0 .952 , and 1.432, respectively, Caiculate the finw portalio s required refurn if it conbist of 20 s of Bartman. e. Constructing a scatter diagram that shows Bartman's and Reynolds's returns on the vertical axis and the Winslow 5909 Index's returns on the horizontal axis 1. Estimiting Bartuan's and Reynolds's betas by running regressions of their returns against the index's returns Banman's Calculation isheetl Etimating Bartman s and Reynolds's betas by running regressions of their returns against the index's returns Raitman's Calculations Formulas \begin{tabular}{lll} \multicolumn{1}{r}{ Bartman } & Reynolds & Winslow \\ \hline \#N/A & \#N/A & \#N/A \\ \#N/A & \#N/A & \#N/A \\ \#N/A & \#N/A & \#N/A \\ \#N/A & \#N/A & \#N/A \\ \#N/A & \#N/A & \#N/A \end{tabular} \begin{tabular}{|ccc} Bartman & Reynolds & Winslow \\ \hline \#N/A & \#N/A & \\ Bartman & Reynolds & Winslow \\ \hline \#N/A & \#N/A & HNA \end{tabular} Calculating each entity's average return over the 5 -year period Average return Bartman Reynolds. Winslow b. Calculating the standard deviations of the returns for Bartman, Reynolds, and the Wiinslow 5000 Bartman Reynolds Winslow Standard deviation Bartman Reynolds: c. Calculating the coefficients of variation for Bartman, Reynolds, and the Wiinslow 5000 Coefficient of varation Bartman Reynolds Winslow d. Calculating the Sharpe ratios for Bartman, Reynolds, and the Index using their average returns Ruskfree rate Bartman Reynolds Winslow Sharpe ratio \begin{tabular}{|c|c|c|} \hline Bartman & Reynolds & Winslow \\ \hline \end{tabular} 0. Constructing 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 axis Sheet1