1.

Use monthly total returns for the S&P 500 index, Xcel Energy (XEL), and NVIDIA (NVDA) from July 2012 through June 2017. You can download the monthly return data Download the monthly return data. 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 R² on each chart. How much of the variability in each stocks 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%

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.

3)Compare R² from the two regression models. How much of the variability in each stocks returns can be explained by variability in the S&P 500?

please answer in 30 mins and tell the process how to solve in excel