Sale Date Month Day Year Weekday Region SalesPerson Product Quantity Price Total
ID: 2750513 • Letter: S
Question
Sale Date Month Day Year Weekday Region SalesPerson Product Quantity Price TotalSale 2/23/13 2 23 2013 7 5 8 Screener 5 145000 725000 7/9/13 7 9 2013 3 5 6 Mining Jack 2 155000 310000 2/11/13 2 11 2013 2 2 1 Hammer Mill 1 155000 155000 1/11/13 1 11 2013 6 4 6 Elevator 2 155000 310000 8/11/13 8 11 2013 1 4 8 Hammer Mill 2 140000 280000 7/11/13 7 11 2013 5 2 6 Elevator 3 155000 465000 2/8/13 2 8 2013 6 1 6 Transport Train 5 140000 700000 8/18/13 8 18 2013 1 1 6 Elevator 3 145000 435000 7/18/13 7 18 2013 5 4 8 Elevator 3 155000 465000 8/23/13 8 23 2013 6 4 3 Elevator 3 140000 420000 2/6/13 2 6 2013 4 4 8 Lifting Plane 5 140000 700000 1/6/13 1 6 2013 1 1 6 Transport Train 2 155000 310000 4/11/13 4 11 2013 5 2 6 Hammer Mill 1 150000 150000 6/2/13 6 2 2013 1 4 6 Screener 5 155000 775000 2/11/13 2 11 2013 2 1 1 Elevator 3 150000 450000 12/8/13 12 8 2013 1 5 2 Hammer Mill 5 140000 700000 8/18/13 8 18 2013 1 2 5 Lifting Plane 1 150000 150000 11/2/13 11 2 2013 7 1 6 Transport Train 5 140000 700000 7/8/13 7 8 2013 2 4 5 Elevator 1 155000 155000 2/10/13 2 10 2013 1 4 5 Lifting Plane 5 140000 700000 10/22/13 10 22 2013 3 4 3 Elevator 2 155000 310000 10/2/13 10 2 2013 4 5 6 Hammer Mill 2 160000 320000 7/22/13 7 22 2013 2 4 6 Hammer Mill 1 140000 140000 1/6/13 1 6 2013 1 1 2 Screener 1 140000 140000 1/2/13 1 2 2013 4 1 2 Screener 3 145000 435000 1/22/13 1 22 2013 3 4 1 Transport Train 1 155000 155000 9/9/13 9 9 2013 2 1 6 Hammer Mill 5 155000 775000 8/9/13 8 9 2013 6 1 8 Screener 1 145000 145000 3/18/13 3 18 2013 2 1 6 Lifting Plane 2 155000 310000 3/22/13 3 22 2013 6 4 6 Lifting Plane 5 155000 775000 2/8/13 2 8 2013 6 5 3 Elevator 2 140000 280000 2/10/13 2 10 2013 1 4 3 Hammer Mill 5 145000 725000 1/8/13 1 8 2013 3 4 6 Elevator 4 155000 620000 8/2/13 8 2 2013 6 4 8 Elevator 5 140000 700000 10/6/13 10 6 2013 1 4 3 Elevator 1 155000 155000 9/9/13 9 9 2013 2 1 1 Hammer Mill 5 155000 775000 1/10/13 1 10 2013 5 1 5 Hammer Mill 3 150000 450000 9/2/13 9 2 2013 2 1 6 Hammer Mill 1 140000 140000 3/9/13 3 9 2013 7 5 2 Elevator 1 140000 140000 10/18/13 10 18 2013 6 1 6 Mining Jack 3 140000 420000 8/18/13 8 18 2013 1 4 6 Hammer Mill 5 155000 775000 7/11/13 7 11 2013 5 4 8 Mining Jack 3 150000 450000 1/11/13 1 11 2013 6 4 8 Lifting Plane 5 140000 700000 6/23/13 6 23 2013 1 1 1 Hammer Mill 2 140000 280000 9/22/13 9 22 2013 1 1 8 Lifting Plane 2 150000 300000 10/6/13 10 6 2013 1 4 6 Screener 2 155000 310000 9/18/13 9 18 2013 4 2 8 Hammer Mill 4 155000 620000 10/23/13 10 23 2013 4 4 6 Hammer Mill 3 140000 420000 6/6/13 6 6 2013 5 1 6 Mining Jack 5 140000 700000 5/10/13 5 10 2013 6 4 6 Screener 3 140000 420000 4/9/13 4 9 2013 3 1 5 Hammer Mill 2 160000 320000 3/18/13 3 18 2013 2 1 6 Hammer Mill 3 140000 420000 3/11/13 3 11 2013 2 4 8 Elevator 3 150000 450000 4/22/13 4 22 2013 2 2 6 Transport Train 1 140000 140000 2/9/13 2 9 2013 7 4 8 Screener 1 140000 140000 3/18/13 3 18 2013 2 4 6 Elevator 5 155000 775000 9/6/13 9 6 2013 6 1 1 Hammer Mill 3 140000 420000 3/23/13 3 23 2013 7 1 5 Transport Train 5 155000 775000 1/11/13 1 11 2013 6 1 5 Transport Train 5 145000 725000 3/23/13 3 23 2013 7 2 8 Lifting Plane 3 145000 435000 12/10/13 12 10 2013 3 1 2 Transport Train 4 155000 620000 10/2/13 10 2 2013 4 5 8 Hammer Mill 2 155000 310000 2/18/13 2 18 2013 2 4 6 Transport Train 2 155000 310000 3/9/13 3 9 2013 7 4 8 Mining Jack 3 140000 420000 11/22/13 11 22 2013 6 1 2 Lifting Plane 4 140000 560000 9/8/13 9 8 2013 1 4 6 Hammer Mill 3 160000 480000 4/6/13 4 6 2013 7 1 8 Screener 2 140000 280000 10/23/13 10 23 2013 4 2 6 Screener 3 155000 465000 2/6/13 2 6 2013 4 4 2 Hammer Mill 2 155000 310000 10/2/13 10 2 2013 4 4 6 Transport Train 2 140000 280000 9/2/13 9 2 2013 2 1 1 Elevator 2 140000 280000 8/10/13 8 10 2013 7 1 1 Hammer Mill 5 145000 725000 7/22/13 7 22 2013 2 5 6 Hammer Mill 4 140000 560000 9/9/13 9 9 2013 2 5 8 Screener 5 145000 725000 6/10/13 6 10 2013 2 1 6 Screener 4 155000 620000 10/9/13 10 9 2013 4 5 5 Elevator 5 140000 700000 2/6/13 2 6 2013 4 4 6 Hammer Mill 3 145000 435000 9/8/13 9 8 2013 1 4 6 Hammer Mill 5 150000 750000 6/18/13 6 18 2013 3 5 2 Screener 3 155000 465000 2/2/13 2 2 2013 7 1 8 Hammer Mill 1 140000 140000 6/2/13 6 2 2013 1 4 3 Hammer Mill 5 150000 750000 6/6/13 6 6 2013 5 2 8 Screener 3 160000 480000 3/2/13 3 2 2013 7 1 3 Screener 2 155000 310000 5/6/13 5 6 2013 2 1 8 Screener 1 140000 140000 6/10/13 6 10 2013 2 2 2 Elevator 2 150000 300000 4/18/13 4 18 2013 5 4 5 Mining Jack 5 155000 775000 5/23/13 5 23 2013 5 4 3 Hammer Mill 4 160000 640000 5/9/13 5 9 2013 5 2 2 Hammer Mill 5 155000 775000 5/18/13 5 18 2013 7 4 2 Hammer Mill 3 155000 465000 5/2/13 5 2 2013 5 1 5 Elevator 1 150000 150000 4/9/13 4 9 2013 3 1 2 Hammer Mill 1 140000 140000 4/11/13 4 11 2013 5 1 1 Transport Train 2 160000 320000 10/9/13 10 9 2013 4 2 8 Screener 3 150000 450000 3/18/13 3 18 2013 2 5 8 Hammer Mill 1 155000 155000 10/11/13 10 11 2013 6 1 5 Screener 2 155000 310000 11/9/13 11 9 2013 7 4 6 Mining Jack 3 140000 420000 2/9/13 2 9 2013 7 5 6 Screener 1 150000 150000 1/18/13 1 18 2013 6 5 5 Transport Train 1 155000 155000 11/22/13 11 22 2013 6 1 3 Lifting Plane 1 150000 150000 5/10/13 5 10 2013 6 5 5 Hammer Mill 2 140000 280000 12/9/13 12 9 2013 2 5 6 Screener 1 145000 145000 8/18/13 8 18 2013 1 4 6 Elevator 2 140000 280000 12/23/13 12 23 2013 2 1 6 Hammer Mill 3 140000 420000 11/6/13 11 6 2013 4 2 8 Hammer Mill 2 145000 290000 4/10/13 4 10 2013 4 5 6 Elevator 1 155000 155000 11/2/13 11 2 2013 7 4 3 Hammer Mill 2 155000 310000 11/11/13 11 11 2013 2 2 5 Screener 2 150000 300000 11/2/13 11 2 2013 7 1 1 Elevator 1 150000 150000 4/18/13 4 18 2013 5 4 6 Lifting Plane 1 155000 155000 12/22/13 12 22 2013 1 1 8 Hammer Mill 2 140000 280000 5/10/13 5 10 2013 6 1 6 Hammer Mill 2 145000 290000 6/18/13 6 18 2013 3 4 6 Screener 5 150000 750000 11/22/13 11 22 2013 6 5 6 Transport Train 4 150000 600000 8/10/13 8 10 2013 7 2 6 Hammer Mill 1 155000 155000 8/18/13 8 18 2013 1 5 6 Elevator 1 150000 150000 8/9/13 8 9 2013 6 2 3 Screener 2 145000 290000 11/11/13 11 11 2013 2 2 2 Elevator 3 155000 465000 12/18/13 12 18 2013 4 5 6 Screener 1 140000 140000 11/2/13 11 2 2013 7 2 3 Lifting Plane 2 145000 290000 8/10/13 8 10 2013 7 5 5 Hammer Mill 4 160000 640000 12/2/13 12 2 2013 2 2 6 Screener 1 140000 140000 6/23/13 6 23 2013 1 2 2 Hammer Mill 2 145000 290000 6/11/13 6 11 2013 3 2 2 Screener 2 155000 310000 5/18/13 5 18 2013 7 5 3 Hammer Mill 2 155000 310000 5/8/13 5 8 2013 4 4 2 Hammer Mill 3 140000 420000 12/2/13 12 2 2013 2 4 2 Hammer Mill 2 155000 310000 6/11/13 6 11 2013 3 1 5 Mining Jack 2 145000 290000 12/9/13 12 9 2013 2 4 5 Mining Jack 1 140000 140000 8/9/13 8 9 2013 6 4 6 Hammer Mill 5 160000 800000 3/9/13 3 9 2013 7 4 3 Elevator 1 140000 140000 4/10/13 4 10 2013 4 4 6 Hammer Mill 1 155000 155000 7/2/13 7 2 2013 3 2 6 Mining Jack 1 160000 160000 9/18/13 9 18 2013 4 1 1 Elevator 2 155000 310000 11/23/13 11 23 2013 7 4 1 Screener 1 140000 140000 12/23/13 12 23 2013 2 1 6 Mining Jack 5 140000 700000 12/18/13 12 18 2013 4 2 1 Transport Train 1 140000 140000 9/11/13 9 11 2013 4 2 6 Lifting Plane 2 150000 300000 7/18/13 7 18 2013 5 2 1 Elevator 4 155000 620000 7/9/13 7 9 2013 3 1 8 Lifting Plane 1 140000 140000 12/6/13 12 6 2013 6 4 6 Screener 1 155000 155000 4/18/13 4 18 2013 5 2 8 Mining Jack 5 140000 700000 10/10/13 10 10 2013 5 4 5 Hammer Mill 1 145000 145000 10/18/13 10 18 2013 6 4 5 Hammer Mill 3 140000 420000 10/11/13 10 11 2013 6 1 1 Transport Train 5 140000 700000 5/22/13 5 22 2013 4 2 6 Mining Jack 2 140000 280000 12/18/13 12 18 2013 4 2 3 Hammer Mill 3 160000 480000 5/2/13 5 2 2013 5 4 5 Hammer Mill 4 145000 580000 10/8/13 10 8 2013 3 5 8 Hammer Mill 2 145000 290000 7/23/13 7 23 2013 3 1 2 Lifting Plane 1 140000 140000 Management has asked you to provide some information concerning sales patterns the company has experienced over the past year. Specifically they would like the following questions answered using sales data from last year 1. Column A of the worksheet displays the date the transaction occurred, columns B, C, D, E contain functions that parse the data into a greater level of detail. Look these functions up in the excel help function and describe what the functions do and how they may be useful to future analyses. 2. How many units did each salesperson sell last year? 3. How many units of each product were sold during the year? 4. Who sold the most product for the year? 5. What was the average number of units of each product sold for each month? 6. How many units were sold in the month of September? 7. How many units were sold in each region? 8. How many units were sold in each region by each sales person? 9. How many units were sold between May 10th and May 22nd, 2013? Management has provided data in a worksheet named Pivot Table Data that is available on the top of this page. Using the data provided, create a single pivot table and manipulate the column and row data to answer the questions. Copy the resulting output data and paste the table into this word document. Format the table in word to provide descriptive labels where appropriate. Sale Date Month Day Year Weekday Region SalesPerson Product Quantity Price TotalSale 2/23/13 2 23 2013 7 5 8 Screener 5 145000 725000 7/9/13 7 9 2013 3 5 6 Mining Jack 2 155000 310000 2/11/13 2 11 2013 2 2 1 Hammer Mill 1 155000 155000 1/11/13 1 11 2013 6 4 6 Elevator 2 155000 310000 8/11/13 8 11 2013 1 4 8 Hammer Mill 2 140000 280000 7/11/13 7 11 2013 5 2 6 Elevator 3 155000 465000 2/8/13 2 8 2013 6 1 6 Transport Train 5 140000 700000 8/18/13 8 18 2013 1 1 6 Elevator 3 145000 435000 7/18/13 7 18 2013 5 4 8 Elevator 3 155000 465000 8/23/13 8 23 2013 6 4 3 Elevator 3 140000 420000 2/6/13 2 6 2013 4 4 8 Lifting Plane 5 140000 700000 1/6/13 1 6 2013 1 1 6 Transport Train 2 155000 310000 4/11/13 4 11 2013 5 2 6 Hammer Mill 1 150000 150000 6/2/13 6 2 2013 1 4 6 Screener 5 155000 775000 2/11/13 2 11 2013 2 1 1 Elevator 3 150000 450000 12/8/13 12 8 2013 1 5 2 Hammer Mill 5 140000 700000 8/18/13 8 18 2013 1 2 5 Lifting Plane 1 150000 150000 11/2/13 11 2 2013 7 1 6 Transport Train 5 140000 700000 7/8/13 7 8 2013 2 4 5 Elevator 1 155000 155000 2/10/13 2 10 2013 1 4 5 Lifting Plane 5 140000 700000 10/22/13 10 22 2013 3 4 3 Elevator 2 155000 310000 10/2/13 10 2 2013 4 5 6 Hammer Mill 2 160000 320000 7/22/13 7 22 2013 2 4 6 Hammer Mill 1 140000 140000 1/6/13 1 6 2013 1 1 2 Screener 1 140000 140000 1/2/13 1 2 2013 4 1 2 Screener 3 145000 435000 1/22/13 1 22 2013 3 4 1 Transport Train 1 155000 155000 9/9/13 9 9 2013 2 1 6 Hammer Mill 5 155000 775000 8/9/13 8 9 2013 6 1 8 Screener 1 145000 145000 3/18/13 3 18 2013 2 1 6 Lifting Plane 2 155000 310000 3/22/13 3 22 2013 6 4 6 Lifting Plane 5 155000 775000 2/8/13 2 8 2013 6 5 3 Elevator 2 140000 280000 2/10/13 2 10 2013 1 4 3 Hammer Mill 5 145000 725000 1/8/13 1 8 2013 3 4 6 Elevator 4 155000 620000 8/2/13 8 2 2013 6 4 8 Elevator 5 140000 700000 10/6/13 10 6 2013 1 4 3 Elevator 1 155000 155000 9/9/13 9 9 2013 2 1 1 Hammer Mill 5 155000 775000 1/10/13 1 10 2013 5 1 5 Hammer Mill 3 150000 450000 9/2/13 9 2 2013 2 1 6 Hammer Mill 1 140000 140000 3/9/13 3 9 2013 7 5 2 Elevator 1 140000 140000 10/18/13 10 18 2013 6 1 6 Mining Jack 3 140000 420000 8/18/13 8 18 2013 1 4 6 Hammer Mill 5 155000 775000 7/11/13 7 11 2013 5 4 8 Mining Jack 3 150000 450000 1/11/13 1 11 2013 6 4 8 Lifting Plane 5 140000 700000 6/23/13 6 23 2013 1 1 1 Hammer Mill 2 140000 280000 9/22/13 9 22 2013 1 1 8 Lifting Plane 2 150000 300000 10/6/13 10 6 2013 1 4 6 Screener 2 155000 310000 9/18/13 9 18 2013 4 2 8 Hammer Mill 4 155000 620000 10/23/13 10 23 2013 4 4 6 Hammer Mill 3 140000 420000 6/6/13 6 6 2013 5 1 6 Mining Jack 5 140000 700000 5/10/13 5 10 2013 6 4 6 Screener 3 140000 420000 4/9/13 4 9 2013 3 1 5 Hammer Mill 2 160000 320000 3/18/13 3 18 2013 2 1 6 Hammer Mill 3 140000 420000 3/11/13 3 11 2013 2 4 8 Elevator 3 150000 450000 4/22/13 4 22 2013 2 2 6 Transport Train 1 140000 140000 2/9/13 2 9 2013 7 4 8 Screener 1 140000 140000 3/18/13 3 18 2013 2 4 6 Elevator 5 155000 775000 9/6/13 9 6 2013 6 1 1 Hammer Mill 3 140000 420000 3/23/13 3 23 2013 7 1 5 Transport Train 5 155000 775000 1/11/13 1 11 2013 6 1 5 Transport Train 5 145000 725000 3/23/13 3 23 2013 7 2 8 Lifting Plane 3 145000 435000 12/10/13 12 10 2013 3 1 2 Transport Train 4 155000 620000 10/2/13 10 2 2013 4 5 8 Hammer Mill 2 155000 310000 2/18/13 2 18 2013 2 4 6 Transport Train 2 155000 310000 3/9/13 3 9 2013 7 4 8 Mining Jack 3 140000 420000 11/22/13 11 22 2013 6 1 2 Lifting Plane 4 140000 560000 9/8/13 9 8 2013 1 4 6 Hammer Mill 3 160000 480000 4/6/13 4 6 2013 7 1 8 Screener 2 140000 280000 10/23/13 10 23 2013 4 2 6 Screener 3 155000 465000 2/6/13 2 6 2013 4 4 2 Hammer Mill 2 155000 310000 10/2/13 10 2 2013 4 4 6 Transport Train 2 140000 280000 9/2/13 9 2 2013 2 1 1 Elevator 2 140000 280000 8/10/13 8 10 2013 7 1 1 Hammer Mill 5 145000 725000 7/22/13 7 22 2013 2 5 6 Hammer Mill 4 140000 560000 9/9/13 9 9 2013 2 5 8 Screener 5 145000 725000 6/10/13 6 10 2013 2 1 6 Screener 4 155000 620000 10/9/13 10 9 2013 4 5 5 Elevator 5 140000 700000 2/6/13 2 6 2013 4 4 6 Hammer Mill 3 145000 435000 9/8/13 9 8 2013 1 4 6 Hammer Mill 5 150000 750000 6/18/13 6 18 2013 3 5 2 Screener 3 155000 465000 2/2/13 2 2 2013 7 1 8 Hammer Mill 1 140000 140000 6/2/13 6 2 2013 1 4 3 Hammer Mill 5 150000 750000 6/6/13 6 6 2013 5 2 8 Screener 3 160000 480000 3/2/13 3 2 2013 7 1 3 Screener 2 155000 310000 5/6/13 5 6 2013 2 1 8 Screener 1 140000 140000 6/10/13 6 10 2013 2 2 2 Elevator 2 150000 300000 4/18/13 4 18 2013 5 4 5 Mining Jack 5 155000 775000 5/23/13 5 23 2013 5 4 3 Hammer Mill 4 160000 640000 5/9/13 5 9 2013 5 2 2 Hammer Mill 5 155000 775000 5/18/13 5 18 2013 7 4 2 Hammer Mill 3 155000 465000 5/2/13 5 2 2013 5 1 5 Elevator 1 150000 150000 4/9/13 4 9 2013 3 1 2 Hammer Mill 1 140000 140000 4/11/13 4 11 2013 5 1 1 Transport Train 2 160000 320000 10/9/13 10 9 2013 4 2 8 Screener 3 150000 450000 3/18/13 3 18 2013 2 5 8 Hammer Mill 1 155000 155000 10/11/13 10 11 2013 6 1 5 Screener 2 155000 310000 11/9/13 11 9 2013 7 4 6 Mining Jack 3 140000 420000 2/9/13 2 9 2013 7 5 6 Screener 1 150000 150000 1/18/13 1 18 2013 6 5 5 Transport Train 1 155000 155000 11/22/13 11 22 2013 6 1 3 Lifting Plane 1 150000 150000 5/10/13 5 10 2013 6 5 5 Hammer Mill 2 140000 280000 12/9/13 12 9 2013 2 5 6 Screener 1 145000 145000 8/18/13 8 18 2013 1 4 6 Elevator 2 140000 280000 12/23/13 12 23 2013 2 1 6 Hammer Mill 3 140000 420000 11/6/13 11 6 2013 4 2 8 Hammer Mill 2 145000 290000 4/10/13 4 10 2013 4 5 6 Elevator 1 155000 155000 11/2/13 11 2 2013 7 4 3 Hammer Mill 2 155000 310000 11/11/13 11 11 2013 2 2 5 Screener 2 150000 300000 11/2/13 11 2 2013 7 1 1 Elevator 1 150000 150000 4/18/13 4 18 2013 5 4 6 Lifting Plane 1 155000 155000 12/22/13 12 22 2013 1 1 8 Hammer Mill 2 140000 280000 5/10/13 5 10 2013 6 1 6 Hammer Mill 2 145000 290000 6/18/13 6 18 2013 3 4 6 Screener 5 150000 750000 11/22/13 11 22 2013 6 5 6 Transport Train 4 150000 600000 8/10/13 8 10 2013 7 2 6 Hammer Mill 1 155000 155000 8/18/13 8 18 2013 1 5 6 Elevator 1 150000 150000 8/9/13 8 9 2013 6 2 3 Screener 2 145000 290000 11/11/13 11 11 2013 2 2 2 Elevator 3 155000 465000 12/18/13 12 18 2013 4 5 6 Screener 1 140000 140000 11/2/13 11 2 2013 7 2 3 Lifting Plane 2 145000 290000 8/10/13 8 10 2013 7 5 5 Hammer Mill 4 160000 640000 12/2/13 12 2 2013 2 2 6 Screener 1 140000 140000 6/23/13 6 23 2013 1 2 2 Hammer Mill 2 145000 290000 6/11/13 6 11 2013 3 2 2 Screener 2 155000 310000 5/18/13 5 18 2013 7 5 3 Hammer Mill 2 155000 310000 5/8/13 5 8 2013 4 4 2 Hammer Mill 3 140000 420000 12/2/13 12 2 2013 2 4 2 Hammer Mill 2 155000 310000 6/11/13 6 11 2013 3 1 5 Mining Jack 2 145000 290000 12/9/13 12 9 2013 2 4 5 Mining Jack 1 140000 140000 8/9/13 8 9 2013 6 4 6 Hammer Mill 5 160000 800000 3/9/13 3 9 2013 7 4 3 Elevator 1 140000 140000 4/10/13 4 10 2013 4 4 6 Hammer Mill 1 155000 155000 7/2/13 7 2 2013 3 2 6 Mining Jack 1 160000 160000 9/18/13 9 18 2013 4 1 1 Elevator 2 155000 310000 11/23/13 11 23 2013 7 4 1 Screener 1 140000 140000 12/23/13 12 23 2013 2 1 6 Mining Jack 5 140000 700000 12/18/13 12 18 2013 4 2 1 Transport Train 1 140000 140000 9/11/13 9 11 2013 4 2 6 Lifting Plane 2 150000 300000 7/18/13 7 18 2013 5 2 1 Elevator 4 155000 620000 7/9/13 7 9 2013 3 1 8 Lifting Plane 1 140000 140000 12/6/13 12 6 2013 6 4 6 Screener 1 155000 155000 4/18/13 4 18 2013 5 2 8 Mining Jack 5 140000 700000 10/10/13 10 10 2013 5 4 5 Hammer Mill 1 145000 145000 10/18/13 10 18 2013 6 4 5 Hammer Mill 3 140000 420000 10/11/13 10 11 2013 6 1 1 Transport Train 5 140000 700000 5/22/13 5 22 2013 4 2 6 Mining Jack 2 140000 280000 12/18/13 12 18 2013 4 2 3 Hammer Mill 3 160000 480000 5/2/13 5 2 2013 5 4 5 Hammer Mill 4 145000 580000 10/8/13 10 8 2013 3 5 8 Hammer Mill 2 145000 290000 7/23/13 7 23 2013 3 1 2 Lifting Plane 1 140000 140000 Management has asked you to provide some information concerning sales patterns the company has experienced over the past year. Specifically they would like the following questions answered using sales data from last year 1. Column A of the worksheet displays the date the transaction occurred, columns B, C, D, E contain functions that parse the data into a greater level of detail. Look these functions up in the excel help function and describe what the functions do and how they may be useful to future analyses. 2. How many units did each salesperson sell last year? 3. How many units of each product were sold during the year? 4. Who sold the most product for the year? 5. What was the average number of units of each product sold for each month? 6. How many units were sold in the month of September? 7. How many units were sold in each region? 8. How many units were sold in each region by each sales person? 9. How many units were sold between May 10th and May 22nd, 2013? Management has provided data in a worksheet named Pivot Table Data that is available on the top of this page. Using the data provided, create a single pivot table and manipulate the column and row data to answer the questions. Copy the resulting output data and paste the table into this word document. Format the table in word to provide descriptive labels where appropriate.Explanation / Answer
1 The Functions Date , month and year are very useful in Pivot table function to trace and find information related to particular year/s , particular date/s or particular month/s. The data has been heavily used in this analysis for such segregation. 2 Sales Person Units Sold 1 38 2 47 3 37 5 55 6 148 8 75 Grand Total 400 3 Products Qty sold Elevator 66 Hammer Mill 143 Lifting Plane 40 Mining Jack 40 Screener 66 Transport Train 45 Grand Total 400 4 Sales person 6 sold the most, 148 units in year 5 Products Avrage no sold each Months Sum of Quantity Column Labels Row Labels 1 2 3 4 5 6 7 8 9 10 11 12 Grand Total Avg No per month Elevator 6 5 10 1 1 2 11 14 4 8 4 66 5.50 Hammer Mill 3 12 4 5 25 9 5 22 26 13 4 15 143 11.92 Lifting Plane 5 10 10 1 2 1 4 7 40 3.33 Mining Jack 3 10 2 7 6 3 3 6 40 3.33 Screener 4 7 2 2 4 22 3 5 10 3 4 66 5.50 Transport Train 9 7 5 3 7 9 5 45 3.75 Grand Total 27 41 34 22 32 40 24 40 39 41 30 30 400 33.33 6 Month Sum of Quantity 1 27 2 41 3 34 4 22 5 32 6 40 7 24 8 40 9 39 10 41 11 30 12 30 Grand Total 400 Qty sold in September = 39 units 7 Region Sum of Quantity 1 125 2 65 4 153 5 57 Grand Total 400 8 Region Sales Person Sum of Quantity Column Labels Row Labels 1 2 3 5 6 8 Grand Total 1 30 14 3 20 48 10 125 2 6 14 7 3 15 20 65 4 2 10 23 20 68 30 153 5 9 4 12 17 15 57 Grand Total 38 47 37 55 148 75 400 9 Month 5 Row Labels Sum of Quantity 2 5 6 1 8 3 9 5 10 7 18 5 22 2 23 4 Grand Total 32 Between May 10th and 22nd the units sold were 14 units
Related Questions
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.