**Purpose **

This assignment provides an opportunity to develop, evaluate, and apply bivariate and multivariate linear regression models.

**Resources: **

Microsoft Excel® DAT5/65 Week 5 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?

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?

>Regression Modeling Data

AssessedValue (

000)

790

4

2

796

2

44

4

2

2

4

2

68

3

2

67

00

4

2

2

1

19

2

1

4

2

74

70

2

1

4

3

2

15

2

1

31

3

2

42

1

2

35

2

1

17

678

30

2

2

5

2

1

13

2

2

45

1

1

45

00

3

1

2

2

41

2

2

33

1

2

50

2

2

39

1

1

20

4

3

17

2

2

3

1

5

2

2

15

3

2

27

2

1

8

3

2

17

FloorArea (Sq.Ft.) | Offices | Entrances | Age | ||||||||||||||||||||||

4 | 8 | 1 | |||||||||||||||||||||||

47 | 20 | 3 | 12 | 1 | 5 | ||||||||||||||||||||

5940 | 2094 | ||||||||||||||||||||||||

5720 | 34 | 19 | |||||||||||||||||||||||

3660 | 38 | 15 | |||||||||||||||||||||||

50 | 31 | 1878 | |||||||||||||||||||||||

2990 | 949 | ||||||||||||||||||||||||

2610 | 48 | 910 | |||||||||||||||||||||||

5650 | 42 | 17 | |||||||||||||||||||||||

35 | 1187 | ||||||||||||||||||||||||

2930 | 11 | 13 | |||||||||||||||||||||||

1280 | 671 | ||||||||||||||||||||||||

4880 | 1 | 678 | |||||||||||||||||||||||

1620 | 710 | ||||||||||||||||||||||||

1820 | |||||||||||||||||||||||||

45 | 1585 | ||||||||||||||||||||||||

2570 | 842 | ||||||||||||||||||||||||

4690 | 15 | 39 | |||||||||||||||||||||||

4 | 33 | ||||||||||||||||||||||||

41 | 27 | 1268 | |||||||||||||||||||||||

3530 | 1251 | ||||||||||||||||||||||||

1094 | |||||||||||||||||||||||||

1110 | 638 | ||||||||||||||||||||||||

2670 | 999 | ||||||||||||||||||||||||

1100 | 653 | ||||||||||||||||||||||||

5810 | 1914 | ||||||||||||||||||||||||

2560 | 24 | 772 | |||||||||||||||||||||||

2340 | 890 | ||||||||||||||||||||||||

3690 | 1282 | ||||||||||||||||||||||||

3580 | 1264 | ||||||||||||||||||||||||

3610 | 1162 | ||||||||||||||||||||||||

3960 | 1447 |