In: Statistics and Probability
There are numerous variables that are believed to be predictors of housing prices, including living area (square feet), number of bedrooms, and number of bathrooms. The data in the Case Study No. 2.xlsx file pertains to a random sample of houses located in a particular geographic area.
Prepare a single Microsoft Excel file using a separate worksheet for each question and upload your Excel file for grading via the Blackboard submission link.
Selling Price | Living Area (Sq Feet) | No. Bathrooms | No Bedrooms |
$145,000 | 1,152 | 1 | 2 |
$103,000 | 1,290 | 1.5 | 3 |
$210,000 | 2,396 | 1.5 | 4 |
$559,000 | 3,090 | 4 | 4 |
$218,000 | 1,428 | 1 | 3 |
$262,138 | 1,631 | 2.5 | 3 |
$125,000 | 1,368 | 1 | 3 |
$130,000 | 1,134 | 1 | 3 |
$157,500 | 1,697 | 1.5 | 3 |
$193,000 | 1,666 | 2.5 | 3 |
$275,000 | 1,738 | 2.5 | 4 |
$240,000 | 1,457 | 1.5 | 2 |
$200,136 | 1,632 | 2.5 | 3 |
$395,000 | 2,186 | 2.5 | 3 |
$366,703 | 2,117 | 2.5 | 3 |
$103,150 | 936 | 1 | 3 |
$310,000 | 3,347 | 2.5 | 6 |
$142,900 | 1,824 | 2.5 | 4 |
$359,770 | 2,592 | 3 | 3 |
a.Sale price based upon square feet of living area.
The regression equation is given by
Y = 142.2543959X - 23064.61598
Y = Sales price in dollars
X = Square feet of living area.
b.Sale price based upon number of bedrooms.
The regression equation is given by
Y = 40113.90385X + 105696.5769
Y = Sales price in dollars
X = No of Bedrooms
c.Sale price based upon the number of bathrooms
The regression equation is given by
Y = 113369.8846X + 9854.809717
Y = Sales price in dollars
X = No of Bathrooms
a.Sale price based upon square feet of living area and number of bedrooms.
Regression equation is given by
Y = 213.9101105X1 - 74833.98496X2 + 90336.18553
Y = Sales price in dollars
X1 = Square feet of living area.
X2 = No of Bedrooms
b.Sale price based upon square feet of living area and number of bathrooms.
Regression equation is given by
Y = 73.07796607X1 + 71746.36155X2 - 40288.5095
Y = Sales price in dollars
X1 = Square feet of living area.
X2 = No of Bathrooms
c.Sale price based upon the number of bedrooms and number of bathrooms.
Regression equation is given by
Y = -6569.109081X1 + 116149.1231X2 + 25732.37296
Y = Sales price in dollars
X1 = No of Bedrooms
X2 = No of Bathrooms
d.Sale price based upon square feet of living area, number of bedrooms, and number of bathrooms
The regression equation is given by
Y = 148.2555647X1 - 61665.83682X2 + 55016.32877X3 + 57174.06061
Y = Sales price in dollars
X1 = Square feet of living area.
X2 = No of Bedrooms
X3 = No of Bathrooms
Question 3:
At 90% Significance
a. Sale price based upon square feet of living area, number of bedrooms, and number of bathrooms
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.904252688 | |||||||
R Square | 0.817672923 | |||||||
Adjusted R Square | 0.781207508 | |||||||
Standard Error | 55765.64154 | |||||||
Observations | 19 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 3 | 2.09196E+11 | 69731957514 | 22.42324 | 8.49E-06 | |||
Residual | 15 | 46647101649 | 3109806777 | |||||
Total | 18 | 2.55843E+11 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 90.0% | Upper 90.0% | |
Intercept | 57174.06061 | 52858.40362 | 1.081645617 | 0.296493 | -55491 | 169839.1 | -35489.4 | 149837.5 |
X Variable 1 | 148.2555647 | 40.52520877 | 3.658354124 | 0.00233 | 61.87813 | 234.633 | 77.21283 | 219.2983 |
X Variable 2 | -61665.83682 | 22374.88195 | -2.756029594 | 0.014707 | -109357 | -13974.9 | -100890 | -22441.5 |
X Variable 3 | 55016.32877 | 23821.56476 | 2.309517839 | 0.035564 | 4241.865 | 105790.8 | 13255.93 | 96776.73 |
At the significance of 95%
b.Sale price based upon square feet of living area, number of bedrooms, and number of bathrooms
Regression Statistics | ||||||||
Multiple R | 0.904252688 | |||||||
R Square | 0.817672923 | |||||||
Adjusted R Square | 0.781207508 | |||||||
Standard Error | 55765.64154 | |||||||
Observations | 19 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 3 | 2.09196E+11 | 69731957514 | 22.42324 | 8.49E-06 | |||
Residual | 15 | 46647101649 | 3109806777 | |||||
Total | 18 | 2.55843E+11 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 57174.06061 | 52858.40362 | 1.081645617 | 0.296493 | -55491 | 169839.1 | -55491 | 169839.1 |
X Variable 1 | 148.2555647 | 40.52520877 | 3.658354124 | 0.00233 | 61.87813 | 234.633 | 61.87813 | 234.633 |
X Variable 2 | -61665.83682 | 22374.88195 | -2.756029594 | 0.014707 | -109357 | -13974.9 | -109357 | -13974.9 |
X Variable 3 | 55016.32877 | 23821.56476 | 2.309517839 | 0.035564 | 4241.865 | 105790.8 | 4241.865 | 105790.8 |
5.Preferred model is based on the regression equation is defined based on R2 value. Higher the value of R2 the better the model suitability explaining the variation in the dependent variable (i.e. Sales price). So the most preferred model is given by
Y = 148.2555647X1 - 61665.83682X2 + 55016.32877X3 + 57174.06061
6. Preferred regression equation is given by
Y = 148.2555647X1 - 61665.83682X2 + 55016.32877X3 + 57174.06061
Y = Sales price in dollars
X1 = Square feet of living area.
X2 = No of Bedrooms
X3 = No of Bathrooms
This means
Variation in Sales price is effected by 148.2 times in Square feet of living area.
Variation in Sales price is effected by -61665.8 times No of Bedrooms.
Variation in Sales price is effected by 55016.3 times No of Bathrooms
7. Regression equation is given by
Y = 148.2555647X1 - 61665.83682X2 + 55016.32877X3 + 57174.06061
Here X1 = 3000sqft
X2 = 3 Bedrooms
X3 = 2.5 Bathrooms
So Y = $454484.1