This is to be done in excel . Class is accounting information system . please help me with the steps please.

1. (4 Points)
2. There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors. (Hint: use a Pivot Table or Sort to examine the two fields and look for typos)
3. Complete the "variable cost percentage" column by using a VLOOKUP function. (Hint: use "State Type" as the lookup value and the information in the "VariableCostPct" tab as the table_array).
4. Complete the "Variable Cost Column" (Hint: Variable Cost = Sales * Variable Cost Percentage).
5. Compete the "Contribution Margin Column) (Hint: Contribution Margin = Sales - Variable Cost).
6. (4 Points) - Create 4 separate tabs to answer the following questions. Label the tabs 2a, 2b, 2c,2d. Use separate Pivot Tables to answer the below:
7. Which Insurance Type had the highest sales?
8. Which Insurance Type had the highest total contribution margin?
9. How many insurance policies were sold in each Insurance Type?
10. What is the average contribution margin per policy in each Insurance Type?
11. (5 Points) - Create 5 separate tabs label the tabs 3a-Auto, 3b-Disability, 3c-Home, 3d-Life, 3e-Professional. Each tab will illustrate a respective insurance type. Each tab will illustrate a pivot table and pivot chart. In summary -5 insurance types therefore 5 separate tabs. Tasks:
12. Filter by the respective insurance type as described above
13. Pivot by sum of sales and by state
14. Use value filter to display the top 10 sales
15. Create a Pivot Chart in the same tab (Clustered Column Chart)
16. (2 Points) - Analyze all the information you have gathered or created in the preceding requirements. Create a word document and import at least three pivot charts. Provide a written analysis (few sentences) to describe any trends or takeaways that you have identified.

