File Home Insert Page Layout Formulas Data Review View Help G37 fx 1963-07-29 D A stationery store aims to divide its customers into four segments based on their loyalty since their first purchase to give them special offers based on their prefer C basic information is recorded. Therefore, the Duration of Loyalty has been calculated and Labels are defined You are required to: 1-Determine the Label of each customer if it is "New, Bronze, Siver, Gold" in the Segment column based on data recorded in Table 1; 3-Copy table in a new sheet and sort table based on Occupation and Date of Fint Purchase, (5 6 2-Use Conditional Formatting to show Durations of Loyalty with different colors (30%) 4-Copy table in a new sheet and filter table based on East York Residental Area; (5%) 5-Calculate sum of Annual Purchase, (10%) 9 6-Calculete average of Annual Purchase; (10%) 2 7-Use Data Validation to show the List of Residental Area in cell M27, (10%) 8-insert a column to automatically write "Free gift card" if the annual purchase is greater than 5000 and the customer's Duration of Loyalty is less than 30 con 9-insert a Chart and show Duration of Loyalty of each customer, (10%) 10 Insert a Pivot Table to show customers based on Gender and Occupation (10%) 11- Insert a Pivot Chart to show the number of customers in each Residental Area filtered based on Gender (10%) *Please insert a New Sheet to respond to each question and flename the sheets as the number of the questions Table First Nam Sophia 2 Evelyn Last Nam Collins Bennett Gende Female Accountant 23-Sep-78 Occupation Residental AcDate of First PurchaAnnual Purchal Duration of Loyalt East Toronto Duration of Loyalty tabel $200 45 21 Male Architect North York 14-Feb-67 $350 55 4 Geneva SiMa Katrina Allen Walsh Male Rodriquez Female Male Project Manager Downtown Toronto Web Developer East York 6-Jun-90 5260 32 BERS New Drin Sitem 60 Grila 30-May-83 5320 39 Translator East Toronto 23-Dec-98 5451 23 STAL