Question

The data set  Roslyn in the accompanying workbook gives appraised values (In $1000’s) and size (in square feet)  for thirty houses in the Roslyn neighborhood.

a)     Use Excel’s Data Analysis ToolPak to produce output for the simple linear regression, with Valueas the response (y) variable and Size as the predictor (x) variable.

b)     Write out the equation of the regression of Value (y) on Size (x)

c)      State the numerical value of the slope of the regression line. What does it tell you in this context?

d)     State the numerical value of the standard error of the estimate? What does it tell you in this context?

e)     State the numerical value of the coefficient of determination? What does it tell you in this context?

f)      Give the 95% confidence interval for the intercept of the regression equation.   




Are there any negative numbers in this interval?   What practicalconclusion can you draw from your answer?

g)     Here is a muddled, inaccurate “explanation” of what it means to be “95% confident” in the interval for the slope.   “For 95% of samples the population slope, b,will be in the interval [0.1399, 0.2336]”
Give an accurateexplanation of “95% confidence” in this context.

h)     What practical information does the regression equation give a realtor about a 3000 square foot house ?

i)       What practical information does the regression equation give a realtor about a 30000 square foot house ?

j)       Conduct an appropriate statistical test for the significance of the regression of value on size, including a clear statement of the hypotheses.  (Note that for a Simple Linear Regression you can use either the Test for Individual Significance or the Test of Joint Significance.   You will reach identical conclusions)

k)              What practical conclusion can a realtor draw from the hypothesis test in j)?

Data:

Address	Appraised Value	House Size (square feet)
182 Village Road	681.4	2194
108 Burnham Avenue	606.0	3032
143 Powerhouse Road	457.9	1970
55 Hummingbird Drive	912.7	3356
40 Maple Street	416.7	2070
47 Magnolia Lane	726.6	2826
35 Harding Avenue	393.1	1606
100 Crescent Lane	612.4	2063
222 Garden Street	355.4	1392
6 Church Street	299.0	1120
12 Ridge Drive	471.0	1817
24 Madison Place	510.7	2496
18 Rockhill Road	517.7	1615
65 Elm Drive	873.3	4067
30 Wren Drive	854.7	3130
54 Lambert Street	374.8	1423
38 Magnolia Lane	543.0	1799
75 Burnham Avenue	554.0	2936
19 Oxford Street	365.2	1439
215 Elm Drive	811.8	4065
34 The Oaks	711.8	2191
2 Circle Lane	598.7	2008
70 Rugby Road	651.3	2070
150 Warner Avenue	511.1	2710
31 West Court	379.3	1416
7 The Locusts	786.0	3244
65 Starling Court	768.7	2493
106 Barberry Lane	679.9	2473
17 South Drive	615.8	1968
8 Woodland Road	766.4	3136

Answer 1

a)First of all you have to install data analysis toolpak add-in which can be added by going to EXCEL OPTIONS -ADDS-IN-DATA ANALYSIS TOOLPAK.

Then enter data and go to Data analysis=>Regression

now in Regression tab follow following steps

1)In y-input range select column of Appraised values(which is our response variable)

2)In x-input range select column of size (which is our predictor variable )

3)check LABELS box

4)check OUTPUT RANGE and select a cell(for eg A11) to display output

5)Hit OK

Now after that you can see the output

From above table INTERCEPT is 151.41 and SLOPE is 0.1874

b) therefore Equation is Y=151.51+0.187*X

c)numerical value of slope is 0.187 which is positive which tells us that there is positive relation

between X and Y that is Appraised value will increase if size increases and vice versa.

d)value of SD is 94.26

e)Coefficient of Determination (R^2)=0.7192 which means that 71.92% of variation in Y(Appraised values) is explained by independent variable X(Size)