Read the Multiple Regression Overview (Mult Regression Overview tab below)

Refer to the data below which report information on homes sold in Somewhere, USA this year.

1. Perform correlation analysis of the independent variables and show the output.

2. Use the selling price of the home (Y) as the dependent variable and determine the regression equation (model) based on the following independent variables:

number of bedrooms (BE), size of the house (SF), whether there is a pool (P), distance from the center of the city (D), whether there is an attached garage G), and the number of bathrooms (BA).

3. Interpret each of the coefficients of partial determination.

4. Predict the selling price of a 2,500 square feet house that has 5 bedrooms, 3 bathrooms, a 3-car attached garage, no pool, and is at 18 miles from the city center.

5. Determine whether the p value of each independent variable is above 5% to decide removal from the model, then re-run the regression analysis using only independent variables with significant (p<=0.05) regression coefficients. Show the new model.

Instructions:

1. Activate the needed Excel tools:

File/Options/Add-ins/Manage: Excel Add-ins/Go/check Analysis ToolPak and Solver Add-in/OK

2. Provide all your outputs and answers on this same worksheet.

3. For #1 (correlation analysis):

Data/Data Analysis/Correlation/OK

Click inside the Input Range box and Highlight the cells containing the Independent Variables data, including their labels.

Check the Labels in First Row box

Select Output and indicate the cell where it will go.

OK

Note: If two independent variables are highly correlated (r >= 0.7, you may need to remove one of them from your model)

4. For #2, the expected results in your regression output:

Intercept

Number of bedrooms

Size in sq. ft.

Pool (1 = yes, 0 = no)

Distance to CBD in miles

Attached garage (1 = yes, 0 = no)

Number of bathrooms

and the resulting regression model is Y = 57,034.9 + 7,117.97 BE + 38 SF - 18,321.45 P .....etc.

5. For #3, one example for BE is "When the number of bedrooms increases by one unit (one bedroom), the selling price of the house will increase 7177.97 dollars."

6. For #4, plug the provided data in the model to solve for the price of that house.

7. For #5, based on analysis of p values, determine which independent variables to remove before re-running regression analysis with the remaining variables.

Note: p value should be < 0.05 to be an acceptable variable.

