Sale Date Month Day Year Weekday Region SalesPerson Product Quantity Price Total
ID: 2484472 • 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
Answer 1 : Function in column B is MONTH which returns 1-12 depending on corresponding calendar month January to December from a particular date. This function will help management to analyze monthly sale and quantity by product.
Function in column C is DAY which returns the calendar day. This will help in daily analysis of data. Like how many units of particular product are sold daily.
Function in column D is YEAR which returns the calendar year in which transaction took place. This can be helpful in preparing yearly budgets.
Function in column ‘E’ is Weekday which returns the day number 1-7 from the week of a date. This function will management weekly reports per salesperson.
Please note management can use this functions to prepare multiple report for different stakeholders.
Answer 2: Units per salesperson is given below
Sales Person
Units sold
1
15
2
18
3
15
5
20
6
54
8
27
Grand Total
149
Answer 3: Product wise sale is given below:
Product
Units sold
Elevator
27
Hammer Mill
51
Lifting Plane
15
Mining Jack
13
Screener
28
Transport Train
15
Grand Total
149
Answer 4:
From analysis in Point number 2 it can be derived that Sales man ‘6’ has sold most product for the year i.e 54 units.
Answer 5
Average units sold for each product can be calculated from below analysis of monthly sale of each product
Month
Elevator
Hammer Mill
Lifting Plane
Mining Jack
Screener
Transport Train
Total Monthly sale
1
2
1
1
2
4
10
2
2
5
2
3
2
14
3
4
2
3
1
1
1
12
4
1
4
1
2
1
2
11
5
1
8
1
2
12
6
1
3
2
6
12
7
4
2
2
3
11
8
5
6
1
2
14
9
2
7
2
1
12
10
3
6
1
4
2
16
11
2
2
3
1
2
2
12
12
5
2
4
2
13
Grand Total
27
51
15
13
28
15
149
Average 2.25 4 1 1 2 1 12
=total/12
Answer 6: From the below analysis it can be derived that 12 units were sold in September.
Month
Units sold
1
10
2
14
3
12
4
11
5
12
6
12
7
11
8
14
9
12
10
16
11
12
12
13
Grand Total
149
Answer 7: Region wise sale is derived below.
Region
Units sold
1
46
2
28
4
52
5
23
Grand Total
149
Answer 8: Below analysis shows units sold in each region by each sales person.
Sales Person
Region 1
Region 2
Region 4
Region 5
Units sold
1
10
3
2
15
2
6
5
4
3
18
3
2
3
8
2
15
5
7
2
7
4
20
6
14
9
22
9
54
8
7
6
9
5
27
Grand Total
46
28
52
23
149
Answer 9 : from the below analysis it can derived that units sold between 10th to 22nd may is 6 units.
Day(from May only)
Units sold
10
3
18
2
22
1
Total
6
Sales Person
Units sold
1
15
2
18
3
15
5
20
6
54
8
27
Grand Total
149
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.