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

Regression Modeling [due Wed] Assignment Content Purpose This assignment provides an opportunity to develop, evaluate, and apply bivariate and multivariate linear regression models. Resources:...

1 answer below »

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?


    Submityour assignment.


Answered Same Day Jul 22, 2021

Solution

Pooja answered on Jul 23 2021
134 Votes
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
    Adjusted R Square    0.9356415415
    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

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here