Selling Price ($)	Number of bedrooms	Size in sq. ft.	Pool (1 = yes, 0 = no)	Distance to CBD in miles	Attached garage (1 = yes, 0 = no)	Number of bathrooms
Y	BE	SF	P	D	G	BA
263,100	4	2,300	1	17	1	2.0
182,400	4	2,100	0	19	0	2.0
242,100	3	2,300	0	12	0	2.0
213,600	2	2,200	0	16	0	2.5
139,900	2	2,100	0	28	0	1.5
245,400	2	2,100	1	12	1	2.0
327,200	6	2,500	0	15	1	2.0
271,800	2	2,100	0	9	1	2.5
221,100	3	2,300	1	18	0	1.5
266,600	4	2,400	0	13	1	2.0
292,400	4	2,100	0	14	1	2.0
209,000	2	1,700	0	8	1	1.5
270,800	6	2,500	0	7	1	2.0
246,100	4	2,100	0	18	1	2.0
194,400	2	2,300	0	11	0	2.0
281,300	3	2,100	0	16	1	2.0
172,700	4	2,200	1	16	0	2.0
207,500	5	2,300	1	21	0	2.5
198,900	3	2,200	1	10	1	2.0
209,300	6	1,900	1	15	1	2.0
252,300	4	2,600	0	8	1	2.0
192,900	4	1,900	1	14	1	2.5
209,300	5	2,100	0	20	0	1.5
345,300	8	2,600	0	9	1	2.0
326,300	6	2,100	0	11	1	3.0
173,100	2	2,200	1	21	1	1.5
187,000	2	1,900	0	26	0	2.0
257,200	2	2,100	0	9	1	2.0
233,000	3	2,200	0	14	1	1.5
180,400	2	2,000	0	11	0	2.0
234,000	2	1,700	0	19	1	2.0
207,100	2	2,000	0	11	1	2.0
247,700	5	2,400	0	16	1	2.0
166,200	3	2,000	1	16	1	2.0
177,100	2	1,900	0	10	1	2.0
182,700	4	2,000	1	14	0	2.5
216,000	4	2,300	0	19	0	2.0
312,100	6	2,600	0	7	1	2.5
199,800	3	2,100	0	19	1	2.0
273,200	5	2,200	0	16	1	3.0
206,000	3	2,100	1	9	0	1.5
232,200	3	1,900	1	16	1	1.5
198,300	4	2,100	1	19	1	1.5
205,100	3	2,000	1	20	0	2.0
175,600	4	2,300	1	24	1	2.0
307,800	3	2,400	1	21	1	3.0
269,200	5	2,200	0	8	1	3.0
224,800	3	2,200	0	17	1	2.5
171,600	3	2,000	1	16	0	2.0
216,800	3	2,200	0	15	1	2.0
192,600	6	2,200	1	14	0	2.0
236,400	5	2,200	0	20	1	2.0
172,400	3	2,200	0	23	0	2.0
251,400	3	1,900	0	12	1	2.0
246,000	6	2,300	0	7	1	3.0
147,400	6	1,700	1	12	0	2.0
176,000	4	2,200	0	15	1	2.0
228,400	3	2,300	0	17	1	1.5
166,500	3	1,600	1	19	0	2.5
189,400	4	2,200	0	24	1	2.0
312,100	7	2,400	0	13	1	3.0
289,800	6	2,000	0	21	1	3.0
269,900	5	2,200	1	11	1	2.5
154,300	2	2,000	0	13	0	2.0
222,100	2	2,100	0	9	1	2.0
209,700	5	2,200	1	13	1	2.0
190,900	3	2,200	1	18	1	2.0
254,300	4	2,500	1	15	1	2.0
207,500	3	2,100	1	10	0	2.0
209,700	4	2,200	1	19	1	2.0
294,000	2	2,100	0	13	1	2.5
176,300	2	2,000	1	17	0	2.0
294,300	7	2,400	0	8	1	2.0
224,000	3	1,900	1	6	1	2.0
125,000	2	1,900	0	18	0	1.5
236,800	4	2,600	1	17	1	2.0
164,100	4	2,300	0	19	0	2.0
217,800	3	2,500	0	12	0	2.0
192,200	2	2,400	0	16	0	2.5
125,900	2	2,400	0	28	0	1.5
220,900	2	2,300	1	12	1	2.0
294,500	6	2,700	0	15	1	2.0
244,600	2	2,300	0	9	1	2.5
199,000	3	2,500	1	18	0	1.5
240,000	4	2,600	0	13	1	2.0
263,200	4	2,300	0	14	1	2.0
188,100	2	1,900	0	8	1	1.5
243,700	6	2,700	0	7	1	2.0
221,500	4	2,300	0	18	1	2.0
175,000	2	2,500	0	11	0	2.0
253,200	3	2,300	0	16	1	2.0
155,400	4	2,400	1	16	0	2.0
186,700	5	2,500	1	21	0	2.5
179,000	3	2,400	1	10	1	2.0
188,300	6	2,100	1	15	1	2.0
227,100	4	2,900	0	8	1	2.0
173,600	4	2,100	1	14	1	2.5
188,300	5	2,300	0	20	0	1.5
310,800	8	2,900	0	9	1	2.0
293,700	6	2,400	0	11	1	3.0
179,000	3	2,400	0	8	1	2.0
188,300	6	2,100	1	14	1	2.5
227,100	4	2,900	0	20	0	1.5
173,600	4	2,100	0	9	1	2.0
188,300	5	2,300	0	11	1	3.0