Question

The file HW_09.xlsx contains data from a survey of 105 randomly selected households (once you click cell C1, “Ownership,” you can see what 0 and 1 stand for).

a. In an effort to explain the variation in the size of the monthly home mortgage or rent payment, estimate a multiple regression equation that includes all of the potential household independent variables.

b. Interpret the ANOVA table for this model. In particular, does this set of independent variables provide at least some power in explaining the variation in the dependent variable? Report the F ratio statistics and p- value for this hypothesis test.

c. Interpret coefficients of independent variables in the model.

d. Using the regression output, determine which of the independent variables should be excluded from the regression equation. Justify your choices.

e. Do you obtain substantially different results if you combine First Income and Second Income into a Total income variable and then use the latter as the only income independent variable?

Household	Family Size	Ownership	First Income	Second Income	Total Income	Utilities	Debt	Family Size	Ownership	Monthly Payment
1	2	1	$58,206	$38,503	$96,709	$252	$5,692	2	1	$1,585
2	6	0	$48,273	$29,197	$77,470	$216	$4,267	6	0	$1,314
3	3	0	$37,582	$28,164	$65,746	$207	$2,903	3	0	$383
4	3	0	$37,731	$21,454	$59,185	$217	$3,011	3	0	$743
5	4	0	$30,434	$26,007	$56,441	$208	$3,718	4	0	$991
6	6	0	$36,970	$31,838	$68,808	$222	$4,845	6	0	$991
7	3	0	$27,350	$20,969	$48,319	$209	$2,256	3	0	$619
8	2	0	$29,692	$20,617	$50,309	$190	$879	2	0	$997
9	2	0	$49,883	$29,240	$79,123	$201	$3,482	2	0	$902
10	4	1	$57,577	$52,088	$109,665	$255	$6,261	4	1	$903
11	3	0	$66,868	$57,974	$124,842	$202	$3,911	3	0	$574
12	2	1	$48,376	$39,894	$88,270	$257	$5,889	2	1	$1,407
13	3	1	$65,241	$47,205	$112,446	$254	$5,737	3	1	$699
14	3	1	$34,531	$21,536	$56,067	$264	$5,538	3	1	$988
15	3	0	$18,846	$12,196	$31,042	$214	$1,852	3	0	$746
16	3	1	$49,181	$35,091	$84,272	$264	$7,020	3	1	$1,510
17	4	1	$65,276	$61,140	$126,416	$261	$7,212	4	1	$1,091
18	2	0	$23,597	$20,798	$44,395	$211	$1,796	2	0	$402
19	5	0	$28,851	$22,682	$51,533	$225	$3,452	5	0	$696
20	3	1	$62,276	$31,231	$93,507	$249	$6,428	3	1	$1,548
21	4	1	$42,639	$32,912	$75,551	$258	$4,469	4	1	$919
22	2	1	$35,592	$23,074	$58,666	$245	$5,516	2	1	$1,669
23	3	1	$49,777	$27,773	$77,550	$256	$5,239	3	1	$1,049
24	3	1	$21,451	$13,309	$34,760	$259	$3,731	3	1	$493
25	7	1	$37,808	$28,022	$65,830	$286	$7,228	7	1	$722
26	5	1	$67,798	$33,945	$101,743	$264	$6,067	5	1	$1,424
27	5	1	$63,354	$52,656	$116,010	$277	$6,153	5	1	$1,458
28	2	0	$30,693	$23,046	$53,739	$199	$3,763	2	0	$977
29	4	1	$49,954	$30,758	$80,712	$260	$5,179	4	1	$1,062
30	2	1	$55,752	$37,404	$93,156	$253	$5,768	2	1	$1,660
31	5	0	$43,436	$21,855	$65,291	$215	$2,767	5	0	$699
32	5	1	$36,780	$21,609	$58,389	$279	$5,311	5	1	$573
33	3	0	$64,489	$43,448	$107,937	$207	$3,934	3	0	$748
34	2	1	$30,433	$23,929	$54,362	$244	$4,648	2	1	$1,318
35	5	0	$20,234	$17,507	$37,741	$223	$3,030	5	0	$572
36	2	0	$16,252	$11,539	$27,791	$206	$1,428	2	0	$669
37	3	1	$75,225	$71,930	$147,155	$260	$6,434	3	1	$1,272
38	2	1	$38,838	$36,513	$75,351	$247	$4,428	2	1	$1,084
39	3	1	$60,993	$44,220	$105,213	$257	$6,675	3	1	$1,476
40	2	0	$31,051	$26,520	$57,571	$195	$1,821	2	0	$1,033
41	2	0	$27,222	$17,215	$44,437	$201	$3,705	2	0	$520
42	2	0	$27,651	$18,644	$46,295	$195	$3,661	2	0	$501
43	3	1	$53,650	$50,636	$104,286	$259	$7,043	3	1	$1,292
44	5	0	$42,168	$39,108	$81,276	$221	$1,739	5	0	$490
45	4	1	$36,472	$33,832	$70,304	$260	$5,895	4	1	$1,435
46	4	1	$63,572	$54,048	$117,620	$249	$6,165	4	1	$1,123
47	2	1	$50,233	$39,539	$89,772	$259	$7,177	2	1	$1,293
48	3	0	$35,466	$28,368	$63,834	$212	$3,949	3	0	$776
49	2	1	$77,892	$74,072	$151,964	$246	$6,309	2	1	$875
50	4	1	$53,117	$48,393	$101,510	$254	$6,956	4	1	$1,534
51	4	1	$56,310	$41,558	$97,868	$261	$7,363	4	1	$1,283
52	4	1	$34,259	$28,438	$62,697	$251	$5,235	4	1	$1,302
53	5	1	$36,065	$28,020	$64,085	$278	$5,430	5	1	$640
54	2	1	$56,330	$54,315	$110,645	$254	$5,429	2	1	$989
55	2	1	$54,863	$41,329	$96,192	$245	$6,172	2	1	$1,751
56	3	0	$36,373	$32,341	$68,714	$207	$3,187	3	0	$428
57	5	0	$60,940	$57,432	$118,372	$218	$2,615	5	0	$888
58	3	1	$53,249	$39,777	$93,026	$254	$7,415	3	1	$1,469
59	2	1	$59,064	$53,545	$112,609	$255	$4,752	2	1	$943
60	2	0	$48,232	$30,728	$78,960	$199	$3,285	2	0	$1,134
61	3	1	$62,280	$52,654	$114,934	$259	$4,497	3	1	$1,278
62	3	1	$37,184	$27,569	$64,753	$265	$5,736	3	1	$1,014
63	2	1	$86,398	$43,989	$130,387	$241	$6,145	2	1	$1,137
64	4	1	$62,522	$55,855	$118,377	$265	$7,345	4	1	$831
65	3	0	$41,490	$35,158	$76,648	$205	$3,144	3	0	$578
66	6	1	$45,391	$30,121	$75,512	$275	$5,991	6	1	$1,040
67	2	0	$36,588	$30,278	$66,866	$202	$1,800	2	0	$602
68	2	0	$48,726	$48,694	$97,420	$203	$3,120	2	0	$622
69	4	0	$38,740	$24,806	$63,546	$208	$2,584	4	0	$568
70	4	0	$26,219	$16,165	$42,384	$211	$3,309	4	0	$530
71	4	1	$46,667	$29,334	$76,001	$255	$5,735	4	1	$1,250
72	4	1	$65,115	$53,755	$118,870	$259	$5,481	4	1	$1,300
73	3	0	$40,397	$31,725	$72,122	$214	$2,916	3	0	$576
74	3	1	$73,734	$73,481	$147,215	$264	$4,112	3	1	$1,731
75	3	1	$58,580	$51,488	$110,068	$261	$3,671	3	1	$670
76	3	1	$55,806	$37,384	$93,190	$257	$6,247	3	1	$1,676
77	3	1	$48,455	$31,331	$79,786	$270	$5,719	3	1	$635
78	2	1	$40,858	$34,114	$74,972	$247	$5,212	2	1	$1,049
79	2	1	$59,737	$59,510	$119,247	$254	$5,924	2	1	$1,588
80	4	0	$36,395	$27,650	$64,045	$215	$3,067	4	0	$655
81	3	1	$42,843	$36,965	$79,808	$261	$4,963	3	1	$1,500
82	5	0	$21,557	$14,322	$35,879	$217	$2,606	5	0	$658
83	3	0	$37,186	$24,577	$61,763	$204	$2,759	3	0	$689
84	2	1	$35,479	$21,271	$56,750	$255	$3,933	2	1	$964
85	6	0	$30,584	$17,372	$47,956	$223	$4,229	6	0	$586
86	5	1	$39,096	$37,336	$76,432	$263	$6,631	5	1	$1,203
87	5	0	$24,128	$13,889	$38,017	$216	$2,812	5	0	$762
88	3	0	$23,302	$20,987	$44,289	$223	$2,961	3	0	$626
89	2	1	$49,132	$36,007	$85,139	$250	$6,796	2	1	$2,171
90	2	1	$56,838	$32,021	$88,859	$252	$5,963	2	1	$810
91	4	1	$61,955	$57,445	$119,400	$253	$6,727	4	1	$1,460
92	5	0	$34,354	$22,543	$56,897	$228	$5,593	5	0	$778
93	4	0	$24,442	$12,940	$37,382	$205	$2,013	4	0	$558
94	5	0	$22,259	$19,683	$41,942	$223	$2,408	5	0	$680
95	6	1	$48,910	$40,063	$88,973	$268	$6,810	6	1	$1,007
96	3	1	$22,991	$14,387	$37,378	$257	$4,661	3	1	$1,252
97	2	1	$84,820	$70,096	$154,916	$245	$6,661	2	1	$1,467
98	7	0	$39,602	$32,198	$71,800	$237	$5,906	7	0	$848
99	3	0	$31,100	$28,489	$59,589	$210	$2,072	3	0	$1,083
100	3	1	$58,451	$29,753	$88,204	$257	$6,623	3	1	$1,470
101	4	0	$20,277	$14,502	$34,779	$215	$2,794	4	0	$500
102	2	1	$33,963	$33,168	$67,131	$243	$4,006	2	1	$991
103	3	0	$30,482	$18,684	$49,166	$208	$4,557	3	0	$588
104	3	1	$90,488	$68,619	$159,107	$262	$7,904	3	1	$1,537
105	3	1	$61,174	$35,481	$96,655	$261	$4,504	3	1	$1,601

