ITC556 Database Systems Assignment Help
Delivery in day(s): 4
This is a solution of descriptive analysis visualization assignment help in which we discuss objective of carrying out exploratory, descriptive and regression analysis for gaining comprehensive understanding of house price in city
This report has been undertaken with the main objective of carrying out exploratory, descriptive and regression analysis for gaining comprehensive understanding of house price in the Shiraz region. It is also going to lay down understanding of most important factors that has been laying down impact over the housing prices in the area of Shiraz. The different analysis that has been carried out in this regard are as follows descriptive statistics, factors influencing house prices, development of multiple regression model and time series analysis.
Table 1: Descriptive statistics
Price($'000)
 


Mean  886.575 
Standard Error  29.66343575 
Median  852 
Mode  811 
Standard Deviation  324.9466579 
Sample Variance  105590.3305 
Kurtosis  0.14778497 
Skewness  0.426005063 
Range  1569 
Minimum  192 
Maximum  1761 
Sum  106389 
Count  120 
From the above scatter diagram it could be interpreted that there is positive correlation between house price and area of the house in square meters as there is rightward movement and a straight line if drawn is going to originate out to high x and yvalues.
From the above scatter diagram it could be interpreted that there is positive correlation between house price and Street appeal as evaluated by the real estate agency as there is rightward movement and a straight line if drawn is going to originate out to high x and yvalues.
The above figure indicates that there is perfect positive linear relationship between the variables that are between house price and number of storey’s or levels in the house. It is because both the variables are moving towards the rightward direction and are seen to be away from each other.
Table 2: Correlation ship
 Price($'000) 
Price($'000)  1 
Rooms  0.505469281 
Street  0.722570048 
Storey’s  0.565098455 
Weekly Rent $  0.665590917 
Bedrooms  0.539744531 
Bathrooms  0.331222685 
This model represents the independent variable that has been identified in order to present the dependent variable.
Y=α + βX1+BX2+BX3+BX4+BX5+ BX6…………..………………………. (Model 1)
Y= 386.984+ 0.174X1+0.212X2+89.86X3+188.42X4+106.80X5+13.37X6
Dependent variable= Selling price of house in $'000
Independent variable= Rooms, Weekly rent, Street, Storey’s, Bedrooms and Bathrooms
Table 3: Multiple regression model 1
Regression Statistics

 
Multiple R  0.913271 
 
R Square  0.834063  
Adjusted R Square  0.825253  
Standard Error  135.8368  
Observations  120  
 
ANOVA  
 df  SS  MS  F  Significance F 

Regression  6  10480214  1746702  94.66378  9.95E42 

Residual  113  2085036  18451.64 



Total  119  12565249 




 
 Coefficients  Standard Error  t Stat  Pvalue  Lower 95%  Upper 95% 
Intercept  386.984  102.3464  3.78111  0.000251  589.75  184.217 
Rooms  0.17444  25.05619  0.006962  0.994457  49.4664  49.81528 
Weekly Rent $  0.212072  0.07814  2.714014  0.007689  0.057263  0.36688 
Street  89.8622  7.429578  12.0952  4.13E22  75.14287  104.5815 
Storey’s  188.4243  26.93576  6.995319  1.98E10  135.0597  241.7889 
Bedrooms  106.8086  31.44359  3.396831  0.000942  44.51315  169.104 
Bathrooms  13.3787  40.95154  0.3267  0.744502  94.5111  67.75365 
RSquare
Rsquare value for the following model is 83.4%, indicates that 83.4% total variance in selling price of house in $'000 can be explained by independent variable Rooms, Weekly rent, Street, Storey’s, Bedrooms and Bathrooms
FValue
The calculated Fvalue is greater than critical value of F thus it can be said that model is accepted. Even it can be said that value ration of explained to unexplained variance is seen to be very high. Hence, it can be said that regression variables are significant for explaining the dependent variable.
PValue
Rooms, Street, Storey’s and Bathrooms have no influence on selling price of house in $'000 as it is not statistically significant because Rooms, Street, Storey’s and Bathrooms Pvalue is greater than 0.01% at 1% level of significance. On the other hand, weekly rent and bedrooms have influence on selling price of house in $'000 as it is statistically significant because weekly rent and bedrooms Pvalue is less than 0.01% at 1% level of significance.
Coefficients
Coefficient value indicates the rooms (0.17), weekly rent (0.21), street (89.8), storey’s (188.42) and bedrooms (106.8) have got dependability on selling price of house in $'000. In a case if Rooms, Weekly rent, Street, Storey’s and Bedrooms changes by one unit then selling price of house in $'000 will increase by 17%, 21%, 8900%, 18800% and 10600%. On the other hand, Bathrooms $ 13.37 have got no dependability on selling price of house in $'000. In a case if Bathrooms changes by one unit then selling price of house in $'000 will decrease by 1300%.
Linear regression model
This model represents the independent variable that has been identified in order to present the dependent variable.
Y=α + βX1+BX2+BX3…………..………………………. (Model 2)
Y= 409.72+ 97.7X1+200.19X2+127.61X3
Dependent variable= Selling price of house in $'000
Independent variable= Street, Storey’s and Bedrooms
Table 4: Multiple regression model 2
Regression Statistics

 
Multiple R  0.906903 
 
R Square  0.822474  
Adjusted R Square  0.817882  
Standard Error  138.6718  
Observations  120  
 
ANOVA  
 df  SS  MS  F  Significance F 

Regression  3  10334586  3444862  179.1413  2.25E43 

Residual  116  2230663  19229.86 



Total  119  12565249 




 
 Coefficients  Standard Error  t Stat  Pvalue  Lower 95%  Upper 95% 
Intercept  409.729  58.34096  7.023  1.58E10  525.28  294.177 
Street  97.711  7.009674  13.93945  1.52E26  83.82745  111.5945 
Storey’s  200.197  27.0691  7.395776  2.39E11  146.5833  253.8108 
Bedrooms  127.6109  11.84395  10.77435  3.52E19  104.1525  151.0693 
Rsquare value for the following model is 82.24%, indicates that 82.24% total variance in selling price of house in $'000 can be explained by independent variable Street, Storey’s, Bedrooms
FValue
The calculated Fvalue is greater than critical value of F thus it can be said that model is accepted. Even it can be said that value ration of explained to unexplained variance is seen to be very high. Hence, it can be said that regression variables are significant for explaining the dependent variable.
PValue
Street, Storey’s and Bedrooms have no influence on selling price of house in $'000 as it is not statistically significant because Street, Storey’s and Bedrooms Pvalue is greater than 0.01% at 1% level of significance.
Coefficients
Coefficient value indicates the street (97.71), storey’s (200.19) and bedrooms (127.61) have got dependability on selling price of house in $'000. In a case if Street, Storey’s and Bedrooms changes by one unit then selling price of house in $'000 will increase by 9700%, 20000%, and 12700%.
Linear regression model
This model represents the independent variable that has been identified in order to present the dependent variable.
Y=α + βX1+BX2+BX3…………..………………………. (Model 3)
Y= 60.14+ 97.7X1+317.87X2+137.10X3
Dependent variable= Selling price of house in $'000
Independent variable= Storey’s and Bedrooms
Table 5: Multiple regression model 3
Regression Statistics

 
Multiple R  0.724641 
 
R Square  0.525104  
Adjusted R Square  0.516986  
Standard Error  225.8353  
Observations  120  
 
ANOVA  
 df  SS  MS  F  Significance F 

Regression  2  6598066  3299033  64.68494  1.21E19 

Residual  117  5967183  51001.56 



Total  119  12565249 




 
 Coefficients  Standard Error  t Stat  Pvalue  Lower 95%  Upper 95% 
Intercept  60.1449  85.78547  0.70111  0.484627  230.039  109.7487 
Storeys  317.8746  41.88497  7.589228  8.52E12  234.9236  400.8255 
Bedrooms  137.1081  19.25664  7.120045  9.4E11  98.97137  175.2449 
Rsquare value for the following model is 52.5%, indicates that 52.5% total variance in selling price of house in $'000 can be explained by independent variable Storey’s and Bedrooms.
FValue
The calculated Fvalue is greater than critical value of F thus it can be said that model is accepted. Even it can be said that value ration of explained to unexplained variance is seen to be very high. Hence, it can be said that regression variables are significant for explaining the dependent variable.
PValue
Storey’s and Bedrooms have no influence on selling price of house in $'000 as it is not statistically significant because Storey’s and Bedrooms Pvalue is greater than 0.01% at 1% level of significance.
Coefficients
Coefficient value indicates the Storey’s (317.87) and bedrooms (137.10) have got dependability on selling price of house in $'000. In a case if Storey’s and Bedrooms changes by one unit then selling price of house in $'000 will increase by 31700% and 13700%.
Table 6: Mean absolute percentage error calculation
Time Period  Quarter  Median House Price ($'000) (A)  Forecasted (F)  Deviation(AF)  Absolute deviation(/AF/)  Absolute percentage of error=100(/AF/)/ A 
1  2012Q1  554  950  396  396  71 
2  2012Q2  589  1320  731  731  124 
3  2012Q3  661  1500  839  839  127 
4  2012Q4  522  1090  568  568  109 
5  2013Q1  610  950  340  340  56 
6  2013Q2  700  1320  620  620  89 
7  2013Q3  850  1500  650  650  76 
8  2013Q4  592  1090  498  498  84 
9  2014Q1  770  950  180  180  23 
10  2014Q2  880  1320  440  440  50 
11  2014Q3  1090  1500  410  410  38 
12  2014Q4  725  1090  365  365  50 
13  2015Q1  932  950  18  18  2 
14  2015Q2  1150  1320  170  170  15 
15  2015Q3  1330  1500  170  170  13 
16  2015Q4  940  1090  150  150  16 
Total 




 943 
A= actual valueWhere,
F= Forecasted value
n= Time period
From the above calculation of Mean absolute percentage error it could be interpreted that the measure of prediction accuracy of a forecasting value for Quarterly median house prices was seen to be around 58.9%. This shows price of the house vale at Shiraz to be accurate by 58.9%.
The present research proposal have been taken on the basis of appropriate variable for measuring the suitability of selling price of house in $'000 on the basis of various factors selected for carrying out the study. Further, if this analysis need to be repeated in the future then the various other factors that can be used for studying the suitability of Selling price of house in $'000 to be charged could be through inflation, personal disposable income, connectivity of city and location of Shiraz city at highway.
From the study it has been found that majority of the variables that has been laying down influence over the house price are Street, Storey’s and Bedrooms. This shows that Shiraz local government area must these independent variables to be important while carrying out their development process.