This is to be done in excel Class is accounting information system please help me with the steps please (4 Points) 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) 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) Complete the Variable Cost Column (Hint Variable Cost Sales Variable Cost Percentage) Compete the Contribution Margin Column) (Hint Contribution Margin Sales Variable Cost) (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 Which Insurance Type had the highest sales Which Insurance Type had the highest total contribution margin How many insurance policies were sold in each Insurance Type What is the average contribution margin per policy in each Insurance Type (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 Filter by the respective insurance type as described above Pivot by sum of sales and by state Use value filter to display the top 10 sales Create a Pivot Chart in the same tab (Clustered Column Chart) (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

Question

This is to be done in excel   Class is accounting information system   please help me with the steps please  (4 Points) 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) 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)  Complete the  Variable Cost Column  (Hint  Variable Cost   Sales   Variable Cost Percentage)  Compete the  Contribution Margin Column) (Hint  Contribution Margin   Sales   Variable Cost)  (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  Which Insurance Type had the highest sales  Which Insurance Type had the highest total contribution margin  How many insurance policies were sold in each Insurance Type  What is the average contribution margin per policy in each Insurance Type  (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  Filter by the respective insurance type as described above Pivot by sum of sales and by state Use value filter to display the top 10 sales Create a Pivot Chart in the same tab (Clustered Column Chart) (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

Accepted Answer

The Answer is in the image, click to view ...

Question

This is to be done in excel . Class is accounting information system . please help me with the steps please. (4 Points) There are

Step by Step Solution

Step: 1

Get Instant Access with AI-Powered Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Financial accounting

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question