KAT Insurance Corporation: Introductory Financial Accounting Data Analytics Case Requirements First you will need to complete the tutorial which leads you through step by step instructions for analyzing sales records of the tutorial data files. You will use these same steps to complete the requirements of the KAT Insurance project and answer the questions below. Once you have completed your work on the actual project data files you will use your spreadsheet to answer various questions on a Quiz related to the requirements of this assignment. Note there are 2 data files included here; one for the tutorial, and for for the actual project. Make sure you use the correct database for the project. Do not open the Quiz until you are done with the excel data set work and ready to use it to answer questions as you will only get one attempt. You are also required to upload your excel files (including analysis) for grading. For the KAT Insurance Data File complete the following requirements and make note of these items for your Quiz: There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors. Rank the states from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest. o Which state had the highest sales? What was the total dollar amount? o Which state had the lowest sales? What was the total dollar amount? . What is the average amount of insurance sold per state? o How many insurance policies were sold in each state? o Do any states not meet the $800,000 minimum sales level? o What factors might impact a state's sales level? Sort the state data by average policy amount, from highest to lowest.Rank the regions from the highest total insurance sales to lowest total insurance sales. . Which state had the highest average policy amount? o Which state had the lowest average policy amount? o How could geographical region impact the average policy amount? Sort the data by sales, from highest to lowest.Create a graph to show total dollar amount of each type of insurance sold, by region. What does this graph show? (Please note your thoughts on this page) o Who is the leading salesperson in each region? What is the total dollar amount sold for each type of insurance? Create a map chart that shows total sales for each state. What does this map chart illustrate for you? (Please note your thoughts on this page)Which region had the lowest sales? What is the total dollar amount? What Factors may contribute to a region having lower sales than others? o Which region had the highest sales? What is the total dollar amount? What factors may contribute to a region having higher sales than others? o A note on maps - if your version of excel is newer and uses 3D maps instead of the maps mentioned in the tutorial, follow this link to learn how to make a 3D map: 3D Maps You can use these instructions to create a 3D map showing total sales for each state. Please contact your instructor if you have any questions. After completion of all the worksheets take some to time to analyze all the worksheets you have created and briefly discuss what you see being presented in the data once it has been formatted. Respond to each of the above questions in your excel workbook under a new tab labeled MY ANALYSIS. o What outcomes do you believe management of KAT Insurance would find most useful? o What outcome(s) differed from what you might have expected? o What questions could you ask about the regions/populations to gain additional insight on these outcomes? Contribution Margin Region Southeast Southeast West Midwest New England Southeast Southeast Southeast West Midwest Northeast Southeast New England Northeast Northeast West West Northeeast Southeast Southeast Southeast West New England Northeast State Salesperson AR Frank AR Frank CA Kellie CO Paul CT Crissy FL John FL John AR Frank AZ Kellie CO Paul DC Matt AL John CT Crissy DE Fred DE Fred AZ Kellie Kellie DE Fred FL John FL John AL John AZ Kellie CT Crissy DE Fred Insurance Type Auto Auto Auto Auto Auto Auto Auto Disability Dissability Disability Disability Home Home Home Home Life Life Life Life Life Professional Professional Professional Professional Sales $ 935.25 $ 691.93 $ 861.89 $ 447.15 $ 845.98 $ 1,270.15 $ 742.20 $ 640.32 $ 488.15 $ 327.04 $ 451.57 $ 1,080.48 $ 662.19 $ 591.06 $ 1,065.46 $ 284.17 $ 211.20 $ 229.31 $ 153.86 $ 211.20 $ 1,141.53 $ 1,318.00 $ 1,120.73 $ 1,332.95 Vallable Lost Variable Date of Sale Invoice No State Type Percentage Cost 11/8/17 A-2017-054995 AR-Auto 10/13/17 A-2017-050573 AR-Auto 5/19/17 A-2017-024590 CA-Auto 12/3/17 A-2017-059464 CO-Auto 11/17/17 A-2017-056679 CT-Auto 3/28/17 A-2017-015376 FL-Auto 8/26/17 A-2017-041947 FL-Auto 7/11/17 D-2017-033806 AR-Disability 6/20/17 D-2017-030104 AZ-Disability 4/12/17 D-2017-018015 CO-Disability 6/16/17 D-2017-029374 DC-Disability 1/7/17 H-2017-001177 AL-Home 2/15/17 H-2017-008234 CT-Home 2/6/17 H-2017-006570 DE-Home 12/31/17 H-2017-064472 DE-Home 10/26/17 L-2017-052739 AZ-Life 8/8/17 L-2017-038859 AZ-Life 1/17/17 L-2017-002932 DE-Life 12/25/17 L-2017-063304 FL-Life 7/25/17 L-2017-036251 FL-Life 1/7/17 M-2017-001122 AL-Professional 3/5/17 M-2017-011434 AZ-Professional 7/17/17 M-2017-034941 CT-Professional 11/13/17 M-2017-056072 DE-Professional Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA AZ KAT Insurance Corporation: Introductory Financial Accounting Data Analytics Case Requirements First you will need to complete the tutorial which leads you through step by step instructions for analyzing sales records of the tutorial data files. You will use these same steps to complete the requirements of the KAT Insurance project and answer the questions below. Once you have completed your work on the actual project data files you will use your spreadsheet to answer various questions on a Quiz related to the requirements of this assignment. Note there are 2 data files included here; one for the tutorial, and for for the actual project. Make sure you use the correct database for the project. Do not open the Quiz until you are done with the excel data set work and ready to use it to answer questions as you will only get one attempt. You are also required to upload your excel files (including analysis) for grading. For the KAT Insurance Data File complete the following requirements and make note of these items for your Quiz: There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors. Rank the states from the highest total insurance sales to lowest total insurance sales. Sort the data by sales, from highest to lowest. o Which state had the highest sales? What was the total dollar amount? o Which state had the lowest sales? What was the total dollar amount? . What is the average amount of insurance sold per state? o How many insurance policies were sold in each state? o Do any states not meet the $800,000 minimum sales level? o What factors might impact a state's sales level? Sort the state data by average policy amount, from highest to lowest.Rank the regions from the highest total insurance sales to lowest total insurance sales. . Which state had the highest average policy amount? o Which state had the lowest average policy amount? o How could geographical region impact the average policy amount? Sort the data by sales, from highest to lowest.Create a graph to show total dollar amount of each type of insurance sold, by region. What does this graph show? (Please note your thoughts on this page) o Who is the leading salesperson in each region? What is the total dollar amount sold for each type of insurance? Create a map chart that shows total sales for each state. What does this map chart illustrate for you? (Please note your thoughts on this page)Which region had the lowest sales? What is the total dollar amount? What Factors may contribute to a region having lower sales than others? o Which region had the highest sales? What is the total dollar amount? What factors may contribute to a region having higher sales than others? o A note on maps - if your version of excel is newer and uses 3D maps instead of the maps mentioned in the tutorial, follow this link to learn how to make a 3D map: 3D Maps You can use these instructions to create a 3D map showing total sales for each state. Please contact your instructor if you have any questions. After completion of all the worksheets take some to time to analyze all the worksheets you have created and briefly discuss what you see being presented in the data once it has been formatted. Respond to each of the above questions in your excel workbook under a new tab labeled MY ANALYSIS. o What outcomes do you believe management of KAT Insurance would find most useful? o What outcome(s) differed from what you might have expected? o What questions could you ask about the regions/populations to gain additional insight on these outcomes? Contribution Margin Region Southeast Southeast West Midwest New England Southeast Southeast Southeast West Midwest Northeast Southeast New England Northeast Northeast West West Northeeast Southeast Southeast Southeast West New England Northeast State Salesperson AR Frank AR Frank CA Kellie CO Paul CT Crissy FL John FL John AR Frank AZ Kellie CO Paul DC Matt AL John CT Crissy DE Fred DE Fred AZ Kellie Kellie DE Fred FL John FL John AL John AZ Kellie CT Crissy DE Fred Insurance Type Auto Auto Auto Auto Auto Auto Auto Disability Dissability Disability Disability Home Home Home Home Life Life Life Life Life Professional Professional Professional Professional Sales $ 935.25 $ 691.93 $ 861.89 $ 447.15 $ 845.98 $ 1,270.15 $ 742.20 $ 640.32 $ 488.15 $ 327.04 $ 451.57 $ 1,080.48 $ 662.19 $ 591.06 $ 1,065.46 $ 284.17 $ 211.20 $ 229.31 $ 153.86 $ 211.20 $ 1,141.53 $ 1,318.00 $ 1,120.73 $ 1,332.95 Vallable Lost Variable Date of Sale Invoice No State Type Percentage Cost 11/8/17 A-2017-054995 AR-Auto 10/13/17 A-2017-050573 AR-Auto 5/19/17 A-2017-024590 CA-Auto 12/3/17 A-2017-059464 CO-Auto 11/17/17 A-2017-056679 CT-Auto 3/28/17 A-2017-015376 FL-Auto 8/26/17 A-2017-041947 FL-Auto 7/11/17 D-2017-033806 AR-Disability 6/20/17 D-2017-030104 AZ-Disability 4/12/17 D-2017-018015 CO-Disability 6/16/17 D-2017-029374 DC-Disability 1/7/17 H-2017-001177 AL-Home 2/15/17 H-2017-008234 CT-Home 2/6/17 H-2017-006570 DE-Home 12/31/17 H-2017-064472 DE-Home 10/26/17 L-2017-052739 AZ-Life 8/8/17 L-2017-038859 AZ-Life 1/17/17 L-2017-002932 DE-Life 12/25/17 L-2017-063304 FL-Life 7/25/17 L-2017-036251 FL-Life 1/7/17 M-2017-001122 AL-Professional 3/5/17 M-2017-011434 AZ-Professional 7/17/17 M-2017-034941 CT-Professional 11/13/17 M-2017-056072 DE-Professional Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA AZ