Managerial Accounting Data Analytics

Posted on by

KAT Insurance Corporation:
Introductory Managerial Accounting Data Analytics Case
Student Guide for Tableau Project

Overview
In this case, you will be using Tableau to analyze the sales and cost transactions for an insurance company. You will first have to find and correct errors in the data set using Excel. Using Tableau, you will then sort the data, join tables, format data, filter data, create a calculated field, create charts, and other items, and will draw conclusions based on these results. A step-by-step tutorial video to guide you through the Tableau portions of the case analysis is available.

General learning objectives
1. Clean the data in a data set
2. Analyze sales trends
3. Interpret findings

Tableau learning objectives
1. Join two tables
2. Create calculated fields
3. Build visualizations by dragging fields to the view
4. Format data types within the view
5. Filter data in Tableau visualization
6. Format data within the Tableau visualization
6. Utilize the Marks card to change measures for sum, count and average
8. Sort data in visualization by stated criteria
9. Create a bar chart in the view
10. Create a map chart
 
KAT Insurance Corporation:
Introductory Managerial Accounting Data Analytics Case Handout

Overview
The demand for college graduates with data analytics skills has exploded, while the tools and techniques are continuing to evolve and change at a rapid pace. This case illustrates how data analytics can be performed, using a variety of tools including Excel, Power BI and Tableau. As you analyze this case, you will be learning how to drill-down into a company’s sales and cost data to gain a deeper understanding of the company’s sales and costs and how this information can be used for decision-making.
Background
This KAT Insurance Corporation data set is based on real-life data from a national insurance company. The data set contains more than 65,000 insurance sales records from 2017. All data and names have been anonymized to preserve privacy.
Requirements
To follow are the requirements for analyzing sales records in the data set.
1. There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors.
2. Calculate the variable cost and contribution margin for each policy sold.
3. Total the sales revenue, variable cost, and contribution margin for each Insurance Type.
a. Which Insurance Type had the highest total contribution margin?
b. Which Insurance Type had the lowest total contribution margin?
c. How many insurance policies were sold in each Insurance Type?
d. What is the average contribution margin per policy in each Insurance Type?
4. Calculate the contribution margin ratio for each policy. Rank the Insurance Type field from the highest contribution margin ratio to lowest contribution margin ratio. Do these rankings agree with the rankings you found in Requirement 3? Should these two rankings always be the same? Explain.
5. Calculate the contribution margin ratio for each state. Rank the states from the highest contribution margin ratio to the lowest contribution margin ratio. Which states had a contribution margin ratio greater than 75%?
6. Within each region, what was the most profitable state in the most recent year, as measured by the contribution margin ratio? The least most profitable state in each region?
7. Analyze all the information you have gathered or created in the preceding requirements. What trends or takeaways do you see? Explain.

 
Data dictionary for main data set
• Region: This field contains the region in which the insurance was sold. There are six regions: Midwest, New England, North Central, Northeast, Southeast, and West.
• State: This field contains the state in which the insurance policy applies. The data is from sales to the 48 states in continental US and the District of Columbia. (KAT Insurance does not offer insurance in the states of Alaska and Hawaii.)
• Salesperson: This field contains the name of the salesperson who sold the policy.
• Insurance Type: This field contains the type of insurance policy.
• State Type: This field is a combination of the State and Insurance Type fields.
• Sales: This field contains the selling price of the insurance policy.
• Date of Sale: This field contains the date that the policy was sold.
• Invoice No: This field contains the invoice number.
• Country: This field contains the country in which the policy was sold. At this time, KAT Insurance only sells policies in the US.
Separate data table for variable cost percentages
• State Type: This field is a combination of the State and Insurance Type fields.
• Variable Cost Percent: This field contains the variable cost of each policy.
Step-by-step tutorial video
The step-by-step tutorial video for this case can be viewed at this link: http://tiny.cc/kat-ma-tableau. These tutorial videos walk through the steps needed for the Tableau portion of the case. The tutorial videos are based on a 24-record subset of the main data set, so the steps will be the same for the complete data set. As an alternative to the video, the scripted slides in pdf format can be downloaded at this link: http://tiny.cc/kat-ma-tableau-pdf.
How to obtain Tableau
You need a licensed copy of Tableau for this project. Tableau is provided at no cost to students. Here are the steps to request your license key for Tableau:
1. Go to the Tableau for Students site: www.tableau.com/students
2. Select the “Get Tableau for Free” button, and fill out the form
3. You should receive your key in a few hours once the form is submitted and Tableau verifies that you are enrolled at your university or school
4. While you wait for your key, you can download the 14-day trial (http://www.tableau.com/products/desktop/download), and start working with Tableau immediately
Tableau file saving instructions
If you have to turn in your Tableau files to your instructor, you need to be sure to save your Tableau files as .twbx extensions (as a packaged workbook), rather than as a .twb file, so your instructor can view your completed work. A .twbx file is a Tableau Packaged Workbook, which includes the original .twb file grouped together with the the datasource(s) in one package. A .twbx file is similar to a zip file, which will contain all the necessary information for the files to be opened in Tableau. These instructions are also in the step-by-step tutorial video.