Due May 21. Late submission deadline May 28
Textbook Chapters:
In this assignment you will create a worksheet showing the number of occurrences of identity theft for the top ten metropolitan areas for 2008 through 2006.
1. Go to the Federal Trade Commission's Identity Theft data at http://www.ftc.gov/bcp/edu/microsites/idtheft/reference-desk/national-data.html . This site provides data about identity theft and fraud on a state by state, year by year and metropolitan area basis.
2. Click the PDF link under the heading FTC Compliant Data, ID Theft Clearinghouse Data January 2008-December 2008. A file should open with the 2008 data. Go to page 17 out of 101 of the PDF file to see the "Largest Metropolitan Areas Ranking for Identity Theft." The page number shows as 16 at the bottom of the page. Make sure you are not looking at the previous page containing fraud data.
3. Open a new Excel workbook.
4. Click in cell A1 and enter a descriptive title for the worksheet. The worksheet will show the number of ID Theft Complaints per 100,000 people in the top ten metropolitan areas.
5. Click in cell A2 and enter your name.
6. Starting in cell A4, enter the first 10 metropolitan area names from page 17 down the column. Skip the "Metropolitan Statistical Area" part. Just use the area name and state. You can copy and paste from the PDF file. You should have labels for Brownsville-Harlington, TX through Vallejo-Fairfield, CA.
7. Starting in cell B3, enter the following labels, one per cell across the row: 2008 Rank, 2007 Rank, 2006 Rank, 2008, 2007, 2006. Press Alt+Enter between the year and the word Rank to wrap the labels in B3, C3 and D3
8. Enter the rankings and number of complaints per 100,000 for 2008 from the PDF file.
9. Go back to the FTC web page list of PDF files for each year. Open the PDF file for 2007 and go to page 18 of 92 (page 17 at the bottom of the page). Locate the identity theft data for the same 10 areas. The order is different.
10. Enter the data for 2007 in the proper columns of your worksheet.
11. Go back to the FTC web page list of PDF files for each year. Open the PDF file for 2006 and go to page 8 of 13. Locate the identity theft data for the same 10 areas. The order is different.
12. Enter the 2006 data in the proper columns of your worksheet.
13. In cell H3, enter the column label: % Change 06 to 08
14. Click in cell H3 and click the Wrap text button in the Home tab of the ribbon. Excel 2003 users click after the e in Change and press Alt+Enter from the keyboard to wrap the text.
15. In cell H4, enter a formula to calculate the percent change. The formula should take the number for 2008 and subtract the number for 2006, then divide the result by the 2006 number. Remember the order of operations when creating this formula. Multiply and divide are completed first (left to right), then plus and minus. You can force a different order by using parenthesis.
16. Format the percent change as % with one decimal place.
17. Use the fill handle to copy the formula down for the other metropolitan areas.
18. In cell A14, type the label: Average
19. In cell E14, enter the AVERAGE function to calculate the average for 2008. Do not include the column label in the cell range.
20. Use the fill handle to copy the function across the row for all years.
21. Format all of the identity theft numbers and averages to have one decimal place.
22. Merge and center the title in A1 across columns A - H.
23. Use a fill color of your choice for the title.
24. Make the font size of the title larger so it fills the area over the data nicely (or smaller if you made a very long title).
25. Adjust column widths as needed so they just fit the data and all data displays.
26. Add a border of your choice.
27. Right-align the column headings and apply some other formatting to make them look attractive.
28. Next you will make a chart showing the percent increase in identity thefts for the top five areas. Select the first 5 area names. Then hold down the Ctrl key on the keyboard and select the corresponding numbers in the percent change column. Both columns of data should be selected at one time. Insert a column chart.
29. Give the chart a descriptive chart title.
30. Delete the legend that says Series 1
31. Move the chart under the data and resize it to be as wide as the data
32. Use Format Selection to add an attractive background to the chart.
33. Change the Sheet1 tab to a descriptive name.
Save this worksheet as Asn11. Upload to Assignments, Tests and Surveys.
Answer the following questions in the answer box in Etudes or save as a Word document and upload.
Search the Internet for an article that discusses recent trends in identity theft (published in 2008 or later). Make sure the article discusses causes of the trend.
1. What is the title of the article?
2. What is the URL?
3. What trend does the author see?
4. Describe the factors the author believes is causing this trend.
5. Does this author's trend match the data in the worksheet you created? Why or why not?