Question

a    Using the Excel’s Regression Tool, develop the estimated regression equation to show how income (y annual income in $1000s) is related to the independent variables education (level of education attained in number of years), age (Develop the dummy variable for the gender variable first.

b.       Use the t test to test whether each of the coefficients obtained in part (a) are significant at .05 level of significance. What are your conclusions?

c.       Use the F test to test for overall significance of the relationship. What is your conclusion?

EDUCATION	AGE	GENDER	INCOME (in $1000)
12	60	female	6.5
16	39	male	120
16	33	female	21.75
12	51	male	82.5
16	42	female	55
14	20	male	7.5
14	57	male	37.5
13	61	female	5.5
16	31	male	9
12	30	male	37.5
14	68	female	13.75
16	50	male	32.5
12	27	male	0.5
16	30	male	55
18	65	female	55
19	36	male	67.5
12	22	male	21.75
6	35	male	21.75
12	67	female	9
12	48	male	23.75
12	48	female	45
15	42	male	120
14	61	female	37.5
13	34	male	82.5
17	53	male	82.5
12	39	male	67.5
16	61	male	175

Please show excel work thank you!

Answer 1

a)

for obtain dummy for females, apply excel func: "=IF(D2="female",1,0)"

data -> data analysis -> regression

Y: income

X: education, age, female

SUMMARY OUTPUT Regression Statistics Multiple R 0.610338499 R Square 0.372513084 Adjusted R Square 0.290666964 Standard Error 35.36791245 Observations 27 ANOVA df Regression Residual Total 3 23 26 S S MSF Significance F 17079.83009 5693.28 4.55138 0.012062464 28770.45231 1250.89 45850.28241 Intercept EDUCATION Coefficients Standard Errort Stat -65.24648663 42.3185124 -1.5418 5.597488417 2.643035457 2.11783 1.162148447 0.589692023 1.97077 -51.28306833 17.53744017 -2.9242 P-value Lower 95% Upper 95%ower 95.09pper 95.0% 0.13677 -152.7889994 22.296 -152.789 22.296 0.04521 0.129953003 11.065 0.12995 11.065 0.0609 -0.057722443 2.38202 -0.05772 2.38202 0.00763 -87.56202741 -15.0041 -87.562 -15.0041 AGE female

the estimated regression equation:
income ($1000) = -65.2465 + 5.5975*EDUCATION + 1.1621*AGE -51.2831*female

b)
Ho; beta1 = 0. V/s h1: beta1 =/= 0
With t=2.1178257, p<5%, i reject ho and conclude that beta1 =/= 0. coefficient of education is significant.

Ho; beta2 = 0. V/s h1: beta2 =/= 0
With t=1.970771865, p>5%, i fail to reject ho and conclude that beta2 = 0. coefficient of age is significant.

Ho; beta3 = 0. V/s h1: beta3 =/= 0
With t=-2.924204892, p<5%, i reject ho and conclude that beta1 =/= 0. coefficient of female is significant.

c)
Ho: model is not significant
h1: model is significant
With F=4.551383573, p<5%, I reject the null hypothesis and conclude that model is significant