Question

1. A survey conducted by a research team was to investigate how the education level, tenure in current employment, and age are related to annual income. A sample of 20 employees is selected and the data are given below.

Please show the process of Excel analysis, thank you

Education (No. of years)	Length of tenure in current employment (No. of years)	Age (No. of years)	Annual income ($)
17	8	40	124,000
12	12	41	30,000
20	9	44	193,000
14	4	42	88,000
12	1	19	27,000
14	9	28	43,000
12	8	43	96,000
18	10	37	110,000
16	12	36	88,000
11	7	39	36,000
16	14	36	81,000
12	4	22	38,000
16	17	45	140,000
13	7	42	11,000
11	6	18	21,000
20	4	40	151,000
19	7	35	124,000
16	12	38	48,000
12	2	19	26,000
10	6	44	124,000

a. Check if the F test leads to conclude that an overall regression relationship exists. If yes, use the t test to determine the significance of each independent variable. What is the conclusion for each test at the 0.05 level of significance?

b. Remove all independent variables that are not significant at the 0.05 level of significance from the estimated regression equation. What is your estimated regression equation in this case? Provide an interpretation of the coefficients in regards to the independent variables.

Answer 1

Enter the data into Excel.

Now, perform the test.

Go to Data > Data Analysis > Regression.

Select the input data and click OK.

The output is:

(a) The hypothesis being tested is:

H₀: β₁ = β₂ = β₃ = 0

H₁: At least one β_i ≠ 0

The p-value is 0.0004.

Since the p-value (0.0004) is less than the significance level (0.05), we can reject the null hypothesis.

Therefore, we can conclude that the model is significant.

The hypothesis being tested is:

H₀: β₁ = 0

H₁: β₁ ≠ 0

The p-value is 0.0013.

Since the p-value (0.0013) is less than the significance level (0.05), we can reject the null hypothesis.

Therefore, we can conclude that the slope is significant.

The hypothesis being tested is:

H₀: β₂ = 0

H₁: β₂ ≠ 0

The p-value is 0.3246.

Since the p-value (0.3246) is greater than the significance level (0.05), we fail to reject the null hypothesis.

Therefore, we cannot conclude that the slope is significant.

The hypothesis being tested is:

H₀: β₃ = 0

H₁: β₃ ≠ 0

The p-value is 0.0149.

Since the p-value (0.0149) is less than the significance level (0.05), we can reject the null hypothesis.

Therefore, we can conclude that the slope is significant.

Perform the test again.

The output is:

(b) The estimated regression equation is:

y = -138672 + 9588.893*x1 + 2234.556*x2

For every additional x1, y will increase by 9588.893.

For every additional x2, y will increase by 2234.556.

Please give me a thumbs-up if this helps you out. Thank you!