Data Analytics Requirement

1

A325 Cost Accounting

Data Analytics Case – KAT Insurance Corporation Student Guide for Tableau Project

Overview In this case, you will use 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.

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 8. Create a bar chart in the view 10. Create a map chart

 

 

2

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.

Requirement A (60 points) The original data set has typographical errors in the Region and Insurance Type fields. Use Excel to find and correct those errors in the data set.

You will turn in a Tableau file with .twbx extension showing the completed results in tables AND graphs to support the following tasks.

• Generate a new sheet AND a graph ranking profitability by insurance type. The profitability is measured by total contribution margin.

• Generate a new sheet AND a graph ranking profitability by insurance type. The profitability is measured by contribution margin ratio.

• Generate a new sheet AND a graph ranking profitability by state. The profitability is measured by contribution margin ratio.

• Generate a map chart to show profitability of each state on the map.

Requirement B (40 points) Answer the following questions.

1. What is the trend or takeaway you have observed in the results of profitability ranking (total contribution margin) by insurance type?

2. What is the trend or takeaway you have observed in the results of profitability ranking (contribution margin ratio) by insurance type?

3. Does the ranking by total contribution margin look similar to the ranking by contribution margin ratio? Explain why they agree or disagree.

4. Based on your answers for Requirement A, what was the average of the contribution margin for home insurance sales?

5. Based on your answers for Requirement A, what was the sum of the contribution margin ratio for professional insurance?

6. Based on your answers for Requirement A, what was the sum of the contribution margin ratio for Pennsylvania

7. Based on your answers for Requirement A, what state had the highest sum of contribution margin? 8. Based on your answers for Requirement A, what was the most profitable state in the Midwest

region as measured by the contribution margin?

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.

 

 

3

• 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. • Variable Cost Percent: This field contains the variable cost of each policy.