Assignment 1 Description 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: 1. 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. 2. 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 beginsnow 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. Information about the UCR can be found online at the FBI's website and in the Week 2 lecture on crime measurement. Again, you are not required or expected to do outside research for this assignment. All the information I expect to see in your paper can be found in the lecture! The Part I offense definitions and limitations of the data can be found in the Week 2 lecture as well. Any outside sources that you use must be properly cited at the end of your paper (i.e., a works cited page in APA format). Remember, if it is not common knowledge, it requires a citation (even the material you take from the lecture)! You must complete some of this write-up before you begin Lab 3, which will show you how to integrate both the text you have written and the graphs you have created. Assignment 1 Grading Criteria Charts: 60% An appropriate title A figure number Correct labels for the X- and Y-axis Correct legend Correct values for the data (rates!) Source properly cited Text: 40% Introduction/Purpose of. the brief Background information on the data Part I offense definitions Text about the graphs Limitations of the data Summary of results Overall quality of writing