Question

Price	Lot size	Trees	Distance
89.7	21.8	45	62
136.1	66.3	79	34
44.7	28.2	53	77
63.2	41.9	64	65
163.4	46.7	69	27
64.1	32.1	12	0
98.7	38.5	59	77
139.9	27.6	10	0
92	47	65	37
66.6	20.7	24	51
16.4	34	22	75
131.9	31.9	56	63
11	28	12	42
27.9	40	52	84
103.5	46.6	36	70
107	23.2	11	83
51.6	46.4	53	44
133.4	32.1	55	98
101.4	35.3	38	75
133.3	40.1	68	0
117.7	35.6	24	41
49.7	20.6	16	77
49.6	22.4	32	86
83.2	45.8	77	19
81.3	29.4	40	0
152.5	51.7	60	34
112.2	27.2	10	16
37.1	37	50	49
130.2	38.9	48	63
39.1	32.5	25	45
81.9	34	12	0
24.6	35.8	16	34
101.9	32.9	44	42
117.6	46.4	62	48
148.8	51.9	59	39
60.2	28.9	10	66
43.7	35.2	57	77
113.1	30.4	50	78
38.1	38.3	24	62
89.2	49.2	61	0
3	21.5	46	83
55.8	41.9	30	69

2. Homework 19, Question 2 A real estate agent is interested in estimating the value of a piece of lakefront property. She believes that price is a function of lot size (thousands of square feet), number of mature trees on the lot, and distance to the lake (in yards). She has collected the data in this Excel® file on the basis of recent sales. (Note: Reload this file each time through this problem.) So in this model the price of a home is said to depend on the lot size, the number of trees in the lot, and the distance from the home to the lake. Run regression on the data using Tools > Data Analysis, selecting the Price column as the Y range and selecting the Lot size, Trees, and Distance columns as the X range Look at the regression output and determine whether any telltale signs of multicollinearity are present. (Hint: Remember the two ways multicollinearity is usually detected: if the results of the overall F-test and the individual t-tests contradict each other, or if the correlation between any pair of independent variables exceeds 0.8 in absolute value.) The overall significance F-test p-value is . So it appears the model as a whole significant, that IS is different from zero Next, test whether each individual independent variable (when tested alone) is significant. Your measured p-values for the three independent variables are Lot size: Trees: Distance: Based on these measured test statistics and allowing for 10% probability of committing a Type 1 error, which of the following variables are significant? Check all that apply. Lot size Trees Distance Is there a contradiction in the results between the F-test and the three t-tests for slope? Now, use Data Analysis > Correlation to generate a correlation matrix for your three independent variables. The largest correlation (in absolute value) you find is As a result of these tests, your conclusion is that there is reason to suspect multicollinearity in these data.

Answer 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.481805
R Square	0.232136
Adjusted R Square	0.171515
Standard Error	38.83705
Observations	42
ANOVA
	df	SS	MS	F	Significance F
Regression	3	17327.45	5775.818	3.829314	0.017213
Residual	38	57316.02	1508.316
Total	41	74643.48
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	57.76602	29.71969	1.943695	0.059365	-2.39836	117.9304
Lot size	0.575668	0.857808	0.671092	0.506221	-1.16087	2.312209
Trees	0.58747	0.394442	1.489371	0.144643	-0.21104	1.385976
Distance	-0.38832	0.232388	-1.67102	0.102934	-0.85877	0.082119

a ) Considering alpha as 0.05 for F-test

the overall F-test significance p-value is 0.017, so it appears the model as a whole is significant , that is slopes are different from zero.

the p-value for the variables

Lot size = 0.506

Trees = 0.145

Distance = 0.103

Based on these measured statistics and alpha as 0.10 there are no independent variables which are significant.

None of the variables are significant.

There exist a contradiction between the F-test and the three t-tests for slopes.

Correlation Matrix

	Lot size	Trees	Distance
Lot size	1
Trees	0.641909	1
Distance	-0.2782	0.023252	1

the largest correlation we find is 0.642 between Lotsize and trees.

As a result of these tests, your conclusion is that there is a reason to suspect multicollinearity in these data.