A B 1 Model Code Model Name 2 1001 1001 1001 1001 1001 1002 1002 1002 1002 1002 1002 1002 1002 1003 1003 1003 1003 1003 1003 1003 1004 1004 1004 1004 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 C D E F Sales Person Dealer's Cost Sales Price Sales Tax Comission Amount Goldsmith 19488 21000 27000 33000 17000 18300 21500 24000 27000 29500 12000 19000 11100 22500 13594 27000 27000 17000 23988 22500 17000 21500 23988 27000 21500 21500 36994 19488 23988 Plant Stutz Stutz Plant Plant Stutz Stutz Stutz Goldsmith Plant Goldsmith Stutz Plant Stutz Goldsmith Plant Plant Goldsmith Plant Stutz Goldsmith Plant Goldsmith # of Sales 15500 21500 29500 32000 15500 29500 31000 18300 26500 26800 18300 24000 26500 29500 24000 24000 41000 21000 26500 G H I Tax J Code 11% K Name 1001 GM 1002 Volvo 1003 Subaru 1004 Honda L 6. Go to the sheet Car Dealership. Create a formula in F2 that determines the Sales Tax. (Sales Price* the Tax in cell J1) With the proper absolute copy the formula down. (10%) 7. In cell B2, create a Vlookup function that looks up the Model Name from the table in Columns J and K. Auto fill the formula down for all the records. (15%) 8. In cell G2 create an IF function that examines the Sales Price in column E. If the Sales Price is greater than 30000 the commission will be the Sales Price * 20%; if not the commission will be the Sales Price * 10%. Auto fill the formula down for all the records. (15%)