Answer 1

We want to estimate the following regression model (we will exclude Total iincome in this model, we will use it in part e)

where is the intercept

are the slope coefficients for variables Family Size,Ownership, First Income, Second Income, Utilities, Debt respectively

is a random disturbance

Using excel option data--->data analysis-->regression

Get the following output

a) The estimated regression equation is

this model is able explain 52% variation in the size of the monthly home mortgage or rent payment, as given by the value of R-square given below

b) The ANOVA table is below

To test if the this set of independent variables provide at least some power in explaining the variation in the dependent variable, we test the following hypotheses

The sample F statistics is given in the ANOVA table above as 17.67 (rounded to 2 decimals)

The p-value is 0.0000 (rounded to 4 decimals)

Since p-value is less than the significance level of 0.05, we reject the null hypothesis.

We say that there is sufficient evidence to conclude that at least one of the independent variables provide at least some power in explaining the variation in the dependent variable

c) the estimate of slope for family size is 4.8404. The positive sign indicates that the monthly payment and family size move in the same direction. Keeping all other variables the same, an increase in the family size by 1 will increase the monthly payment by $4.84

the estimate of slope for ownership is 702.4540 The positive sign indicates that monthly payment when Ownership =1 (that is if you own the house) is higher than when ownership =0 (when you do not own). Keeping all other variables the same, ownership increases the the monthly payment by $702.45

