Question

2. Regression review II, problem 6 Aa Aa This question aims to review the La Quinta Inn model building exercise as discussed in lecture and described in the course packet. The data collected can be obtained in this Excel file. (Note: Reload this file each time you go through this problem.) The coefficient of multiple determination for the regression of profit margin (MARGIN) on all the other variables is The adjusted coefficient of multiple determination is From the regression output, at a 10% level of significance, which of the following variables are insignificant individually? Check all that apply ROOMS INCOME OFFICE DISTTWN NEAREST COLLEGE Perform a partial F-test to see whether these variables are really useless as a group in explaining the behavior of . The p-value of At a 5% level of significance profit margin. The F-test statistic for testing the significance of the subset variables is the test statistic for testing the significance of the subset variables is these variables significant as a group. Therefore, you choose as your final model. Now, using this final model, predict the profit margin in each the following locations: EITI Characteristic Ann Arbor Bloomington Champaign 2,300 Rooms 2,672 2,500 Competitor distance 1.3 1.2 0.5 Office space ('000s) 952 604 1,430 Students ('000s) 42 21 45 Income ('000s) 35 37 33.5 Distance to downtown 3.4 4.5 1.4 Predicted profit margin

INN	MARGIN	ROOMS	NEAREST	OFFICE	COLLEGE	INCOME	DISTTWN
1	61	3203	0.1	549	8	37	12.1
2	34	2810	1.5	496	17.5	39	0.4
3	46	2890	1.9	254	20	39	12.2
4	31.9	3422	1	434	15.5	36	2.7
5	57.4	2687	3.4	678	15.5	32	7.9
6	47.5	3080	2.4	488	13.5	31	6.7
7	54.4	2756	1.1	832	14.5	35	6.9
8	46.2	2244	0.7	496	15.5	38	8.9
9	54.1	2862	1.4	809	16.5	33	3
10	43.5	3198	2.5	548	17	38	10.2
11	52.7	3378	1	815	10	40	7.2
12	49.5	2726	3.1	464	19	38	5.5
13	43.4	3597	0.4	616	15.5	39	1.6
14	33.5	3657	2.8	372	18	46	11.3
15	46.3	2554	2.2	585	10	42	4.9
16	44.9	3003	3.4	402	15.5	37	4.6
17	56.5	2045	2.8	562	15	36	10.5
18	39.3	3591	2.3	510	18.5	45	7.2
19	62.8	1613	2.6	686	21.5	45	10.7
20	40.3	2848	1.8	744	19	44	6.4
21	41.1	3098	2	449	11.5	40	7.1
22	35.7	3591	2.9	270	9.5	31	9.8
23	58.5	2210	1.6	611	17.5	39	12.1
24	53.5	3285	2.3	548	23.5	32	11.9
25	54.2	3018	1.2	529	20	35	6.6
26	43.3	3383	2.1	790	11.5	41	5.3
27	58	2382	1.1	484	12	35	13.5
28	38.1	3568	2	365	16.5	37	11.1
29	45.1	2172	2.9	198	7	39	5.6
30	42.8	3003	2.1	457	19.5	35	2.8
31	51.4	2640	0.5	431	19.5	37	7.2
32	45.4	2443	4.2	515	10.5	38	7.3
33	48.2	2429	2	498	20	41	7.2
34	40.2	3670	1.5	240	17.5	37	4.9
35	36.8	3006	1.8	456	15.5	37	10.6
36	54.2	2484	1.5	443	16	41	14.2
37	36.9	2923	3.1	449	14	34	7.2
38	53	2844	0.8	406	13.5	44	4
39	39.3	3159	1.6	392	11	40	14.6
40	54.4	3241	2.1	578	21.5	30	9.7
41	43.7	2915	2.7	620	13.5	38	9.5
42	51.5	3069	1	512	20.5	37	7.1
43	43.5	2910	2.9	418	12.5	39	13
44	48.8	2935	0.4	551	20	39	8.7
45	36.1	2962	3.4	164	12.5	33	6.2
46	33.2	2629	2.9	359	20	39	7.6
47	41.9	3517	1.4	516	6	36	5.7
48	49.8	2859	2.6	544	14.5	35	11.8
49	45	3697	2.2	236	20.5	34	5.8
50	43.2	2724	3.1	597	14.5	32	14.5
51	38.8	3330	1.6	410	13.5	33	10.8
52	37.8	2980	1.1	427	12	42	8.2
53	41	2846	1.2	231	20.5	43	6
54	48.8	2825	3.2	774	13	34	5.8
55	34.3	3548	1.8	449	13.5	43	6.5
56	49.6	2922	2.4	459	19.5	42	10.4
57	49.9	3255	2.1	551	22.5	38	6.1
58	39.2	2996	3	359	15.5	36	6.3
59	42.5	2403	3.3	345	19	42	6.6
60	52.6	3013	0.6	830	20	33	0.7
61	47.3	2642	2.5	450	9	44	10
62	41	3480	2.4	265	18	43	3.8
63	51.1	3227	2.4	711	11	37	8.8
64	52.1	3140	2.1	433	23.5	40	11.1
65	47.6	2751	1.3	426	10.5	38	5.7
66	40.8	2676	1	300	25	33	9.7
67	52.2	2879	2.2	747	19.5	38	11.9
68	60.1	2619	1.4	683	12	43	8
69	52	3354	2.3	614	26.5	37	7.6
70	51.2	3082	2.8	637	22	34	3.6
71	51.2	2775	1.8	497	15	35	9.2
72	44	2813	0.5	233	15	41	3
73	49.6	3359	2.7	473	10.5	30	12.2
74	47.5	3080	2.4	488	13.5	31	6.7
75	54.4	2756	1.1	832	14.5	35	6.9
76	46.2	2244	0.7	496	15.5	38	8.9
77	54.1	2862	1.4	809	16.5	33	3
78	43.5	3198	2.5	548	17	38	10.2
79	52.7	3378	1	815	10	40	7.2
80	49.5	2726	3.1	464	19	38	5.5
81	43.4	3597	0.4	616	15.5	39	1.6
82	33.5	3657	2.8	372	18	46	11.3
83	46.3	2554	2.2	585	10	42	4.9
84	42.9	3838	1.9	875	10	34	11.9
85	27.3	4214	1.9	358	16	40	9.2
86	41.6	2776	2.2	360	16	43	7.5
87	49	1998	0.7	461	19	33	9.9
88	55.4	2790	2.7	645	12	37	5.7
89	54.1	2432	1.4	370	17.5	39	4.5
90	32.4	3124	2.7	349	17	45	7.5
91	47.3	2761	0.9	485	16	35	8.2
92	30.8	3622	2	347	15	40	7.2
93	49	2826	0.1	390	17	40	13.6
94	57.3	2601	1.2	520	18.5	35	3.8
95	60.5	2932	2.5	469	19.5	35	8.7
96	53.4	2772	3.5	622	14.5	29	3.5
97	35.9	2786	2.4	511	15.5	37	6.3
98	40	3397	2.7	855	19.5	42	11.7
99	39.8	3823	0.7	202	17	36	10
100	32	3251	2.6	275	13	39	10.5

Answer 1

using excel>data analysis>regression

we have

Regression Statistics
Multiple R	0.724953
R Square	0.525556
Adjusted R Square	0.494947
Standard Error	5.517757
Observations	100
ANOVA
	df	SS	MS	F	Significance F
Regression	6	3136.483	522.7472	17.16985	2.9E-13
Residual	93	2831.445	30.44564
Total	99	5967.928
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	74.08432	7.985766	9.277046	6.93E-15	58.22617	89.94247
Rooms	-0.00797	0.001249	-6.38667	6.61E-09	-0.01045	-0.0055
Nearest	-1.56552	0.632113	-2.47665	0.01507	-2.82077	-0.31027
Ofice	0.019463	0.003417	5.695413	1.43E-07	0.012677	0.026249
College	0.199082	0.137898	1.443691	0.152186	-0.07476	0.47292
Income	-0.42306	0.143796	-2.94211	0.004115	-0.70861	-0.13751
DISTTwn	0.229487	0.173906	1.319599	0.190208	-0.11586	0.57483

the coefficient of multiple determination is 0.5256

the adjusted coefficient of multiple determination is 0.4950

the only College and DiSSTWN variable is insignificant at 10% level of significance because p value 0.152 and 0.190 are >0.10

the f test statistic is 17.20. the p-value of f test statistic is 0.0000. at 5 % level of significance, these variable are significant , therefore you choose this model as your final model.

characterstics	Ann Arbor	Bloomington	Champaign
Rooms	2672	2500	2300
Competitor distance	1.3	1.2	0.5
Office Space	952	604	1430
Students	42	21	45
Income	35	37	33.5
Disst to downtown	3.4	4.5	1.4
predicted profit margin	63.61236	53.593	77.90432