Question

The data resulting from a random sample of 5 observations are shown below. Y is the dependent variable, and X1 and X2 are the independent variables.

Observation	Y	X1	X2
1	87	95	11
2	86	94	11
3	84	94	11
4	83	93	12
5	84	93	12

Use Excel's Regression tool to answer the following questions. To copy the data set, highlight the table, press Ctrl-c, click on the Excel cell to which you want to copy, then press Ctrl-v.

Fill in Multiple Blanks

For all numerical answers, show one (1) digit to the right of the decimal point, for example, 1.0, 1.2. Apply the appropriate rounding rule if necessary. Hint: You can use the “Format Cell” option in the Regression output so that it shows two digits after the decimal point. Excel will automatically round the values up or down, if necessary.

1. The estimated regression line is (enter the estimated coefficients in the appropriate space):

yhat =____________ +____________ X1 +____________ X2

2. At 95% confidence, X1  (enter is, is not) significantly related to Y.

3. At 95% confidence, X2  (enter is, is not) significantly related to Y.

4. Predict Y when X1 = 94.8 and X2 = 11.2. Yhat = .

5. While testing for overall significance at 95% confidence, the null hypothesis (shown below)  (enter should, should not) be rejected.

Ho: beta1 = beta2 = 0

Ha: At least one of the beta's does not equal 0

6. True or false? About 77% of the variability in Y is explained by X1 and X2, and about 23% of the variability in Y is explained by the other factors not included in the regression.  (Enter true, false).

Answer 1

1. The estimated regression line is (enter the estimated coefficients in the appropriate space):

yhat = -108.5+ 2X1 + 0.5X2

2. At 95% confidence, X1 is not significantly related to Y.

because p-value = 0.2815 > 0.05

3. At 95% confidence, X2 is not significantly related to Y.

because p-value = 0.8333 > 0.05

4. Predict Y when X1 = 94.8 and X2 = 11.2. Yhat = -108.5+2*94.8+0.5*11.2=86.7

5. While testing for overall significance at 95% confidence, the null hypothesis (shown below) should not be rejected.

Ho: beta1 = beta2 = 0

Ha: At least one of the beta's does not equal 0

because p-value = 0.2314 > 0.05

6. True or false? About 77% of the variability in Y is explained by X1 and X2, and about 23% of the variability in Y is explained by the other factors not included in the regression.

Answer: true

because R =Square =0.768519 =77