A business venture by a new Canadian automobile company is trying to enter the US auto market. They have contracted a consulting company to understand what factors impact the pricing of the cars depends. Specifically, they want to understand which variables are significant in predicting the price (dependent variable) of a car. They have collected the following data which can be found in the HW 4_1 Data.xlsx file: curbweight: the weight of a car without occupants or baggage (lbs) enginesize: size of the engine (in cubic inches) horsepower: engine horsepower (hp) citympg: mileage in city (mpg) highwaympg: mileage on highway (mpg) price: price of car (US$)

a) Using Excel's Correlation procedure, determine which of these variables has the highest correlation with price. Identify the variable has the highest correlation with quality and explain whether the correlation would be a strong correlation. Be sure to use quantitative values in your explanation. b) Prepare a scatter plot showing the relationship between the price and variable you identified above. Properly format (trend line with equation, modify equation with variable names rather than x & y, axes labels, etc.) this scatterplot and paste into a Word document with a Figure number. Write an interpretation which discusses the model fit and write the regression equation. c) Perform the backward stepwise procedure, which is the first variable that is removed in the procedure? Provide an explanation of why this would be the first variable removed. Be sure to use quantitative values in your explanation. d) After completing the backward stepwise procedure, which independent variables remain in the model? Write the multiple regression equation for this model. Is this a good fitting model? Compare the fit in this model to the model you plotted in (b). Be sure to use quantitative values in your explanation.

