Hello im a foreign exchange student and need help with this on excel I pasted it so you could use the data . Thank u so much
| | Bartman Industries | Reynolds Inc. | Winslow 5000 |
| Year | Stock Price | Dividend | Stock Price | Dividend | Includes Divs. |
| 2003 | 7.625 | 0.85 | 55.75 | 2 | 4705.97 |
| 2004 | 11.375 | 0.9 | 60 | 2.25 | 5602.28 |
| 2005 | 10.75 | 0.95 | 57.25 | 2.5 | 6434.03 |
| 2006 | 16.5 | 1 | 48.75 | 2.75 | 8679.98 |
| 2007 | 14.75 | 1.06 | 52.3 | 2.9 | 8785.7 |
| 2008 | 17.25 | 1.15 | 48.75 | 3 | 11663.98 |
Problem 2 Bartman Industries' and Reynolds Inc.'s stock prices and dividends, along with the Winslow 5000 Index, are shown on the following table for the period 2003-2008. The Winslow 5000 data are adjusted to include dividends. Data as given in the problem are shown below: Bartman Industries Winslow 5000 ncludes Divs. Reynolds Inc Year 2003 2004 2005 20D6 2007 2008 Stock Price 7.625 11.375 10.75 16.5 14.75 17.25 Dividend 0.85 0.9 0.95 Dividend Stock Price 55.75 60 57.25 48.75 52.3 48.75 2.25 2.5 2.75 2.9 4705.97 5602.28 6434.03 8679.98 8785.7 11663.98 1.06 1.15 a. Use the data to calculate annual rates of return for Bartman, Reynolds, and the Winslow 5000 Index. Then calculate each entity's average return over the 5-year period. Note: You cannot calculate the rate of return for 2003 because you do not have 2002 data b. Assume the risk-free rate is 6.04%. 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. So use 11% as the expected return on the market. Now use the CAPM to calculate the two companies' expected returns. Beta for Bartman is 1.539 and Reynolds is-0.560 C. If you formed a portfolio that consisted of 50% Bartman and 50% Reynolds, what would the portfolio's beta and expected return be? d. Suppose an investor wants to include Bartman 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 portfolio's required return if it consists of 25% of Bartman, 15% of Stock A, 40% of Stock B, and 20% of Stock C. Problem 2 Bartman Industries' and Reynolds Inc.'s stock prices and dividends, along with the Winslow 5000 Index, are shown on the following table for the period 2003-2008. The Winslow 5000 data are adjusted to include dividends. Data as given in the problem are shown below: Bartman Industries Winslow 5000 ncludes Divs. Reynolds Inc Year 2003 2004 2005 20D6 2007 2008 Stock Price 7.625 11.375 10.75 16.5 14.75 17.25 Dividend 0.85 0.9 0.95 Dividend Stock Price 55.75 60 57.25 48.75 52.3 48.75 2.25 2.5 2.75 2.9 4705.97 5602.28 6434.03 8679.98 8785.7 11663.98 1.06 1.15 a. Use the data to calculate annual rates of return for Bartman, Reynolds, and the Winslow 5000 Index. Then calculate each entity's average return over the 5-year period. Note: You cannot calculate the rate of return for 2003 because you do not have 2002 data b. Assume the risk-free rate is 6.04%. 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. So use 11% as the expected return on the market. Now use the CAPM to calculate the two companies' expected returns. Beta for Bartman is 1.539 and Reynolds is-0.560 C. If you formed a portfolio that consisted of 50% Bartman and 50% Reynolds, what would the portfolio's beta and expected return be? d. Suppose an investor wants to include Bartman 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 portfolio's required return if it consists of 25% of Bartman, 15% of Stock A, 40% of Stock B, and 20% of Stock C
