May 10, 2018 
As promised in my data analytics introductory financial accounting project blog post, here is the second data analytics project I have developed. This one covers budgeting and performance evaluation in introductory managerial accounting.
Background
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.) If a student becomes familiar with pivot tables, pivot charts, and dashboards in Excel, the transition to Power BI or Tableau in later classes will be easier. The specific tools we teach are not as important as the ability to learn, adapt, and interpret.
Role of the instructor
What I am providing in these projects are the data sets, step-by-step tutorials for the Excel portion of the project, and some discussion questions for selected project requirements. These projects are not meant to be stand-alone lessons. The instructor will need to introduce the topic of data analytics, Excel tools, and the like. The instructor can help the student to develop the critical thinking skills needed to interpret the Excel results. Also, the instructor can also add tips and hints for using Excel effectively and efficiently. In the step-by-step tutorial videos, I am showing just one way how to do each step. You may very well have another way to accomplish the same thing more efficiently, so you can share that with your students.
Overview of this 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. The student will also prepare a budget variance report, using the information from a pivot table, and will analyze the variances.
Project resources
- Project requirements (Word doc)
- Full data set (Excel file)
- Tutorial video (YouTube link)
- Annotated slides for tutorial video (pdf file)
- 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.
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. (There is a separate request form for each project.) 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.
Preview of future projects
I am developing 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 data analytics projects I post will be appropriate for introductory financial accounting or managerial accounting – and possibly other classes with adaptation.
Feedback welcomed
If you have any suggestions or questions, please feel free to email me at wtietz@kent.edu. When you email me, please refer to“Somerville budget and performance evaluation project” so I know to which data analytics project you are referring.
Accounting in the Headlines 
I love this and will integrate it in my undergraduate Government and Not-for-Profit accounting class next year. I’ve been thinking about how I can integrate data analytics in this course and your project seems to be a perfect fit! Thank you for this great learning resource.
Great, glad it is useful for you. You can also do something similar with the City of Austin, Texas – Austin has also put its checkbook online. It is structured a bit differently, but it may be an option for you also.
The games you have out there are great. This will be very helpful in my intro class.