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

Data analytics is here! I have been working on some projects for the past few months to be able to incorporate data analytics into the introductory accounting classes. After thinking about the best tool to use for data analytics in the introductory classes, I decided to use Excel as the analytics tool since introductory accounting students are more likely to be familiar with, and have access to, Excel than other tools such as Power BI or Tableau. (I will be using Office 365 for most of the data analytics projects I post here; instructions will be similar for Excel 2016.)

Note: I will be developing and posting a few data analytics projects that are appropriate for introductory financial accounting and introductory managerial accounting over the next few months, so stay tuned. The next project I am posting relates to managerial accounting, specifically budgeting and performance evaluation.

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.

Special thanks to Dr. Karen Braun, Case Western Reserve University, for her helpful feedback. Among other things, I edited the project requirements to include her suggestions (see Teaching Notes in the revised project requirements document.)

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.

Feedback welcomed

If you have any suggestions or questions, please feel free to email me at wtietz@kent.edu. When you email me, please put “Somerville transaction analysis” in the subject line so I know to which data analytics project you are referring.

Have a great end of the semester!

Wendy

About Dr. Wendy Tietz, CPA, CMA, CSCA, CGMA

Dr. Wendy Tietz is a professor of accounting at Kent State University in Kent, Ohio, USA. She is also a textbook author with Pearson Education.

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

  1. Hello Dr. Tietz,

    I am responsible for teaching corporate finance to employees at Best Buy. I started this job knowing very little about financial statements but I found your accounting in the headlines resource a year ago and it has significantly improved my understanding and ability to make them fun. I am so appreciative that you created this and share it. Seeing as how I am not a finance professional, my only question for you would be on how would someone like me know if I answered correctly? Is there anywhere that provides an answer key? Before, I can use these with our employees I would want to ensure I am giving them accurate information. Can you provide any guidance here?

    • Dr. Wendy Tietz, CPA, CMA, CSCA, CGMA Reply May 4, 2018 at 9:31 am

      The best source would be a good reference book (for example, a textbook) on accounting. This blog is meant to provide material for accounting instructors to use in their teaching, so the thought is that they would have accounting textbooks available. I do not post answers here for two main reasons: 1) Many times, there is no “right” answer – the questions are meant to generate discussion and thought – I do not want to shut down that discussion by recommending one “right” answer; and (2) Many instructors do use the blog posts as graded assignments – I have no way to monitor who would get the solutions. Have a nice day!

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.

%d bloggers like this: