Question

Problem 1

The dependent variable is assumed to be values of a land.

a) Use the Excel regression tool to do the linear regression, and provide the

“Line Fit Plots” (which is provided in the regression interface). (5 points)

b) What can the plot tell you? E.g., does it show that the fitting is good?

(5 points)

c) Now check the output.

c.1) What is the standard error of the estimate of the slope? (5 points)

c.2) What is the t-test statistic for the slope? Reproduce the t-test statistic

in Excel using other values in the output (e.g., point estimate and standard

error). (10 points)

c.2) What is the 95% confidence interval of the slope? Reproduce the con-

fidence interval in Excel using other values in the output (e.g., t stat). Can

we use the confidence interval to claim that the independent variable can be

dropped in the linear regression model? (10 points)

c.3) What is the p-value for the estimate of the slope? Reproduce the p-value

in Excel using other values in the output (e.g., t stat). Can we use the p-values

to claim that the independent variable can be dropped in the linear regression

model? (10 points)

c.4) Does the linear regression model fit well? Explain your answer. (5

points)

d) Assume that the area of the land you are considering to sell is only one

acre. Does the linear regression model provide a good prediction for the value

of your land? (5 points)

e) Assume that you want to check if the slope should be significantly bigger

than 10,000.

e.1) Write the hypotheses. (5 points)

e.2) What is the new t-test statistic? (5 points)

e.3) What is the new p-value for the estimate of the slope? Is the slope

significantly bigger than 10,000? (10 points)

Values
836,586,000
986,547,000
1,075,609,000
381,443,000
889,148,000
1,096,422,000
1,340,628,000
903,129,000
785,261,000
1,407,381,000
799,722,000
1,242,590,000
378,638,000
395,110,000
582,299,000
286,805,000
1,286,312,000
188,313,000
529,053,000
700,357,000
1,123,597,000
392,277,000
1,068,679,000
576,348,000
535,527,000
797,064,000
854,322,000
1,415,763,000
1,110,576,000
543,485,000
621,503,000
44,632,000
473,953,000
129,286,000
372,399,000
604,300,000
432,818,000
748,532,000
139,826,000
456,433,000
1,694,543,000
967,926,000
1,009,765,000
1,085,302,000
1,089,378,000
1,331,657,000
364,124,000
1,070,730,000
1,536,796,000
1,426,503,000
796,188,000
1,559,685,000
493,466,000
743,640,000
376,926,000
957,234,000
169,340,000
157,625,000
309,507,000
265,410,000
251,621,000
412,789,000
136,533,000
184,032,000
256,578,000
228,716,000
565,330,000
219,363,000
388,716,000
81,059,000
371,794,000
853,684,000
618,448,000
1,032,717,000
876,501,000
157,428,000
726,993,000
1,178,550,000
762,332,000
1,269,773,000
1,018,473,000
895,709,000
2,412,768,000
1,211,090,000
1,060,153,000
2,145,334,000
1,050,692,000
1,227,843,000

Answer 1

a)

The regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Value' column, Input X Range: 'Land' column, tick on Line Fit Plots then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The line fit plot is shown below,

b)

The scatterplot for data values shows a nonlinear pattern hence the line fit plot is not a good fit.

c)

c.1) The standard error of the estimate of the slope is,

c.2) The t-test statistic for the slope is,

The t statistic is obtained by using the formula

Where is point estimate of slope and is the standard error of ,

c.2) The 95% confidence interval of the slope is,

Now, the 95% confidence interval is reproduced using the formula,

Where t value is obtained using the the excel function =T.INV.2T(0.05,86) for 95% confidence interval

Yes, if the value lies outside the confidence interval then we can dropped the variable in the linear regression model

c.3) The p-value for the estimate of the slope is,

The P-value can be reproduced using the excel function =T.DIST.2T(x, deg_freedom) where x is the t value = 0.504332 and degree of freedom = 87.

P-value =T.DIST.2T(D18,87) = 0.615318

Yes, if the P-value is greater than the confidence level then we can dropped the variable in the linear regression model

c.4) The R square value in the regression model tells how well the model fits the data value.

From the regression model,

The R square value is approximately, 0.0029 which mean the model explains only 0.29% variance of the data values. Since the model explains the very small variance of the data values. This is not a good model.

e)

e.1) The hypotheses are defined as,

e.2) The t-test statistic is obtained as follow,

e.3) The p-value is obtained using the excel function =T.DIST.2T(x,deg_freedom)

Where x = 0.499295, deg_freedom = 87

The P-value is greater than 0.05 at 5% significance level hence null hypothesis is failed to reject hence the slope is no significantly bigger than 10,000