Sales & Logistics Optimization

Overview

This project focuses on optimizing sales and logistics operations by analyzing raw data, cleaning it, and extracting actionable insights. The goal is to improve business performance through data-driven decision-making.

Table of Contents

• Data Processing
  • Understanding the Data
  • Cleaning the Data
  • Adding Calculations and Columns
  • Modeling the Data
• Key Questions and Insights
• Visualizations and Dashboards
• KPI Calculations

Data Processing

Understanding the Data

The dataset includes key columns such as:

• Order ID
• Customer Name
• Sales
• Profit
• Ship Mode
• Segment
• City
• State
• Category
• Sub-Category

Cleaning the Data

The following steps were performed using Excel and Power Query:

1. Removing Duplicates - Eliminated duplicate entries to maintain accuracy.
2. Handling Missing Values - Addressed missing or incomplete data by filling gaps or removing irrelevant rows.
3. Ensuring Consistency in Formatting - Standardized formats for dates, numerical values, and text.

Adding Calculations and Columns

New columns were created for enhanced analysis:

• Cost of Goods Sold (COGS)
• Shipping Time Range

Modeling the Data

The data was structured to establish relationships between tables (if applicable). Measures such as total sales, total profit, and average shipping time were added to facilitate better analysis.

Key Questions and Insights

The analysis was guided by key business questions:

1.To Enhance Customer Loyalty:

• What is the most used ship mode by customers?
• Who are the top 5 customers in terms of sales and order frequency?

2.To Monitor Strengths and Weak Points:

• Which segment generates the most sales?
• Which city has the highest sales value?
• Which state generates the most sales revenue?

3.Performance Measurement:

• What are the top-performing product categories in terms of sales and profit?
• What is the most profitable product?
• What is the least profitable product?

4.Customer Experience:

• On average, how long does it take for orders to reach customers?

Visualizations and Dashboards

The report includes:

• Bar charts for top customers by sales and order frequency.
• Pie charts for sales distribution by customer segment.
• Line charts for average shipping times by ship mode.
• Comparative charts for product category performance (sales and profit).

KPI Calculations

To measure sales team performance, the following KPIs were calculated:

• Sales Target Achievement
• Performance Metrics