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 companys sales data to gain a deeper understanding of the companys 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.

State Type Variable Cost Percent (data set) AL-Auto 0.58 AL-Disability 0.3 AL-Home 0.22 AL-Life 0.09 AL-Professional 0.29 AR-Auto 0.54 AR-Disability 0.27 AR-Home 0.26 AR-Life 0.11 AR-Professional 0.31 AZ-Auto 0.61 AZ-Disability 0.29 AZ-Home 0.18 AZ-Life 0.12 AZ-Professional 0.31 CA-Auto 0.53 CA-Disability 0.29 CA-Home 0.19 CA-Life 0.12 CA-Professional 0.17 CO-Auto 0.58 CO-Disability 0.28 CO-Home 0.23 CO-Life 0.12 CO-Professional 0.28 CT-Auto 0.51 CT-Disability 0.3 CT-Home 0.21 CT-Life 0.13 CT-Professional 0.27 DC-Auto 0.47 DC-Disability 0.26 DC-Home 0.25 DC-Life 0.11 DC-Professional 0.18 DE-Auto 0.38 DE-Disability 0.27 DE-Home 0.19 DE-Life 0.1 DE-Professional 0.28 FL-Auto 0.45 FL-Disability 0.32 FL-Home 0.22 FL-Life 0.12 FL-Professional 0.32 GA-Auto 0.38 GA-Disability 0.27 GA-Home 0.23 GA-Life 0.09 GA-Professional 0.25 IA-Auto 0.49 IA-Disability 0.27 IA-Home 0.25 IA-Life 0.09 IA-Professional 0.25 ID-Auto 0.58 ID-Disability 0.3 ID-Home 0.22 ID-Life 0.12 ID-Professional 0.14 IL-Auto 0.48 IL-Disability 0.31 IL-Home 0.25 IL-Life 0.1 IL-Professional 0.15 IN-Auto 0.35 IN-Disability 0.27 IN-Home 0.2 IN-Life 0.11 IN-Professional 0.3 KS-Auto 0.46 KS-Disability 0.32 KS-Home 0.22 KS-Life 0.12 KS-Professional 0.24 KY-Auto 0.52 KY-Disability 0.32 KY-Home 0.21 KY-Life 0.12 KY-Professional 0.2 LA-Auto 0.37 LA-Disability 0.32 LA-Home 0.2 LA-Life 0.12 LA-Professional 0.28 MA-Auto 0.37 MA-Disability 0.31 MA-Home 0.27 MA-Life 0.13 MA-Professional 0.28 MD-Auto 0.52 MD-Disability 0.26 MD-Home 0.23 MD-Life 0.1 MD-Professional 0.17 ME-Auto 0.47 ME-Disability 0.26 ME-Home 0.25 ME-Life 0.1 ME-Professional 0.16 MI-Auto 0.55 MI-Disability 0.27 MI-Home 0.2 MI-Life 0.1 MI-Professional 0.32 MN-Auto 0.54 MN-Disability 0.32 MN-Home 0.25 MN-Life 0.12 MN-Professional 0.32 MO-Auto 0.38 MO-Disability 0.29 MO-Home 0.21 MO-Life 0.09 MO-Professional 0.29 MS-Auto 0.44 MS-Disability 0.31 MS-Home 0.24 MS-Life 0.12 MS-Professional 0.16 MT-Auto 0.58 MT-Disability 0.28 MT-Home 0.21 MT-Life 0.11 MT-Professional 0.19 NC-Auto 0.5 NC-Disability 0.29 NC-Home 0.25 NC-Life 0.13 NC-Professional 0.26 ND-Auto 0.39 ND-Disability 0.27 ND-Home 0.25 ND-Life 0.1 ND-Professional 0.27 NE-Auto 0.52 NE-Disability 0.3 NE-Home 0.23 NE-Life 0.11 NE-Professional 0.29 NH-Auto 0.48 NH-Disability 0.31 NH-Home 0.26 NH-Life 0.13 NH-Professional 0.17 NJ-Auto 0.54 NJ-Disability 0.27 NJ-Home 0.18 NJ-Life 0.09 NJ-Professional 0.22 NM-Auto 0.36 NM-Disability 0.26 NM-Home 0.27 NM-Life 0.11 NM-Professional 0.14 NV-Auto 0.62 NV-Disability 0.28 NV-Home 0.18 NV-Life 0.13 NV-Professional 0.18 NY-Auto 0.46 NY-Disability 0.32 NY-Home 0.22 NY-Life 0.12 NY-Professional 0.17 OH-Auto 0.44 OH-Disability 0.26 OH-Home 0.23 OH-Life 0.09 OH-Professional 0.29 OK-Auto 0.44 OK-Disability 0.31 OK-Home 0.18 OK-Life 0.13 OK-Professional 0.26 OR-Auto 0.52 OR-Disability 0.26 OR-Home 0.25 OR-Life 0.09 OR-Professional 0.24 PA-Auto 0.42 PA-Disability 0.3 PA-Home 0.22 PA-Life 0.13 PA-Professional 0.18 RI-Auto 0.51 RI-Disability 0.29 RI-Home 0.27 RI-Life 0.11 RI-Professional 0.18 SC-Auto 0.62 SC-Disability 0.32 SC-Home 0.22 SC-Life 0.13 SC-Professional 0.14 SD-Auto 0.39 SD-Disability 0.29 SD-Home 0.17 SD-Life 0.1 SD-Professional 0.16 TN-Auto 0.45 TN-Disability 0.26 TN-Home 0.18 TN-Life 0.11 TN-Professional 0.24 TX-Auto 0.55 TX-Disability 0.31 TX-Home 0.22 TX-Life 0.11 TX-Professional 0.29 UT-Auto 0.48 UT-Disability 0.28 UT-Home 0.19 UT-Life 0.12 UT-Professional 0.21 VA-Auto 0.35 VA-Disability 0.32 VA-Home 0.23 VA-Life 0.13 VA-Professional 0.2 VT-Auto 0.55 VT-Disability 0.31 VT-Home 0.19 VT-Life 0.09 VT-Professional 0.26 WA-Auto 0.52 WA-Disability 0.27 WA-Home 0.26 WA-Life 0.1 WA-Professional 0.22 WI-Auto 0.57 WI-Disability 0.27 WI-Home 0.17 WI-Life 0.12 WI-Professional 0.16 WV-Auto 0.4 WV-Disability 0.28 WV-Home 0.23 WV-Life 0.09 WV-Professional 0.3 WY-Auto 0.52 WY-Disability 0.32 WY-Home 0.27 WY-Life 0.12 WY-Professional 0.16