DATA ANALYSIS WITH MS EXCEL

Introduction

This project involved using only Microsoft Excel for data cleaning, exploration, and visualization. The analysis focused on uncovering meaningful insights into how much products were sold and status of each product.

Data Structure

The dataset, sourced from Kaggle.com, includes sales record covering details such order number, quantity ordered, price, order date, status, product line, customer name, customer id, phone, state, country, deal size, and more.

Data Cleaning and Preparation

The data cleaning and preparation phase was done to ensure the dataset is free from errors, outliers and duplicates.
Here is a picture of the dataset prior to cleaning:

Some of the steps taken to clean the data include:

1. Removal of duplicates
2. And the removal of Blank rows and columns

The analysis was carried out using pivot tables and charts, with slicers integrated for seamless navigation across different dashboard segments. The dashboard addresses key insights, including:

1.Sales by Status
2.Sales by Product
3.Sales by Deal Size
Here is a picture of the dashboard:

Sales by Status
In this dataset, the sales by status reveals significant insights:

Products that were successfully shipped: $9,291,501

Products that were that were disputed: $72,213

Products that were cancelled: $194,487

Products that were in process: $144,730

Products that were on hold: $178,979

Products that were resolved: $150,718

Successful shipments dominate the total sales, accounting for an overwhelming majority of revenue — over $9.2 million. This suggests strong fulfillment rates and efficient logistics.

Disputed transactions make up the smallest portion at just $72,213, indicating relatively low customer dissatisfaction or conflict in transactions.

Cancelled orders ($194,487) are significantly higher than disputed ones, which may point to issues in order processing, payment failures, or changing customer preferences.

In-process ($144,730) and on-hold ($178,979) statuses represent pending transactions, suggesting a pipeline of potential revenue that is yet to be finalized.

Resolved transactions stand at $150,718, which could mean successful resolution of issues or disputes, ensuring that some initially problematic orders were ultimately fulfilled.

Sales by Product

In this dataset, the sales of product are as follows:

Classic Cars — $3,919,616

Motorcycles — $1,166,388

Planes — $975,004

Ships — $714,437

Trains — $226,243

Trucks and Buses — $1,127,790

Vintage Cars — $1,903,151

This analysis indicates the total sales or revenue generated from different categories of vehicles. Classic Cars lead by a significant margin at $3.9 million, followed by Vintage Cars at $1.9 million. Motorcycles and Trucks/Buses also contribute substantial amounts, while Trains and Ships bring in the least.

It suggests that Classic and Vintage Cars are the most valuable or in-demand categories, possibly due to collector interest or higher market value. On the other hand, Trains and Ships may represent niche interests or lower transaction volumes.

Sales by Deal Size

In this dataset, I analyzed the sales by deal size.

Medium: Has the highest sales at $6,087,432.

Small: Has the least sales with $1,302,119.

Large: Has the second highest sales with $2,643,077.

This offers a valuable perspective on how deal size influences sales performance.

Medium-sized deals drive the majority of sales, totaling over $6 million, indicating they are the most common and profitable transaction size. This may suggest that customers prefer moderately priced products that balance affordability with value.

Small deals generate the least revenue, contributing $1.3 million, which could mean they are either lower-margin items or occur less frequently in comparison to larger transactions.

Large deals rank second with $2.6 million in sales, showing that while high-value transactions contribute significantly, they are less frequent than medium-sized ones.

Dashboard