Worksheet 1, Task 1: 2 questions (2 marks per question) Question Cells Task/problem Example Excel Functions/features to you should use 18:113e Calculate consultant bonus: Bob Inaros generates sales in IF, SUM, VLOOKUP, Queensland for $5.000, AND- If a consultant generates sales Victoria for $100,000 and greater than or equal to: $10,000 NSW for $60,000. Therefore, for Queensland, and $20,000 for he receives no bonus. Victoria, and $50,000 for NSW Define the named range then the bonus for the total Frank Chan generates sales of E19:F23 as amount should be calculated $25,000 in Queensland. "Bonus rates" and use based on the total amount (e.g., $75,000 in Victoria, and this in your VLOOKUP Queensland + NSW + Victoria) S65,000 NSW. His total and the rate from the therefore is $165,000 and he is "Bonus rates table". entitled to 20% commission which equals $33,000 i.e. the number $33,000 should be displayed in cell 19. J8:J13e Create email address: Bob Inaros' email address in VLOOKUP, LEFT, cell J8 should display as: RIGHT, CONCAT (or Write a formula to create an "Bob.Inaros@bbgroup.com" CONCATENATE) email address in 18:J13 using the his Consultant ID is BROS). first name and last name of the consultant. Add a "." between the first and last name. Add the Define the name range "@" symbol after the last name. Jon Lannister's email address H19:124 as Finally use the in cell J13 should display: "Consultant details" and "Consultant details table" to "Jon Lannister@ tychos.com" use this in your obtain the email domain name (his Consultant ID is TER). VLOOKUP by using the first letter of the first name and the last three letters of the last name to match the consultant ID. 2 le Email address D G 7 Title First Name Last Name Date Started Consultant location Queensland sales Victoria sales NSW Sales Bonus (S) 8 M Bob naros 2/09/14 Melbourne $ 5000.00 $ 100,000.00 S 60.000.00 9 M Frank Chan 11/08/15 Sydney S 25000.00 $ 75,000.00 $ 65,000.00 10 Miss Xiao Wang 11/11/19 Brisbane $ 15.000.00 $ 35,000.00 150.000.00 11 Me Macro Toure 14/06/20 Brisbane $ 85000.00 15.000.00 1000000 12 Mrs Jane Mao 1/04/20 Sydney $ 45 000.00 $ 30,000.00 6500000 13 Melon Lannister 1/07/17 Melbourne $ 90 000.00 $ 55.000.00 120.000.00 14 15 16 17 Bonus rates table Consultant details table 18 Sales threshold Bonus Consultant ID Email domain 19 5 80.000.00 5 bbgroup.com 20 5 100 000 00 104 FHAN byhoo.com 21 $ 120,000.00 15 XANG pmsolutions.com 22 $ 150 000 00 200 MURE sprint.com 23 $ 200 000.00 25 | IMAO bbgroup.com 24 ITER bychos.com BROS 20