Sales data analysis report using Pivot Tables - Excel
The workbook consist of solutions to ModelOff 2013 Data Analysis questions. The data file consisted of 5,016 sales records and 11 columns. 3 columns were added that were required for analysis of the below questions. The answers to following questions are included in the Excel workbook:
- Over the entire analysis period, what was the quantity of item 10 sold while John Jones was the Manager on duty?
- Over the entire analysis period, what were the 3 highest selling items by quantity?
- Over the entire analysis period, which sales person sold the highest cumulative quantity of a single item, and which item was it?
- What was sales person Wendel’s total Sales over the analysis period? Select the closest answer.
- How many invoices did sales person Sally create over the analysis period?
- During the month of May, which postal code bought the most of item 5 by quantity?
- During the month of February, how many postal codes bought more than 400 products by quantity?
- Over the entire dataset, which 3 items did Postcode 3020 spend the greatest dollars on?
- What is the rank of sales persons from highest to lowest based on the number of invoices written during the month of May?
- What was the invoice number of the largest invoice by revenue that sales person Wendel wrote during the analysis period?
- Over the entire analysis period, what is the rank of sales persons according to the dollar value of discounts given, from most discounts to least discounts?
- Which month had the highest revenue?
- Only considering postal codes 3013, 3017 and 3031, which item had the highest total profit during the month of February?
- What is the rank of months from highest to lowest based on profit over the entire analysis period?
- During which 3 months did manager John Jones have the highest cumulative profit ignoring all sales to postal codes 3019 and 3028 and ignoring all sales of items 4, 5, 6, 17 and 18?
- What quantity of item 3 was sold by sales persons Benny and Kelly together during the month of June?