Instructions: Write the answers to the questions below in the spreadsheet. The Excel file for this assignment contains a database with information about the tax assessment value assigned to medical...

1 answer below »

Instructions: Write the answers to the questions below in the spreadsheet.






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? Why?

  • 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? Why?






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? Why?

  • 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 would be 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? If so, which observations?

dat565-excel-spreadsheet-1tf0km5d.xlsx
Answered 1 days AfterJun 17, 2021

Answer To: Instructions: Write the answers to the questions below in the spreadsheet. The Excel file for this...

Shakeel answered on Jun 19 2021
130 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            Yes, I observe the linear relationship between these two variables as data points are very close and R square has also a large value.
    4690    2    2    45    1539
    1280    1    1    45    433            SUMMARY OUTPUT
    4100    3    1    27    1268
    3530    2    2    41    1251            Regression Statistics
    3660    2    2    33    1094            Multiple R    0.9683582089
    1110    1    2    50    638            R Square    0.9377176208
    2670    2    2    39    999            Adjusted R Square    0.9356415415
    1100    1    1    20    653            Standard Error    115.5993039377
    5810    4    3    17    1914            Observations    32
    2560    2    2    24    772
    2340    3    1    5    890            ANOVA
    3690    2    2    15    1282                df    SS    MS    F    Significance F
    3580    3    2    27    1264            Regression    1    6035851.90287359    6035851.90287359    451.6771673353    1.22547865248041E-19
    3610    2    1    8    1162            Residual    30    400895.972126411    13363.1990708804
    3960    3    2    17    1447            Total    31    6436747.875
                                    Coefficients    Standard Error    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 (Sq.Ft.)    0.306732084    0.0144326187    21.2526978837    1.22547865248042E-19    0.2772567445    0.3362074236    0.2772567445    0.3362074236
                                The floor area variable has p-value less than 0.05 that shows it is a significant predictor of Assessment value.
                                No, I donot observe linear relationship as the data points of Assessed value are widely scattered and R square is also...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?