## Description

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*= 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?

NO PLAGARISM

### Unformatted Attachment Preview

Purchase answer to see full attachment

## Explanation & Answer

Thank you so much 🙏 Please let me know in case you have any questions.

Outline

I.

Bivariate linear regression model

II.

Multiple linear regression model

Offices

4

3

4

4

3

4

2

2

4

2

3

2

3

1

2

2

Entrances

2

2

2

2

2

2

1

1

2

1

2

1

2

2

1

2

Age

8

12

2

34

38

31

19

48

42

4

15

31

42

35

17

5

AssessedValue ($'000)

1796

1544

2094

1968

1567

1878

949

910

1774

1187

1113

671

1678

710

678

1585

2570

2

1

13

842

4690

1280

4100

3530

3660

2

1

3

2

2

2

1

1

2

2

45

45

27

41

33

1539

433

1268

1251

1094

1110

1

2

50

638

2670

2

2

39

999

1100

1

1

20

653

5810

2560

2340

4

2

3

3

2

1

17

24

5

1914

772

890

3690

2

2

15

1282

3580

3

2

27

1264

3610

3960

2

3

1

2

8

17

1162

1447

2500

AssessedValue ($'000)

FloorArea (Sq.Ft.)

4790

4720

5940

5720

3660

5000

2990

2610

5650

3570

2930

1280

4880

1620

1820

4530

2000

1500

1000

500

0

Regression Sta

AssessedValue ($'000) vs FloorArea (Sq.Ft.)

2500

AssessedValue ($'000)

2500

y = 0.3067x + 162.66

R² = 0.9377

2000

1500

1000

500

2000

1500

1000

500

0

0

0

1000

2000

3000

4000

5000

6000

7000

0

FloorArea (Sq.Ft.)

SUMMARY OUTPUT

Regression Stat

Regression Statistics

Multiple R

0.968358

R Square

0.937718

Adjusted R Square

0.935642

Standard Error

115.5993

Observations

32

ANOVA

df

Regression

Residual

Total

Intercept

FloorArea (Sq.Ft.)

1

30

31

SS

6035852

400896

6436748

MS

F

Significance F

6035852 451.6772 1.23E-19

13363.2

Coefficients

Standard Error t Stat

P-value Lower 95% Upper 95%

162.6628 54.47857 2.985812 0.005586 51.40269 273.9228

0.306732 0.014433 21.2527 1.23E-19 0.277257 0.336207

AssessedValue ($'000) Vs Age

y = -5.5942x + 1377.4

R² = 0.032

10

20

30

40

50

60

Age

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.179004

R Square

0.032043

Adjusted R Square

-0.00022

Standard Error

455.7228

Observations

32

ANOVA

df

Regression

Residual

Total

Intercept

Age

SS

MS

F

Significance F

1 206249.6 206249.6 0.993097 0.326957

30 6230498 207683.3

31 6436748

Coefficients

Standard Error t Stat

P-value Lower 95% Upper 95%Lower 95.0%

1377.366 163.1906 8.440231 2.03E-09 1044.086 1710.646 1044.086

-5.59421 5.613615 -0.99654 0.326957 -17.0587 5.870325 -17.0587

Upper 95.0%

1710.646

5.870325

FloorArea (Sq.Ft.)

Offices Entrances

4790

4

2

4720

3

2

5940

4

2

5720

4

2

3660

3

2

5000

4

2

2990

2

1

2610

2

1

5650

4

2

3570

2

1

2930

3

2

1280

2

1

4880

3

2

1620

1

2

1820

2

1

4530

2

2

2570

2

1

4690

2

2

1280

1

1

4100

3

1

3530

2

2

3660

2

2

1110

1

2

2670

2

2

1100

1

1

5810

4

3

2560

2

2

2340

3

1

3690

2

2

3580

3

2

3610

2

1

3960

3

2

Age

8

12

2

34

38

31

19

48

42

4

15

31

42

35

17

5

13

45

45

27

41

33

50

39

20

17

24

5

15

27

8

17

AssessedValue ($'000)

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

SUMMARY OUTPUT

Regression Sta...