Assignment 1

Use the 2_Somerville_2013-2016 Excel data file that contains payments data for 2013 to 2016 for the City of Somerville in Massachusetts. Somerville is a city with a population of 80,000 people and it is located two miles north of Boston and occupies 4 square miles. There are 55,746 records (check payments) and a header row. Assume that the 15 check amounts equal to $0 were checks that were lost in the mail and that were later reissued for the correct amount. Do not treat them as missing values. Use Excel and show your screenshots or text for the requirements.

Data Dictionary

ID: This field identifies each payment uniquely.

GovCategory: This field indicates whether the transaction relates to Education, General Government, or Public Works, the three divisions of government for the City of Somerville.

VendorName: This field contains the name of the check payee. Amount: This field is the dollar amount of the check.

CheckDate: This field is the date that the check was written.

Department: This field is the department in the Category of Government related to this payment.

CheckNum: This field is the sequential check number.

OrgDescription: This field gives additional detail about the specific organization within the general Department related to this payment.

AcctDescription: This field provides details about the type of expense.

ExpenseType: This field classifies the payments into broad expense classes.

Required:

Prepare the data profile for the payments data using five categories, $500.00 and over, $0.01 to

$499.99, equal to zero, $-0.01 to $-499.99, $-500.00 and under, & show Count, % of Total, $ Amount and % of Total of each category. Show a screenshot of your data profile.

Prepare the data profile for the payments data using the NigriniCycle.xlsx template. Use the five categories, $500.00 and over, $0.01 to $499.99, equal to zero, $-0.01 to $-499.99, $-500.00 and under, & show Count, % of Total, $ Amount and % of Total of each category. This means that you can update all the formulas in the DataProfile tab to reflect the definition of the five categories. Show a screenshot of your data profile. A blurred clue is shown below.

Create a histogram of the check payment amounts using 21 intervals (0.01-4,999.99, 5,000-9,999.99,

, 100,000 and higher). The last interval is for all payments 100,000 and higher. The x-axis labels should show the integer values of the upper bounds of the intervals (4,999, 9,999, 14,999, 19,999,

, 99,999, 100,000+). Be sure to add a graph title and to label the x-axis and the y-axis with descriptive words and text that can be easily read on a screen. A blurred clue is shown below: 14

4. Create a periodic graph of the monthly (January to December) totals for the invoices. There should only be 12 bars, one bar for each month in a year. The total for January should be the total for all four Januaries in the data (2013, 2014, 2015, and 2016), and so on for February to December. A blurred clue is shown below: