USA: +1(669)289-8683 
Sign in
Must document the work and record the answers ON THIS TEMPLATE. Answers must be TYPED, in 12-point font, in the boxes provided on this template. You need to have your own Excel spreadsheet that shows the analytic work you did to produce the results on this EMA.Β
Notes: 1) All numerical answers should be rounded to three decimal places unless otherwise specified; 2) Any graphs you produce should have all the elements clearly labeled.Β
Sheet1
District
month
Aggravated Assault
Arson
Auto Theft
Burglary
Homicide
Larceny
Larceny From Auto
Rape
Robbery – Carjacking
Robbery – Commercial
Robbery – Residential
Robbery – Street
Robbery – Total
Shooting
Median Income ($)
Median Income Quartile
Population
Average Temp (F)
Poverty Rate (%)
Eastern
January 2018
32
0
38
45
5
61
33
4
1
5
1
20
27
2
33066
1
54658
35
33
Northwestern
January 2018
23
1
54
62
3
69
25
6
12
12
8
34
66
3
41416
1
91936
35
23
Southern
January 2018
27
1
30
52
3
68
43
3
5
7
6
25
43
3
56092
2
64330
35
25
Northern
January 2018
23
0
33
97
2
93
63
2
7
11
4
28
50
4
59991
3
123964
35
20
Southeastern
January 2018
29
0
54
55
0
104
94
4
4
10
2
60
76
2
67381
4
70479
35
22
Eastern
February 2018
27
2
27
55
0
57
48
4
2
3
2
14
21
3
33066
1
54658
45
33
Northwestern
February 2018
29
1
39
41
2
64
26
0
6
5
1
8
20
4
41416
1
91936
45
23
Southern
February 2018
29
3
19
53
1
64
48
3
7
5
4
19
35
0
56651
2
63584
45
24
Northern
February 2018
25
0
29
44
2
82
44
4
3
8
1
16
28
1
60773
4
118865
45
19
Southeastern
February 2018
21
2
30
58
2
83
93
3
6
8
5
44
63
2
67633
4
69935
45
22
Eastern
March 2018
37
1
17
52
3
56
49
5
3
5
4
28
40
3
33066
1
54658
43
33
Northwestern
March 2018
28
3
50
44
2
73
21
1
8
6
4
13
31
0
41520
2
90310
43
24
Southern
March 2018
29
3
39
42
2
77
54
3
6
7
11
19
43
4
56651
2
63584
43
24
Northern
March 2018
46
1
26
42
2
70
54
1
3
10
3
20
36
2
59379
3
122584
43
20
Southeastern
March 2018
42
0
26
47
0
100
91
4
4
4
4
44
56
2
65944
4
71364
43
22
Eastern
April 2018
42
2
18
29
5
69
31
5
3
8
3
24
38
10
33038
1
51769
55
33
Northwestern
April 2018
32
3
51
52
1
72
37
4
4
3
6
21
34
2
42497
2
97495
55
23
Southern
April 2018
55
2
40
56
6
82
36
5
4
6
5
33
48
5
57688
3
61387
55
24
Northern
April 2018
31
2
20
61
2
75
43
1
1
5
7
30
43
7
59917
3
116706
55
20
Southeastern
April 2018
34
0
21
44
2
113
99
2
1
11
3
42
57
4
65944
4
71364
55
22
Eastern
May 2018
53
1
29
30
3
74
36
3
3
5
3
24
35
7
33066
1
54658
72
33
Northwestern
May 2018
47
1
44
54
1
66
40
2
5
5
8
24
42
4
40829
1
90379
72
24
Southern
May 2018
55
0
29
61
4
112
49
5
6
6
4
34
50
11
57688
3
61387
72
24
Northern
May 2018
28
1
34
58
3
134
43
1
3
11
4
18
36
4
59573
3
118636
72
20
Southeastern
May 2018
45
1
33
54
1
152
109
5
1
12
4
57
74
2
65944
4
71364
72
22
Eastern
June 2018
45
2
31
38
2
66
42
2
4
5
3
14
26
6
33066
1
54658
77
33
Northwestern
June 2018
40
0
33
49
3
92
27
6
4
13
6
17
40
6
42576
2
95310
77
23
Southern
June 2018
52
0
44
70
3
117
58
4
3
12
6
32
53
5
56651
2
63584
77
24
Northern
June 2018
29
2
27
60
0
110
45
5
4
14
6
14
38
5
60028
3
119776
77
20
Southeastern
June 2018
44
0
22
45
1
148
105
3
2
7
1
49
59
1
64942
4
69679
77
22
Eastern
July 2018
42
1
28
60
3
73
37
2
3
6
4
20
33
4
33066
1
54658
82
33
Northwestern
July 2018
34
2
39
68
1
80
35
1
1
13
3
16
33
5
43101
2
95321
82
23
Southern
July 2018
40
0
51
88
7
97
71
4
4
13
2
37
56
6
57688
3
61387
82
24
Northern
July 2018
32
0
36
68
2
112
52
3
9
6
2
24
41
5
60060
3
119787
82
20
Southeastern
July 2018
46
1
31
56
1
138
83
1
6
11
5
46
68
3
67381
4
70479
82
22
Eastern
August 2018
47
0
25
41
4
54
19
4
1
3
1
26
31
12
33066
1
54658
82
33
Northwestern
August 2018
34
2
49
32
4
90
53
5
7
9
1
29
46
8
43333
2
96013
82
23
Southern
August 2018
58
0
39
99
4
122
68
3
6
6
4
27
43
5
56651
2
63584
82
24
Northern
August 2018
29
2
50
71
0
135
93
4
8
6
7
47
68
4
61065
4
119221
82
19
Southeastern
August 2018
53
0
38
56
1
151
101
0
5
4
5
53
67
5
67381
4
70479
82
22
Eastern
September 2018
43
3
20
26
4
68
35
4
0
2
3
21
26
6
33066
1
54658
75
33
Northwestern
September 2018
31
0
52
37
4
74
50
6
6
6
6
28
46
9
40816
1
92564
75
24
Southern
September 2018
40
1
40
97
5
101
67
3
2
7
2
34
45
4
56651
2
63584
75
24
Northern
September 2018
27
1
30
48
3
126
76
7
3
8
4
32
47
2
59991
3
123964
75
20
Southeastern
September 2018
44
0
49
57
1
146
102
2
2
8
5
55
70
6
65944
4
71364
75
22
Eastern
October 2018
33
1
21
36
3
71
54
2
1
2
4
26
33
8
33066
1
54658
62
33
Northwestern
October 2018
27
1
42
69
7
84
45
5
3
8
2
31
44
8
43317
2
94467
62
23
Southern
October 2018
45
1
63
85
1
99
65
4
6
3
4
35
48
3
57688
3
61387
62
24
Northern
October 2018
40
1
42
60
1
132
111
6
1
9
3
26
39
4
59541
3
125691
62
20
Southeastern
October 2018
39
1
33
68
1
135
233
2
6
8
4
52
70
6
65944
4
71364
62
22
Eastern
November 2018
36
3
27
36
1
58
48
2
4
9
2
25
40
6
33066
1
54658
46
33
Northwestern
November 2018
33
1
46
71
2
98
30
3
7
14
6
28
55
6
43438
2
92282
46
22
Southern
November 2018
31
1
60
70
0
97
63
1
7
6
4
39
56
5
57688
3
61387
46
24
Northern
November 2018
35
0
43
47
4
125
76
1
3
14
2
30
49
3
60225
4
119838
46
20
Southeastern
November 2018
31
0
30
58
0
118
160
0
5
13
1
61
80
3
65944
4
71364
46
22
Eastern
December 2018
37
2
22
34
7
76
86
1
2
4
6
22
34
7
33066
1
54658
43
33
Northwestern
December 2018
38
1
48
39
3
80
32
6
8
11
2
19
40
7
43333
2
96013
43
23
Southern
December 2018
40
1
45
102
2
107
55
3
3
17
6
28
54
3
57688
3
61387
43
24
Northern
December 2018
23
1
41
63
1
125
79
1
2
12
5
30
49
6
59379
3
122584
43
20
Southeastern
December 2018
41
0
40
48
1
137
117
0
3
7
1
66
77
8
65944
4
71364
43
22
Eastern
January 2019
32
0
16
46
5
65
49
1
5
4
2
21
32
8
33066
1
54658
36
33
Northwestern
January 2019
40
1
41
51
2
81
43
4
6
12
1
17
36
5
42680
2
96641
36
23
Southern
January 2019
37
2
31
61
3
94
60
2
3
14
5
35
57
6
57688
3
61387
36
24
Northern
January 2019
30
1
32
38
2
101
57
1
2
8
3
25
38
4
60173
3
121983
36
19
Southeastern
January 2019
29
0
35
54
3
112
134
2
6
7
4
37
54
3
65944
4
71364
36
22
Eastern
February 2019
35
0
20
34
3
55
25
5
2
2
3
10
17
2
33672
1
53756
40
33
Northwestern
February 2019
32
1
33
35
3
70
28
7
5
8
6
15
34
3
43193
2
91590
40
23
Southern
February 2019
23
0
22
43
0
78
26
2
6
11
7
24
48
3
55488
2
60081
40
24
Northern
February 2019
16
1
36
33
1
79
39
1
3
4
2
17
26
1
60010
3
124536
40
20
Southeastern
February 2019
38
0
35
73
2
98
92
0
8
9
0
24
41
3
67381
4
70479
40
22
Eastern
March 2019
45
1
14
29
5
56
20
3
1
9
6
23
39
5
33066
1
54658
45
33
Northwestern
March 2019
34
0
39
33
1
80
35
2
7
11
2
20
40
9
42576
2
95310
45
23
Southern
March 2019
29
2
32
53
1
96
24
1
2
6
3
24
35
3
57688
3
61387
45
24
Northern
March 2019
29
0
30
26
1
105
54
4
2
6
1
12
21
1
60689
4
121167
45
19
Southeastern
March 2019
35
2
36
55
1
94
87
5
1
8
3
23
35
5
67381
4
70479
45
22
Eastern
April 2019
38
1
21
30
4
66
38
5
2
4
2
23
31
10
33066
1
54658
60
33
Northwestern
April 2019
36
0
38
51
2
90
44
3
2
5
3
10
20
8
43209
2
93136
60
23
Southern
April 2019
37
0
22
59
0
109
33
1
3
10
1
14
28
4
57688
3
61387
60
24
Northern
April 2019
30
0
38
39
1
102
39
3
2
5
1
20
28
1
59991
3
123964
60
20
Southeastern
April 2019
31
2
36
53
1
118
91
4
2
5
2
40
49
4
69195
4
69050
60
21
Eastern
May 2019
38
0
21
37
3
72
27
6
6
4
1
27
38
6
33672
1
53756
70
33
Northwestern
May 2019
33
1
29
54
3
70
35
3
5
7
3
26
41
4
40816
1
92564
70
24
Southern
May 2019
55
3
37
41
5
106
49
2
3
5
2
25
35
5
55164
2
66527
70
25
Northern
May 2019
39
0
20
38
2
145
69
2
8
10
3
26
47
3
59173
3
126710
70
20
Southeastern
May 2019
43
0
44
65
1
138
80
1
6
10
6
44
66
6
65944
4
71364
70
22
Eastern
June 2019
58
1
28
43
6
71
29
1
6
4
4
20
34
9
33066
1
54658
77
33
Northwestern
June 2019
48
0
30
52
5
94
26
1
11
7
3
25
46
5
44806
2
99398
77
23
Southern
June 2019
51
3
30
61
3
95
44
3
2
4
4
24
34
2
56863
3
67591
77
24
Northern
June 2019
31
1
33
55
0
124
47
6
6
9
3
18
36
5
60278
4
115839
77
20
Southeastern
June 2019
51
1
41
62
4
131
60
1
8
9
3
48
68
4
65944
4
71364
77
22
Eastern
July 2019
56
0
34
35
4
72
30
6
3
3
1
21
28
7
33066
1
54658
84
33
Northwestern
July 2019
38
1
48
50
7
72
36
1
4
7
3
25
39
7
42497
2
97495
84
23
Southern
July 2019
51
0
37
61
1
111
44
9
5
4
5
32
46
7
56651
2
63584
84
24
Northern
July 2019
37
0
39
47
5
124
66
4
11
6
4
36
57
4
60202
3
120016
84
19
Southeastern
July 2019
35
0
47
58
3
141
117
2
8
3
5
54
70
4
65944
4
71364
84
22
Eastern
August 2019
51
1
24
41
4
68
30
1
12
5
4
32
53
14
32162
1
49022
80
33
Northwestern
August 2019
51
2
45
47
5
119
69
2
13
10
5
24
52
0
44977
2
97213
80
22
Southern
August 2019
46
1
39
54
0
123
42
0
2
5
4
40
51
8
56651
2
63584
80
24
Northern
August 2019
27
0
26
53
3
110
64
2
8
4
3
25
40
5
59345
3
117051
80
20
Southeastern
August 2019
34
1
35
53
2
136
102
4
8
3
4
56
71
8
65944
4
71364
80
22
Eastern
September 2019
51
0
18
35
7
63
30
2
4
3
4
18
29
10
33066
1
54658
76
33
Northwestern
September 2019
22
2
36
61
4
85
62
1
7
7
3
25
42
4
40829
1
90379
76
24
Southern
September 2019
51
0
28
58
4
97
53
1
6
3
7
26
42
6
57688
3
61387
76
24
Northern
September 2019
33
0
34
75
2
137
68
2
8
4
0
30
42
5
60037
3
119965
76
19
Southeastern
September 2019
44
1
33
46
1
95
101
3
9
8
4
58
79
3
67381
4
70479
76
22
Eastern
October 2019
49
0
15
31
8
69
33
4
1
6
4
27
38
8
33104
1
51125
64
33
Northwestern
October 2019
42
0
38
77
4
88
45
2
12
7
3
15
37
11
44977
2
97213
64
22
Southern
October 2019
44
0
41
46
4
111
47
2
4
6
1
27
38
7
56651
2
63584
64
24
Northern
October 2019
28
0
22
53
2
107
59
2
3
6
3
27
39
4
60448
4
115890
64
20
Southeastern
October 2019
35
1
29
57
2
100
96
1
2
6
3
43
54
2
65944
4
71364
64
22
Eastern
November 2019
39
0
24
30
3
52
32
3
4
8
1
24
37
5
33104
1
51125
46
33
Northwestern
November 2019
31
1
31
66
3
79
40
0
8
3
4
20
35
4
42612
2
100372
46
23
Southern
November 2019
40
3
32
47
4
77
48
4
6
7
6
20
39
3
57688
3
61387
46
24
Northern
November 2019
20
0
28
50
1
116
49
1
5
6
5
21
37
3
59687
3
125559
46
20
Southeastern
November 2019
29
1
34
49
3
98
75
3
6
7
4
49
66
5
67381
4
70479
46
22
Eastern
December 2019
42
0
16
36
5
52
34
2
4
1
4
17
26
6
32425
1
48141
42
34
Northwestern
December 2019
33
3
34
45
1
84
38
1
5
4
5
15
29
2
42576
2
95310
42
23
Southern
December 2019
31
3
20
35
4
111
38
0
6
5
8
24
43
8
55488
2
60081
42
24
Northern
December 2019
17
1
29
52
4
125
34
0
5
7
3
11
26
3
59866
3
119167
42
20
Southeastern
December 2019
23
0
35
36
4
114
70
2
1
3
2
33
39
2
65944
4
71364
42
22
2
Question 1. What is the covariance of median income and average rate of carjackings (per
100,000)?
Your Answer: Step One: Use the =AVERAGE() function in Excel to calculate the mean of
the median income.
Step Two: Use the =AVERAGE() function in Excel to calculate the average
monthly rate of carjackings (per 100,000 residents).
Step Three: Subtract the mean of the variable from each observation for median
income and then for carjackings. What are the deviations of median income and
carjackings for January 2018 for each district?
Step Four: For each observation, multiply the deviation of median income to
the deviation of the rate of carjackings. What are the products of the deviations
for January 2018 for each district?
Step Five: Use =SUM() in Excel to sum the products of the deviations. Report
the total.
Step Six: Divide the sum of the products by n-1. Report the covariance.
Step Seven: Check your answer using =COVARIANCE.S() in Excel.
3
1.A. Interpret the covariance
.
Your Answer:
Question 2. Letβs use a linear regression to estimate the relationship between median income
and rate of carjackings (per 100,000).
2.A. What is the independent and dependent variable? And why?
Your Answer:
Independent Variable:
Dependent Variable:
Explain your answer.
2.B. What is the model of the population relationship?
Your Answer:
2.C. Calculate π·π·οΏ½.
Your Answer: Step One: Subtract the overall mean of median income from each observation
of median income & then square those values. What are the squared deviations
for January 2018 for each district?
Step Two: Sum the squared deviations. Report the sum of squared deviations of
median income.
Step Three: Divide the sum of squared deviations by n-1. Report the variance.
Step Four: Use =VAR.S() in Excel to check the variance of median income.
4
Step Five: Divide the covariance of median income and carjackings by the
variance of median income to find the estimated beta.
2.D. Calculate πΆπΆοΏ½.
Your Answer: Subtract the product of οΏ½ΜοΏ½π½ and the mean of median income from the average
monthly rate of carjackings to find the estimated alpha.
2.E. Report your estimated regression model ππππ = πΆπΆοΏ½ + π·π·οΏ½πΏπΏππ + ππππ.
Your Answer:
2.F. What is the predicted monthly rate of carjackings for January 2018 for each district?
Your Answer: Step One: Calculate the predicted values for each observation using πποΏ½ = πΌπΌοΏ½ +
οΏ½ΜοΏ½π½ππππ.
Step Two: Report the predicted values for monthly rate of carjackings for
January 2018 for each district.
Question 3. Letβs conduct a hypothesis test to assess whether the observed relationship
between median income and rate of carjackings is statistically significant (95% confidence).
3.A. Write down the null hypothesis and the alternative hypothesis.
Your Answer:
Null:
Alternative:
5
3.B. Calculate the degrees of freedom for your test and use the t-table to find the critical
value.
Your Answer:
ππππ:
Critical Value:
Show how you arrived at your answer.
Note: If your t-table does not have the exact degrees of freedom, use the d.f. on
the table that is the closest to the d.f. in this test.
3.C. Draw the null distribution & delineate and shade the rejection region (label with the
positive and negative t-critical values).
Your Answer:
3.D. Compute the test statistic for this test & map onto the distribution of the null.
Note: You should label the t-stat on the distribution you drew in the previous step.
Your Answer: To compute the standard error of οΏ½ΜοΏ½π½, letβs start with calculating the mean squared
error (MSE).
Step One: For each observation, subtract the observed value for carjackings
from the predicted value for carjackings. What are the residuals for January 201
8
for each district?
Step Two: Square the residual for each observation. What are the squared
residuals for January 2018 for each district?
6
Step Three: Use =SUM() in Excel to sum the squared residuals for all the
observations. Report the total.
Step Four: Divide the sum of squared residuals by n-2. Report the mean squared
error (MSE).
Step Five: Calculate the standard error of οΏ½ΜοΏ½π½ by taking the square root (use
=SQRT() in Excel) of the MSE divided by the sum of squared deviations of X.
Report the standard error of οΏ½ΜοΏ½π½.
Step Six: Calculate the t-stat using: π½π½
οΏ½β0
π π π π (π½π½οΏ½)
.
3.E. Compare the test statistic with the critical value & make a decision to reject or fail to
reject the null hypothesis.
Your Answer: Show how you arrived at your answer.
3.F. Find the p-value for this test.
Your Answer: Use =T.DIST.2T( | tstat |, d.f.) function in Excel to calculate the p-value.
Note: Make sure you input the absolute value of your test statistic.
3.G. State your conclusion [Use plain language].
Your Answer:
7
Question 4. Letβs visualize the relationship between median income and rate of carjackings
using a scatter plot. [For this question, copy and paste your figure below]
Step One: You can insert scatter plots by selecting the data (columns that you want to graph) and
choose Scatter plots in the Insert tab > Charts group. Note: You can add the linear regression
trendline to your scatter plot by checking Linear Trendline in Add Chart Elements.
Note: Make sure you include all the key components of a figure (refer to the Final Paper instructions
for all necessary components of a figure).
8
Question 5. For the ANOVA & F-test analysis in the Final Paper, you will have to create a bar
chart to visualize the relationship between median income and rate of carjackings. In order
to do this, you will need to separate median incomes into quartiles. [For this question, copy
and paste your figure below. There is a step-by-step guide on ELMS if you need help adding
the graph to your document.]
Step One: Calculate the mean rate of carjackings for each quartile of median income separately.
Step Two: You can insert a bar chart by selecting the data (columns that you want to graph) and
choose Bar Chart in the Insert tab > Charts group.
Note: Make sure you include all the key components of a figure (refer to the Final Paper instructions
for all necessary components of a figure).
9
Extra Credit [+2 points]: What is the thing you are most looking forward to over winter break?
And why?
Your Answer: Explain your answer.
