Pivot Analysis Project
In this project you will create pivot tables and pivot charts to analyze sales, customer and product data.
Cleanse the data
1. Open Pivot Analysis Raw Data and save it as Pivot Analysis your name completed.
2. Review and interpret the data. Resize and/or format the data as you desire.
3. Create a column for Order_Date_Year. Use a function to extract the information from Order_Date and fill in this new column.
4. Create a Product_Order_Total column. 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.
1. How were monthly sales in 2013 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.
2. In terms of Product_Order_Total, what were the top 10 customers in 2013? 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.
3. In terms of Product_Order_Total, what were the top 10 products in 2013? 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.
4. In terms of Product_Order_Total, what were the top 10 countries in 2013? 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.
5. Create a "dashboard" sheet that contains all 5 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.
Attachment:- Pivot Analysis Raw Data.rar