Question

A sample with two variables (x and y) is given in the table. According to the sample, please develop an excel spreadsheet to calculate the missing values given in the table of “summary output” attached below. The excel spreadsheet needs to be submitted together with your assignment. Note: You can use “data analysis toolpak” to check your answers, but calculations should be based on the formulas that we learnt in module 2.

x	y
1.0	5.2
1.5	7.2
2.0	5.5
2.5	4.9
3.0	7.3
3.5	9.5
4.0	11.3
4.5	10.9
5.0	11.8
5.5	12.3
6.0	11.7
6.5	12.1

SUMMARY OUTPUT
Regression Statistics
Multiple R	?
R Square	?
Adjusted R Square	?
Standard Error	?
Observations	?
ANOVA
	df	SS	MS	F	Significance F
Regression	?	?	?	?	?
Residual	?	?	?
Total	?
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	3.602	?	?	?	?	?
Slope	1.476	?	?	?	?	?

Answer 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.909569429
R Square	0.827316547
Adjusted R Square	0.810048202
Standard Error	1.275204175
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	77.90770979	77.90771	47.90943	4.08527E-05
Residual	10	16.26145688	1.626146
Total	11	94.16916667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	3.605827506	0.880435886	4.095503	0.00216	1.644094109	5.5675609
x	1.476223776	0.21327586	6.921664	4.09E-05	1.001015547	1.95143201