Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
| TATA MOTORS COMPANY | |||
| products | manufacturing | sales | |
| Classic Cars | ₹ 4,20,000.00 | ₹ 4,80,000.00 | |
| Motorcycles | ₹ 25,000.00 | ₹ 28,000.00 | |
| Planes | ₹ 50,00,000.00 | ₹ 60,00,000.00 | |
| Ships | ₹ 18,00,00,000.00 | ₹ 22,00,00,000.00 | |
| Trains | ₹ 20,00,000.00 | ₹ 24,00,000.00 | |
| Trucks and Buses | ₹ 5,00,000.00 | ₹ 6,00,000.00 | |
| Vintage Cars | ₹ 7,00,000.00 | ₹ 7,40,000.00 | |
| total sum of sales | ₹ 18,86,45,000.00 | ₹ 23,02,48,000.00 | |
| average of sales | ₹ 4,71,61,250.00 | ₹ 5,75,62,000.00 | |
| max sales | ₹ 18,00,00,000.00 | ₹ 22,00,00,000.00 | |
| min sales | ₹ 25,000.00 | ₹ 28,000.00 | |
| std deviation | ₹ 6,75,10,578.21 | ₹ 8,25,32,266.42 | |
| variance | ₹ 4,55,76,78,17,02,38,090.00 | ₹ 8,70,09,82,64,52,08,480.00 | |
Row LabelsSum of salesAverage of manufacturingGrand Total23024800026949285.71
| Classic Cars | 480000 | 420000 | |
| Motorcycles | 28000 | 25000 | |
| Planes | 6000000 | 5000000 | |
| Ships | 220000000 | 180000000 | |
| Trains | 2400000 | 2000000 | |
| Trucks and Buses | 600000 | 500000 | |
| Vintage Cars | 740000 | 700000 | |
Grand Total3950000119108
| Row Labels | Sum of solds | Sum of unit cost | Sum of no of products |
| 11-07-20 | 430000 | 27 | 20 |
| germany | 430000 | 27 | 20 |
| hyundai | 200000 | 14 | 14 |
| kia | 230000 | 13 | 6 |
| 11-07-21 | 3520000 | 92 | 88 |
| germany | 3520000 | 92 | 88 |
| maruti suzuki | 120000 | 16 | 56 |
| tata | 3400000 | 76 | 32 |
| e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table | |||
Grand Total3950000119108
| Row Labels | Sum of solds | Sum of unit cost | Sum of no of products |
| 11-07-20 | 430000 | 27 | 20 |
| germany | 430000 | 27 | 20 |
| hyundai | 200000 | 14 | 14 |
| kia | 230000 | 13 | 6 |
| 11-07-21 | 3520000 | 92 | 88 |
| germany | 3520000 | 92 | 88 |
| maruti suzuki | 120000 | 16 | 56 |
| tata | 3400000 | 76 | 32 |
| e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table | |||
Grand Total3950000119108
| Row Labels | Sum of solds | Sum of unit cost | Sum of no of products |
| 11-07-20 | 430000 | 27 | 20 |
| germany | 430000 | 27 | 20 |
| hyundai | 200000 | 14 | 14 |
| kia | 230000 | 13 | 6 |
| 11-07-21 | 3520000 | 92 | 88 |
| germany | 3520000 | 92 | 88 |
| maruti suzuki | 120000 | 16 | 56 |
| tata | 3400000 | 76 | 32 |
| e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table | |||
| f. Present the data in different colours for values in field Status and ProductLine using conditional formatting. |
j. Highlight the top 10% values of QuantityOrdered field using Conditional Formatting
countryunit costno of products
Motorcyclespercentage
india
₹ 12.00₹ 15.00 pulsar95% h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products.
germany
₹ 14.00₹ 14.00 yamaha75%
germany
₹ 16.00₹ 56.00 yamaha80% productsshippedcancelleddisputedin processonholdresolved
germany
₹ 76.00₹ 32.00 yamaha20% Motorcycles5412823101
germany
₹ 11.00₹ 12.00 splendor30% Classic Cars451212876
germany
₹ 13.00₹ 15.00 splendor50% Planes561010151011 yamaha65% Ships4512210165 yamaha70% total sales2004632564323 yamaha27% yamaha45% splendor60% i. Add two new fields in data sheet with reference to Productcode using text functions splendor100% royalenfieldroyalenfield g. Clean the Data with respect to date by proper date formatting and change values of 2003 to 2019, 2004 to 2020 and 2005 to 2021. 01-01-20012001 02-01-20022002 03-01-20032003 04-01-20042004 05-01-20052005 06-01-20062006 07-01-20072007 08-01-20082008 09-01-20092009