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 Excel. As you analyze this case, you will be learning how to drill-down into a companys sales and cost data to gain a deeper understanding of the companys 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.

Part 1 -

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. Adapted from case: KAT Insurance Corporation Managerial Accounting by Dr. Wendy Tietz, Jennifer Cainas, and Tracie Miller Nobles

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.

Questions: Using the pivot table created in requirement 2 in Assure Insurance, what was the variable cost of the life insurance policy sold by Bailey on 4/4/17

2. Using the pivot table you created for Requirement 3 in Assure Insurance, what was the sum of variable cost for disability insurance sales?

3.Using the pivot table you created for Requirement 3 in Assure Insurance, what was the average of the contribution margin for home insurance sales?

4.Using the pivot table you created for Requirement 4 in Assure Insurance, which insurance type had the highest contribution margin ratio?

5. Using the pivot table you created for Requirement 5 in Assure Insurance, what was the sum of the contribution margin ratio for Pennsylvania

6.Using the pivot table you created for Requirement 6 in Assure Insurance, what was the sum of the contribution margin ratio for the Northeast region?

Requirement 1: Cell B7: 3,301,627.20

Requirement 2: Cell J25: 432.68

Requirement 3: Cell F7: 341.37

Requirement 4: Cell B6: 76.71%

Requirement 5: Cell B23: 72.67%

Requirement 6: Cell B15: 77.43%