question archive Perform a Regression on this data
Subject:BusinessPrice:9.82 Bought3
Perform a Regression on this data.
Year | Return on Stock Z | Return on S&P 500 |
1 | 10% | 5% |
2 | -15 | -10 |
3 | 15 | 10 |
4 | 5 | 0 |
5 | -5 | -10 |
Follow these steps with the data you entered above:
? Enter returns on stock Z as y variable range.
? Enter returns on S&P 500 as x variable range.
? Plot a scatter diagram from the returns given.
? Estimate the beta for stock Z.
? Provide a linear equation relating stock Z returns to returns on the S&P 500 index.
? What type of relationship between the two variables is predicted from your equation?
? If the return on the S&P 500 index is expected to be 10% over the next year, using your regression equation what
is the expected return on stock Z over the same period?
Step-by-step explanation
Scatter Diagram:
Scatter Diagram on the Returns Given 0.2 0.15 0.1 0.05 O Return on Stock Z 0.15 -0.1 -0.05 O 0.05
0.15 0.05 -0.1 0. 15 -0.2 Return on S&P 500
After using data analysis (regression tool) in Excel using the given data from Stock Z and S&P 500 returns, it returned the following data:
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.951688608 | |||||||
R Square | 0.905711207 | |||||||
Adjusted R Square | 0.874281609 | |||||||
Standard Error | 0.042695628 | |||||||
Observations | 5 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 1 | 0.052531 | 0.052531 | 28.81714 | 0.012654 | |||
Residual | 3 | 0.005469 | 0.001823 | |||||
Total | 4 | 0.058 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 0.0328125 | 0.019243 | 1.705196 | 0.186703 | -0.02843 | 0.094051 | -0.02843 | 0.094051 |
X Variable 1 | 1.28125 | 0.238676 | 5.36816 | 0.012654 | 0.521677 | 2.040823 | 0.521677 | 2.040823 |
From here, it can be seen that the beta for Stock Z = 1.28125 or 1.28.
The y-intercept, on the other hand, is 0.0328125 or 0.03. Having these 2 figures, we can come up with the following linear equation:
A positive relationship can be predicted from the equation, because the 2 figures (y-intercept and slope/beta) are both positive. This can also be seen in the scatter diagram, where the points are not far off a straight line and indicates that these 2 have a positive relationship. This signifies that as S&P 500 returns increase, Stock Z returns will also increase.
Lastly, using the linear equation from above, we can compute for the expected return on Stock Z if return on S&P 500 will be 10%. We will simply substitute x with 10% or 0.10 and multiply it with our beta of 1.28125 and add it with the y-intercept of 0.0328125:
yi = 0.0328125 + 1.28125xi + ε
= 0.0328125 + (1.28125 x .10) + ε
= 0.1609375
Please see attached file