2 e) Create an ANOVA table for your best model based on your answer to part (d) (using Excel's Regression procedure), format it to show 3 decimal places and remove duplicate columns. Copy and paste here. f) Test the multiple regression model for significance. Be sure that in your answer you state the null and alternative hypothesis; the p-value for the test, and an interpretation of the result of the hypothesis test (what does it mean if you reject the null hypothesis or are unable to reject the null hypothesis.) g) Based on your best model (part d), how should the company price a car with a 2,500 lb. curb weight, 125 cubic inch engine, 104 horsepower, 25 mpg in the city, and 31 mpg on the highway? Show how you calculated this value not just the final value, by writing the multiple regression equation. h) Considering all four independent variables, does there appear to be any multicollinearity among the independent variables? Explain how you determined whether multicollinearity is present. Be sure to use quantitative values in your explanation.

curbweight	enginesize	horsepower	citympg	highwaympg	price
2954	136	110	19	25	18920
1951	97	69	31	37	7299
2290	108	82	28	32	7463
3505	209	182	15	20	36880
2976	171	161	20	24	16558
1918	97	69	31	37	6649
2714	146	116	24	30	11549
3750	183	123	22	25	28248
2385	122	84	26	32	10595
2403	110	116	23	30	9279
2370	110	116	23	30	9959
2670	140	120	19	27	18280
2480	110	73	30	33	10698
3016	171	161	19	24	15998
3034	141	114	23	28	13415
3485	152	95	25	25	17075
2037	97	69	31	37	7999
2756	194	207	17	25	34028
2190	108	82	28	33	7775
2765	164	121	21	28	21105
3086	131	140	17	20	23875
1876	90	68	37	41	5572
1909	90	70	38	43	6575
2380	70	101	17	23	10945
2050	97	69	31	36	5118
2823	152	154	19	26	16500
1918	92	68	37	41	5389
2240	108	73	26	31	7603
2847	121	160	19	26	18620
3157	130	162	17	22	18950
2935	141	114	24	28	15985
3296	181	152	17	22	14399
1876	90	68	31	38	6377
2975	146	116	24	30	17669
2122	98	70	28	34	8358
2507	136	110	19	25	15250
4066	258	176	15	19	32250
1890	91	68	30	31	5195
2535	122	88	24	30	8921
2500	80	135	16	23	15645
2734	119	90	24	29	11048
3515	183	123	22	25	25552
3110	92	62	27	32	8778
2015	92	62	31	38	6488
2304	110	86	27	33	8845
2212	109	85	27	34	8195
2921	156	145	19	24	14869
2365	122	88	25	32	6989
2264	97	52	37	46	7995
2128	98	102	24	30	7957
2109	98	70	30	37	7198
2455	108	94	25	31	10198
2540	146	116	24	30	8449
2385	122	84	26	32	8845
3217	145	106	26	27	22470
1909	90	70	38	43	8916.5
2420	108	82	23	29	8013
2372	110	86	27	33	10295
1989	90	68	31	38	6692
3095	181	152	17	22	13499
2094	98	70	38	47	7738
2700	134	72	31	39	18344
2458	122	92	27	32	11248
3060	181	152	19	25	13499
2008	97	69	31	37	8249
1837	79	60	38	42	5399
2778	151	143	19	27	22018
2024	92	76	30	34	7295
2811	156	145	19	24	12964
2010	92	76	30	34	7295
2510	108	111	24	29	11259
2756	194	207	17	25	32528
2535	122	88	24	30	8921
3139	181	200	17	23	19699
3366	203	288	17	28	31400.5
2758	121	110	21	28	15510
3075	120	95	19	24	15580
3197	152	95	28	33	13200
3230	209	182	16	22	30760
2328	122	88	25	32	8499
2254	109	90	24	29	11595
3900	308	184	14	16	40960
2536	146	116	24	30	9639
3071	181	160	19	25	17199
3285	120	95	19	24	16695
2460	132	90	23	31	9895
2120	108	73	26	31	7053
2926	156	145	19	24	14489
2414	122	92	27	32	9988
2414	122	92	27	32	10898
1945	91	68	31	38	6695
3380	209	182	16	22	41315
2952	141	114	23	28	16845
3495	183	123	22	25	28176
2844	136	110	19	25	17710
2405	122	88	25	32	8189
2808	121	160	19	26	18150
2290	92	62	27	32	7898
1905	91	68	31	38	6795
3053	131	160	16	22	17859.17
2824	136	115	18	22	17450
1989	90	68	31	38	7609
2385	108	82	24	25	9233
3131	171	156	20	24	15690
2326	122	92	29	34	8948
3151	161	156	19	24	15750
2324	120	97	27	34	8949
2380	70	101	17	23	11845
2275	109	85	27	34	8495
1971	97	69	31	37	7499
2017	103	55	45	50	7099
2410	122	84	26	32	10245
2707	121	110	21	28	15040
1967	90	68	31	38	6229
1938	97	69	31	37	6849
2395	108	101	23	29	16925
1956	92	76	30	34	7129
2319	97	68	37	42	9495
3950	326	262	13	17	36000
2081	98	70	30	37	6938
1985	92	62	35	39	5348
2293	110	100	25	31	10345
2385	70	101	17	23	13645
2800	194	207	17	25	37028
3252	152	95	28	33	17950
1967	90	68	31	38	6229
2579	97	68	33	38	13845
2551	146	116	24	30	9989
2302	120	97	27	34	9549
2403	110	116	23	30	9279
1874	90	70	38	43	8916.5
1940	92	76	30	34	6529
1944	92	68	31	38	6189
2679	146	116	24	30	11199
2024	97	69	31	37	7349
1900	91	68	31	38	6095
2140	98	70	28	34	9258
3139	181	160	19	25	18399
3062	141	114	19	25	22625
2548	130	111	21	27	16500
2340	108	94	26	32	9960
1918	90	68	37	41	5572
2004	92	68	31	38	6669
2040	92	62	31	38	6338
3049	141	160	19	25	19045
2912	141	114	23	28	12940
3230	120	97	19	24	12440
2818	156	145	19	24	12764
2465	110	101	24	28	12945
2710	164	121	21	28	20970
1488	61	48	47	53	5151
2410	122	84	26	32	8495
3740	234	155	16	18	34184
2191	98	68	31	38	7609
1989	90	68	31	38	6692
2300	98	112	26	29	9538
2910	140	175	19	24	16503
4066	258	176	15	19	35550
2548	130	111	21	27	13495
2661	136	110	19	24	13295
1889	97	69	31	37	5499
2563	109	88	25	31	12290
1713	92	58	49	54	6479
3045	130	162	17	22	18420
2280	92	62	31	37	6918
2443	122	64	36	42	10795
2191	98	102	24	30	8558
3252	152	95	28	33	16900
2128	98	102	24	30	7957
2204	98	70	29	34	8238
1874	90	70	38	43	6295
2169	98	70	29	34	8058
2695	121	110	21	28	12170
2145	108	82	32	37	7126
2145	98	102	24	30	7689
2337	111	78	24	29	6785
2275	110	56	34	36	7898
2028	97	69	31	37	7799
3055	164	121	20	25	24565
2833	156	145	19	24	12629
3020	120	97	19	24	11900
2209	109	85	27	34	7975