Apply: Regression Modeling
This assignment provides an opportunity to develop, evaluate, and apply bivariate and multivariate linear regression models.
Resources:Microsoft Excel, DAT565_v3_Wk5_Data_File
Instructions:
The Excel file for this assignment contains a database with information about the tax assessment value assigned to medical office buildings in a city. The following is a list of the variables in the database:
FloorArea: square feet of floor space
Offices: number of offices in the building
Entrances: number of customer entrances
Age: age of the building (years)
AssessedValue: tax assessment value (thousands of dollars)
Use the data to construct a model that predicts the tax assessment value assigned to medical office buildings with specific characteristics.
Construct a scatter plot in Excel with FloorArea as the independent variable and AssessmentValue as the dependent variable. Insert the bivariate linear regression equation and r^2 in your graph. Do you observe a linear relationship between the 2 variables?
Use Excels Analysis ToolPak to conduct a regression analysis of FloorArea and AssessmentValue. Is FloorArea a significant predictor of AssessmentValue?
Construct a scatter plot in Excel with Age as the independent variable and AssessmentValue as the dependent variable. Insert the bivariate linear regression equation and r^2 in your graph. Do you observe a linear relationship between the 2 variables?
Use Excels Analysis ToolPak to conduct a regression analysis of Age and Assessment Value. Is Age a significant predictor of AssessmentValue?
Construct a multiple regression model.
Use Excels Analysis ToolPak to conduct a regression analysis with AssessmentValue as the dependent variable and FloorArea, Offices, Entrances, and Age as independent variables. What is the overall fit r^2? What is the adjusted r^2?
Which predictors are considered significant if we work with =0.05? Which predictors can be eliminated?
What is the final model if we only use FloorArea and Offices as predictors?
Suppose our final model is:
AssessedValue = 115.9 + 0.26 x FloorArea + 78.34 x Offices
What wouldbe the assessed value of a medical office building with a floor area of 3500 sq. ft., 2 offices, that was built 15 years ago? Is this assessed value consistent with what appears in the database?
Regression Modeling Data
FloorArea (Sq.Ft.) Offices Entrances Age AssessedValue ($’000)
4790 4 2 8 1796
4720 3 2 12 1544
5940 4 2 2 2094
5720 4 2 34 1968
3660 3 2 38 1567
5000 4 2 31 1878
2990 2 1 19 949
2610 2 1 48 910
5650 4 2 42 1774
3570 2 1 4 1187
2930 3 2 15 1113
1280 2 1 31 671
4880 3 2 42 1678
1620 1 2 35 710
1820 2 1 17 678
4530 2 2 5 1585
2570 2 1 13 842
4690 2 2 45 1539
1280 1 1 45 433
4100 3 1 27 1268
3530 2 2 41 1251
3660 2 2 33 1094
1110 1 2 50 638
2670 2 2 39 999
1100 1 1 20 653
5810 4 3 17 1914
2560 2 2 24 772
2340 3 1 5 890
3690 2 2 15 1282
3580 3 2 27 1264
3610 2 1 8 1162
3960 3 2 17 1447
