City of Somerville, MA: Data Analysis

Data Project Instructions

You are required to analyze the City of Somervilles dataset, and interpret result and report results. A tutorial dataset and the data project dataset are posted in ReggieNet under Resources/Project. Note: this case was written by Wendy M. Tietz, PhD, CPA, CMA, CSCA from Kent State University.

You may complete this project either individually or in a group (of 2 or 3 people). It is due on May 8, 2023. One submission per group (clearly identify names of all team members).

Project background

Paul Revere rode through the City of Somerville, Massachusetts, during his famous Midnight Ride. Its Prospect Hill was where the first Grand Union flag was raised under orders by General George Washington on January 1, 1776.

Today, Somerville is a city with a population of almost 80,000 and is one of the most ethnically diverse cities in the country. It is located just two miles north of Boston and occupies just over 4 square miles.

The City of Somerville, MA, posts its checkbook online for public use. This data analytics activity uses the City of Somerville (Somerville) checkbook dataset for the years 2013 2016

What you need to do:

You will need to write a short report and submit an excel spreadsheet answering the assigned questions.

a) To start the assignment, write down the type of information from the data dictionary you will need to answer each of the questions in the requirements. This must be done before watching the video. This will be included in the report.

b) Next, watch the data analytics tutorial video from Dr. Tietz. Access the video at https://www.youtube.com/watch?v=zLu8FEdnePQ&feature=youtu.be.

c) For each of the following requirements, create a new pivot table in a new worksheet. Name each new worksheet as Req 1, Req 2, etc. Format the dollar amounts in each pivot table or pivot chart using the accounting format with zero decimal places. Format non-currency numbers in each pivot table or pivot chart using the number format with zero decimal places.

d) For each requirement, after creating each pivot table, write out the answers to the questions including your interpretation of what is shown in the pivot table. This will also be included in the report.

Requirements:

1. From 2013 2016, what was the total spending in each of the four calendar years? Is spending trending up, trending down, or remaining stable?

2. In each of the years 2013 - 2016, how much was spent in each of the three categories of government (Education, General Government, and Public Works)? What does the pivot table show you?

3. How much in expenditures did Somerville have in each Item Class in the General Government category for each of the years 2013 2016? What insights can you draw from the pivot table?

The text for the report should be no more than 2 pages.

The report must be submitted on ReggieNet in MS Word or similar software package. The data analysis must be done in MS Excel or a similar spreadsheet package. Do not submit a shared document that can be changed after submission.

Data dictionary

Item Number: This field is a sequential number assigned during the year. The year was added to the item number to create a unique identifier for each transaction.

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

Vendor Name: This field contains the name of the entity related to the transaction.

Amount: This field is the amount of the check.

Check Date: This field is the date that the check was written.

Department: This field is the department within the Category of Government related to

this transaction.

Check # : This field is the sequential check number.

Org Description: This field gives additional detail about the specific organization within

the general Department related to this transaction.

Account Desc: This field provides additional detail about the purpose of the transaction.

Item Class: This is the only fictitious field that was added to Somervilles dataset. This

field attempts to classify Somervilles checkbook items into broad classes for ease of analysis and interpretation.