Assignment:
Stock regression
There are two components in the risk of a stock: systematic risk and specific risk.
The systematic risk of a stock is the variability in its returns that is explained by the market (usually gauged by using the S&P 500 index). Specific risk is the variability associated with a particular stock, excluding the variability in the stock market as a whole. Systematic risk is also called a stock's beta risk (or -value). A beta value close to 1.0 means that, on the average, the rise and fall of the stock coincides with those of the market. That is, a 1% increase (decrease) in the market will, on the average, result in a 1% increase (decrease) in the stock. A beta value less than 1.0 indicates a stock less volatile than the market, whereas a beta value greater than 1.0 represents a stock more risky than the market.
Beta values can be calculated with a regression line, where X=Average returns (in percent) from the market and Y=returns (in percent) from the stock. The beta risk of a stock equals the slope of the regression line relating the stock's returns (the dependent variable) to the market returns (the independent variable).
The following gives stock market returns for 22 months compared to GM stock.The data file for this problem is in Canvas. The Market column is the average stock market return for each month (it was the S & P returns for those months). Let GM be the dependent variable and Market be the independent variable.
Month Market GM Month Market GM
1 0.01 -0.02 12 0.05 -0.06
2 0.04 0.05 13 0.01 -0.01
3 -0.04 -0.03 14 0.04 0.10
4 0.05 0.06 15 -0.02 -0.06
5 0.00 0.03 16 -0.06 -0.09
6 -0.02 0.02 17 0.05 -0.01
7 0.03 0.03 18 0.05 0.00
8 0.00 -0.03 19 0.02 0.04
9 0.08 0.05 20 0.08 0.13
10 -0.01 0.04 21 0.00 0.03
11 -0.06 -0.12 22 0.02 -0.01
a. Construct a scatterplot for the data in Excel. Paste your scatterplot below. Make certain to clearly label the axes and your plot.
What does the scatter plot suggest about the relationship between average returns in the market and returns for GM?
b. Do regression in Excel and paste your regression below.
c. Find the least squares regression line in Excel.
d. Interpret the y-intercept and the beta risk for GM stock.
e. Use the least squares regression line to find the expected Y (returns ) in percent from the stock when the X=Average returns (in percent) from the market is 0.04.
f. Find the Coefficient of Determination R2 in Excel and interpret its value.
g. Find s (the standard error of the model) in Excel. Comment on its value.
h. Find the Correlation Coefficient r in Excel and interpret its value. Discuss the strength and direction of the relationship between returns from the stock and returns from the market.
i. In Excel, test the alternative hypothesis that there is a linear relationship between average stock returns and GM returns. Be sure to include the hypotheses, p-value, and conclusion. Explain your answer in detail.
j. Estimate the mean Y (returns ) in percent from the stock when the X=Average returns (in percent) from the market is 0.04 with a 95% confidence interval in Excel. Interpret your result in the context of the situation given.
k. Predict the new Y (returns ) in percent from the stock when the X=Average returns (in percent) from the market is 0.04 with a 95% prediction interval in Excel. Interpret your result in the context of the situation given.