NEW! Data analytics project: Contribution margin analysis and segment margins for introductory managerial accounting

This blog post contains a data analytics project that is suitable for introductory managerial accounting. The student uses Excel to analyze a data set that contains more than 48,000 records. The data analytics project can be assigned in the course once the concepts of contribution margin, contribution margin ratio, and segment margins 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

• Cabinet Accessories Company contribution margin, CM ratio, and segment margin activity requirements (Word file)
• Full data set (Excel file)
• Tutorial video (YouTube link)
• Annotated slides for tutorial video (pdf file)
• Tutorial data set (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 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, usually 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 contribution margin project” in the subject line so I know to which data analytics project you are referring.

Wendy