This is to be done in excel. What would be the steps to complete this process. This is for Accounting Information System class.
a) 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).
b) 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).
c) Complete the "Variable Cost Column" (Hint: Variable Cost = Sales * Variable Cost Percentage.
d) Compete the "Contribution Margin Column) (Hint: Contribution Margin = Sales - Variable Cost).
Rochi Borbon RB X SafetyCoverInsurance-Data-SetA Search AutoSave . Off 15 Share Comments Help File Home Insert Draw Page Layout Formulas Data Review View abo BE New Delete Previous Next Show Notes Protect Protect Allow Edit Unshare Hide Spelling Thesaurus Workbook heck Smart Translate Sheet Workbook Ranges Workbook Ink Statistics Accessibility Lookup Comment Comment Comment Comments Comments Notes Protect Ink Proofing Accessibility | Insights | Language A1 X V Region Y Z K M N O P Q R S T U V W X A B C D E G H Variable State Cost Variable Contribution Country 1 Region IState Salesperson Insurance Type Sales Date of Sale Invoice No Type Percentage Cost Margin TX Tom Auto 883.03 1/1/2017 A-2017-000010 TX-Auto USA 2 Midwest 3 Southeast TN Jennifer Auto 532.41 1/1/2017 A-2017-000012 TN-Auto USA USA New England CT Crissy Auto 388.97 1/1/2017 A-2017-000016 CT-Auto USA 5 New England MA Crissy Auto 821.25 1/1/2017 A-2017-000017 MA-Auto USA 6 Southeast GA John Auto 470.51 1/1/2017 A-2017-000028 GA-Auto 7 West UT Kellie Auto 552.27 1/1/2017 A-2017-000029 UT-Auto USA USA 8 West NV Kellie Auto 430.8 1/1/2017 A-2017-000033 NV-Auto 943.11 1/1/2017 A-2017-000036 WA-Auto USA 9 West WA Peter Auto 10 North Central MO Mario Auto 401.6 1/1/2017 A-2017-000037 MO-Auto USA 11 Southeast MS 1/1/2017 A-2017-000041 MS-Auto USA Frank Auto 476.35 12 North Central MI Nicole Auto 468.17 1/1/2017 A-2017-000046 MI-Auto USA 1/1/2017 A-2017-000050 ME-Auto USA 13 New England ME Sara Auto 413.5 14 West Peter Auto 1/1/2017 A-2017-000059 MT-Auto USA MT 509.05 USA 15 Northeast DE Fred Auto 520.28 1/1/2017 A-2017-000061 DE-Auto USA 16 New England ME Sara Auto 693.63 1/1/2017 A-2017-000065 ME-Auto 17 Southeast TN Jennifer Auto 665.89 1/1/2017 A-2017-000078 TN-Auto USA USA 18 Midwest Tor Auto 519.56 1/1/2017 A-2017-000089 KS-Auto like Auto 960.22 1/1/2017 A-2017-000096 OH-Auto USA 19 Northeast HO 20 New England MA Crissy Auto 497.6 1/1/2017 A-2017-000099 MA-Auto USA USA 21 Midwest NE Dave Auto 1070.19 1/1/2017 A-2017-000107 NE-Auto Auto 1/1/2017 A-2017-000113 VA-Auto USA 22 Northeast VA Matt 955.79 USA 23 North Central KY Mario Auto 553.44 1/1/2017 A-2017-000114 KY-Auto 24 Northeast MC Fred Auto 610.22 1/1/2017 A-2017-000115 MD-Auto USA John 1/1/2017 A-2017-000117 FL-Auto USA 25 Southeast FL Auto 505.5 553.44 1/1/2017 A-2017-000122 OK-Auto USA 26 Midwest OK Ton Auto 27 North Central IA Nicole Auto 795.76 1/1/2017 A-2017-000124 IA-Auto USA Tom 567.7 1/1/2017 A-2017-000128 TX-Auto USA 28 Midwest rx Auto USA 29 Northeast DC Matt Auto 790.34 1/1/2017 A-2017-000139 DC-Auto 429.63 1/1/2017 A-2017-000142 MN-Auto USA 30 North Central MN Nicole Auto 346.92 1/1/2017 A-2017-000145 MA-Auto USA 31 New England MA Crissy Auto USA 32 Southeast GA John Auto 1138.63 1/1/2017 A-2017-000146 GA-Auto USA 33 Northeast PA Mike Auto 479.4 1/1/2017 A-2017-000153 PA-Auto 1/1/2017 A-2017-000154 MS-Auto USA 34 Southeast MS Frank Auto 624.83 USA 35 Northeast DA like Auto 1018.12 1/1/2017 A-2017-000166 PA-Auto USA 36 Northeast Fred Auto 598.54 1/1/2017 A-2017-000168 NJ-Auto MainData VariableCostPct + O - 100% 7:28 PM 9 W 7/18/2021 Type here to search O
