DAT 565 UOP Wk 5 Data Analysis & Bivariate Linear Regression Modeling Worksheet

User Generated

zn2013

Mathematics

DAT 565

University of Phoenix

DAT

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

FloorArea (Sq.Ft.) 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 Offices 4 3 4 4 3 4 2 2 4 2 3 2 3 1 2 2 2 2 1 3 2 2 1 2 1 4 2 3 2 3 2 3 Entrances 2 2 2 2 2 2 1 1 2 1 2 1 2 2 1 2 1 2 1 1 2 2 2 2 1 3 2 1 2 2 1 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 FloorArea (Sq.Ft.) 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 Offices 4 3 4 4 3 4 2 2 4 2 3 2 3 1 2 2 2 2 1 3 2 2 1 2 1 4 2 3 2 3 2 3 Entrances 2 2 2 2 2 2 1 1 2 1 2 1 2 2 1 2 1 2 1 1 2 2 2 2 1 3 2 1 2 2 1 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
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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...


Anonymous
I was struggling with this subject, and this helped me a ton!

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Related Tags