You are currently employed by the State Statistical Analysis Center, which is
under the authority of the Governor’s Anti-Crime Council. One of your
responsibilities is to prepare research briefs for the Governor concerning crime
issues. You have been tasked with preparing a short research brief describing
the crime trends in Massachusetts from 2000 through 2010. The Governor is
also particularly interested in the percentage of homicide victims by age for the
state in 2009. The brief will be distributed to the Anti-Crime Council, which is
comprised of the heads of criminal justice agencies and the press. The brief
should be no longer than 6 (single-spaced) pages.
Developing Spreadsheets for the Research Brief
For this assignment, you need to construct spreadsheets on the following:
• Violent Crimes
o Data for the total violent crime rates in MA and your comparison
state from 2000-2010
o Data for a specific UCR violent crime (pick 1 of the following:
murder and nonnegligent manslaughter (criminal homicide),
forcible/legacy rape, aggravated assault, or robbery) rate in MA and
your comparison state from 2000-2010
• Property Crimes
o Data for the total property crime rates in MA and your comparison
state from 2000-2010
o Data for a specific UCR crime property (pick 1 of the following:
burglary, larceny-theft, or motor vehicle theft) rate in MA and your
comparison state from 2000-2010
• Homicide Victims by Age
o Data for the percentage of homicide victims by age group in MA for
2009
Remember, Excel allows you to have multiple spreadsheets within one file. Use
the workbook tabs, located in the bottom left corner in Excel to store each of your
spreadsheets (i.e., total violent and specific violent, total property and specific
property, and homicide victims by age). You can also rename the workbooks by
right-clicking on the tab. This will help you keep track of where your data are.
Review the sample spreadsheet in this week’s lecture or in the Assignment 1
folder for an example of how you should set up your spreadsheets.
Keep in mind that you want to report crime rates, so you have a standard by
which to compare your states. You also wish to report the percent/rate of change
over time from 2000-2010, as well as the average rates, so you will need cells
that estimate these values for each state.
Formulas:
• 𝑅𝑎𝑡𝑒 = (
𝑜𝑓 𝑐𝑟𝑖𝑚𝑒𝑠
𝑝𝑜𝑝𝑢𝑙𝑎𝑡𝑖𝑜𝑛) (100,000)
• 𝑅𝑎𝑡𝑒 𝑜𝑓 𝑐ℎ𝑎𝑛𝑔𝑒 = (
(𝑛𝑒𝑤𝑒𝑠𝑡 𝑟𝑎𝑡𝑒−𝑜𝑙𝑑𝑒𝑠𝑡 𝑟𝑎𝑡𝑒)
𝑜𝑙𝑑𝑒𝑠𝑡 𝑟𝑎𝑡𝑒 ) (100)
• 𝐴𝑣𝑒𝑟𝑎𝑔𝑒 𝑅𝑎𝑡𝑒 =
sum of the rates
11
Even though it is over a 10-year period, if you count the number of rates during that period there are 11!
Helpful Hints:
• Plan ahead and be sure to save your work often and more than one place!
• Since the data structure is similar for these spreadsheets, you can cut and
paste the years column and the population column instead of doing each
four times.
• Do not try and crowd five tables and graphs onto one page!
• Imagine this workbook may be used by some coworkers as well. Try to
make it look nice using the techniques discussed in Labs 1 and 2 (e.g.,
make the title stand out, use text-wrapping for the column titles, etc.).
• Make sure your data entry is correct. If any of the rates do not seem to
make sense (e.g., they seem absurdly large or small), you probably have
a data entry error!
Developing Graphs for the Research Brief
For this assignment, you need to construct five graphs:
• A line graph for the total violent crime rates in MA and your comparison
state from 2000-2010.
• A line graph for the total property crime rates in MA and your comparison
state from 2000-2010.
• A line graph for a specific UCR violent crime (pick 1 of the following:
murder and nonnegligent manslaughter (criminal homicide),
forcible/legacy rape, aggravated assault, or robbery) rate in MA and your
comparison state from 2000-2010.
• A line graph for a specific UCR property crime (pick 1 of the following:
burglary, larceny-theft, or motor vehicle theft) rate in MA and your
comparison state from 2000-2010.
• A pie chart or a bar graph for the percentage of homicide victims by age
group in MA for only 2009 (think carefully about which one is better!).
Be sure when constructing these graphs to:
• Give them a title and appropriate figure number (e.g., Figure 1: UCR
Violent Crime Rates in Massachusetts and New York: 2000-2010).
• Label the X and Y axes appropriately; if you do not have a place to insert
these labels, select the chart and under the Layout tab, select Axis Titles
from the Labels group, and select the appropriate axis lab (refer to Lab 2
for more specific instructions).
• Label your data series/legend (e.g., display which data belong to MA and
which belong to the comparison state).
• Finally, cite the source for the data along the bottom portion of each
graph. Do that by following these steps:
- To make space at the bottom of the graph for the source, you need
to resize the plot area. First, select the plot area (click on the line
space). Resize the plot area to make space for the source by using
the selection handles and making this area smaller. - To add the source, select the chart, then under the Layout tab,
select Text Box from the Insert group. Click the lower left inside of
the chart area to select a spot to place your source information.
Type in the source of the data; the Bureau of Justice Statistics
for the line graphs and the Registry of Vital Records and
Statistics for the percentage of homicide victims by age graph.
When you are done, click outside of the text box, click on one of the
selection handles and resize it to fit into the graph. You will have to
fiddle with this a bit to ensure the entire source is visible and does
not overlap with any other part of your graph, but it gets easier with
practice.
a. You should not be inserting the source once the graphs have
been moved from Excel to Word. If this happens in your
final paper, you will lose formatting points.
Writing the Research Brief
By this point you should have your spreadsheets and graphs completed. This is
the point at which your brief begins—now that you have the data, you must report
it to your audience. Thus, some writing is in order!
Using Microsoft Word, develop text around the following:
• Discuss the purpose of your brief and what it will cover.
• Describe the data you are using (i.e., provide background information on
the data) and how they are generated (assume your reader knows nothing
about the UCR).
• Define the eight Part I offenses for the UCR.
• Present the data in graphical form.
o For the line graphs, discuss the overall trend (increasing,
decreasing, remaining about the same, etc.), the percent/rate of
change for the 10-year period, and the average rate for MA and the
comparison state.
o For the pie chart/bar graph, is there some kind of pattern? What is
it? What group has the highest/lowest percentage of victims?
• Discuss the limitations of the data (the six drawbacks of the UCR
discussed in the lecture).
• Summarize what you have found