SafetyCoverInsurance-Data-SetA Q . Search Sheet '+ Share Insert Page Layout Formulas Data Review View Home 2 AutoSum X Cut Calibri (Body) * 11 . A- A- So Wrap Text General Fill Copy Insert Format Sort & E Merge & Center $ % 2 60 $.0 Conditional Format Cell Delete B Clear Filter Paste Formatting as Table Styles Format A1 X fx |Region L M N 0 P Q R S T U V W G H A B D E Variable Cost Variable Contribution Cost Margin Country 1 Region State Salesperson Insurance Type Sales Date of Sale Invoice No State Type Percentage Midwest Tom Auto 883.03 DIV-XI OTOOO0-2TOZ-V ZT/T/T USA TX 3 Southeast IN Jennifer Auto 532.41 1/1/17 A-2017-000012 TN-Auto USA USA 4 New England T Crissy Auto 388.97 1/1/17 A-2017-000016 CT-Auto 5 New England MA Crissy auto 821.25 1/1/17 A-2017-000017 MA-Auto USA USA 6 Auto 470.51 1/1/17 A-2017-000028 GA-Auto Southeas GA John 7 West UT Kellie Auto 552.27 1/1/17 A-2017-000029 UT-Auto USA 8 West NV Kellie 430.8 1/1/17 A-2017-000033 NV-Auto USA Auto 943.11 1/1/17 A-2017-000036 WA-Auto USA 9 Wes WA Pete Auto USA 10 North Central VIC Mari auto 401.6 1/1/17 A-2017-000037 MO-Auto USA 11 Southeast Frank Auto 476.35 1/1/17 A-2017-000041 MS-Auto USA 12 North Central VI icole Auto 468.17 1/1/17 A-2017-000046 MI-Auto 13 New England ME Auto 113.5 1/1/17 A-2017-000050 ME-Auto USA Sara VIT Peter Auto 509.05 1/1/17 A-2017-000059 MT-Auto USA 14 West DE Fred auto 520.28 1/1/17 A-2017-000061 DE-Auto USA 15 Northeast USA 16 New England ME Sara Auto 693.63 1/1/17 A-2017-000065 ME-Auto 17 Southeast Jennifer 665.89 1/1/17 A-2017-000078 TN-Auto USA Auto 519.56 1/1/17 A-2017-000089 KS-Auto USA 18 Midwest Tom Auto USA 19 Northeast OH Mike Auto 960.22 1/1/17 A-2017-000096 OH-Auto 20 New England MA Crissy Auto 497.6 1/1/17 A-2017-000099 MA-Auto Dave auto 1070.19 1/1/17 A-2017-000107 NE-Auto 21 Midwest USA 22 Northeast VA Matt Auto 955.79 1/1/17 A-2017-000113 VA-Auto USA 23 North Central KY Maric Auto 553.44 1/1/17 A-2017-000114 KY-Auto 24 Northeast 610.22 1/1/17 A-2017-000115 MD-Auto USA Fred Auto 25 Southeast John auto 505.55 1/1/17 A-2017-000117 FL-Auto OK Tom 553.44 1/1/17 A-2017-000122 OK-Auto 26 Midwest auto USA 27 North Central IA icole Auto 795.76 1/1/17 A-2017-000124 IA-Auto USA 28 Midwest Tom Auto 567.7 1/1/17 A-2017-000128 TX-Auto USA 29 Northeast Matt Auto 790.34 1/1/17 A-2017-000139 DC-Auto USA 30 North Central MN Nicole auto 429.63 1/1/17 A-2017-000142 MN-Auto 31 New England MA Crissy auto 346.92 1/1/17 A-2017-000145 MA-Auto USA 1138.63 1/1/17 A-2017-000146 GA-Auto USA 32 Southeast GA John Auto USA 33 Northeast PA Mike Auto 479.4 1/1/17 A-2017-000153 PA-Auto USA 34 Southeast VS Frank Auto 624.83 1/1/17 A-2017-000154 MS-Auto 1018.12 1/1/17 A-2017-000166 PA-Auto USA 35 Northeast PA Mike Auto 36 Northeast auto 598.54 1/1/17 A-2017-000168 NJ-Auto USA NJ Fred 37 EL John Auto 424.96 1/1/17 A-2017-000170 FL-Auto USA USA 38 Northeast WV Mike Auto 530.8 1/1/17 A-2017-000178 WV-Auto USA 39 West WA Peter Disability 329.38 1/1/17 D-2017-000008 WA-Disability USA 40 New England RI Crissy Disability 164.92 1/1/17 D-2017-000009 RI-Disability USA 41 Northeas Matt Disability 456.24 1/1/17 D-2017-000015 DC-Disability USA 42 Southeast Frank Disability 238.28 1/1/17 D-2017-000019 MS-Disability LISA 43 Midwest Dave Disability 334.05 AT!qESIO 3N OZOO00 2TOZ-0 ZT/T/T MainData VariableCostPct + + 100% ReadySafetyCoverInsurance-Data-SetA Q . Search Sheet '+ Share Home Insert Page Layout Formulas Data Review View 2 AutoSum X Cut Calibri (Body) * 11 A- A So Wrap Text General Fill Copy E Merge & Center $ ~ % 2 60 .0 Conditional Format Cell Insert Delete Format Sort & Paste B Clear Format Formatting as Table Styles Filter A1 X V fx Region K L M N O P Q R S T U V W A B D E G H 44 Northeast DIC Matt Disability 616.23 1/1/17 D-2017-00002/ DC-Disability USA NE Dave Disability 799.37 1/1/17 D-2017-000030 NE-Disability USA 45 Midwest Paul Disability 1/1/17 D-2017-000042 CO-Disability USA 46 Midwest 967.09 USA 47 Southeast 858 Jennifer Disability 289.67 1/1/17 D-2017-000048 SC-Disability 48 Midwest Paul Disability 244.12 1/1/17 D-2017-000049 CO-Disability USA 49 Southeast AL John Disability 232.44 1/1/17 D-2017-000055 AL-Disability USA VA USA 50 Northeast |Matt Disability 392 1/1/17 D-2017-000058 VA-Disability HC 1/1/17 D-2017-000064 OH-Disability USA 51 Northeast Mike Disability 850.07 USA 52 North Central M Maric Disability 258.13 1/1/17 D-2017-000068 MO-Disability 53 Southeast Jennifer Disability 945.66 1/1/17 D-2017-000081 TN-Disability USA West OR Peter Disability 217.25 1/1/17 D-2017-000083 OR-Disability USA 65 Northeast NV Mike Disability 476.1 1/1/17 D-2017-000084 WV-Disability USA 56 Northeast PA Mike Disability 790.7 1/1/17 D-2017-000093 PA-Disability USA 57 Northeast MID Fred Disability 344.12 1/1/17 D-2017-000100 MD-Disability USA USA 58 North Central MN Nicole Disability 304.85 1/1/17 D-2017-000101 MN-Disability 59 Midwest SD Dave Disability 446.21 1/1/17 D-2017-000104 SD-Disability USA 60 West AZ Kellie Disability 632.61 1/1/17 D-2017-000105 AZ-Disability USA 61 North Central Mario Disability 378.44 1/1/17 D-2017-000106 IL-Disability USA USA 62 New England Crissy Disability 750.28 1/1/17 D-2017-000108 RI-Disability 63 Midwest Dave Disability 774.53 1/1/17 D-2017-000109 NE-Disability USA 64 Northeast Matt Disability 642.67 1/1/17 D-2017-000110 DC-Disability USA 65 West Kellie Disability 308.36 1/1/17 D-2017-000116 NV-Disability USA 66 Northeast Fred Disability 885.88 1/1/17 D-2017-000118 NJ-Disability USA USA 67 Southeast AL John Disability 290.84 1/1/17 D-2017-000119 AL-Disability USA 68 North Central IA Nicole Disability 926.74 1/1/17 D-2017-000121 IA-Disability 69 New England Sara Disability 875.57 1/1/17 D-2017-000125 NH-Disability USA 70 Midwest X Tom Disability 248.79 1/1/17 D-2017-000127 TX-Disability USA USA 71 West Kellie Disability 515.2 1/1/17 D-2017-000131 NV-Disability 72 Midwest Dave Disability 213.75 1/1/17 D-2017-000138 NE-Disability 73 Midwest OK Tom Disability 627.84 1/1/17 D-2017-000143 OK-Disability 74 Southeast Frank Disability 792.42 1/1/17 D-2017-000147 LA-Disability USA 75 North Central KY Maric Disability 273.32 1/1/17 D-2017-000148 KY-Disability USA 76 Midwest Tom Disability 615.16 1/1/17 D-2017-000152 KS-Disability USA 77 Midwest Tom Disability 565.68 1/1/17 D-2017-000162 OK-Disability USA 78 Northeast Matt Disability 394.34 1/1/17 D-2017-000164 NY-Disability USA 79 Southeast .A ran Disability 657.88 1/1/17 D-2017-000167 LA-Disability USA 80 Midwest NE Dave Disability 344.56 1/1/17 D-2017-000171 NE-Disability USA 81 West Peter Disability 302.52 1/1/17 D-2017-000177 ID-Disability USA 82 Northeast DE Fred Home 748.92 1/1/17 H-2017-000001 DE-Home USA USA 83 Southeast A Frank Home 581.88 1/1/17 H-2017-000002 LA-Home 84 Northeast OH Mike Home 572.37 aWOH-HO SOOO00-2TOZ-H ZT/T/T USA USA 85 North Central Mari Home 889.36 1/1/17 H-2017-000014 MO-Home Home USA 86 Midwest Paul 517.92 1/1/17 H-2017-000018 WY-Home USA 87 Southeast John Home 664.44 1/1/17 H-2017-000021 FL-Home K5-Home USA 88 Midwes Fom Home MainData VariableCostPct + + 100% ReadySafetyCoverInsurance-Data-SetA Q . Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share X Cut Calibri (Body) * 11 . A- A Wrap Text General 2 AutoSum Copy Fill Paste Format E Merge & Center $ ~ % 2 60 .0 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear Filter B2 X V fx 0.58 A C D E F G H I J K L M N P Q R S T U V W X 1 State Type Variable Cost Percentage 2 AL-Auto 0.58 AL-Disability 0.3 4 AL-Home 0.22 5 AL-Life 0.09 6 AL-Professional 0.29 7 AR-Auto 0.54 B AR-Disability 0.27 9 AR-Home 0.26 10 AR-Life 0.11 11 AR-Professional 0.31 12 AZ-Auto 0.61 13 AZ-Disability 0.29 14 AZ-Home 0.18 15 AZ-Life 0.12 16 AZ-Professional 0.31 17 CA-Auto 0.53 18 CA-Disability 0.29 19 CA-Home 0.19 20 CA-Life 0.12 21 CA-Professional 0.17 22 CO-Auto 0.58 23 CO-Disability 0.28 24 CO-Home 0.23 25 CO-Life 0.12 26 CO-Professional 0.28 27 CT-Auto 0.51 28 CT-Disability 0.3 29 CT-Home 0.21 30 CT-Life 0.13 31 CT-Professional 0.27 32 DC-Auto 0.47 33 DC-Disability 0.26 34 DC-Home 0.25 35 DC-Life 0.11 36 DC-Professional 0.18 37 DE-Auto 0.38 38 DE-Disability 0.27 39 DE-Home 0.19 40 DE-Life 0.1 41 DE-Professional 0.28 42 FL-Auto 0.45 43 FL-Disability 0.32 44 FL-Home 0.22 MainData VariableCostPct + Ready + 100% Homework Help-GMfrcmO x I u 21027994r619x348) x I Nqu Blackboard Ultra x I Vim-(Assessment x e SaferCoverlnsurance Corpo x + e a c (D Filel a'Usersa'rochiborbonfDownloadsr'lnstructionsmlml p a I and \"II VI I IUI qu vs II IVI-I HULIVI Iv "1 The following are the requirements for analyzing sales records in the data set. 1. (4 Points) 2. There are some typographical errors in the data set in the Flegion and Insurance Type fields. Find and correct these errors. (Hint: use a Pivot Table or Sort to examine the two fields and look for M305) 3. Complete the \"variable cost percentage" column by using a VLOOKUP function. (Hint: use "State Type\" as the lockup value and the information in the \"VariableCostPct" tab as the tableiarray). 4. Complete the \"Variable Cost Column" (Hint: Variable Cost = Sales ' Variable Cost Percentage). 5. Compete the \"Contribution Margin Column) (Hint: Contribution Margin = Sales Variable Cost). 5. (4 Points) - Create 4 separate tabs to answer the following questions. Label the tabs 2a, 2b, 2c,2d. Use separate Pivot Tables to answer the below: 7. Which Insurance Type had the highest sales? 8. Which Insurance Type had the highest total contribution margin? 9. How many insurance policies were sold in each Insurance Type? 10. What is the average contribution margin per policy in each Insurance Type? 11. (5 Points) - Create 5 separate tabs label the tabs aa-Auto, {ab-Disability. tic-Home, 3d-Lile, (e-Professional. Each tab will illustrate a respective insurance type. Each tab will illustrate a pivot table and pivot chart. In summary -5 insurance types therefore 5 separate tabs. Tasks: 1. Filter by the respective insurance type as described above 2. Pivot by sum of sales and by state 3. Use value filter to display the top 10 sales 4. Create a Pivot Chart in the same tab (Clustered Column Chart) 12. (2 Points) - Analyze all the information you have gathered or created in the preceding requirements. Create a word document and import at least three pivot charts. Provide a written analysis (few sentences) to describe any trends or takeaways that you have identified. 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 43 states in continental US and the District of Columbia. (SafetyCover 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. SafetyCover Insurance only sells policies in the US