May 4, 2018 
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
- City of Somerville activity – FA – transaction analysis (updated 5/9/18)
- Full data set (Excel file) (updated 5/7/18)
- Tutorial video (YouTube link) (updated 6/12/18)
- Annotated slides for tutorial video (pdf file) (updated 6/12/18)
- Tutorial data set (Excel file)
- Customizable grading rubric (Excel file)
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
Accounting in the Headlines 
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?
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!