Please do it on excel and show the formulas

AFF 210 ASSIGNMENT TwoO WINTER 2019 Problem Four (15 marks) You work for a small invcstmcnt managcment firm. You havc bcen provided with the following historical information for thrcc stocks and thc market index. The information is shown in the tablc below AAA Inc BBB Inc CCC Inc Stock Dividend Stock Price Dividend Stock Price Dividend Market Price Index S35 S40 S45 2012 2013 2014 2015 2016 2017 2018 S1.20 S1.40 S1.40 S1.70 S1.85 S1.85 S1.90 $110 $120 $98 $115 $140 S0.45 S0.50 S0.55 S0.65 S0.75 S0.90 S1.00 $1O0 %85 $%95 $110 $105 %125 $140 $1.50 $1.50 $1.60 $1.65 $1.65 $1.75 $1.90 10.840 .320 12.220 3.325 S42 13.820 14.400 S70 %120 Lsing the data provided you are to calculate the following for each of the stocks and the market index a) What is the annual return for the past six years? b) What is the average annual return for the past six years? c) What is the geometric average annual return (effective annual rate of return) for the past six years d) What is the population standard deviation and the sample standard deviation? c) What is the sample covariance and correlation cocfficient between Stocks AAA and BBB? Between Stocks AAA and CCC? Between Stocks BBB and CCC? f) What is the coefficient of variation for each of the stocks and the market index? g) What is the beta of each of the stocks? You can use Excel's "slope" function to estimate this h) Assuming that the beta of stock AAA is 0.80, the beta of stock BBB is 1.2 and the beta of Stock CCC is 16. what is the beta of a portolio where the weight ln stock AAA is 20% the weight in stock BBB is 50% and the weight in stock CCC is 30% i) Assuming the risk free rate is 3% and the expected return on the market is 7% what is the expected return on the portfolio in part (h) above? Ryerson Page 5 of7 AFF 210 ASSIGNMENT TwoO WINTER 2019 Problem Four (15 marks) You work for a small invcstmcnt managcment firm. You havc bcen provided with the following historical information for thrcc stocks and thc market index. The information is shown in the tablc below AAA Inc BBB Inc CCC Inc Stock Dividend Stock Price Dividend Stock Price Dividend Market Price Index S35 S40 S45 2012 2013 2014 2015 2016 2017 2018 S1.20 S1.40 S1.40 S1.70 S1.85 S1.85 S1.90 $110 $120 $98 $115 $140 S0.45 S0.50 S0.55 S0.65 S0.75 S0.90 S1.00 $1O0 %85 $%95 $110 $105 %125 $140 $1.50 $1.50 $1.60 $1.65 $1.65 $1.75 $1.90 10.840 .320 12.220 3.325 S42 13.820 14.400 S70 %120 Lsing the data provided you are to calculate the following for each of the stocks and the market index a) What is the annual return for the past six years? b) What is the average annual return for the past six years? c) What is the geometric average annual return (effective annual rate of return) for the past six years d) What is the population standard deviation and the sample standard deviation? c) What is the sample covariance and correlation cocfficient between Stocks AAA and BBB? Between Stocks AAA and CCC? Between Stocks BBB and CCC? f) What is the coefficient of variation for each of the stocks and the market index? g) What is the beta of each of the stocks? You can use Excel's "slope" function to estimate this h) Assuming that the beta of stock AAA is 0.80, the beta of stock BBB is 1.2 and the beta of Stock CCC is 16. what is the beta of a portolio where the weight ln stock AAA is 20% the weight in stock BBB is 50% and the weight in stock CCC is 30% i) Assuming the risk free rate is 3% and the expected return on the market is 7% what is the expected return on the portfolio in part (h) above? Ryerson Page 5 of7