the estimate of slope for First Income is 0.0039. The positive sign indicates that the monthly payment and First Income move in the same direction. Keeping all other variables the same, an increase in the First income by $1 will increase the monthly payment by $0.0039

the estimate of slope for Second Income is -0.0016. The negative sign indicates that the monthly payment and Second Income move in opposite direction. Keeping all other variables the same, an increase in the Second income by $1 will decrease the monthly payment by $0.0016

the estimate of slope for Utilities is -9.5017. The negative sign indicates that the monthly payment and Utility move in opposite direction. Keeping all other variables the same, an increase in the Utilties by $1 will decrease the monthly payment by $9.50

the estimate of slope for Debt is 0.0755. The positive sign indicates that the monthly payment and Debt move in the same direction. Keeping all other variables the same, an increase in the Debt by $1 will increase the monthly payment by $0.0755

d) We want to test the following hypotheses for each of the slope coefficient to test if they are significant

we look at the t-statistics and the -value of each of the coefficient estimates from the table below

If the p-value is greater than 0.05 we can accept the null hypothesis and conclude that there is no sufficient evidence that the independent variable has power to explain variation in monthly payment.

We can see that the the p-values for the slopes of Family size, First income and second income are greater than the 0.05. That means these 3 varaibles should be excluded from the regression

e) We want estimate the following model after combing incomes into total income

We estimate the regression again using

Get the followng output

the estimated regression equation is

Incomparison to the earlier model

we can see that the effect of fall the independent variables (other than income) has increased slightly. The total income has a slight positive effect on monthly payment.

The amount of variation explained indicated by R-square has not changed much. Still around 52% of variation is being explained by the independent variables

The model is overall significant as indicated by ANOVA, p-statistics, which is 0.0000 (rounding to 4 decimals)

The slopes of Total income and family sizes are still not significant (from the t-stats and p-values) and hence have to be excluded from the regression.

Hence there does not seem to be any significant difference due to combining the incomes