Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
| a. Do proper formatting of the data with using borders, font size and appropriate alignment of the content. | ||||||||
| classic cars | ||||||||
| Motorcycles | ||||||||
| Planes | ||||||||
| Ships | ||||||||
| Trains | ||||||||
| Trucks and Buses | ||||||||
| Vintage Cars | ||||||||
| b. Make summary of Producs Sales by providing | ||||||||
| Total Sales | Average Sales | Standard Deviation | Variance of Sales | Maximum Sales | Minimum Sales | |||
| Product | 1000 | 200 | 15 | 225 | 250 | 50 | ||
| Classic Cars | 500 | 100 | 10 | 100 | 150 | 50 | ||
| Motorcycles | 200 | 40 | 5 | 25 | 50 | 20 | ||
| Planes | 150 | 30 | 5 | 25 | 40 | 20 | ||
| Ships | 100 | 20 | 5 | 25 | 30 | 10 | ||
| Trains | 50 | 10 | 5 | 25 | 20 | 0 | ||
| Trucks and Buses | 250 | 50 | 10 | 100 | 100 | 10 | ||
| Vintage Cars | 250 | 50 | 10 | 100 | 100 | 10 | ||
| c. Analyse the sales happened by product line & status using pivot table | ||||||||
| Values | ||||||||
| Sum of Minimum Sales | Sum of Maximum Sales | Sum of Variance of Sales | Sum of Standard Deviation | Sum of Average Sales | Sum of Total Sales | |||
| 50 | 250 | 225 | 15 | 200 | 1000 | |||
| d. Make Customized Tab by naming ABC and keep few data formatting commands in the newly created Tab. | ||||||||
| Total Sales | Average Sales | Standard Deviation | Variance of Sales | Maximum Sales | Minimum Sales | |||
| Product | 1000 | 200 | 15 | 225 | 250 | 50 | ||
| Classic Cars | 500 | 100 | 10 | 100 | 150 | 50 | ||
| Motorcycles | 200 | 40 | 5 | 25 | 50 | 20 | ||
| Planes | 150 | 30 | 5 | 25 | 40 | 20 | ||
| Ships | 100 | 20 | 5 | 25 | 30 | 10 | ||
| Trains | 50 | 10 | 5 | 25 | 20 | 0 | ||
| Trucks and Buses | 250 | 50 | 10 | 100 | 100 | 10 | ||
| Vintage Cars | 250 | 50 | 10 | 100 | 100 | 10 | ||
| e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table. | ||||||||
| Country | Year | Quarter | Quantity | |||||
| United States | 2019 | Q1 | 1000 | |||||
| United States | 2019 | Q2 | 2000 | Country | (All) | |||
| United States | 2019 | Q3 | 3000 | |||||
| United States | 2019 | Q4 | 4000 | Values | ||||
| United States | 2020 | Q1 | 5000 | Row Labels | Sum of Year | Sum of Quantity | ||
| United States | 2020 | Q2 | 6000 | Q1 | 6060 | 15000 | ||
| United States | 2020 | Q3 | 7000 | Q2 | 6060 | 18000 | ||
| United States | 2020 | Q4 | 8000 | Q3 | 6060 | 21000 | ||
| United States | 2021 | Q1 | 9000 | Q4 | 6060 | 24000 | ||
| United States | 2021 | Q2 | 10000 | Grand Total | 24240 | 78000 | ||
| United States | 2021 | Q3 | 11000 | |||||
| United States | 2021 | Q4 | 12000 | |||||
| h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products. (Hint:Use pivot table or sumif function) | ||||||||
| Status | Quantity | Row Labels | Sum of Quantity | |||||
| Shipped | 1000 | Cancelled | 500 | |||||
| Cancelled | 500 | Disputed | 250 | |||||
| Disputed | 250 | In Process | 150 | |||||
| In Process | 150 | On Hold | 100 | |||||
| On Hold | 100 | Resolved | 50 | |||||
| Resolved | 50 | Shipped | 1000 | |||||
| Grand Total | 2050 | |||||||
| f. Present the data in different colours for values in field Status and ProductLine using conditional formatting. | ||||||||
| Product Line | Status | Quantity | Color | |||||
| Classic Cars | Shipped | 1000 | Green | |||||
| Motorcycles | Cancelled | 500 | Red | |||||
| Planes | Disputed | 250 | Yellow | |||||
| Ships | In Process | 150 | Blue | |||||
| Trains | On Hold | 100 | Orange | |||||
| Trucks and Buses | Resolved | 50 | Purple | |||||