KAT Insurance Corporation: Introductory Managerial Accounting Data m Case Overview The demand for business school graduates with data agalyti/gvsvskills has exploded, while the tools and techniques are continuing to evolve and change at a rapid pace. This case illustrates how data Moan be performed, using a variety of tools including Excel, Power BI and Tableau. As you analyze this case, you will be learning how to use EXCEL 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 (meta/KB). The data set contains more than 65,000 insurance sales records from 2017. All data and names have been Wm preserve privacy. Requirements The following are the requirements for analyzing sales records in the data set (the PDF \"ACCT 3000 51a; l'ns. Tutorial\" will walk you through requirements 1 6). You \\.-'-.-'l|| be required to create \\.-*-.-'orkshccts v-rithin your EXCZL file please insert your responses to the questions below in the related worksheet. 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 acontribution 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. A B C D E F G H K L State Variable Cost Variable Contribution Region State Salesperson Insurance Type Sales Date of Sale Invoice No Type Percentage Cost Margin Country 2 Midwest TX Tom Auto 883.03 01/01/2017 A-2017-000010 TX-Auto USA 3 Southeast TN Jennifer Auto 532.41 01/01/2017 A-2017-000012 TN-Auto USA 4 New England CT Crissy Auto 388.97 01/01/2017 A-2017-000016 CT-Auto USA 5 New England MA Crissy Auto 821.25 01/01/2017 A-2017-000017 MA-Auto USA 6 Southeast GA John Auto 470.51 01/01/2017 A-2017-000028 GA-Auto USA 7 West UT Kellie Auto 552.27 01/01/2017 A-2017-000029 UT-Auto USA 8 West NV Kellie Auto 430.8 01/01/2017 A-2017-000033 NV-Auto USA 9 West WA Peter Auto 943.11 01/01/2017 A-2017-000036 WA-Auto USA 10 North Central MO Mario Auto 401.6 01/01/2017 A-2017-000037 MO-Auto USA 11 Southeast MS Frank Auto 476.35 01/01/2017 A-2017-000041 MS-Auto USA 12 North Central MI Nicole Auto 468.17 01/01/2017 A-2017-000046 MI-Auto USA 13 New England ME Sara Auto 413.5 01/01/2017 A-2017-000050 ME-Auto USA 14 West MT Peter Auto 509.05 01/01/2017 A-2017-000059 MT-Auto USA 15 Northeast DE Fred Auto 520.28 01/01/2017 A-2017-000061 DE-Auto USA 16 New England ME Sara Auto 693.63 01/01/2017 A-2017-000065 ME-Auto USA 17 Southeast TN Jennifer Auto 665.89 01/01/2017 A-2017-000078 TN-Auto USA 18 Midwest KS Tom Auto 519.56 01/01/2017 A-2017-000089 KS-Auto USA 19 Northeast OH Mike Auto 960.22 01/01/2017 A-2017-000096 OH-Auto USA 20 New England MA Crissy Auto 497.6 01/01/2017 A-2017-000099 MA-Auto USA 21 Midwest NE Dave Auto 1070.19 01/01/2017 A-2017-000107 NE-Auto USA 22 Northeast VA Matt Auto 955.79 01/01/2017 A-2017-000113 VA-Auto USA 23 North Central KY Mario Auto 553.44 01/01/2017 A-2017-000114 KY-Auto USA 24 Northeast MD Fred Auto 610.22 01/01/2017 A-2017-000115 MD-Auto USA 25 Southeast FL John Auto 505.55 01/01/2017 A-2017-000117 FL-Auto USA 26 Midwest OK Tom Auto 553.44 01/01/2017 A-2017-000122 OK-Auto USA 27 North Central IA Nicole Auto 795.76 01/01/2017 A-2017-000124 IA-Auto USA 28 Midwest TX Tom Auto 567.7 01/01/2017 A-2017-000128 TX-Auto USA 29 Northeast DC Matt Auto 790.34 01/01/2017 A-2017-000139 DC-Auto USA 30 North Central MN Nicole Auto 429.63 01/01/2017 A-2017-000142 MN-Auto USA 31 New England MA Crissy Auto 346.92 01/01/2017 A-2017-000145 MA-Auto USA 32 Southeast GA John Auto 1138.63 01/01/2017 A-2017-000146 GA-Auto USA 33 Northeast PA Mike Auto 479.4 01/01/2017 A-2017-000153 PA-Auto USA 34 Southeast MS Frank Auto 624.83 01/01/2017 A-2017-000154 MS-Auto USA 35 Northeast PA Mike Auto 1018.12 01/01/2017 A-2017-000166 PA-Auto USA 36 Northeast NJ Fred Auto 598.54 01/01/2017 A-2017-000168 NJ-Auto USA 37 Southeast FL John Auto 424.96 01/01/2017 A-2017-000170 FL-Auto USA