Sales Analysis and Data Visualization at Private Company

Sales Analysis and Data Visualization at a Private Company

Introduction:

This project aims to analyze and visualize the sales data for different product lines at Company ABC Private Limited. As a Business Analyst, the goal is to gain insights into sales performance, identify trends, and present the findings in a clear and meaningful manner.

The dataset provided contains information on various products manufactured and sold by the company, including Classic Cars, Motorcycles, Planes, Ships, Trains, Trucks and Buses, and Vintage Cars. The dataset also includes relevant details such as sales figures, status, product codes, and country information.

Project Tasks:

The project tasks can be summarized as follows:

a. Data Formatting: Proper formatting of the data will be performed, including the use of borders, appropriate font size, and alignment, to enhance readability and presentation.

b. Summary of Product Sales: The sales data will be analyzed to provide a comprehensive summary, including the total sum of sales, average sales, standard deviation, variance, maximum sales, and minimum sales. This will give an overview of the sales performance across different product lines.

c. Categorical Representation using Pivot Table: A worksheet will be created to represent the data categorically using pivot tables. The fields to be considered for this analysis are the status, product line, and sales. This will allow for a deeper understanding of the distribution of sales based on these categories.

d. Customized Ribbon Creation: A customized ribbon named "ABC" will be created to house all the data formatting commands. This will provide a user-friendly interface for accessing and applying formatting options efficiently.

e. Quantities Ordered Analysis: Using pivot tables, the quantities ordered by each country for different quarters in the years 2019, 2020, and 2021 will be analyzed. This analysis will provide insights into the demand patterns and trends over time.

f. Conditional Formatting: Conditional formatting will be applied to the data to visually highlight different values in the "Status" and "ProductLine" fields. This will enable easier identification and interpretation of different categories within the dataset.

g. Data Cleaning: The data will be cleaned with respect to date formatting. Additionally, the values of 2003, 2004, and 2005 will be updated to 2019, 2020, and 2021, respectively, to ensure consistency and accuracy in the analysis.

h. Calculation of Total Sales: The total sales of products in different statuses, including "Shipped," "Cancelled," "Disputed," "In Process," "On Hold," and "Resolved," will be calculated. This will provide a comprehensive understanding of the overall sales performance for each status category.

i. Text Function Usage: Two new fields will be added to the data sheet based on the Productcode column using text functions. The Productcode, for example, "S10_1678," will be split into "S10" and "1678" in separate columns for better analysis and representation.

j. Highlighting Top 10% Values: Conditional formatting will be applied to highlight the top 10% of values in the QuantityOrdered field. This will assist in identifying the highest quantities ordered and their significance in the overall sales.

By performing these tasks, the project aims to provide a comprehensive analysis of the sales data and present the findings in an organized and visually appealing manner. The insights gained from this analysis will help inform decision-making processes, identify areas for improvement, and enhance the overall sales performance at Company ABC Private Limited.