4. The Excel file Stock Data contains monthly data for the stock Cin and the S&P 500 Index (i.e., the market).

a. Compute the annualized return for Cin on a compounded basis.

b. Compute the annualized standard deviation for Cin.

c. Compute Beta for Cin using the Covariance/Variance relationship.

d. Compute Beta for the S & P 500 using the EXCEL slope function.

e. Compute the equation of the Characteristic Line for Cin using the appropriate EXCEL functions. i. Compute R2 for this equation using the appropriate EXCEL function. Explain the meaning of the R2 value for this equation. ii. Explain the meaning of each value and variable contained in this equation. iii. Perform, report, and interpret the results of the appropriate statistical significance test for each value contained in this equation.

f. Construct a graph of the Characteristic Line for Cin using EXCELs graphing features.

g. Compute the Weighted Average Cost of Capital (WACC) for Cin using the classic CAPM if the annual risk free rate of return is 3%, the corporate tax rate is 40%, the cost of debt is 10% and the capital structure is: 70% equity and 30% debt

This is all the information we were given. Would the dividend excel sheet be helpful?

F G H 1 j L www. 61 62 63 64 65 66 67 68 69 70 71 A B D E 1 Monthly Return Data for Cin and the S&P 500 2 3 Month Cin S&P 500 4 1 -7.20% -9.00% 31 5 2 -0.86% -4.90% 32 6 3 5.10% -0.40% 33 7 4 0.34% 0.30% 34 8 5 0.00% 4.30% 35 9 6 1.18% -4.60% 36 10 7 0.34% 1.30% 37 11 8 -2.69% -2.00% 38 12 9 3.45% 2.90% 39 13 10 -1.69% 3.40% 40 14 11 2.58% 1.30% 41 15 12 3.20% 0.70% 42 16 13 -2.85% 2.60% 43 17 14 -3.05% -2.50% 44 18 15 4.85% 9.80% 45 19 16 1.26% -0.70% 46 20 17 5.82% -0.30% 47 21 18 -7.20% -9.00% 48 22 19 -0.86% -4.90% 49 23 20 5.10% -0.40% 50 24 21 1.69% 6.40% 51 25 22 -0.83% 2.70% 52 26 23 0.86% 4.40% 53 27 24 4.66% 7.20% 54 28 25 4.45% 2.40% 55 29 26 0.34% 0.30% 56 30 27 0.00% 4.30% 57 31 28 1.18% -4.60% 58 32 29 6.25% 4.70% 59 33 30 0.37% 2.40% 60 34 Sheet1 + 5.95% 0.00% 6.43% 7.72% -6.22% 0.34% -2.69% 3.45% -6.46% 0.00% 8.77% 0.34% 3.39% -1.30% -1.69% 2.58% 3.20% 3.47% 2.91% 4.25% 5.30% 0.41% -2.85% -3.05% 4.45% 0.34% 0.00% -2.69% -1.69% 3.45% 72 -1.60% 1.30% -4.00% 11.40% -1.90% 1.30% -2.00% 2.90% 0.50% -1.50% 4.00% -2.00% 1.20% 0.40% 3.40% 1.30% 0.70% 1.40% 4.00% -0.50% 9.00% 1.30% 2.60% -2.50% 2.40% 0.30% 4.30% -2.00% 3.40% 2.90% -2.90% 4.79% 2.91% 4.25% 5.30% -0.88% 3.54% 3.76% 2.94% 2.86% -0.86% 0.41% 4.45% 0.34% 0.00% 1.18% 6.25% 0.37% 2.40% 5.20% 4.00% -0.50% 9.00% 1.90% -0.40% -2.30% 2.10% 2.40% -6.70% 1.30% 2.40% 0.30% 4.30% -4.60% 4.70% 2.40% 73 74 75 76 77 78 Rondu F G H 1 j L www. 61 62 63 64 65 66 67 68 69 70 71 A B D E 1 Monthly Return Data for Cin and the S&P 500 2 3 Month Cin S&P 500 4 1 -7.20% -9.00% 31 5 2 -0.86% -4.90% 32 6 3 5.10% -0.40% 33 7 4 0.34% 0.30% 34 8 5 0.00% 4.30% 35 9 6 1.18% -4.60% 36 10 7 0.34% 1.30% 37 11 8 -2.69% -2.00% 38 12 9 3.45% 2.90% 39 13 10 -1.69% 3.40% 40 14 11 2.58% 1.30% 41 15 12 3.20% 0.70% 42 16 13 -2.85% 2.60% 43 17 14 -3.05% -2.50% 44 18 15 4.85% 9.80% 45 19 16 1.26% -0.70% 46 20 17 5.82% -0.30% 47 21 18 -7.20% -9.00% 48 22 19 -0.86% -4.90% 49 23 20 5.10% -0.40% 50 24 21 1.69% 6.40% 51 25 22 -0.83% 2.70% 52 26 23 0.86% 4.40% 53 27 24 4.66% 7.20% 54 28 25 4.45% 2.40% 55 29 26 0.34% 0.30% 56 30 27 0.00% 4.30% 57 31 28 1.18% -4.60% 58 32 29 6.25% 4.70% 59 33 30 0.37% 2.40% 60 34 Sheet1 + 5.95% 0.00% 6.43% 7.72% -6.22% 0.34% -2.69% 3.45% -6.46% 0.00% 8.77% 0.34% 3.39% -1.30% -1.69% 2.58% 3.20% 3.47% 2.91% 4.25% 5.30% 0.41% -2.85% -3.05% 4.45% 0.34% 0.00% -2.69% -1.69% 3.45% 72 -1.60% 1.30% -4.00% 11.40% -1.90% 1.30% -2.00% 2.90% 0.50% -1.50% 4.00% -2.00% 1.20% 0.40% 3.40% 1.30% 0.70% 1.40% 4.00% -0.50% 9.00% 1.30% 2.60% -2.50% 2.40% 0.30% 4.30% -2.00% 3.40% 2.90% -2.90% 4.79% 2.91% 4.25% 5.30% -0.88% 3.54% 3.76% 2.94% 2.86% -0.86% 0.41% 4.45% 0.34% 0.00% 1.18% 6.25% 0.37% 2.40% 5.20% 4.00% -0.50% 9.00% 1.90% -0.40% -2.30% 2.10% 2.40% -6.70% 1.30% 2.40% 0.30% 4.30% -4.60% 4.70% 2.40% 73 74 75 76 77 78 Rondu