Question

Create an excel worksheet and populate it, in columns, with values of X that are -2, -1, 0, 1, 2 and values of Y that are -1, 1,1,1,3. As an aside, note that the sample mean of X is 0. Create the columns shown below, and enter the proper formulas into excel.

					(X minus	(X minus mean)
					Mean)	times (Y
	Y	Y-mean	X	X-mean	squared	minus mean)	Y-hat	u-hat
	-1		-2
	1		-1
	1		0
	1		1
	3		2
sum
sum*1/n
B1-hat =
B0-hat =

Given this data (and formulas in Key Concept 4.2), calculate B1-hat and B0-hat on your spreadsheet .

Recall that the way to create Y-hat is to use your calculated B1-hat and B0-hat to create the formula for the line (Y-hat = B1-hat and B0-hat *X), then for each X value, put it into the line equation and get out the associated Y-hat for that X value. Then the u-hat is the actual Y value minus the predicted Y value, the Y-hat, for each observation. Create these values on your spreadsheet.

Answer 1

No.	Y	(x - 0)	X	(y - 1)	(x- 0) (y - 1)
1	-1	-2	-2	-2	4	4	4
2	1	0	-1	-1	0	1	0
3	1	0	0	0	0	0	0
4	1	0	1	1	0	1	0
5	3	2	2	2	4	4	4
Total	5	0	0	0	8	10	8
Mean X	0
Mean Y	1

The regression equation of Y on X

=

= 1 - 0.8* 0

Therefore the regression equation is

The table with the predicted values is

No.	Y	(x - 0)	X	(y - 1)	(x- 0) (y - 1)
1	-1	-2	-2	-2	4	4	4	-0.6	-0.4	0.16
2	1	0	-1	-1	0	1	0	0.2	0.8	0.64
3	1	0	0	0	0	0	0	1	0	0
4	1	0	1	1	0	1	0	1.8	-0.8	0.64
5	3	2	2	2	4	4	4	2.6	0.4	0.16
Total	5	0	0	0	8	10	8	5	0	1.6
Mean X	0
Mean Y	1