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