Q1 If a consultant generates sales above $10,000 for both the "JanuaryJune" and "July and December" periods then the commission for the total amount should be calculated using the "Commission rates " table

(please use IF, SUM, VLOOKUP, AND Define the name range E16 to F20 as "Commission rates" * and use this in your VLOOKUP

Q 2 Consultants that started in the year 2020 are entitled to bonus commission if they generate over $75,000 in sales in both periods. If an employee started in 2020, and both periods "January-June sales" and "July December sales" are greater than $75,000 then then the cell should display "First name Last Name receives bonus commission", if the conditions are not

(Please use IF, CONCAT, AND, YEAR (or DATEVALUE)

Q 3 Create the email address in I5-I10 using the initial from the first name, plus the full last name. Add a "." between the initial and last name. Add the "@" symbol after the last name. Finally use the Consultant details table to obtain the email domain name.

(please use VLOOKUP, Left, Right, CONCAT Define the name range E25 to F30 as Consultant details*** and use this in your VLOOKUP)

Bonus commission Email address First Name Last Name Date Startec Consultant 10 January-June sale July-December sale Commission (S) David Silver 2/09/14 XXDASIL $ 40,000.00 $ 90,000.00 Harriette Brown 11/08/15 XXHABRO $ 100,000.00 $ 90,000.00 Hugo Chevi 11/11/19 XXHUCHE $ 8,000.00 $ 33,000.00 Sally Chan 14/06/20 XOXSACHA $ 85,000.00 $ 76,000.00 Tyrion Smith 1/04/20 XXTYSMI $ 60,000.00 $ 95,000.00 Jamie Brown 1/07/17 XXIABRO $ 90,000.00 $ 110,000.00 Commission rates table Sales threshold Commission $ 10,000.00 $ 51,000.00 $ 101,000.00 $ 150,000.00 $ 200,000.00 2% 4% 6% 10% Consultant details table Consultant ID Email domain DASIL bgconsulting.com HABRO flywizards.com HUCHE acesolutions.com SACHA formation.com TYSMI smothers.com JABRO tbit.com