Queen Essays

Sheet1

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