Assignment Description
The use of visual models and graphics is important for the communication of data to audiences. For this homework assignment, your task is to create a variety of visual models that represent complex data.
Specifically, you will be using frequency and proportion tables as well as other visual models to conduct multi-year comparisons.
Scenario Overview
Response times are often considered an important public safety metric when evaluating police performance. In 2022, your local police department performed a benchmarking exercise comparing response times with similar- sized communities in your state. Your community’s performance ranked in the lower third. Subsequently, your police department decided to implement a new software-based system utilizing in-car GPS modules to deploy officers efficiently. This system considered officer’s location, call address, and current traffic conditions when dispatching officers. Response time data for 2022 (before the new system) and 2023 (after implementing the system) are provided in the provided spreadsheet.
Steps
By Day 7, download the provided spreadsheet and complete the following tasks:
Open the “Tables” tab:
Construct a Frequency Table for police response times in 2022 and 2023. Fill out the table provided in the spreadsheet. Use the COUNTIFS function in Excel for counting. For example, if you want to count the frequency for 2022 response times from 0 to 5 minutes, you’ll use the formula =COUNTIFS($A$2:$A$500,">=0",$A$2:$A$500,"<=5"), but make sure to reference the data tab. Likewise if you want to count the frequency of 2022 response times between 5 and 10 minutes, you’ll use =COUNTIFS($A$2:$A$500,">5",$A$2:$A$500,"<=10"), again, referencing the data tab.
Create a Frequency Proportion Table for police response times for the 2022 and 2023 using the table provided in the spreadsheet.
Generate a Cumulative Frequency Table for police response times in 2022 and 2023. This table represents a running total of response times. Fill out the table provided in the spreadsheet.
Create a Cumulative Proportion Table for police response times in 2022 and 2023.
Open the “Histogram” tab:
Create two histograms, one for 2022 data and one for 2023 data, using your raw data (rows A and C from the “Police Response Data” tab).
Add axes labels and chart titles.
Open the “Frequency Polygon” tab and construct a frequency polygon for both years using the table you created from “1” above.
Select the data in your table. Don’t select the “Total” row.
- Generate a frequency polygon by navigating to the "Insert" tab, choosing "Recommended Charts" and selecting "Insert Line".
Add a title, axes labels, and a legend.
- Open the “Response” tab and answer the following questions:
Now that you have your data presented in tabular and graphical fashion, what can you say (generally) about the effectiveness of the new software to improve response times?
What graphic did you find most helpful in generating your response?