Great Deal! Get Instant \$10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

# Regression Modeling [due Wed]

## Assignment Content

1. Purpose

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 Excelâ€™s 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 Excelâ€™s 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 Excelâ€™s 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 = XXXXXXXXXXx FloorArea XXXXXXXXXXx 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?

Answered Same Day Jul 22, 2021

## Solution

Pooja answered on Jul 23 2021
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
1
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?
all points are close to each other and there is an upward trend in the scatterplot.
there is a strong positive linear relationship between Floor Area and Assessed Value.
with the increase in Floor Area, the Assessed value also increases drastically.
ScatterPlot
AssessedValue (\$'000)
4790    4720    5940    5720    3660    5000    2990    2610    5650    3570    2930    1280    4880    1620    1820    4530    2570    4690    1280    4100    3530    3660    1110    2670    1100    5810    2560    2340    3690    3580    3610    3960    1796    1544    2094    1968    1567    1878    949    910    1774    1187    1113    671    1678    710    678    1585    842    1539    433    1268    1251    1094    638    999    653    1914    772    890    1282    1264    1162    1447    Floor Area (Sq. Ft.)
AssessedValue (\$'000)
2
Use Excelâ€™s Analysis ToolPak to conduct a regression analysis ofÂ FloorAreaÂ andÂ AssessmentValue. IsÂ FloorAreaÂ a significant predictor ofÂ AssessmentValue?
SUMMARY OUTPUT
Regression Statistics
Multiple R    0.9683582089
R Square    0.9377176208
Standard E
or    115.5993039377
Observations    32
ANOVA
df    SS    MS    F    Significance F
Regression    1    6035851.90287359    6035851.90287359    451.6771673353    1.22547865248041E-19
Residual    30    400895.972126411    13363.1990708804
Total    31    6436747.875
Coefficients    Standard E
or    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
Intercept    162.6627672772    54.4785653123    2.9858122428    0.0055856116    51.402693881    273.9228406733    51.402693881    273.9228406733
FloorArea...
SOLUTION.PDF