Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
Project Documentation: Optimizing Team Hiring Insights through Data Cleaning and Transformation
Introduction
In today's data-driven environment, businesses thrive on informed decision-making. At ABC Private Limited, a manufacturer and seller of diverse products ranging from Classic Cars to Trucks and Buses, understanding sales performance and operational efficiency is crucial for strategic planning and team hiring. This project focuses on transforming a dataset to derive actionable insights through data cleaning, pivot table analysis, and visualizations.
Project Overview
Objective
The primary objective of this project was to clean and transform the sales data of various product lines to support the hiring decisions based on sales performance. The tasks included data formatting, analysis, and visual representation to enable a comprehensive understanding of the company's sales dynamics.
Dataset Description
The dataset encompasses sales transactions including:
- Order Numbers
- Quantity Ordered
- Sales Price
- Sales Amount
- Order Dates
- Product Lines
- Customer Information
Data Cleaning and Transformation Steps
Step 1: Data Formatting
- Borders and Alignment: Proper borders were applied to delineate sections of the dataset, and all content was aligned for clarity.
- Font Size Adjustments: The font size was standardized for consistency and readability.
Step 2: Sales Summary Analysis
A detailed summary of product sales was generated, revealing:
- Total Sum of Sales: Calculated to understand overall revenue.
- Average Sales: To gauge typical sales performance.
- Standard Deviation and Variance: These metrics provide insight into sales variability.
- Maximum and Minimum Sales: Identifying the highest and lowest performing products.
Step 3: Pivot Table Analysis
A pivot table was created to analyze sales by product line and status, allowing for an intuitive view of sales performance across different categories.
Step 4: Custom Tab Creation
A customized tab named "ABC" was established within the spreadsheet, containing essential data formatting commands for future analysis.
Quantity Analysis by Country (2019-2021)
Step 5: Quarterly Analysis
Utilizing a pivot table, the quantities ordered by each country from 2019 to 2021 were analyzed, broken down into quarters for detailed insights.
Step 6: Conditional Formatting
To enhance visual interpretation:
- Different colors were applied to the Status and Product Line fields using conditional formatting.
Data Correction
Step 7: Year Adjustment
The years in the dataset were corrected as follows:
- Values of 2003 were changed to 2019
- Values of 2004 were changed to 2020
- Values of 2005 were changed to 2021
Step 8: Status-Based Sales Calculation
Using a pivot table or SUMIF function, the total sales for various statuses (e.g., "Shipped", "Cancelled", "Disputed") were calculated.
Data Transformation
Step 9: Product Code Splitting
The Product Code field was split into two columns for improved clarity:
- Product Code Left: S10
- Product Code Right: 1678
Step 10: Highlighting Top Performers
Conditional formatting was applied to highlight the top 10% of values in the Quantity Ordered field, facilitating quick identification of high-performing products.
Results and Conclusion
The data cleaning and transformation process yielded a well-structured dataset, enriched with meaningful insights and visualizations. These findings provide a solid foundation for strategic hiring decisions at ABC Private Limited, helping align workforce capabilities with business demands.
Future Directions
Further analyses can be conducted to predict future sales trends, assess customer behavior, and optimize inventory management, ultimately leading to enhanced operational efficiency and profitability.
Appendices
- Appendix A: Sample Data Snapshot
- Appendix B: Pivot Table Outputs
- Appendix C: Graphical Representations of Sales Trends