Analyzing Customer Demographics and Sales: Part B (Use the best equation to solve these problems, do not use pivot tables and make sure "flexible" equations are used, meaning the data set can be updated without the need to update your equations. You may have the "right" answer, but if your answer doesn't change as data is updated, points will be deducted): 1. Show the top 5 "best" customers defined by the most number of purchases and then defined by the total value of purchases. How are they different/similar? 2. Use an equation learned in class and pull in the companies of those customers that made the top 3 number of purchases. 3. What is the distribution of customer diversity (race) by country? How does the diversity in the data set compare to that of the nation? Would this be an accurate comparison, why or why not? 4. What is the unique customer gender distribution in the data set for those customers in the US? 5. Show the total sales by gender and race in the data set. Which gender / race combination has the most/least total sales? For the following scenarios / questions use Data Set 2: Wrangling: Part 1. Create two additional columns called Month and Year. Use formulas learned in class to extract the month and year into each column respectively. Analyzing Sales: Part D (Do not use equations to solve these problems, use pivot tables.) 1. Show the total sales amount by year. Which year had the highest/ least amount of sales? 2. Show the average sales amount by month for all years. Which month had the highest/ lowest average of sales amount? AutoSave 3 Customer Demographics Sales Search Home Page Layout Formulas Data View Developer Help Calibri Bad Good Neutral X cut [Copy Formatai 11 - AA SE Wrap Test A-EEEEE Merge Center $ . %69 Normal Confort Calculation formatting the Che cell Explanatory input. Font Spes C ID A D F H 1 K M N O first nam last name email gender Race purchase credit cardate State/Pro City Postal Cod Country Username Companyame 525 Shawn Edwards sedwards Male Black or A $381.00 witch 1/1/2011 Massachu Worcestei 1630 United States sedwardsek Eadel 334 Albert Murrayamuray Male Asian 0.00 1/1/2011 Colorado Denver 80243 United States amurray 477 Tina Mccoy tmccoy Female White $571.00 mastercar 1/1/2011 California Glendale 51210 United States tmccoyds Twiyo 727 Mark Chapman chapma Male white $811.00 diners-cu 1/4/2011 Kentucky Louisville 40215 United States mchapanks Chatterbridge 75 Brandon Hawkins bhawkins Male Black or $353.00 1/6/2011 Connectic Hartford 6120 United States thawkins23 Oyoyo 34 Us Tharfes female Mexican $164.00 1/9/2011 West Virg Huntingto 25775 United States that Fadeo 611 Barbara Hicks thickyFemale White $235.00 diners-cu 1/W2011 Kansas Wichita 67200 United States thicker Feedfire 399 Brandon Jacobs jacobs Male Asian $78.00 maestro Nebraska Omaha 68117 United States bjacobsop Pumps 131 Kenneth Perry per Male White $313.00 Texas Austin 7KMUnited States Aperrys Blognation 55 Ruth Simpson imponifemale White $750.00 bankcard Georg Atlanta 30321 United States sont Babbleblab 717 Mark Phillips mphilips Male Black or A $95.00 maestro Michigan Southek 4807 United States mphis Poorry Bryant bryanti Male White $112.00 Instapay Massachu Lynn 1905 United States bryant19 Voonder 161 anke Vasquer emale Black or A $10.00 Midland 70s United States Link Stephen Romero womero Male Black or A $887.00 bankcarde Texas Austin 7872 wtedes sromero Finti o Brandon Hawkins thawkins Male Black or A $177.00 Connecticarlod 6120 United States bhawki23 Oyoyo Vasquer que female Black or AS300 Mb we Texas Midland 7705 mited Mates 26 Ralph Oliver roliver Male Black or $16.00 americane wwwwwww Indiana Souther United was relverta Flip 113 Kenneth Perry per Male White A Texas Austin 789 United Sperry Bloration 753 Peterson petero Female Black or $36.00 KB Colorado Boulder 2012 United States ppeterson Leo 7 tirandon wins thawkins, Male Black or $575.00 Connectic Hartford 6120 united States bhw 872 Pamela Thompsorpthomps Female Asian $141.00 bankcard s Michigan Detroit 41206 United Matesothompsono? Wordtune 755 Wanda Coleman wolema Female Black or A $935.00 visa Pennsylvi Pittsburg 15266 United Mates wolemanky Abata 151 More Cooper cooper Female White $0.00 maestro Florida Oca 36179 United States noopers Thoughtbeat Wight lightb. Female White $454.00 jcb 2/1/2011 Loutan Shrevepo 71161 United States who Jabbersphere 731 Andrea Holmes holmesk Female White 600.000 3/2/2017 Missouri Saint Loui 63120 united States wholmesta Toplestorm Ray Female White $330.00 switch 2/9/2011 California Fresno 93704 United States ayat Shufflag 720 Susan Elliott selotte Female Black or A $295.00 mastercar 2/1/2011 Howall Honolulu 640 United States selo Yousplan 461 Christina FOR couco Female Black or A $237.00 jcb 2/6/2011 Oregon Portland 97201 United States concb Voor 330 Brandon Jacobs bjacobsof Male $104.00 maestro 2/6/2011 Nebraska Omaha 68117 United States bjacobsop Pumps 816 Jessica ricene Female $66.00 masterca 2/7/2011 Florida Miami 33129 United States prem Riffpath Edwards Sedwards Male Black or SALDO switch 2/8/2011 Massachu Worceste 1610 United States sedwardsek Eadel 8.30 Nicole Lackson jackson Female White $486.00 mastercar Wisconsir Green Bay 54305 United States jacksonni Browse 206 Ruth Medina medias Female Argentin $467.00 kb Ohio Youngstos 44555 United States medias Edgeblab 492 Wayne Oliver woliverdr Male Black or A $166.00 Florida Vero Beac 32964 United States wolverde Wikia 450 E Davis edavisie Male White 5436.00 diner chu Pennsylvi Philadelp! 19111 United States edavisi Wordware 208 Michael Gilbert molberts Male White $109.00 china un California Oceanside 92056 United States mulbert Twinder 339 Brenda H bmeyer Female White Texas El Paso 795 United States meyere Photobean CustomerDemographies Sales Customer Demographics Sales Data Part 1 Part A Part 8.1 Sheet1 a Job Title Full Name Web Developer Shawn Edwards General Manager Albert Murray Systems Administrator Tina Mccoy VP Quality Control Mark Chapman Information Systems Manager Brandon Hawkins Physical Therapy Assistant Lisa Hart Staf Scient Barbara Hicks Associate Professor Brandon Jacobs Gas Technical Architect Kenneth Perry Internal Aud Ruth Simpson Information Systems Manager Mark Phillips General Manager Jesse Bryant Payment Adjustment Coordinator Anice Vasquez Design Engineer Stephen Romero Information Systems Manager Brandon Hawkins Payment Adjustment Coordinator ane Vasque Operator Ralph Oliver GIS Technical Architect Kenneth Perry Geologi Phyllis Peterson information Systems Manager Brandon Hawkins Food Chemist Pamela Thompson VP Accounting Wanda Coleman Recruiter Norma Cooper Human Resources Asustant IV Laura WYM Cost Accountant Andrea Holmes VP Accounting Nuru Susan Lott Account Coordinator Christina Fox Associate Professe Brandon Jacobs Edito Jessica Rice Web Developer Shawn_Edwards Occupational Therapist Nicole Jackson Account Coordinator Ruth Medina Research Associate Wayne Oliver VP Marketing Ene Davis Community Outreach Specialist Michael Gilbert Payment Adjustment Coordinator Brenda Meyer L Analyzing Customer Demographics and Sales: Part B (Use the best equation to solve these problems, do not use pivot tables and make sure "flexible" equations are used, meaning the data set can be updated without the need to update your equations. You may have the "right" answer, but if your answer doesn't change as data is updated, points will be deducted): 1. Show the top 5 "best" customers defined by the most number of purchases and then defined by the total value of purchases. How are they different/similar? 2. Use an equation learned in class and pull in the companies of those customers that made the top 3 number of purchases. 3. What is the distribution of customer diversity (race) by country? How does the diversity in the data set compare to that of the nation? Would this be an accurate comparison, why or why not? 4. What is the unique customer gender distribution in the data set for those customers in the US? 5. Show the total sales by gender and race in the data set. Which gender / race combination has the most/least total sales? For the following scenarios / questions use Data Set 2: Wrangling: Part 1. Create two additional columns called Month and Year. Use formulas learned in class to extract the month and year into each column respectively. Analyzing Sales: Part D (Do not use equations to solve these problems, use pivot tables.) 1. Show the total sales amount by year. Which year had the highest/ least amount of sales? 2. Show the average sales amount by month for all years. Which month had the highest/ lowest average of sales amount? AutoSave 3 Customer Demographics Sales Search Home Page Layout Formulas Data View Developer Help Calibri Bad Good Neutral X cut [Copy Formatai 11 - AA SE Wrap Test A-EEEEE Merge Center $ . %69 Normal Confort Calculation formatting the Che cell Explanatory input. Font Spes C ID A D F H 1 K M N O first nam last name email gender Race purchase credit cardate State/Pro City Postal Cod Country Username Companyame 525 Shawn Edwards sedwards Male Black or A $381.00 witch 1/1/2011 Massachu Worcestei 1630 United States sedwardsek Eadel 334 Albert Murrayamuray Male Asian 0.00 1/1/2011 Colorado Denver 80243 United States amurray 477 Tina Mccoy tmccoy Female White $571.00 mastercar 1/1/2011 California Glendale 51210 United States tmccoyds Twiyo 727 Mark Chapman chapma Male white $811.00 diners-cu 1/4/2011 Kentucky Louisville 40215 United States mchapanks Chatterbridge 75 Brandon Hawkins bhawkins Male Black or $353.00 1/6/2011 Connectic Hartford 6120 United States thawkins23 Oyoyo 34 Us Tharfes female Mexican $164.00 1/9/2011 West Virg Huntingto 25775 United States that Fadeo 611 Barbara Hicks thickyFemale White $235.00 diners-cu 1/W2011 Kansas Wichita 67200 United States thicker Feedfire 399 Brandon Jacobs jacobs Male Asian $78.00 maestro Nebraska Omaha 68117 United States bjacobsop Pumps 131 Kenneth Perry per Male White $313.00 Texas Austin 7KMUnited States Aperrys Blognation 55 Ruth Simpson imponifemale White $750.00 bankcard Georg Atlanta 30321 United States sont Babbleblab 717 Mark Phillips mphilips Male Black or A $95.00 maestro Michigan Southek 4807 United States mphis Poorry Bryant bryanti Male White $112.00 Instapay Massachu Lynn 1905 United States bryant19 Voonder 161 anke Vasquer emale Black or A $10.00 Midland 70s United States Link Stephen Romero womero Male Black or A $887.00 bankcarde Texas Austin 7872 wtedes sromero Finti o Brandon Hawkins thawkins Male Black or A $177.00 Connecticarlod 6120 United States bhawki23 Oyoyo Vasquer que female Black or AS300 Mb we Texas Midland 7705 mited Mates 26 Ralph Oliver roliver Male Black or $16.00 americane wwwwwww Indiana Souther United was relverta Flip 113 Kenneth Perry per Male White A Texas Austin 789 United Sperry Bloration 753 Peterson petero Female Black or $36.00 KB Colorado Boulder 2012 United States ppeterson Leo 7 tirandon wins thawkins, Male Black or $575.00 Connectic Hartford 6120 united States bhw 872 Pamela Thompsorpthomps Female Asian $141.00 bankcard s Michigan Detroit 41206 United Matesothompsono? Wordtune 755 Wanda Coleman wolema Female Black or A $935.00 visa Pennsylvi Pittsburg 15266 United Mates wolemanky Abata 151 More Cooper cooper Female White $0.00 maestro Florida Oca 36179 United States noopers Thoughtbeat Wight lightb. Female White $454.00 jcb 2/1/2011 Loutan Shrevepo 71161 United States who Jabbersphere 731 Andrea Holmes holmesk Female White 600.000 3/2/2017 Missouri Saint Loui 63120 united States wholmesta Toplestorm Ray Female White $330.00 switch 2/9/2011 California Fresno 93704 United States ayat Shufflag 720 Susan Elliott selotte Female Black or A $295.00 mastercar 2/1/2011 Howall Honolulu 640 United States selo Yousplan 461 Christina FOR couco Female Black or A $237.00 jcb 2/6/2011 Oregon Portland 97201 United States concb Voor 330 Brandon Jacobs bjacobsof Male $104.00 maestro 2/6/2011 Nebraska Omaha 68117 United States bjacobsop Pumps 816 Jessica ricene Female $66.00 masterca 2/7/2011 Florida Miami 33129 United States prem Riffpath Edwards Sedwards Male Black or SALDO switch 2/8/2011 Massachu Worceste 1610 United States sedwardsek Eadel 8.30 Nicole Lackson jackson Female White $486.00 mastercar Wisconsir Green Bay 54305 United States jacksonni Browse 206 Ruth Medina medias Female Argentin $467.00 kb Ohio Youngstos 44555 United States medias Edgeblab 492 Wayne Oliver woliverdr Male Black or A $166.00 Florida Vero Beac 32964 United States wolverde Wikia 450 E Davis edavisie Male White 5436.00 diner chu Pennsylvi Philadelp! 19111 United States edavisi Wordware 208 Michael Gilbert molberts Male White $109.00 china un California Oceanside 92056 United States mulbert Twinder 339 Brenda H bmeyer Female White Texas El Paso 795 United States meyere Photobean CustomerDemographies Sales Customer Demographics Sales Data Part 1 Part A Part 8.1 Sheet1 a Job Title Full Name Web Developer Shawn Edwards General Manager Albert Murray Systems Administrator Tina Mccoy VP Quality Control Mark Chapman Information Systems Manager Brandon Hawkins Physical Therapy Assistant Lisa Hart Staf Scient Barbara Hicks Associate Professor Brandon Jacobs Gas Technical Architect Kenneth Perry Internal Aud Ruth Simpson Information Systems Manager Mark Phillips General Manager Jesse Bryant Payment Adjustment Coordinator Anice Vasquez Design Engineer Stephen Romero Information Systems Manager Brandon Hawkins Payment Adjustment Coordinator ane Vasque Operator Ralph Oliver GIS Technical Architect Kenneth Perry Geologi Phyllis Peterson information Systems Manager Brandon Hawkins Food Chemist Pamela Thompson VP Accounting Wanda Coleman Recruiter Norma Cooper Human Resources Asustant IV Laura WYM Cost Accountant Andrea Holmes VP Accounting Nuru Susan Lott Account Coordinator Christina Fox Associate Professe Brandon Jacobs Edito Jessica Rice Web Developer Shawn_Edwards Occupational Therapist Nicole Jackson Account Coordinator Ruth Medina Research Associate Wayne Oliver VP Marketing Ene Davis Community Outreach Specialist Michael Gilbert Payment Adjustment Coordinator Brenda Meyer L