Prep 5.1 +5.2

Please ensure no AI usage or plagiarism, and complete this by 11 pm Sunday. Two separate preps to do. I have attached the data for each one

Assignment Overview

Prep Assignment #5.1: Comparing Mean Scores in Police Agencies

This assignment gives you practice in basic bivariate analysis, a core research skill in criminal justice. By calculating and comparing simple mean scores across groups, you will learn how to summarize patterns in real-world data without relying on statistical tests. This directly connects to our module learning objectives: developing the ability to examine the relationship between a categorical variable (BWC_Adopted: Yes/No) and several continuous variables (Use of Force, Complaints, Response Time), interpreting group differences cautiously, and communicating findings clearly in a policy-relevant context.

Background Story

Youve just been hired as a junior policy analyst in your citys Criminal Justice Reform Task Force. The mayors office is under pressure to decide whether to expand the use of body-worn cameras (BWCs) across all local police agencies.

Some agencies in the region already use BWCs, while others do not. The Task Force has asked you to summarize what the data show about how these two groups of agencies compare on key outcomes.

Important: This is a quick, first-pass review for policymakers. You are not expected to run complex statistics or prove causation. The city council just wants to see simple averages and plain-language descriptions.

Your Data

You are provided with information from 40 agencies, including:

• Use of Force per 100 Arrests
• Citizen Complaints per 100 Officers
• Response Time (minutes) to high-priority calls
• Whether the agency has adopted BWCs (Yes/No)
• Region of the agency (optional context only)

(See Excel file:

sheet)

---

Guidelines

Your Task

• Calculate group means.
  • For each outcome, compute the mean for agencies with BWCs and for agencies without BWCs.
  • Compute the simple difference (Yes No).
• Create a mean-comparison table.
  • Each variable becomes a column (Use of Force, Complaint, etc.)
  • The two rows indicate whether BWC was adopted by the agency or not.

• Create charts based on the table.
• Write a brief policy memo (up to 250 words). Report the results: describe how the averages differ between the two groups. Interpret the results but exercise caution: offer at least 23 non-BWC reasons why these differences might exist (e.g., training, staffing, community demographics, leadership).
• Submit the Excel sheet with your calculations, charts, and text box.

Assignment Overview

Prep Assignment #5.2: Police Department Activity Analysis

This assignment is designed to help you practice basic descriptive data analysis in Excel by calculating subgroup means and creating charts. It connects directly to our module learning objectives on interpreting criminal justice data, comparing outcomes across groups, and presenting findings visually for non-technical audiences. By working with a fictional police department dataset, you will gain hands-on experience in summarizing data patterns that are commonly used in oversight, policy, and accountability discussions.

Background Story

The City of Riverbend recently asked its Police Oversight Committee to review officer activity data. The city has faced public pressure to improve transparency, particularly around officer workload, use of force, and professional training.

To respond, the Chief of Police provided a dataset of 44 officers across different divisions (Patrol, Traffic, Narcotics, Investigations). The Oversight Committee wants to understand:

• How officer outcomes differ by gender and
• How training hours compare across divisions.

The committee emphasizes that this is only a descriptive review the goal is to summarize and visualize trends, not to test for statistical significance.

As a new criminal justice analyst for the Oversight Committee, your task is to prepare a short Excel report showing subgroup means and charts, along with a written interpretation of key findings.

(See Excel file:

)

---

Guidelines

Your Task

Use Excel formulas (e.g., AVERAGEIF) to calculate the following:

1. Gender Comparisons

• Average Arrests (Male vs. Female)
• Average Use_of_Force_Incidents (Male vs. Female)

2. Division Comparisons

• Average Arrests by Division (Patrol, Traffic, Narcotics, Investigations)
• Average Training_Hours by Division

Charts

Create the following visuals in Excel:

1. Bar Chart comparing mean Arrests for Male vs. Female officers.
2. Bar Chart comparing the average use of force incidents for Male vs. Female officers.
3. Bar Chart showing mean Arrests by Division.

Submit one Excel file with:

1. 1. Calculations neatly labeled subgroup means.
   2. Charts all three charts, formatted professionally.
   3. Summary 23 short paragraphs summarizing your findings for the Oversight Committee added in a text box. In your summary, focus on these questions:
      • Which gender makes more arrests and uses force more often on average?
      • Which division averages the most arrests, and what might explain that?
      • Do officers in Narcotics or Investigations receive more training hours?

WRITE MY PAPER