Pivot Analysis Project
In this project you will create pivot tables and pivot charts to analyze sales, customer and product data.
Cleanse the data
Open Pivot Analysis Raw Data and save it as Pivot Analysis your name completed.
Review and interpret the data. Resize and/or format the data as you desire.
In Column I enter the label Product_Order_Total. Create a formula to calculate the total and fill this column.
Analysis
For each question below place the pivot table and pivot chart on a separate sheet and name the sheet tab with the Question number.
Tables should be sorted.
Charts should have appropriate titles.
Column headings should be modified to be “user friendly”.
Numbers should be formatted with commas and no decimal places.
How were monthly sales in 2016 compared to the prior year? Create a pivot table and then create a pivot chart. Both should show sales grouped by month using the month name, not the month number. You will need to use the Order_Date and then group the field.
In terms of Product_Order_Total, what were the top 10 customers in 2016? Create a pivot table and then create a pivot chart. The pivot chart should be a bar chart and display the customers in order, showing the best customer at the top.
In terms of Product_Order_Total, what were the top 10 products in 2016? Create a pivot table and then create a pivot chart. The pivot chart should be a bar chart and display the products in order, showing the best product at the top.
In terms of Product_Order_Total, what were the top 10 countries in 2016? Create a pivot table, sorted in descending order, and then create a pivot chart. The pivot chart should be a pie chart with data labels.
Create a “dashboard” sheet that contains all 4 pivot charts and move it to the beginning of the workbook. Verify that the size of each chart is large enough to show all labels.