D NOTES: No elaboration necessary. Please enter your answer here. B 1 Data Analytics Assignment 2 2 QUESTIONS: ANSWERS: 1) Add a new column for Commissions on the DAA2 Data worksheet. Note that commissions are only earned on sales transactions where the COGS/Sales % is greater than or equal to 50%. In that new column, calculate Commissions as 2% of sales for only those transactions (and $0 for all transactions that fail to 3 meet the threshold). 2) Use a formula to determine the number of transactions in the DAAZ Data worksheet where Product1 4 was sold. 3) Analyzing data from the DAA2 Data worksheet, use reference/lookup formulas to find the following information about the first sale by SalesRep6. Note that reference/lookup formulas will automatically pull 5 the first return value in the event that there are duplicates. 6 3a) In which region did this transaction take place? 7 3b) On what date this transaction occur? 8 3c) Which product was sold? 9 4) Analyzing the data from the DAA2 Data worksheet, use SUMIFS to calculate the following: 10 4a) Total Sales for sales to Cust16 11 4b) Total Sales for sales of Product2 in Region3 in August 2019 5) Use SUMIFS to populate the B2:E6 array in the cross-sectional table on the DAA2 Table worksheet with 12 total sales by region and product, analzying the data from the DAA2 Data worksheet. 13 6) Use Pivot Tables to answer the following questions: 14 6a) What is total sales made by SalesRep5 in Region1 during Quarter 2? Please enter your answers here. Please enter your answers here. No elaboration necessary. Please enter your answers here and also use separate worksheets entitled 6a and 6b respectively to show your work. 15 6b) What percentage of total sales of the entire year were sales of Product2 in Region4 during Quarter 3? A B D E F G H 1 1 Date Customer Sales COGS 2 529 3 891 253 478.5 620 4 1364 1134 1281 5 6 7 8 1495 230 1288 499.5 564.25 715 110 616 473 550 444 9 989 1150 1008 920 440 10 11 12 13 14 15 16 17 748 340 588 259 486 261 18 Cust23 Cust31 Cust11 Cust10 Cust19 Cust17 Cust30 Cust29 Cust33 Cust3 Cust4 Cust20 Cust7 Cust25 Cust16 Cust9 Cust24 Cust10 Cust34 Cust23 Cust14 Cust10 Cust26 Cust15 Cust10 Cust18 Cust8 Cust24 Cust 19 Cust9 380 351 1081 19 20 21 405 483 819 1/1/19 1/1/19 1/2/19 1/2/19 1/2/19 1/3/19 1/3/19 1/3/19 1/4/19 1/4/19 1/4/19 1/4/19 1/5/19 1/5/19 1/6/19 1/6/19 1/6/19 1/7/19 1/7/19 1/7/19 1/7/19 1/7/19 1/8/19 1/8/19 1/8/19 1/8/19 1/8/19 1/9/19 1/9/19 1/9/19 1/9/19 1/9/19 1/9/19 1/10/19 1/10/19 1/10/19 1/11/19 1/11/19 1/11/19 1/12/19 1/12/19 1/12/19 1/12/19 Product Region Sales Rep Product1 Region1 SalesRep2 Product Region2 SalesRep6 Product2 Region3 Sales Rep6 Product3 Region4 SalesRepo Product3 Region2 SalesRep2 Product1 Region1 Sales Rep4 Product1 Region3 SalesRep6 Product1 Region4 SalesRep3 Product1 Region4 SalesRep2 Product1 Region4 Sales Rep1 Product3 Region2 SalesRep2 Product1 Region2 SalesRep2 Product2 Region1 SalesRep2 Product3 Region2 Sales Rep5 Products Region1 SalesRep3 Product Region4 SalesRep2 Products Region2 SalesRep5 Product1 Region1 SalesRep1 Product Region3 Sales Rep2 Product1 Region3 Sales Rep5 Product3 Region4 Sales Rep6 Product1 Region4 Sales Rep5 Products Region1 Sales Rep1 Product Region3 Sales Rep1 Product3 Region3 Sales Rep2 Product2 Region3 Sales Rep3 Product4 Region2 Sales Rep2 Product Region1 SalesRep3 Product3 Region1 Sales Rep6 Product Region3 SalesRep2 Products Region3 Sales Rep1 Product3 Region4 SalesRep3 Product2 Region2 Sales Rep6 Products Region3 Sales Rep6 Product2 Region4 SalesRep6 Product2 Region2 Sales Rep3 Product Region1 SalesRep4 Product4 Region3 Sales Rep1 Product2 Region3 Sales Rep1 Product1 Region1 Sales Rep3 Product3 Region3 Sales Rep4 Product3 Region2 SalesRep2 Product Paginn 1 Salos Pont 160 188.5 517 217.5 231 360.75 242 22 23 24 25 506 621 855 26 630 333.5 360 277.5 390 48 858 27 28 29 114 1269 1134 1080 681.5 499.5 580 1647 884.5 30 31 32 83 34 35 1365 601.25 430 Cust26 Cust28 Cust29 Cust19 Cust11 Cust28 Cust19 Cust31 Cust23 Cust8 946 297 616 682 36 37 159.5 280 310 754 48 38 1404 114 39 40 506 230 41 552 264 1008 444 12 13 Cust33 Cust26 315 138.75 14 Custa 961 379 25 A B D E F G Region1 Region2 Region3 Region4 1 2 Product1 3 Product2 4 Product3 5 Products 6 Products 7 8 9 10