Question: I need help creating descriptives (pivot tables, graphs, etc.), for this data. The data set is so large I'm not gonna include the entire thing,
File 3: Sales Data Analyst You are the chief analyst that is responsible for monitoring sales at your company. The CEO has tasked you with providing the board of directors with some high-level insights regarding the sales trends of the company and its various locations. He has provided you with customer orders from various sales centers and other data that might be helpful in your analysis. He noted the following: O . The company sells five different items, each with its own selling price. Customer loyalty is important. Customers with a code of 2 receive a 2% discount of the total price, code 3 customers receive a 4% discount, and code 4 customers receive a 6% discount. The company charges a flat shipping fee based on the shipping mode selected: Standard Class - $0 (free), First Class - $100, Second Class - $50, Same Day - $150. o Note that the shipping fee is NOT revenue for the company. It is what is being charged by the carrier. As such, the fee should NOT be incorporated into revenue, discount, and net sale calculations. Requirements: I 1. Create 4 descriptives that provide insights into the profitability of the venture. Each of the following variables MUST show up in at least once: o Order Date (feel free to analyze by month) o Region Product Quantity Revenue (Price) Discounts Net Sales (Revenue - Discounts) 2. A descriptive that answers the following: Are customers who paid for quicker shipping getting their orders processed quicker? o Data Formulas Review View Insert Draw Page Layout Home 101 = 11AA IG Calibri (Body) v a. Au Paste I U B fx Order Date . C1 G D B Ship Date Region Item Code Shipping Mode Deder Date 1 Order ID Customer Code E 2 Central 4 1/1/21 1/4/21 2 CA-2021-04574 4 Central D 4 1/1/21 1/1/21 3 CA-2021-04575 E 4 Central 1 1/1/21 1/1/21 4 CA-2021-04576 2 D 1/1/21 1/2/21 4 Central 5. CA 2021-04577 1 4 East 1/1/21 1/1/21 6 CA 2021-04578 1 E 1/4/21 3 South 1/1/21 7 CA-2021-04529 1/1/21 4 South 1/1/21 B 4 & CA 2021-06580 4 East 1/2/21 9 CA 2021-00001 3 1/3/21 1 1/2/21 10 CA 2021-00002 1/5/21 2 East E 2 1/2/21 1/3/21 4 East 11 CA 2021-04581 1 1/2/21 12 CA 2021-04582 1/6/21 1 East E 2 1/2/21 13 CA 2021-04583 1/4/21 3 East B 14 CA 2021-04584 4 1/2/21 1/4/21 1 East 15 CA 2021-04585 1 1/2/21 1/4/21 1 East C 16 CA-2021-04586 3 1/2/21 1/3/21 4 East E 17 CA 2021-04587 3 1/2/21 1/3/21 4 East 8 18 CA 2021-04588 1 1/2/21 1/5/21 1 East D 19 CA 2021-04589 3 1/2/21 1/6/21 1 East D 20 CA 2021-04590 1 1/2/21 1/5/21 3 Central D 21 CA 2021-04591 4 1/2/21 1/4/21 1 West 22 CA 2021-04592 3 1/2/21 1/5/21 2 West D 23 CA 2021-04593 2 1/2/21 1/5/21 1 West E 24 CA 2021-04594 2 1/2/21 1/3/21 4 West E 25 CA 2021-04595 3 1/2/21 1/7/21 1 West D 26 CA 2021-04596 2 1/2/21 1/4/21 2 West D 27 CA 2021-04597 2 1/2/21 1/3/21 2 West 2 CA 2021-04598 C 1 1/2/21 1/5/21 3 West O 29 CA 2021-00003 3 1/3/21 1/6/21 1 West 30 CA 2021-00004 1 1/3/21 1/5/21 3 West A 21 CA 2021-00005 1 1/3/21 32 CA 2021-00006 1/4/21 4 West D 2 1/3/21 1/3/21 33 CA 2021-00007 4 Central c 4 1/3/21 34 CA 2021-00008 1/6/21 3 West D 1 1/3/21 1/5/21 35 CA 2021-00009 2 West D 3 1/3/21 36 CA 2021-04599 1/4/21 2 West 4 E 1/3/21 1/4/21 37 CA 2021-04600 4 West 2 B 38 CA2021-04601 1/3/21 1/4/21 4 West 2 D 39 CA 2021-04602 1/3/21 1/8/21 4 West 4 E 40 CA 2021-04603 1/3/21 1/4/21 2 West 1 41 CA 2021-04604 1/3/21 1/6/21 4. South B 42 CA 2021-04605 1/6/21 2 3 South D APARE 1/8/21 1/5/21 1 South 0 Sales Data TRA Purchase Listing Shipping into Discount Ready + 1/3/21 Wrap Text Date E Merge & Center $ % ) Conditional Format Formatting as Table Cell Styles G H 160 Region Item Code Quantity purchased 2 Central E 4 Central D 4 Central E 4 Central D 4 East 3 South E 4 South B 4 East c 2 East E 4 East 1 East E 3 East B 1 East 1 East 4 East E 4 East B 1 East D 1 East D 3 Central D 1 West 2 West D 1 West E 4 West E 1 West D 2 West D 2 West C 3 West D 1 West B 3 West A 4 West D 4 Central 3 West D 2 West D 2 West E 4 West B D 4 West E 2 West C 3. South B 3 South D 1 South D Ft Discount + Product Revenue (Price) 24 Accessories 100 21 Machines 300 15 Accessories 100 12 Machines 300 21 Furniture 225 14 Accessories 100 19 Storage 160 19 Furniture 225 16 Accessories 100 17 Furniture 225 22 Accessories 100 20 Storage 160 19 Paper 125 10 Furniture 225 19 Accessories 100 10 Storage 15 Machines 300 11 Machines 300 11 Machines 300 20 Furniture 225 15 Machines 300 18 Accessories 100 10 Accessories 100 12 Machines 300 21 Machines 300 15 Furniture 225 15 Machines 300 11 Storage 160 13 Paper 125 25 Machines 300 19 Furniture 225 14 Machines 300 13 Machines 300 10 Accessories 100 19 Storage 160 19 Machines 300 24 Accessories 100 23 Furniture 225 19 Storage 160 11 Machines 300 25 Machines 300 TES an Discount DiscounNet al 0.06 6 0.06 18 0 0 0.02 6 0 0 0 0 0.06 9.6 0.04 9 0 0 0.02 4.5 0 0 0.02 3.2 0.06 7.5 0 0 0.04 4 0.04 6.4 0 0 0.04 12 0 0 0.06 13.5 0.04 12 0.02 2 0.02 2 0.00 12 0.02 6 0.02 4.5 0 0 0.04 6.4 0 0 0 0 0.02 4,5 0.06 18 0 0 0.04 4 0.06 9.6 0.02 6 0.02 2 0.05 13.5 0 0.06 18 0.02 4 West OW Insert 27.0 Delete Conditional Format Cell Formatting as Table Styles Sort & Filter Format Find & Select Analyze Data Sensiti 160 Paper M duc Revenue Pricel Discour DiscourNet Sales (Revenues Discounts) essories 100 0.06 6 chines 300 0.06 18 tessories 100 0 0 chines 300 0.02 6 rniture 225 0 0 Ecessories 100 0 0 torage 0.06 9,6 urniture 225 0.04 9 ccessories 100 0 0 Furniture 225 0.02 4.5 Accessories 100 0 0 Storage 160 0.02 3.2 125 0.06 7.5 Furniture 225 0 0 Accessories 100 0.04 4 Storage 160 0.04 6.4 Machines 300 0 0 Machines 300 0.04 12 Machines 300 0 0 Furniture 225 0.06 135 5 Machines 300 0.04 12 8 Accessories 100 0.02 2 o Accessories 100 0.02 2 2 Machines 300 0.04 12 21 Machines 300 0.02 6 15 Furniture 225 0.02 45 15 Machines 300 0 0 11 Storage 160 0.04 6.4 13 Paper 125 D 25 Machines 300 0 0 19 Furniture 225 0.02 4.5 14 Machines 300 0.06 18 13 Machines 300 0 0 10 Accessories 100 0.04 4 19 Storage 160 0.06 9.6 19 Machines 300 0.02 6 24 Accesories 100 0.02 2 23 Furniture 225 0.06 13.5 19 Storage 160 O 0 12 Machines 300 0.06 18 25 Machines 300 0.02 6 an Ship Mode Type 94 First Class 282 Same Day 100 Same Day 294 Same Day 225 Same Day 100 Second Class 150 4 Same Day 216 Same Day 100 First Class 220.5 Same Day 100 Standard Class 156.8 Second Class 117.5 Standard Class 225 Standard Class 96 Same Day 153,6 Same Day 300 Standard Class 288 Standard Class 300 Second Class 211.5 Standard Class 288 First Class 98 Standard Class 98 Same Day 288 Standard Class 294 First Class 220.5 First Class 300 Second Class 153.6 Standard Class 125 Second Class 300 Same Day 220.5 Same Day 282 Second Class 300 First Class 96 First Class 150.4 Same Day 294 Same Day 98 Same Day 2115 First Class 160 Second Class 282 Second Clow 294 Standard Class Fiat fee 100 150 150 150 150 50 150 150 100 150 0 50 0 0 150 150 0 o 50 O 100 0 150 0 100 100 50 0 50 150 150 50 100 100 150 -150 150 100 50 50 0 + 101 File 3: Sales Data Analyst You are the chief analyst that is responsible for monitoring sales at your company. The CEO has tasked you with providing the board of directors with some high-level insights regarding the sales trends of the company and its various locations. He has provided you with customer orders from various sales centers and other data that might be helpful in your analysis. He noted the following: O . The company sells five different items, each with its own selling price. Customer loyalty is important. Customers with a code of 2 receive a 2% discount of the total price, code 3 customers receive a 4% discount, and code 4 customers receive a 6% discount. The company charges a flat shipping fee based on the shipping mode selected: Standard Class - $0 (free), First Class - $100, Second Class - $50, Same Day - $150. o Note that the shipping fee is NOT revenue for the company. It is what is being charged by the carrier. As such, the fee should NOT be incorporated into revenue, discount, and net sale calculations. Requirements: I 1. Create 4 descriptives that provide insights into the profitability of the venture. Each of the following variables MUST show up in at least once: o Order Date (feel free to analyze by month) o Region Product Quantity Revenue (Price) Discounts Net Sales (Revenue - Discounts) 2. A descriptive that answers the following: Are customers who paid for quicker shipping getting their orders processed quicker? o Data Formulas Review View Insert Draw Page Layout Home 101 = 11AA IG Calibri (Body) v a. Au Paste I U B fx Order Date . C1 G D B Ship Date Region Item Code Shipping Mode Deder Date 1 Order ID Customer Code E 2 Central 4 1/1/21 1/4/21 2 CA-2021-04574 4 Central D 4 1/1/21 1/1/21 3 CA-2021-04575 E 4 Central 1 1/1/21 1/1/21 4 CA-2021-04576 2 D 1/1/21 1/2/21 4 Central 5. CA 2021-04577 1 4 East 1/1/21 1/1/21 6 CA 2021-04578 1 E 1/4/21 3 South 1/1/21 7 CA-2021-04529 1/1/21 4 South 1/1/21 B 4 & CA 2021-06580 4 East 1/2/21 9 CA 2021-00001 3 1/3/21 1 1/2/21 10 CA 2021-00002 1/5/21 2 East E 2 1/2/21 1/3/21 4 East 11 CA 2021-04581 1 1/2/21 12 CA 2021-04582 1/6/21 1 East E 2 1/2/21 13 CA 2021-04583 1/4/21 3 East B 14 CA 2021-04584 4 1/2/21 1/4/21 1 East 15 CA 2021-04585 1 1/2/21 1/4/21 1 East C 16 CA-2021-04586 3 1/2/21 1/3/21 4 East E 17 CA 2021-04587 3 1/2/21 1/3/21 4 East 8 18 CA 2021-04588 1 1/2/21 1/5/21 1 East D 19 CA 2021-04589 3 1/2/21 1/6/21 1 East D 20 CA 2021-04590 1 1/2/21 1/5/21 3 Central D 21 CA 2021-04591 4 1/2/21 1/4/21 1 West 22 CA 2021-04592 3 1/2/21 1/5/21 2 West D 23 CA 2021-04593 2 1/2/21 1/5/21 1 West E 24 CA 2021-04594 2 1/2/21 1/3/21 4 West E 25 CA 2021-04595 3 1/2/21 1/7/21 1 West D 26 CA 2021-04596 2 1/2/21 1/4/21 2 West D 27 CA 2021-04597 2 1/2/21 1/3/21 2 West 2 CA 2021-04598 C 1 1/2/21 1/5/21 3 West O 29 CA 2021-00003 3 1/3/21 1/6/21 1 West 30 CA 2021-00004 1 1/3/21 1/5/21 3 West A 21 CA 2021-00005 1 1/3/21 32 CA 2021-00006 1/4/21 4 West D 2 1/3/21 1/3/21 33 CA 2021-00007 4 Central c 4 1/3/21 34 CA 2021-00008 1/6/21 3 West D 1 1/3/21 1/5/21 35 CA 2021-00009 2 West D 3 1/3/21 36 CA 2021-04599 1/4/21 2 West 4 E 1/3/21 1/4/21 37 CA 2021-04600 4 West 2 B 38 CA2021-04601 1/3/21 1/4/21 4 West 2 D 39 CA 2021-04602 1/3/21 1/8/21 4 West 4 E 40 CA 2021-04603 1/3/21 1/4/21 2 West 1 41 CA 2021-04604 1/3/21 1/6/21 4. South B 42 CA 2021-04605 1/6/21 2 3 South D APARE 1/8/21 1/5/21 1 South 0 Sales Data TRA Purchase Listing Shipping into Discount Ready + 1/3/21 Wrap Text Date E Merge & Center $ % ) Conditional Format Formatting as Table Cell Styles G H 160 Region Item Code Quantity purchased 2 Central E 4 Central D 4 Central E 4 Central D 4 East 3 South E 4 South B 4 East c 2 East E 4 East 1 East E 3 East B 1 East 1 East 4 East E 4 East B 1 East D 1 East D 3 Central D 1 West 2 West D 1 West E 4 West E 1 West D 2 West D 2 West C 3 West D 1 West B 3 West A 4 West D 4 Central 3 West D 2 West D 2 West E 4 West B D 4 West E 2 West C 3. South B 3 South D 1 South D Ft Discount + Product Revenue (Price) 24 Accessories 100 21 Machines 300 15 Accessories 100 12 Machines 300 21 Furniture 225 14 Accessories 100 19 Storage 160 19 Furniture 225 16 Accessories 100 17 Furniture 225 22 Accessories 100 20 Storage 160 19 Paper 125 10 Furniture 225 19 Accessories 100 10 Storage 15 Machines 300 11 Machines 300 11 Machines 300 20 Furniture 225 15 Machines 300 18 Accessories 100 10 Accessories 100 12 Machines 300 21 Machines 300 15 Furniture 225 15 Machines 300 11 Storage 160 13 Paper 125 25 Machines 300 19 Furniture 225 14 Machines 300 13 Machines 300 10 Accessories 100 19 Storage 160 19 Machines 300 24 Accessories 100 23 Furniture 225 19 Storage 160 11 Machines 300 25 Machines 300 TES an Discount DiscounNet al 0.06 6 0.06 18 0 0 0.02 6 0 0 0 0 0.06 9.6 0.04 9 0 0 0.02 4.5 0 0 0.02 3.2 0.06 7.5 0 0 0.04 4 0.04 6.4 0 0 0.04 12 0 0 0.06 13.5 0.04 12 0.02 2 0.02 2 0.00 12 0.02 6 0.02 4.5 0 0 0.04 6.4 0 0 0 0 0.02 4,5 0.06 18 0 0 0.04 4 0.06 9.6 0.02 6 0.02 2 0.05 13.5 0 0.06 18 0.02 4 West OW Insert 27.0 Delete Conditional Format Cell Formatting as Table Styles Sort & Filter Format Find & Select Analyze Data Sensiti 160 Paper M duc Revenue Pricel Discour DiscourNet Sales (Revenues Discounts) essories 100 0.06 6 chines 300 0.06 18 tessories 100 0 0 chines 300 0.02 6 rniture 225 0 0 Ecessories 100 0 0 torage 0.06 9,6 urniture 225 0.04 9 ccessories 100 0 0 Furniture 225 0.02 4.5 Accessories 100 0 0 Storage 160 0.02 3.2 125 0.06 7.5 Furniture 225 0 0 Accessories 100 0.04 4 Storage 160 0.04 6.4 Machines 300 0 0 Machines 300 0.04 12 Machines 300 0 0 Furniture 225 0.06 135 5 Machines 300 0.04 12 8 Accessories 100 0.02 2 o Accessories 100 0.02 2 2 Machines 300 0.04 12 21 Machines 300 0.02 6 15 Furniture 225 0.02 45 15 Machines 300 0 0 11 Storage 160 0.04 6.4 13 Paper 125 D 25 Machines 300 0 0 19 Furniture 225 0.02 4.5 14 Machines 300 0.06 18 13 Machines 300 0 0 10 Accessories 100 0.04 4 19 Storage 160 0.06 9.6 19 Machines 300 0.02 6 24 Accesories 100 0.02 2 23 Furniture 225 0.06 13.5 19 Storage 160 O 0 12 Machines 300 0.06 18 25 Machines 300 0.02 6 an Ship Mode Type 94 First Class 282 Same Day 100 Same Day 294 Same Day 225 Same Day 100 Second Class 150 4 Same Day 216 Same Day 100 First Class 220.5 Same Day 100 Standard Class 156.8 Second Class 117.5 Standard Class 225 Standard Class 96 Same Day 153,6 Same Day 300 Standard Class 288 Standard Class 300 Second Class 211.5 Standard Class 288 First Class 98 Standard Class 98 Same Day 288 Standard Class 294 First Class 220.5 First Class 300 Second Class 153.6 Standard Class 125 Second Class 300 Same Day 220.5 Same Day 282 Second Class 300 First Class 96 First Class 150.4 Same Day 294 Same Day 98 Same Day 2115 First Class 160 Second Class 282 Second Clow 294 Standard Class Fiat fee 100 150 150 150 150 50 150 150 100 150 0 50 0 0 150 150 0 o 50 O 100 0 150 0 100 100 50 0 50 150 150 50 100 100 150 -150 150 100 50 50 0 + 101
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help