tan continues. Unless you need to edit it's safe to stay in Protected ve J a . Use the data given to calculate annual returns for Bartman Reynolds, and the Market Index, and then calculate r able Editing 45 c. Construct a scatter diagram graphs that shows Barman's and Reynolds returns on the vertical axis and the 46 Market Index's returns on the horizontal axis. 48 It is easiest to make callerdings with a dataset that has the X-axis variable in the left column, so we 49 reformat the returns data calculated above and show it just below. 50 51 Yen Inder 2010 Reynolds 0.08 0 0 0.06 2009 0 0 00 0 0 2007 0.0% 0.06 2006 0.096 0.0% 2008 To make the plot let them with the sad the col d s, then click on the scrab the select the scatter diagram without connected lines. That gave us the data points. You can then use the drawing toolbar to make free hand by yearson lines and change the lines color w hats to match the dots. male Hartmann Weishes the same a Risk Return s me with lack 5 ls. Use the data diventa calculate annual returns for Bartman, Reynolds, and the Market Index, and then 6 calculate average returns over the five-year period. (Hint: Remember, returns are calculated by subtracting the 7 beginning price from the coding price to get the capital gain or loss, adding the dividend to the capital gain or loss, B and dividing the result by the beginning price. Assume that dividends are already included in the index. Also, you Cannot calculate the rate of return for 2005 because you do not have 2004 data.) 11 Detais given in the problem we shown below Barth Indian Yea Stock Price 2010 $17.250 2009 14.750 2008 16 500 2007 10.750 2006 11 375 7625 Dividend $1.150 1060 1.000 0950 0.900 0.850 Reynolds incorporated Stock Price $48.750 52 300 48.750 57.250 60.000 55.750 Market Index Dividend Includes Die $3.000 11.663.98 2.900 8.785.70 2.750 3.679 98 2.500 6.41603 2.250 5.602.28 2000 4.705 971 2005 now calculate the rates of return for the two companies and the index RENN Baran Reynolds Index 2010 2009 2008 2007 2006 28 30 Average 32 Note: To get the aver , you could get the columsum and divide by 5. but you could also use the function wird, 33 Click Ex, then atistical them Average, and then use the mouse to select the proper range. Do this for Bartman and then 34 copy the cell for the other items 35 36 h. Calculate the standard deviation of the returns for Bartman, Reynolds, and the Market Index. (HintUse the 37 sample standard deviation formula given in the chapter, which corresponds to the STDEV function in Excel) 39 Use the function ward to calculate the standard deviations Risk Return 2 Views File Home Insert Draw Page Layout Formulas Data Review View Help at you want to do 0 PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View X f xa. Use the data given to calculate annual returns for Bartman, Reynolds, and the Marketing 81 d. Estimate Bartman's and Reynolds's belas as the slopes of regression lines with stock returns on the vertical 82 axis (y axis) and market return on the horizontal axis (x-axis). (Hint: use Excel's SLOPE function.) Are these 83 betas consistent with your graph? Bartman's beta Reynolds' beta = 92 . The risk-free rate on long term Treasury bonds is 6,04% Assume that the market risk premium is 5%. What is the expected 93 return on the market? Now use the SML equation to calculate the two companies' required returns. 5.000 95 Market risk premium (RPM) 96 Risk-free rate- 60.00 98 Expected return on market Risk-free rate 6.010% 11.0-1004 Market risk premium 5.00096 100 101 102 Required return 103 104 Bartman: 105 Required retum 106 107 108 Reynolds: 109 Required retum 110 112 114 116. If you formed a portfolio that consisted of 50%. Bartman stock and 50%. Reynolds stock, what would be its beta 117 and its r ivedrud Risk-Return Type here to search 3 4 5 6 File Mome n O PROTECTED VIEW t Dr Page Layout Formulas Outs Review View Help Be careful-Files from the Internet can contain viruses. Unless you need to edit d View AS X Use the data given to calculate annual returns for Bartman... and the Market 113 115 116. If you formed a portfolio that consisted of 50% Bartmas stock and 50% Reynolds stock, what would be its beta 117 and its required return? 118 119 The beta of a portfolio is simply a weighted average of the beas of the stocks in the portfolio, so this portfolios beta 120 would be 121 122 Portfolio beta 126 Suppose an investor wants to include Baraladustries stock in his or her portfolio Stacks A, B and Care 127 currently in the portfolio, and their bets are 0.769, 0.985, and 1,423, respectively. Calculate the new portfolio's 128 required return if it consists of 26% of artmas, 15% of Stock A. 40% of Stock B, and 20% of Stock C. Beta Portfolio Weight Stock A Stock 0.769 0985 1.423 006 Stock 2006 100% Portfolio Beta- 137 138 Required retunion portfolio Risk-free rate + Maket Risk Premium - Portfolio Beta 139 140 141 142 143 145 146 147 148 149 150 Risk-Return - Type here to search te M BA