Data analytics project: Transaction analysis for introductory financial accounting using the City of Somerville, MA, online checkbook data set

Overview of project

This project uses the transaction data from the City of Somerville, Massachusetts, which posts its checkbook online for public use. In this project, the student will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, and create calculated columns. In addition, the student will create a pivot chart. Essentially, the student is analyzing the transaction data from Somerville to answer several questions using pivot tables and charts in Excel.

Project resources

The tutorial video has closed captioning; the annotated slides for the tutorial video have the same script. The tutorial data set is an Excel file containing a small subset of the full data set; I used this small data set in creating the tutorial video.

Office 365 or Excel 2016 (newest versions)

Anyone doing this project will need to have either Office 365 or Excel 2016. Updates Microsoft has made to Excel 2016 include the ability to automatically group dates by quarters and years (an integral part of this project), so if a student says that their pivot table does not have quarters or years (or any other issue), tell them to update their version of Office 365 and/or Excel 2016.

Solution

If you would like to have a copy of the Excel solution file, please fill out the request form. Once I verify your status as an instructor, I will email the file to you within a week. Please do not post the solution file on the internet and please do not share the solutions file with your students. The Excel solutions file is for your own use only.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.