KAT Insurance Corporation: Introductory Financial Accounting Data Analytics Case
Student Guide for Excel Project
Overview
In this case, you will be using Excel to analyze the sales transactions for an insurance company. You will first have to find and correct errors in the data set. You will then create calculated columns, pivot tables, charts, and other items, and will draw conclusions based on these results. A step-by-step tutorial video to guide you through the Excel 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

Excel learning objectives
1. Sort the data

2. Use VLOOKUP function

3. Create a pivot table

4. Format a pivot table

5. Apply filters to a pivot table

6. Create sum, count, and average columns in a pivot table

7. Sort a pivot table by stated criteria

8. Create a pivot chart

9. Create a calculated field

10. Create a map chart

KAT Insurance Corporation: Introductory Financial 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 data to gain a deeper understanding of the company’s sales 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. Rank the states from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest.

a. Which state had the highest sales? What was the total dollar amount?

b. Which state had the lowest sales? What was the total dollar amount?

c. What is the average amount of insurance sold per state?

d. How many insurance policies were sold in each state?

e. Do any states not meet the $800,000 minimum sales level?

3. Sort the state data by average policy amount, from highest to lowest.

a. Which state had the highest average policy amount?

b. Which state had the lowest average policy amount?

4. Rank the regions from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest.

a. Which region had the highest sales? What is the total dollar amount?

b. Which region had the lowest sales? What is the total dollar amount?

5. Who is the leading salesperson in each region?

6. What is the total dollar amount sold for each type of insurance? Create a graph to show total dollar amount of each type of insurance sold, by region. What does this graph show?

7. Create a map chart that shows total sales for each state. What can you surmise from this map chart?

8. 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.

· 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.

· State Type: This field is a combination of the State and Insurance Type fields.

· 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 percentage of each policy.

Step-by-step tutorial video
The step-by-step tutorial video for this case can be downloaded at this link: http://tiny.cc/kat-fa-excel. These tutorial videos walk through the steps needed for the Excel 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-fa-excel-pdf.

Update your version of Excel 2016 or Office 365
To do the project with Excel, you will need to use Office 365 or Excel 2016. Before starting the project, be sure to update your version of Excel. For instructions on how to update your Excel application, search for “how to update Office 365” (or “how to update Excel 2016”) on the internet. Either Windows or Mac will work. The instructions included in the step-by-step tutorial video use Excel for Windows, but Excel for Mac will be similar. Because there are still a few differences between the online version of Excel and the desktop version, the desktop version of Excel is recommended. The mobile app for Excel is also not full-featured and will not be adequate for this project.
Student Guide (in modules)
Tutorial Data Set (in modules)
Excel Project Tutorial Video https://youtu.be/I3yq2X5mvjU (Links to an external site.
Data Set A (in modules)

To start, read over the student guide. Once you’re familiar with the project, complete the tutorial using the tutorial data set while following along with the tutorial video. You’ll then complete your own project using data set A..