XYZ Co stock has the price of $30, $32, $28, $31, $35, $34, $40, $38, $42, $45 and $50 from 2010 to 2020, while a stock

[answerhappygod]

XYZ Co stock has the price of $30, $32, $28, $31, $35, $34, $40,
$38, $42, $45 and $50 from 2010 to 2020, while a stock market index
has the value of 100, 105, 102, 110, 125, 120, 135, 130, 145, 155
and 170 from 2010 to 2020. The stock is expected to pay dividend of
$3 in one year and $3 in two years. Its expected price in two years
is $60. The risk-free rate is 3%. The stock market index has an
expected return of 12%.
1. Enter the stock prices and the stock market index into your
Excel file column wise. (Two columns and 11 rows), Derive the
annual returns of the stock and the stock market index.
2. Use Excel Regression under Data Analysis to run a linear
regression using stock return as y variable and stock market index
as x variable to estimate the beta of the stock.
3. Find standard deviation of stock and of stock index, and the
correlation coefficient of stock return and stock index return to
estimate the beta of the stock. Does the stock have a greater risk
than the stock market index? Explain.
4. Use the security market line to determine the required rate
of return of the stock.
5. Determine the per share value of the stock.