Because of house sale price is depends on several factors.
We would like to see what factors to drive the home prices in city of Ames, Iowa
To identify and analyze the factors that drive house sale prices in order to gain insights into the real estate market and inform investment decisions in mortgage-backed securities
Does the age of the homes affect the home sale price?
Does the living space size affect the home sale price?
Does the overall home quality affect on the home sale price?
Does having a garage affect the home sale price?
Does having a fireplace affect the home sale price?
The data set was collected from 1460 homes that sold since 1872-2010 in City of Ames, Iowa
From the distribute chart of house sale prices, This variable appears to be normally distributed.
Average : $180,921
Min : $34,900
Max : $755,000
H1 Influence of homes built after the year 2000 on the sale price of the house.
H0: Homes built after the year 2000 has no effect on the sale price
Ha: Homes built after the year 2000 has an effect on the sale price
H2 Influence of living space more than 2000 sq. feet on the sale price of the house
H0: Living space more than 2000 sq. feet has no effect on the sale price
Ha: Living space more than 2000 sq. feet has an effect on the sale price
H3 Influence of overall material and finish quality on the sale price of the home
H0: Overall material and finish quality has no effect on the sale price
Ha: Overall material and finish quality has an effect on the sale price
H4 Influence of having a garage on the sale price of the house
H0: Having a garage has no effect on the sale price
Ha: Having a garage has an effect on the sale price
H5 Influence of having fireplaces on the sale price of the house
H0: Having fireplaces has no effect on the sale price
Ha: Having fireplaces has an effect on the sale price
Testing H1, H2, H3, H4 and H5 by independent-samples t-test
Step 1 Data wrangling: Split data into treatment and control groups by Excel’s Pivot table filtering and Analysis Toolpak
Step 2 Test: Analyze the data by using t-Test: Two-Sample Assuming Unequal Variances and get the p-value
Step 3 Visualization : Create visualization with error bar
Step 4 Get insights: Identify each hypothesis if reject or fail to reject the null hypothesis
Problem statement 1 : Does the age of the homes affect the home sale price?
Hypothesis 1 : Influence of homes built after the year 2000 on the sale price of the house
T-Test :
p = 1.11880726028853E-57
total sample size = 1460
mean difference = -83783.79763
standard error of difference = 4654.320832
margin of error = 9141.746609
Confidence Interval diff lower limit = -92925.54424
Confidence Interval diff upper limit = -74642.05102
Result : The p-value is less than the significance level of 0.05 So we reject the null hypothesis
So, there is evidence of a statistically significant difference in the average sales prices for houses that built before the year 2000 and after.
House built after the year 2000 sell at a higher price.
With 95% confidence, old house (house that built before the year 2000) sell between $74,642 and $92,925 lass than new house(houses that built after the year 2000)
Problem statement 2 : Does the living space size affect the home sale price?
Hypothesis 2 : Influence of living space more than 2000 sq. feet on the sale price of the house
H0: Homes built after the year 2000 has no effect on the sale price
Ha: Homes built after the year 2000 has an effect on the sale price
T-Test :
p = 1.91345339591425E-38
total sample size = 1460
mean difference = -118309.9833
standard error of difference = 7545.81347
margin of error = 14865.757
Confidence Interval diff lower limit = -133175.7403
Confidence Interval diff upper limit = -103444.2263
Result : The p-value is less than the significance level of 0.05 So we reject the null hypothesis
So, there is evidence of a statistically significant difference in the average sales prices for houses that have living area above the ground less than 2000 sq. feet and more than 2000 sq. feet
Home that have more than 2000 sq. feet living space above the ground sell at a higher price.
With 95% confidence, home with less 2000 sq. feet living space sell between $103,444 and $133,175 lass than home with more than 2000 sq. feet living space
Problem statement 3 : Does the overall home quality affect on the home sale price?
Hypothesis 3 : Influence of overall material and finish quality on the sale price of the home
T-Test :
p = 5.95356464450778E-69
total sample size = 1460
mean difference = -141212.1881
standard error of difference = 5114.184841
margin of error = 10086.86876
Confidence Interval diff lower limit = -151299.0569
Confidence Interval diff upper limit = -131125.3193
Result : The p-value is less than the significance level of 0.05 So we reject the null hypothesis
So, there is evidence of a statistically significant difference in the average sales prices home quality level 5 and home quality level 8.
Higher home quality level sell at a higher price.
With 95% confidence, home quality level 5 sell between $131,125 and $151,299 lass than home quality level 8
Problem statement 4 : Does having a garage affect the home sale price?
Hypothesis 4 : Influence of having a garage on the sale price of the house
T-Test :
p = 5.95356464450778E-69
total sample size = 1460
mean difference = 82162.22729
standard error of difference = 4221.685229
margin of error = 8344.972255
Confidence Interval diff lower limit = 73817.25503
Confidence Interval diff upper limit = 90507.19954
Result : The p-value is less than the significance level of 0.05 So we reject the null hypothesis
So, there is evidence of a statistically significant difference in the average sales prices for houses with and without a garage.
Home with a garage sell at a higher price.
With 95% confidence, house without a garage sell between $73,817 and $90,507 lass than houses with a garage
Problem statement 5 : Does having a fireplace affect the home sale price?
Hypothesis 5 : H5 Influence of having fireplaces on the sale price of the house
H0: Having fireplaces has no effect on the sale price
Ha: Having fireplaces has an effect on the sale price
T-Test :
p = 5.95356464450778E-69
total sample size = 1460
mean difference = 75066.2096
standard error of difference = 3556.734002
margin of error = 6978.277125
Confidence Interval diff lower limit = 68087.93247
Confidence Interval diff upper limit = 82044.48672
Result : The p-value is less than the significance level of 0.05 So we reject the null hypothesis
So, there is evidence of a statistically significant difference in the average sales prices for houses with and without a fireplace.
Home with fireplace sell at a higher price.
With 95% confidence, house without a fireplace sell between $68,087 and $82,044 lass than houses with a fireplace.
Conclusions
The house sale price will significantly drive by home’s age, living space, quality and feature like a garage and fireplaces.
House built after 2000 sell 52.8% higher
Living space larger than 2000 sq. feet sell 72.4% higher
Overall home quality level 8 sell 105.8% higher
Having garage sell 44.3% higher
Having fireplace sell 34.7% higher
Recommendation
Based on our analysis, we recommend that the investment bank consider investing in mortgage-backed securities that are backed by homes meeting the following criteria:
Built after 2000
Living space larger than 2,000 sq. ft.
High overall quality ratings
Have garages and fireplaces
By prioritizing these securities, the bank can increase the likelihood of generating a higher return on their investment.