please answer using excel. I posted extra pictures to help read the question.

You work for a small investment management firm. You have been provided with the following historical information for three stocks and the market index. The information is shown in the table below. 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 AAA Inc. BBB Inc Stock Dividend Stock Dividend Price Price $33 $60 $41 $0.70 $70 $1.40 $45 $0.70 $48 $1.55 $50 $0.85 $65 $1.65 $42 $0.90 $90 $1.75 $52 $0.95 $100 $1.90 $60 $1.00 $80 $2.00 $75 $1.05 $90 $3.20 $85 $1.20 $110 $3.50 $95 $1.35 $140 $4.00 CCC Inc. Stock Dividend Market Price Index $130 18.840 $115 $3.50 20,320 $125 $3.60 22,220 $140 $3.65 25,325 $135 $3.65 19,520 $155 $3.75 24,100 $185 $3.80 25,500 $205 $4.33 29,020 $215 $4.85 33,572 $245 $5.45 39,372 Using the data provided you are to calculate the following for ench of the stocks and the market index. a) What is the average annual return for the past nine years? b) What is the geometric average annual return (effective annual rate of return) for the past nine years? c) What is the population standard deviation and the sample standard deviation? d) What is the correlation coefficient between Stocks AAA and BBB? Between Stocks AAA and CCC? Between Stocks BBB and CCC? e) What is the sample covariance between Stocks AAA and BBB, between Stocks AAA and CCC and between stocks BBB and CCC? d) What is the coefficient of variation for each of the stocks and the market index? You should use the sample standard deviation. g) What is the beta of each of the stocks? You can use Excel's "slope" function to estimate this. h) Assume that the beta of stock AAA is 0.70, the beta of stock BBB is 1.4 and the beta of stock CCC is 2.3. What is the beta of a portfolio where the weight in stock AAA is 30% the weight in stock BBB is 50% and the weight in stock CCC is 20%? ta 75% and the ownecter return the marketie 8. Satie $33 S41 $45 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 $50 $42 $52 $60 $75 $85 $95 $0.70 $0.70 $0.85 $0.90 $0.95 $1.00 $1.05 $1.20 $1.35 $60 $70 $48 $65 $90 $100 $80 $90 $110 $140 $1.40 $1.55 $1.65 $1.75 $1.90 $2.00 $3.20 $3.50 $4.00 $130 $115 $125 $140 $135 $155 $185 $205 $215 S245 $3.50 $3.60 $3.65 $3.65 $3.75 $3.80 $4.33 $4.85 $5.45 18,840 20,320 22,220 25,325 19,520 24,100 25,500 29,020 33,572 39,372 Using the data provided you are to calculate the following for each of the stocks and the market index. a) What is the average annual return for the past nine years? b) What is the geometric average annual return (effective annual rate of return) for the past nine years? c) What is the population standard deviation and the sample standard deviation? d) What is the correlation coefficient between Stocks AAA and BBB? Between Stocks AAA and CCC? Between Stocks BBB and CCC? e) What is the sample covariance between Stocks AAA and BBB, between Stocks AAA and CCC and between stocks BBB and CCC? ) What is the coefficient of variation for each of the stocks and the market index? You should use the sample standard deviation g) What is the beta of each of the stocks? You can use Excel's "slope" function to estimate this. h) Assume that the beta of stock AAA is 0.70, the beta of stock BBB is 1.4 and the beta of stock CCC is 2.3. What is the beta of a portfolio where the weight in stock AAA is 30% the weight in stock BBB is 50% and the weight in stock CCC is 20% i) Assuming the risk-free rate is 2.75% and the expected return on the market is 8.50% what is the expected return on the portfolio in part (h) above? $45 30.70 AVA 2015 548 $135 5125 53.60 S50 $0.85 1220 2016 565 $1.65 542 S40 $0.90 $16 315325 390 $1.75 2017 SIJS SS 5165 $0.93 12.520 2018 S100 S1.90 5155 8.5 24.100 560 51.00 2019 $80 53.00 $185 38.80 $75 $1.05 23.500 2020 590 $320 5205 $4.31 585 31:20 29,020 $110 $350 2021 5215 54.85 33.373 595 $115 SIMO 54.00 5245 $5.45 19.11 Using the data provided you are to calculate the following for enslift dhe modes 1) What is the average manual rum for the past nine you b) What is the geometrie average til retum (effective amal rate of return) for die past line yenes? c) What is the population standard deviation and the sample saded deviation d) What is the correlation coefficient between Stocka AAA and 37 Between Socks AM and CCC? Between Stocks BBB and CCCT .) What is the sample covariance between Stocka AAA and RB, between Stock And CCC and between stock BBB CCC 1) What is the coefficient of variation for each of the stock and the met index? You should use the sample standard deviation 3) What is the beta of each of the stock? You can ice Excel's slope fiction to estimate this h) Asume that the beta of stock AAA : 0.70, the bets of stock BABI 1.4 and the beta of stock CCC is 23. What is the beta of a portfolio where the weight in stock AAA is 30%. the weight in stock BBB is 50% and the weight in stock CCC E 20% i) Asuming the risk-free rate is 2.75 and the expected are on the market is 50% what is the expected return on the portfolio in part (h) above