I need to transfer information over (from Sales Reference) to fill numbers in on my sales data sheet. (At least this is how I understand it, but I am not certain.)
I attached:
1. Directions (#4)
2. Sales Reference
3. Sales Data
Thank you for your help in advance.
3) Use the appropriate technique in the Date column only to create a rule which disallows any values other than 4 those between 1/1/2020 and 12/31/2020. 4) Create a new column called Sales and populate it with the appropriate calculation (in dollars). The calculation is as follows: Sales = [# units * (Product SP/unit per Sales Reference worksheet)] + [# units * (Material SP/unit)] + [# units * (Size SP/unit)]. Before you can perform this calculation, you will need to take information from the Sales Data worksheet and look it up on the information from the Sales Reference worksheet to find each set of values to plug into the formula. In addition, you need to find a way to convert the data from each worksheet to match formats (as opposed to the inconvenient two different formats they have now, i.e., PROD-001 vs. Product 1). As always, you should make sure to show all of your work to the extent that I can verify the methods that you used, and preferably you should use formulas and systematic approaches (i.e., avoiding manually-typed text and judgment). Using helper columns is acceptable. I recommend looking at text manipulation and conditional formulas 5 for the text conversion part of this instruction. Good luck! 5) Create a new column called COGS and populate it with the appropriate calculation (in dollars). The calculation is similar to that for Sales, only using the COGS (instead of the Sales column) information from the Sales Reference 6 worksheet. 6) Create a new column called Collections and populate it with the appropriate calculation (in dollars). Collections = 7 Sales * Collection %. = Calibri I 11 ab Wrap Text Custom * Cut E Copy Format Painter Paste BIU- A Insert Delete Format $ - % . Merge & Center - AutoSum - A Fill - Sort & Clear Filter Editing 9 Conditional Format as Cell Formatting Table Styles Styles 00 Clipboard Font Alignment Number Cells B4 X fx 6167 K M N O G Size XS H SP / Unit COGS / Unit $ 50 $ 15 $ 85 $ 27 $ 175 $ 56 $ 330 $ 89 $ 1,000 $ 410 Commissions Calculation Threshold percentage 80.00% Commission rate 0.50% S M L XL A B D E F. 1 Product SP / Unit COGS / Unit Material SP / Unit COGS / Unit 2. Product 1 $ 2,911 $ 1,747 Gold $ 64 $ 28 3 Product 2 $ 9,689 $ 5,523 Silver $ 49 $ 22 4 Product 3 $ 6,167 $ 4,502 Bronze $ 38 $ 18 5 Product 4 $ 5,130 $ 2,719 Copper $ 36 $ 16 6 Product 5 $ 5,507 $ 4,406 Brass $ 36 $ 14 7 Product 6 $ 1,315 $ 579 Sapphire $ 32 $ 16 8 Product 7 $ 1,802 $ 1,189 Ruby $ 30 $ 17 9 Product 8 $ 2,640 $ 1,716 Emerald $ 28 $ 16 10 Product 9 $ 9,776 $ 5,866 Topaz $ 27 $ 10 11 Product 10 $ 4,570 $ 3,016 Jade $ 26 $ 11 12 Product 11 $ 3,286 $ 2,267 Amethyst $ 26 $ 13 13 Product 12 $ 9,894 $ 7,816 Garnet S 25 $ 11 14 Product 13 $ 2,795 $ 1,286 Aquamarine $ 22 $ 9 15 Product 14 $ 7,488 $ 5,766 Peridot S 19 $ 7 16 Product 15 $ 3,443 $ 2,479 Tanzanite $ 18 $ 9 17 Product 16 $ 702 $ 442 Opal $ 16 $ 5 18 Product 17 $ 3,995 $ 1,918 Moonstone $ 15 $ 5 19 Product 18 S 8,575 $ 5,831 Jasper $ 14 $ 5 20 Product 19 $ 4,264 $ 3,155 Citrine $ 11 $ 4 21 Product 20 $ 9,304 $ 6,234 Quartz S 10 S 3 22 Product 21 $ 4,537 $ 2,813 23 Product 22 $ 2,765 $ 2,018 24 Product 23 $ 9,843 $ 6,103 25 Product 24 $ 4,835 $ 1,982 26 Product 25 $ 4,351 $ 3,307 27 Product 26 $ 5,791 $ 3,533 28 Product 27 $ 5,409 $ 3,786 29 Product 28 $ 828 $ 571 Overview Sales Instructions Sales Reference Sales Data + Customer Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Customer 6 Customer 7 Customer 8 Customer 9 Customer 10 Customer 11 Customer 12 Customer 13 Customer 14 Customer 15 Customer 16 Customer 17 Customer 18 Customer 19 Customer 20 Customer 21 Customer 22 Customer 23 Customer 24 Customer 25 Customer 26 Customer 27 Customer 28 Location MSA 19 MSA 59 MSA 7 MSA 53 MSA 63 MSA 8 MSA 42 MSA 9 MSA 9 MSA 37 MSA 72 MSA 46 MSA 75 MSA 47 MSA 4 MSA 27 MSA 52 MSA 55 MSA 27 MSA 31 MSA 51 MSA 4 MSA 56 MSA 38 MSA 22 MSA 48 MSA 23 MSA 62 Clipboard Font 12 Alignment Number Styles Cells K2 D H K Sales Size E Product PROD-090 PROD-146 PROD-053 PROD-148 PROD-086 PROD-144 PROD-009 PROD-072 PROD-116 PROD-076 PROD-165 PROD-012 Customer CUST-060 CUST-121 CUST-146 CUST-083 CUST-145 CUST-146 CUST-130 CUST-051 CUST-133 CUST-012 CUST-122 CUST-019 CUST-076 CUST-037 CUST-143 CUST-053 CUST-123 CUST-078 CUST-128 CUST-007 CUST-145 Warehouse # Units MSA-10 76 MSA-03 40 MSA-31 79 MSA-70 15 MSA-57 22 MSA-36 8 MSA-61 88 MSA-42 52 MSA-18 12 MSA-02 46 MSA-02 39 MSA-06 89 MSA-16 3 1 2. 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 PROD-016 A B Date Month Salesperson 1/2/2020 1 SP-01 1/2/2020 1 SP-01 1/2/2020 1 SP-02 1/2/2020 1 SP-02 1/2/2020 1 SP-04 1/2/2020 1 SP-04 1/2/2020 1 SP-05 1/2/2020 1 SP-05 1/2/2020 1 SP-05 1/2/2020 1 SP-07 1/2/2020 1 SP-07 1/2/2020 1 SP-08 1/2/2020 1 SP-08 1/2/2020 1 SP-09 1/2/2020 1 SP-11 1/2/2020 1 SP-12 1/2/2020 1 SP-12 1/2/2020 1 SP-13 1/2/2020 1 SP-15 1/2/2020 1 SP-15 1/2/2020 1 SP-16 1/2/2020 1 SP-16 1/2/2020 1 SP-17 1/2/2020 1 SP-17 1/2/2020 1 SP-19 1/2/2020 1 SP-19 1/2/2020 1 SP-20 1/2/2020 1 SP-21 Overview Sales Instructions F Material MATL-Silver MATL-Bronze MATL-Silver MATL-Bronze MATL-Silver MATL-Sapphire MATL-Emerald MATL-Garnet MATL-Opal MATL-Ruby MATL-Emerald MATL-Moonstone MATL-Copper MATL-Quartz MATL-Quartz MATL-Amethyst MATL-Tanzanite MATL-Citrine MATL-Opal MATL-Ruby MATL-Opal MATL-Jasper MATL-Aquamarine MATL-Moonstone MATL-Silver MATL-Emerald MATL-Citrine MATL-Jasper 86 SIZE-S SIZE-XL SIZE-XS SIZE-S SIZE-S SIZE-XS SIZE-S SIZE-XS SIZE-L SIZE-XL SIZE-M SIZE-L SIZE-L SIZE-S SIZE-M SIZE-S SIZE-S SIZE-L SIZE-XS SIZE-XS SIZE-XL SIZE-L SIZE-S SIZE-XL SIZE-M SIZE-XS SIZE-S SIZE-S 22 Collection % 100% 100% 100% 100% 100% 100% 50% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 64 30 46 53 PROD-087 PROD-111 PROD-059 PROD-073 PROD-103 PROD-093 PROD-158 PROD-097 PROD-138 PROD-008 PROD-065 PROD-053 PROD-145 PROD-005 PROD-023 Sales Data 98 MSA-11 MSA-32 MSA-39 MSA-32 MSA-44 MSA-49 MSA-08 MSA-62 MSA-20 MSA-42 MSA-01 MSA-35 MSA-35 MSA-13 MSA-74 71 44 82 80 85 CUST-026 CUST-021 CUST-072 CUST-048 CUST-001 CUST-035 CUST-085 Sales Reference 92 100 29 48 + Ready 3) Use the appropriate technique in the Date column only to create a rule which disallows any values other than 4 those between 1/1/2020 and 12/31/2020. 4) Create a new column called Sales and populate it with the appropriate calculation (in dollars). The calculation is as follows: Sales = [# units * (Product SP/unit per Sales Reference worksheet)] + [# units * (Material SP/unit)] + [# units * (Size SP/unit)]. Before you can perform this calculation, you will need to take information from the Sales Data worksheet and look it up on the information from the Sales Reference worksheet to find each set of values to plug into the formula. In addition, you need to find a way to convert the data from each worksheet to match formats (as opposed to the inconvenient two different formats they have now, i.e., PROD-001 vs. Product 1). As always, you should make sure to show all of your work to the extent that I can verify the methods that you used, and preferably you should use formulas and systematic approaches (i.e., avoiding manually-typed text and judgment). Using helper columns is acceptable. I recommend looking at text manipulation and conditional formulas 5 for the text conversion part of this instruction. Good luck! 5) Create a new column called COGS and populate it with the appropriate calculation (in dollars). The calculation is similar to that for Sales, only using the COGS (instead of the Sales column) information from the Sales Reference 6 worksheet. 6) Create a new column called Collections and populate it with the appropriate calculation (in dollars). Collections = 7 Sales * Collection %. = Calibri I 11 ab Wrap Text Custom * Cut E Copy Format Painter Paste BIU- A Insert Delete Format $ - % . Merge & Center - AutoSum - A Fill - Sort & Clear Filter Editing 9 Conditional Format as Cell Formatting Table Styles Styles 00 Clipboard Font Alignment Number Cells B4 X fx 6167 K M N O G Size XS H SP / Unit COGS / Unit $ 50 $ 15 $ 85 $ 27 $ 175 $ 56 $ 330 $ 89 $ 1,000 $ 410 Commissions Calculation Threshold percentage 80.00% Commission rate 0.50% S M L XL A B D E F. 1 Product SP / Unit COGS / Unit Material SP / Unit COGS / Unit 2. Product 1 $ 2,911 $ 1,747 Gold $ 64 $ 28 3 Product 2 $ 9,689 $ 5,523 Silver $ 49 $ 22 4 Product 3 $ 6,167 $ 4,502 Bronze $ 38 $ 18 5 Product 4 $ 5,130 $ 2,719 Copper $ 36 $ 16 6 Product 5 $ 5,507 $ 4,406 Brass $ 36 $ 14 7 Product 6 $ 1,315 $ 579 Sapphire $ 32 $ 16 8 Product 7 $ 1,802 $ 1,189 Ruby $ 30 $ 17 9 Product 8 $ 2,640 $ 1,716 Emerald $ 28 $ 16 10 Product 9 $ 9,776 $ 5,866 Topaz $ 27 $ 10 11 Product 10 $ 4,570 $ 3,016 Jade $ 26 $ 11 12 Product 11 $ 3,286 $ 2,267 Amethyst $ 26 $ 13 13 Product 12 $ 9,894 $ 7,816 Garnet S 25 $ 11 14 Product 13 $ 2,795 $ 1,286 Aquamarine $ 22 $ 9 15 Product 14 $ 7,488 $ 5,766 Peridot S 19 $ 7 16 Product 15 $ 3,443 $ 2,479 Tanzanite $ 18 $ 9 17 Product 16 $ 702 $ 442 Opal $ 16 $ 5 18 Product 17 $ 3,995 $ 1,918 Moonstone $ 15 $ 5 19 Product 18 S 8,575 $ 5,831 Jasper $ 14 $ 5 20 Product 19 $ 4,264 $ 3,155 Citrine $ 11 $ 4 21 Product 20 $ 9,304 $ 6,234 Quartz S 10 S 3 22 Product 21 $ 4,537 $ 2,813 23 Product 22 $ 2,765 $ 2,018 24 Product 23 $ 9,843 $ 6,103 25 Product 24 $ 4,835 $ 1,982 26 Product 25 $ 4,351 $ 3,307 27 Product 26 $ 5,791 $ 3,533 28 Product 27 $ 5,409 $ 3,786 29 Product 28 $ 828 $ 571 Overview Sales Instructions Sales Reference Sales Data + Customer Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Customer 6 Customer 7 Customer 8 Customer 9 Customer 10 Customer 11 Customer 12 Customer 13 Customer 14 Customer 15 Customer 16 Customer 17 Customer 18 Customer 19 Customer 20 Customer 21 Customer 22 Customer 23 Customer 24 Customer 25 Customer 26 Customer 27 Customer 28 Location MSA 19 MSA 59 MSA 7 MSA 53 MSA 63 MSA 8 MSA 42 MSA 9 MSA 9 MSA 37 MSA 72 MSA 46 MSA 75 MSA 47 MSA 4 MSA 27 MSA 52 MSA 55 MSA 27 MSA 31 MSA 51 MSA 4 MSA 56 MSA 38 MSA 22 MSA 48 MSA 23 MSA 62 Clipboard Font 12 Alignment Number Styles Cells K2 D H K Sales Size E Product PROD-090 PROD-146 PROD-053 PROD-148 PROD-086 PROD-144 PROD-009 PROD-072 PROD-116 PROD-076 PROD-165 PROD-012 Customer CUST-060 CUST-121 CUST-146 CUST-083 CUST-145 CUST-146 CUST-130 CUST-051 CUST-133 CUST-012 CUST-122 CUST-019 CUST-076 CUST-037 CUST-143 CUST-053 CUST-123 CUST-078 CUST-128 CUST-007 CUST-145 Warehouse # Units MSA-10 76 MSA-03 40 MSA-31 79 MSA-70 15 MSA-57 22 MSA-36 8 MSA-61 88 MSA-42 52 MSA-18 12 MSA-02 46 MSA-02 39 MSA-06 89 MSA-16 3 1 2. 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 PROD-016 A B Date Month Salesperson 1/2/2020 1 SP-01 1/2/2020 1 SP-01 1/2/2020 1 SP-02 1/2/2020 1 SP-02 1/2/2020 1 SP-04 1/2/2020 1 SP-04 1/2/2020 1 SP-05 1/2/2020 1 SP-05 1/2/2020 1 SP-05 1/2/2020 1 SP-07 1/2/2020 1 SP-07 1/2/2020 1 SP-08 1/2/2020 1 SP-08 1/2/2020 1 SP-09 1/2/2020 1 SP-11 1/2/2020 1 SP-12 1/2/2020 1 SP-12 1/2/2020 1 SP-13 1/2/2020 1 SP-15 1/2/2020 1 SP-15 1/2/2020 1 SP-16 1/2/2020 1 SP-16 1/2/2020 1 SP-17 1/2/2020 1 SP-17 1/2/2020 1 SP-19 1/2/2020 1 SP-19 1/2/2020 1 SP-20 1/2/2020 1 SP-21 Overview Sales Instructions F Material MATL-Silver MATL-Bronze MATL-Silver MATL-Bronze MATL-Silver MATL-Sapphire MATL-Emerald MATL-Garnet MATL-Opal MATL-Ruby MATL-Emerald MATL-Moonstone MATL-Copper MATL-Quartz MATL-Quartz MATL-Amethyst MATL-Tanzanite MATL-Citrine MATL-Opal MATL-Ruby MATL-Opal MATL-Jasper MATL-Aquamarine MATL-Moonstone MATL-Silver MATL-Emerald MATL-Citrine MATL-Jasper 86 SIZE-S SIZE-XL SIZE-XS SIZE-S SIZE-S SIZE-XS SIZE-S SIZE-XS SIZE-L SIZE-XL SIZE-M SIZE-L SIZE-L SIZE-S SIZE-M SIZE-S SIZE-S SIZE-L SIZE-XS SIZE-XS SIZE-XL SIZE-L SIZE-S SIZE-XL SIZE-M SIZE-XS SIZE-S SIZE-S 22 Collection % 100% 100% 100% 100% 100% 100% 50% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 64 30 46 53 PROD-087 PROD-111 PROD-059 PROD-073 PROD-103 PROD-093 PROD-158 PROD-097 PROD-138 PROD-008 PROD-065 PROD-053 PROD-145 PROD-005 PROD-023 Sales Data 98 MSA-11 MSA-32 MSA-39 MSA-32 MSA-44 MSA-49 MSA-08 MSA-62 MSA-20 MSA-42 MSA-01 MSA-35 MSA-35 MSA-13 MSA-74 71 44 82 80 85 CUST-026 CUST-021 CUST-072 CUST-048 CUST-001 CUST-035 CUST-085 Sales Reference 92 100 29 48 + Ready
