Data analytics project: Gross profit analysis for introductory financial accounting

This blog post contains a data analytics project that is suitable for introductory financial accounting. The student uses Excel to analyze a data set that contains more than 48,000 records. The data analytics project can be assigned early in the course once the concept of gross profit is reviewed.

Overview of project

This project uses sales and cost data for a fictitious cabinet hardware company, Cabinet Accessories Company (CAC.) 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. The student also needs to cleanse the data set by finding and correcting errors in the data set. In addition, the student will create a pivot chart.

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 Tracie Miller Nobles for her helpful feedback.

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.

Role of the instructor

A little background here on these data analytics projects I am sharing on this blog. Our students need to learn to analyze data and think critically. New AACSB accounting standards essentially call for data analytics throughout the accounting curriculum. We can use Excel in the introductory courses to get students used to working with data and thinking about what the data shows. Excel is an appropriate tool at this early level because students are likely to have access to it and have some familiarity with it.

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 that I include, 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.

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 “CAC gross profit analysis” in the subject line so I know to which data analytics project you are referring.

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: Gross profit analysis for introductory financial accounting”

  1. Wendy – this is FANTASTIC! you did such a great job with this. I’m using it for sure this semester!

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: