Could you list the steps for each question please? Excel is so difficult for me.
DAA Instructions DAA Rubric DAA Questions DAA Data DAA Reference Date Salesperson Customer Product Material Size Warehouse # Units Sales COGS Collections 1/1/19 SP-20 CUST-035 PROD-005 MATL-Citrine SIZE-S MSA-13 100 524020 373708 524020 1/1/19 SP-17 CUST-021 PROD-008 MATL-Aquamarine SIZE-S MSA-42 82 415680 286510 $15680 1/1/19 SP-05 CUST-130 PROD-009 MATL-Emerald SIZE-S MSA-61 88 456541 254174 228269 1/1/19 SP-08 CUST-019 PROD-012 MATL-Moonstone SIZE-L MSA-06 89 2172 9222 21720 1/1/19 SP-08 CUST-076 PROD-016 MATL-Copper SIZE-L MSA-16 3 35134 24258 35134 1/1/19 SP-21 CUST-085 PROD-023 MATL-Jasper SIZE-S MSA-74 48 53816 35859 53816 1/1/19 SP-02 CUST-146 PROD-053 MATL-Silver SIZE-XS MSA-31 79 870232 519937 870232 1/1/19 SP-19 CUST-048 PROD-053 MATL-Silver SIZE-M MSA-35 352612 241911 352612 1/1/19 SP-12 CUST-053 PROD-059 MATL-Amethyst SIZE-S MSA-39 64 5568 806 5568 1/1/19 SP-17 CUST-072 PROD-065 MATL-Moonstone SIZE-XL MSA-01 80 399050 248241 399050 1/1/19 SP-05 CUST-051 PROD-072 MATL-Garnet SIZE-XS MSA-42 52 387309 298717 387309 1/1/19 SP-12 CUST-123 PROD-073 MATL-Tanzanite SIZE-S MSA-32 30 911271 704058 911271 1/1/19 SP-07 CUST-012 PROD-076 MATL-Ruby SIZE-XL MSA-02 46 3204 1643 3204 1/1/19 SP-0 CUST-145 PROD-086 MATL-Silver SIZE-S MSA-57 22 711134 452503 711134 1/1/19 SP-09 CUST-037 PROD-087 MATL-Quartz SIZE-S MSA-11 86 166804 121723 66804 1/1/19 SP-0 CUST-060 PROD-090 MATL-Silver SIZE-S MSA-10 76 431104 278177 431104 1/1/19 SP-15 CUST-128 PROD-093 MATL-Opal SIZE-XS MSA-49 53 287850 120680 287850 1/1/19 SP-16 CUST-145 PROD-097 MATL-Opal SIZE-XL MSA-62 71 141082 63237 141082 1/1/19 SP-13 CUST-078 PROD-103 MATL-Citrine SIZE-L MSA-44 46 91372 44960 1372 1/1/19 SP-1 CUST-143 PROD-111 MATL-Quartz SIZE-M MSA-32 22 762734 507032 762734 1/1/19 SP-05 CUST-133 PROD-116 MATL-Opal SIZE-L MSA-18 242465 99286 42465 1/1/19 SP-16 CUST-026 PROD-138 MATL-Jasper SIZE-L MSA-20 44 305712 169770 305712 1/1/19 SP-04 CUST-146 PROD-144 MATL-Sapphire SIZE-XS MSA-36 225254 143700 225254 1/1/19 SP-19 CUST-001 PROD-145 MATL-Emerald SIZE-XS MSA-35 92 151280 70374 151280 1/1/19 SP-01 CUST-121 PROD-146 MATL-Bronze SIZE-XL MSA-03 40 501840 277006 501840 1/1/19 SP-02 CUST-083 PROD-148 MATL-Bronze SIZE-S MSA-70 15 238556 139282 238556 1/1/19 SP-15 CUST-007 PROD-158 MATL-Ruby SIZE-XS MSA-08 98 560300 443720 560300 1/1/19 SP-07 CUST-122 PROD-165 MATL-Emerald SIZE-M MSA-02 39 477216 294475 477216 1/1/19 SP-2 CUST-110 PROD-194 MATL-Garnet SIZE-M MSA-38 96 899040 560683 899040 1/2/19 SP-23 CUST-140 PROD-009 MATL-Sapphire SIZE-XL MSA-07 54 235996 131543 235996 1/2/19 SP-2 CUST-075 PROD-011 MATL-Copper SIZE-S MSA-65 20 231570 134532 231570 1/2/19 SP-18 CUST-122 PROD-016 MATL-Ruby SIZE-M MSA-44 70 739530 376253 739530 1/2/19 SP-1 CUST-015 PROD-018 MATL-Topaz SIZE-XS MSA-58 39 416583 241677 416583 1/2/19 SP-14 CUST-087 PROD-021 MATL-Silver SIZE-XL MSA-50 42 442560 96738 442560 1/2/19 SP-16 CUST-035 PROD-037 MATL-Silver SIZE-XL MSA-39 58 132658 90175 132658 1/2/19 SP-15 CUST-125 PROD-038 MATL-Topaz SIZE-XS MSA-47 90 305340 221390 $05340 1/2/19 SP-20 CUST-076 PROD-040 MATL-Topaz SIZE-XL MSA-10 22 130093 89266 130093 1/2/19 SP-18 CUST-140 PROD-041 MATL-Garnet SIZE-S MSA-18 200452 368806 1/2/19 SP-12 CUST-119 PROD-045 MATL-Sapphire SIZE-XL MSA-45 78 19035 539 1903 1/2/19 SP-01 CUST-076 PROD-053 MATL-Amethyst SIZE-XS MSA-34 9864 6307 9864 1/2/19 SP-08 CUST-073 PROD-053 MATL-Copper SIZE-L MSA-34 61 664794 448055 664794 1/2/19 SP-06 CUST-072 PROD-056 MATL-Opal SIZE-M MSA-31 96 517089 354520 517089 1/2/19 SP-17 CUST-087 PROD-066 MATL-Ruby SIZE-XS MSA-05 96 51624 28188 51624 1/2/19 SP-07 CUST-026 PROD-070 MATL-Jasper SIZE-XL MSA-17 30 234612 13629 234612 1/2/19 SP-21 CUST-084 PROD-070 MATL-Moonstone SIZE-XS MSA-65 52 41664 22955 41664 1/2/19 SP-06 CUST-137 PROD-077 MATL-Jade SIZE-M MSA-25 337428 228364 1/2/19 SP-0 CUST-020 PROD-077 MATL-Opal SIZE-XS MSA-42 26190 15301 26190 1/2/19 SP-17 CUST-045 PROD-077 MATL-Copper SIZE-S MSA-75 10578 5695 10578 1/2/19 SP-13 CUST-089 PROD-082 MATL-Garnet SIZE-S MSA-48 188895 79205 88895 1/2/19 SP-2 CUST-111 PROD-096 MATL-Quartz SIZE-S MSA-32 34 464986 259465 464986 1/2/19 SP-20 CUST-123 PROD-097 MATL-Aquamarine SIZE-M MSA-09 501216 254756 501216 1/2/19 SP-1 CUST-119 PROD-102 MATL-Sapphire SIZE-XL MSA-49 55216 37778 55216 1/2/19 SP-05 CUST-026 PROD-113 MATL-Moonstone SIZE-M MSA-20 53490 6089 63490 1/2/ 19 SP-12 CUST-146 PROD-119 MATL-Emerald SIZE-L MSA-45 51 827433 420353 827433 1/2/19 SP-20 CUST-021 PROD-122 MATL-Jade SIZE-L MSA-56 113004 35645 113004 1/2/19 SP-02 CUST-114 PROD-142 MATL-Amethyst SIZE-XS MSA-06 81 215336 136450 215336 1/2/19 SP-15 CUST-069 PROD-145 MATL-Citrine SIZE-XL MSA-65 231044 93346 231044 1/2/19 SP-19 CUST-097 PROD-145 MATL-Emerald SIZE-S MSA-19 43 124740 56888 124740 1/2/19 SP-21 CUST-006 PROD-145 MATL-Quartz SIZE-XS MSA-14 49 479908 363196 479908 1/2/19 SP-0 CUST-150 PROD-151 MATL-Amethyst SIZE-L MSA-43 93 180608 100362 1/2/19 SP-09 CUST-104 PROD-174 MATL-Gold SIZE-XS MSA-46 18 126175 73571 126175 1/2/19 SP-15 CUST-014 PROD-181 MATL-Emerald SIZE-XS MSA-49 49 583632 339729 583632 1/2/19 SP-08 CUST-118 PROD-190 MATL-Peridot SIZE-M MSA-66 47 68140 46215 68140 1/3/19 SP-21 CUST-086 PROD-004 MATL-Amethyst SIZE-M MSA-03 26 9858 6871 9858 1/3/19 SP-05 CUST-090 PROD-007 MATL-Quartz SIZE-M MSA-16 83 190575 101195 190575 1/3/19 SP-21 CUST-037 PROD-011 MATL-Silver SIZE-XL MSA-35 918946 689773 918946 1/3/19 SP-14 CUST-098 PROD-012 MATL-Sapphire SIZE-XS MSA-73 490336 310573 490336 1/3/19 SP-20 CUST-149 PROD-012 MATL-Emerald SIZE-S MSA-60 60736 8475 60736 1/3/19 SP-14 CUST-004 PROD-014 MATL-Moonstone SIZE-XS MSA-75 34 59618 43051 59618 1/3/19 SP-08 CUST-038 PROD-021 MATL-Garnet SIZE-M MSA-19 13 361512 207763 $61512 1/3/19 SP-10 CUST-110 PROD-023 MATL-Gold SIZE-XS MSA-58 24 550935 348767 550935 1/3/19 SP-08 CUST-110 PROD-024 MATL-Moonstone SIZE-M MSA-52 99 164921 03619 164921 1/3/19 SP-12 CUST-142 PROD-029 MATL-Peridot SIZE-XL MSA-15 w 117162 82454 117162 1/3/19 SP-04 CUST-066 PROD-044 MATL-Quartz SIZE-L MSA-58 315886 59050 315886 1/3/19 SP-1 CUST-004 PROD-044 MATL-Garnet SIZE-L MSA-11 156650 83840 156650 1/3/19 SP-10 CUST-108 PROD-046 MATL-Bronze SIZE-XL MSA-37 62 115520 68744 115520 1/3/19 SP-15 CUST-057 PROD-047 MATL-Ruby SIZE-XL MSA-41 60 784944 414195 784944 1/3/19 SP-07 CUST-037 PROD-052 MATL-Opal SIZE-M MSA-38 21 106092 50276 106092 1/3/19 SP-15 CUST-142 PROD-054 MATL-Peridot SIZE-XS MSA-17 48 61581 37433 51581 1/3/19 SP-1 CUST-004 PROD-063 MATL-Brass SIZE-L MSA-69 28 497475 202331 497475 1/3/19 SP-06 CUST-048 PROD-066 MATL-Bronze SIZE-M MSA-08 59 15360 10360 15360 1/3/19 SP-02 CUST-083 PROD-070 MATL-Bronze SIZE-M MSA-27 98 622500 466428 622500 1/3/19 SP-04 CUST-064 PROD-071 MATL-Jasper SIZE-S MSA-33 26 64080 29057 64080 1/3/19 SP-09 CUST-092 PROD-071 MATL-Brass SIZE-L MSA-10 238968 147478 238968 1/3/19 SP-17 CUST-010 PROD-071 MATL-Jade SIZE-XL MSA-59 586768 60871 586768 1/3/19 SP-22 CUST-109 PROD-071 MATL-Gold SIZE-XL MSA-47 98 104104 48840 104104 1/3/19 SP-21 CUST-148 PROD-081 MATL-Bronze SIZE-L MSA-08 66 266000 171684 266000 1/3/19 SP-06 CUST-075 PROD-096 MATL-Silver SIZE-XL MSA-29 25 388720 180627 388720 1/3/19 SP-0 CUST-059 PROD-102 MATL-Opal SIZE-S MSA-08 31083 3583 31083 1/3/19 SP-0 CUST-067 PROD-106 MATL-Amethyst SIZE-L MSA-46 134872 67511 134872 1/3/19 SP-06 CUST-042 PROD-109 MATL-Silver SIZE-M MSA-36 251785 139720 251785 1/3/ 19 SP-17 CUST-126 PROD-110 MATL-Ruby SIZE-M MSA-55 54 29928 23539 29928 1/3/19 SP-01 CUST-092 PROD-119 MATL-Topaz SIZE-XS MSA-38 256802 96712 256802 1/3/19 SP-0 CUST-031 PROD-122 MATL-Silver SIZE-XL MSA-62 25 465420 292027 465420 1/3/19 SP-02 CUST-058 PROD-132 MATL-Ruby SIZE-M MSA-62 56 406656 307590 406656 1/3/19 SP-04 CUST-052 PROD-132 MATL-Peridot SIZE-M MSA-70 63 523069 333227 523069 1/3/19 SP-17 CUST-149 PROD-133 MATL-Silver SIZE-S MSA-44 129616 80970 129616 1/3/19 SP-15 CUST-068 PROD-140 MATL-Opal SIZE-XS MSA-11 67 241500 53356 241500 1/3/19 SP-15 CUST-114 PROD-140 MATL-Ruby SIZE-L MSA-43 16 271836 165633 271836 1/3/19 SP-18 CUST-057 PROD-142 MATL-Quartz SIZE-S MSA-47 21 586690 165588 586690 1/3/19 SP-18 CUST-092 PROD-145 MATL-Quartz SIZE-XL MSA-75 60 192129 97605 192129 1/3/19 SP-1 CUST-063 PROD-154 MATL-Tanzanite SIZE-M MSA-47 80 211500 113817 211500 1/3/19 SP-13 CUST-108 PROD-167 MATL-Jade SIZE-L MSA-41 37 603345 353846 603345 1/3/19 SP-06 CUST-034 PROD-169 MATL-Silver SIZE-XL MSA-65 72 340861 176552 340861DAA Instructions DAA Rubric DAA Questions DAA Data DAA Re Data Analytics Assignment INSTRUCTIONS: ANSWERS: NOTES: 1) Use a DATE/TIME function to generate the current date and time in the following cell: _ 2) Use a formula to calculate the second highest Sales across all transactions. _ 3) Analyzing the data from the BAA Data worksheet, use UMIF8 to calculate the ollowing. 3a) Total Sales for sales to GUST-146 Please enter your formula 3h) Total Sales for sales of PROD-070 during Quarter 1 (January through March 2019). yellow cell 4) Use Pivot Tables to answer the following questions: Please enter your formul 4a) What 15 Total Sales made by SP-08 during Quarter 4 (October through December 2019)? the 3:11;]: 5:21:12: :3: 4b) What percentage of all Total Sales were sales that were made of PROD- 166 to CUST- 041? 5) Use Logical Formulas (and any other formulas) to meet the following request: Add a new column for Commissions on the BAA Data worksheet. Note that commissions are only earned on sales transactions where Collection/Sales is greater than or equal to 55%. In that new column, calculate Commissions as 1% in Sales for only those transactions (and $0 for all transactions that fail to meet the threshold). 6) Create dened names for every column on the BAA Data worksheet. Complete the steps as 7) Use Data Validation to restrict values for the entire Date column (excluding the header row) on the DAA Data worksheet to have to be less than or equal to today's date (so that dates which have not occurred yet Complete the steps as cannot be entered). 8) You must use what you know about LOOKUPs as well as other formulas to calculate Shipping Expense, for each transaction, per the details below. You may use additional columns or any other reference information or Please enter your formula yellow cell. Please enter your formula yellow cell. Complete the steps as tables you create to solve for the answer. To calculate Shipping Expense: [# units multiplied by X] + [Sales $ divided by 10] Shipping Expense is calculated for each transaction based on its Warehouse and Product information on the BAA Data worksheet. Use any of VLOOKUP, XLOOKUP, or INDEX/MATCH to take that information, Complete the Steps as look it up on the BAA Reference worksheet, and then return a Warehouse Location and a Product Location. Once you have each of those, then take the number at the end of each location designation (i.e., 2 for Location 2), calculate X by taking the absolute value difference (for example, Location 3 & Location 2 gives you 3 and 2 and then the absolute value of 3 minus 2 = 1). Then plug X (where in that example, X = 1) into this equation: [# units multiplied by X] + [Sales $ divided by 10] 9) Protect the BAA Data worksheet with the option to allow users to only select locked and unlocked cells. C 1 to th t . Please use the password "acct307". amp 6 e s CPS as
