Instructions: This part of the assignment requires you to use the MS Excel skills you recently learned to analyze U.S. homicide data. Download from D2L a fresh copy of the Excel File posted in this module (e.g., “SHR Incidents_2010.xlsx”). You may also want to review the PDF file provided that details the Supplemental Homicide Report data[a].
To complete the assignment read each of the questions below carefully and then use Excel to come up with an answer.
How many homicide incidents were there in the U.S.[b] for 2010 (i.e., # rows in dataset)? 1
#:
How many homicide incidents were there in Washington? 2
# Washington:
How many homicide incidents happened in Autumn (September, October, and November)? 3
# Autumn:
How many and what percent of homicide incidents involved multiple victims (Use Situation and round to 1 decimal place)? 54
# Multiple:
% Multiple:
What percent of the victims in the U.S. were female? Percent in California that were female?[c]76
% in US female:
% in California female:
How old (yrs) was the youngest suspect in the U.S.? Age of the oldest suspect? 98
Youngest age:
Oldest age:
Of the women killed in the U.S., what percent were killed by males as opposed to females (adjust the denominator by removing “unknowns” from the suspect gender count)? 10
% Killed by males:
Section 2
In this assignment you will use actual data from PSU’s Transportation and Parking Office. The entire dataset contains all of the parking citations issued for a given year (e.g., 2007, 08, 09, 10, 11, or 12). However, the file you will be working with has been split into four quarters. Your task involves importing the records into Excel using the commands demonstrated in class and/or lecture videos.
Start with the Excel file listed in D2L (e.g., Parking Citations_2007_4th Quarter.xls), which has citations for the fourth quarter of your assigned year. Analyze these data to answer the initial questions below.
Fourth Quarter (Oct, Nov, Dec)
Number of citations issued this quarter (includes warnings)?……………………………………………………………………………………………….
11
Maximum fine issued (exclude blanks; use “Fine_Amount_$”)?……………………………………………………………………………………………….
12
Minimum fine issued (exclude blanks)?……………………………………………………………………………………………….
13
Percent of vehicles registered in Oregon (use “Vehicle_State)?……………………………………………………………………………………………….
14
Sum of fines for the quarter (total up all fine amounts)?……………………………………………………………………………………………….
155
Entire Year – this will require you to import and merge all parking citation files
Number of citations issued this year?……………………………………………………………………………………………….
16
Percent of all citations issued that were for parking in a No Parking Zone (use “Violation”)?……………………………………………………………………………………………….
17
Number citations issued in the Evening 6 to 11pm (use “Time_of_Day_Issued”)?……………………………………………………………………………………………….
18
Total or sum of all fines issued that year?……………………………………………………………………………………………….
19
Percent of all citations issued in September?……………………………………………………………………………………………….
20
Section 3
In this exercise you will be asked to calculate percent changes using various Criminal Justice datasets. Use the Excel file provided in D2L (e.g., “Homework_1_Percent Change_2013.xlsx”) and proceed through the worksheet tabs from left to right, answering the questions below.
Worksheet – Murder_Weapons
The data on this worksheet come from the FBI’s Supplemental Homicide Report or SHR system. Provided are counts of all U.S. homicides from 1970 to 2011. Homicide totals are also broken down into separate counts for each type of weapon used (e.g., firearm, knife). Finally, the U.S. population is also provided.
How many total murders were there in 2011?……………………………………………………………………………………………….
21
Firearms accounted for what percent of all murders from 1970 through 2011 (round to 1 decimal)?……………………………………………………………………………………………….
22
Which year, from 1970 to 2011, had the highest number of murders?……………………………………………………………………………………………….
23
What was the percent change in total murders from 1970 to 2011?……………………………………………………………………………………………….
24
Calculate totals for each full decade (e.g., 1970 to 1979). Use these totals to answer the following questions.
What was the percent change in total murders from 1970’s to 1980’s?……………………………………………………………………………………………….
25
What was the percent change in firearm murders from 1980’s to 1990’s?……………………………………………………………………………………………….
26
Worksheet – Prison Population
These data provide total counts for the U.S. population as a whole (i.e., Census data on total residents, males, females) and counts of sentenced prisoners (i.e., total, males, females) from 1925 to 2011.
What was the percent change from 1925 to 2011 for the U.S. TOTAL POPULATION?……………………………………………………………………………………………….
27
What was the percent change from 1925 to 2011 for the TOTAL prison population?……………………………………………………………………………………………….
28
What was the percent change from 1925 to 2011 for the MALE prison population?……………………………………………………………………………………………….
29
What was the percent change from 1925 to 2011 for the FEMALE prison population?……………………………………………………………………………………………….
30
[a] The SHR file provided to you is a randomly chosen subset of the full dataset and has one row for each incident of homicide – this is what we call the unit of analysis. The majority of homicides have just one victim and one offender. The victim and offender demographics provided in the columns are for the first person in each category. Information on any additional victims and/or offenders has been deleted for the sake of simplicity. For homework purposes I will refer to the rows as incidents, victims, or offenders. For example, if I ask how many victims there were you simply count the number of rows. Same for offenders and incidents.
[b] By “U.S.” we mean the whole dataset provided which may include territories.
[c] Unless instructed otherwise, include “Unknown” and missing values in the denominator or bottom number when calculating percents.
All papers are written by ENL (US, UK, AUSTRALIA) writers with vast experience in the field. We perform a quality assessment on all orders before submitting them.
