Reference no: EM133491801

Question 1. Use monthly total returns for the S&P 500 index, Xcel Energy (XEL), and NVIDIA (NVDA) from July 2012 through June 2017.

a) Create an XY (Scatter) plot to show the relationship between the returns on each individual stock and the S&P 500. Estimate the slope of a regression equation of this data (SLOPE function).b) Add a linear trend line to each chart, and place the equation and R2 on each chart. How much of the variability in each stock's returns can be explained by variability in the S&P 500?

c) Using the Analysis ToolPak add-in, run two regression models using returns from Xcel Energy and NVIDIA. Your dependent variable will be the Xcel Energy or NVIDIA monthly returns, and the independent variable will be the S&P 500 returns.

Date	S&P 500	XEL	NVDA
7/31/12	1.39%	3.13%	-2.03%
8/31/12	2.25%	-4.81%	3.62%
9/28/12	2.59%	0.34%	-4.92%
10/31/12	-1.84%	1.94%	-10.24%
11/30/12	0.57%	-4.24%	0.61%
12/31/12	0.91%	-0.27%	2.42%
1/31/13	5.18%	4.02%	0.00%
2/28/13	1.35%	3.31%	3.89%
3/29/13	3.75%	4.46%	1.35%
4/30/13	1.92%	7.03%	7.33%
5/31/13	2.34%	-9.66%	5.61%
6/28/13	-1.34%	-0.37%	-2.97%
7/31/13	5.08%	5.68%	2.81%
8/30/13	-2.90%	-6.78%	2.70%
9/30/13	3.14%	-0.09%	5.49%
10/31/13	4.60%	4.53%	-2.39%
11/29/13	3.04%	-2.91%	3.27%
12/31/13	2.54%	0.70%	2.70%
1/31/14	-3.46%	3.47%	-2.00%
2/28/14	4.57%	4.78%	17.60%
3/31/14	0.84%	1.22%	-2.55%
4/30/14	0.74%	4.97%	3.12%
5/30/14	2.34%	-3.48%	3.35%
6/30/14	2.06%	5.80%	-2.42%
7/31/14	-1.38%	-4.44%	-5.61%
8/29/14	4.00%	4.06%	11.63%
9/30/14	-1.40%	-4.24%	-5.15%
10/31/14	2.44%	10.10%	5.91%
11/28/14	2.69%	1.40%	7.77%
12/31/14	-0.25%	6.73%	-4.39%
1/30/15	-3.00%	4.48%	-4.21%
2/27/15	5.75%	-5.99%	15.30%
3/31/15	-1.58%	-0.39%	-5.14%
4/30/15	0.96%	-2.58%	6.07%
5/29/15	1.29%	0.41%	0.17%
6/30/15	-1.94%	-4.56%	-9.13%
7/31/15	2.09%	7.74%	-0.79%
8/31/15	-6.03%	-2.71%	13.16%
9/30/15	-2.47%	5.99%	9.65%
10/30/15	8.43%	0.62%	15.09%
11/30/15	0.29%	0.08%	12.22%
12/31/15	-1.58%	1.61%	3.91%
1/29/16	-4.96%	6.43%	-11.13%
2/29/16	-0.14%	3.46%	7.46%
3/31/16	6.78%	6.65%	13.62%
4/29/16	0.39%	-4.28%	-0.28%
5/31/16	1.79%	3.35%	31.83%
6/30/16	0.26%	9.10%	0.62%
7/29/16	3.69%	-1.79%	21.46%
8/31/16	0.14%	-5.96%	7.62%
9/30/16	0.02%	0.30%	11.71%
10/31/16	-1.82%	1.00%	3.85%
11/30/16	3.70%	-6.11%	29.76%
12/30/16	1.98%	5.21%	15.77%
1/31/17	1.89%	1.52%	2.29%
2/28/17	3.97%	5.78%	-6.93%
3/31/17	0.11%	2.54%	7.34%
4/28/17	1.03%	1.35%	-4.25%
5/31/17	1.41%	6.35%	38.54%
6/30/17	0.63%	-3.51%	0.14%

Question 2) What are your estimates of beta for XEL and NVDA, respectively? Are both of your estimated beta statistically significant at 5% level? Use three methods from the regression output to show why or why not.

Question 3) Compare R2 from the two regression models. How much of the variability in each stock's returns can be explained by variability in the S&P 500?