Excel Project Overview Complete the steps necessary in Excel to answer all of the requests on the Instructions worksheet using the data from both 2022 and 2021 Data worksheets. Make sure to properly label your work for each Instruction. Please note that gross inefficiencies [such as but not limited to (a) using manual text in formulas instead of cell references (as talked about in Topic 4 Cell References) and (b) inconsistent formulas (where one formula in an array is meaningfully different than the other)] should be avoided whenever possible for full credit to be awarded. INSTRUCTIONS: This dataset is the 2021 and 2022 raw data exported from software for the same gemstone company that we've seen in our previous Excel Assignments (2023 data). That company's start date was January 1st, 2020. 1) Using Pivot Tables: Create a series of tables. You should create separate tables for 2022 and 2021 data. You will need to calculate a new column called Gross Profit (calculated by taking Sales - COGS). Table A: a table displaying total sales and total gross profit by salesperson. Table B: a table displaying total sales and total gross profit by month. Tables C-D: two cross-sectional tables with Table C displaying total sales and Table D total gross profit, both by salesperson [rows] and month [columns]. Please label the tables 1A, 1B, and 1C-1D accordingly. 2) Using Pivot Charts: Management has all kinds of questions about its performance since it began operations. They are worried that sales have slowed down in 2023 and that they will have to fire some of the sales staff and stop selling certain products or materials. Create a series of charts that will help inform management's decisions. Are 2023 sales really worse than 2022 and 2021? They should answer that question at a minimum, but you should go deeper (see hint for more). You should create separate charts for 2022, 2021, and even 2023 (from Excel Assignment 1 or 2) data. I recommend that you could make a copy of your original 2023 data worksheet from an earlier Excel Assignment and insert it into this project for ease of reference. NOTE: Charts should be easy to understand and informative as they are presented. Your grade for this instruction will be based as much on presentation of your information as it will on functionality. 3) Using LOOKUPs / reference formulas & text manipulation formulas: Take steps to calculate Shipping Expense for each sales transaction (for both 2022 and 2021). The Shipping Expense worksheet contains information to help you calculate. There are three components to each calculation, which depend on the transaction's size, material, and product. To calculate Shipping Expense: [# units for the transaction * (Size per unit basis from reference sheet column B)] + [# units for the transaction * (Material per unit basis from reference sheet column D)] + [# units for the transaction * (Product per unit basis from reference sheet column F)]. 4) Using SUMIFS or other formulas (but they must be formulas such as lookups, non-formula tools like Pivot Tables are not allowed), find the answers for the following requests (for both 2022 and 2021). NOTE: It is important that your formulas are able to be updated with other inputs and that they update. In other words, manual text inside your formulas will break these solutions and will significantly negatively impact your grade! 4a: The date with the largest shipping expense of SIZE-XL 4b: Annual total sales for the top 3 salespeople and bottom 3 salespeople 4c: Summary of how many units were sold by each warehouse for MATL-Gold 4d: Summary of total sales sold by each customer 5) Using MACROs: Create two MACROs (pick either 2022 or 2021), one MACRO to replicate Instruction 3 (Shipping expense) and another MACRO to replicate Instruction 4 (SUMIFS / requests). Please name each MACRO after the Instruction it is replicating (see below). Each MACRO should fully replicate the steps involved in each Instruction. MACRO 5-3: Instruction 3 MACRO 5-4: Instruction 4 6) Consider improving the visual presentation of your data. This could include tasks such as specific kinds of number formatting, and even other formatting like colors and borders. ** EXTRA CREDIT / OPTIONAL: FOR UP TO 10 POINTS OF EXTRA CREDIT * * Extra Credit) Using any topics that were not explicitly asked above: Incorporate as many topics as you would like into your submission. Get creative! For example, you could use Data Validation to create "edit tests" to help prevent errors from being input into the dataset. I will grade on a 1 to 10 point scale, more points for how many topics you incorporated, how creative your approach was, and how insightful it could be to highlighting something that would be helpful for anyone looking at or analyzing the data.

Data 2022 Date Salesperson Customer Product Material Size # Units Warehouse Sales COGS 6/3/2022 SP-11 CUST-122 PROD-075 MATL-Peridot SIZE-L 1 MSA-42 505873 379013 7/16/2022 SP-19 CUST-085 PROD-056 MATL-Aquamarine SIZE-S 91 MSA-05 675580 349670 8/15/2022 SP-02 CUST-025 PROD-184 MATL-Amethyst SIZE-M 86 MSA-37 310518 123323 1/21/2022 SP-15 CUST-048 PROD-118 MATL-Quartz SIZE-L 22 MSA-33 358892 159056 8/3/2022 SP-24 CUST-117 PROD-026 MATL-Sapphire SIZE-M 69 MSA-09 21125 8341 2/14/2022 SP-23 CUST-004 PROD-136 MATL-Brass SIZE-L 78 MSA-16 226828 117675 12/21/2022 SP-19 CUST-126 PROD-109 MATL-Sapphire SIZE-XS 92 MSA-44 2410 1363 4/29/2022 SP- 18 CUST-046 PROD-153 MATL-Peridot SIZE-XL 58 MSA-25 530228 290622 5/17/2022 SP-15 CUST-034 PROD-138 MATL-Gold SIZE-S 21 MSA-36 520674 381482 11/11/2022 SP-10 CUST-064 PROD-110 MATL-Tanzanite SIZE-XS 34 MSA-48 224094 142895 10/12/2022 SP-17 CUST-136 PROD-066 MATL-Emerald SIZE-S 40 MSA-21 384008 305016 8/28/2022 SP-11 CUST-065 PROD-033 MATL-Gold SIZE-L 2 MSA-35 726827 441585 7/10/2022 SP-03 CUST-055 PROD-197 MATL-Emerald SIZE-XS 27 MSA-04 372495 204142 7/10/2022 SP-13 CUST-062 PROD-018 MATL-Opal SIZE-XS 41 MSA-34 51960 38096 12/27/2022 SP-10 CUST-069 PROD-127 MATL-Topaz SIZE-XL 53 MSA-33 309244 170227 10/22/2022 SP-05 CUST-141 PROD-004 MATL-Sapphire SIZE-XS 37 MSA-52 701208 384590 7/9/2022 SP-05 CUST-104 PROD-071 MATL-Bronze SIZE-S 12 MSA-01 367607 233821 8/6/2022 SP-20 CUST-008 PROD-106 MATL-Garnet SIZE-XL 39 MSA-08 116930 53610 3/8/2022 SP-06 CUST-107 PROD-041 MATL-Peridot SIZE-S 46 MSA-38 170128 108533 4/8/2022 SP-15 CUST-144 PROD-019 MATL-Emerald SIZE-XS 13 MSA-29 387844 247288 8/6/2022 SP-19 CUST-008 PROD-094 MATL-Bronze SIZE-XS 83 MSA-63 21528 9318 5/4/2022 SP-07 CUST-056 PROD-005 MATL-Brass SIZE-XL 39 MSA-45 164934 97508 6/29/2022 SP-23 CUST-108 PROD-138 MATL-Peridot SIZE-S 16 MSA-36 164775 67355 9/21/2022 SP-23 CUST-066 PROD-015 MATL-Amethyst SIZE-L 59 MSA-70 78584 30669 5/29/2022 SP-19 CUST-034 PROD-092 MATL-Bronze SIZE-XS 48 MSA-28 357480 164002 3/27/2022 SP-17 CUST-100 PROD-008 MATL-Moonstone SIZE-L 8 MSA-17 45927 31682 6/29/2022 SP-23 CUST-029 PROD-070 MATL-Aquamarine SIZE-XS 25 MSA-50 645579 366267 5/30/2022 SP-04 CUST-024 PROD-019 MATL-Jade SIZE-XS 98 MSA-38 37950 22114 11/28/2022 SP-03 CUST-100 PROD-151 MATL-Jade SIZE-L 57 MSA-12 423464 175738 8/3/2022 SP-19 CUST-038 PROD-067 MATL-Brass SIZE-XS 73 MSA-63 35496 26682 8/15/2022 SP-20 CUST-014 PROD-046 MATL-Brass SIZE-XS 94 MSA-36 84490 34412 6/11/2022 SP-17 CUST-102 PROD-084 MATL-Amethyst SIZE-XL 45 MSA-05 407537 253439 4/9/2022 SP-03 CUST-060 PROD-023 MATL-Citrine SIZE-M 89 MSA-64 429456 280448 

Shipping Expense Shipping Shipping Shipping Size Expense (per Material Expense (per Product Expense (per unit basis unit basis) unit basis KS Gold 55 Product 1 W S 15 Silver 40 Product 2 M 35 Bronze 29 Product 3 L 75 Copper 27 Product 4 XL 100 Brass 27 Product 5 Sapphire 23 Product 6 Ruby 21 Product 7 Emerald 19 Product 8 Topaz 18 Product 9 Jade 17 Product 10 Amethyst Product 11 Garnet 16 |Product 12 Aquamarine 13 Product 13 Peridot 10 Product 14 Tanzanite 9 Product 15 Opal Product 16 Moonstone 6 Product 17 Jasper Product 18 Citrine Product 19 Quartz Product 20 Product 21 Product 22 Product 23 Product 24 Product 25 Product 26 Product 27 Product 28 Product 29 Product 30 Product 31 Product 32 Product 33 Product 34 Product 35 Product 36 Product 37 Product 38 Product 39 Product 40 00 10 10 00 Product 41 Product 42 Product 43 Product 44