This project analyzes sales trends, shipment patterns, and revenue drivers using Microsoft Excel. The dataset includes sales and shipment data from 2015 to 2018. The objective is to extract meaningful insights and create visual representations to aid in decision-making.
- Total Profit by Category - Identify total profit for each product category.
- Total Profit by Sub-Category - Breakdown of profits per sub-category.
- Total Sales by Product - Determine sales figures for each product.
- Top Sales City - Identify the city with the highest recorded sales.
- Most Common Shipping Mode - Determine the most frequently used shipment mode.
- Highest Sales Per Unit Product - Compute sales per unit using a calculated column.
- Unique Customers Count - Total unique customers in the dataset (793).
- Pivot Table for Customer Sales
- Show total sales per customer.
- Identify top 3 customers with the highest purchases.
- Pivot Chart for Sales Distribution
- Visualize sales by segment (Consumer, Corporate, Home Office).
- Use slicers to filter by Region and Ship Mode.
- Customer Segment Percentage - Determine the percentage distribution of customers across segments.
- Category Contribution to Sales - Calculate the contribution of each category to total sales.
- Sales by State & Region
- Use the FILTER function to find the region for each state and compute total sales.
- Count states with sales exceeding $100,000.
- Pivot Table for Sales by Region & Category - Visualize total sales by region and category.
- Average Sales Value per Order by Region - Compute the average order value per region.
- Regional Sales Contribution - Use SUMIF to calculate regional sales and their percentage of total sales.
-
Order Sales Analysis
- Compute total sales per order ID and highlight values above the average using conditional formatting.
- Identify top 5 orders and create a structured table listing category-wise sales using INDEX-MATCH.
-
Order Distribution by Sales Range
- Categorize orders into specific sales ranges (<$100, $100-$200, $200-$300, etc.).
- Use COUNTIFS to define multiple ranges and count occurrences.
- Excel Formulas: SUMIF, COUNTIFS, INDEX-MATCH, FILTER, Conditional Formatting
- Pivot Tables & Charts: For sales breakdown and visualization
- Slicers & Data Filters: For dynamic data interaction
- Excel dashboard with interactive pivot tables & charts
- Sales analysis reports with actionable insights
This project provides a structured approach to analyzing sales and shipment trends using Excel. It enables businesses to make data-driven decisions based on detailed insights into customer behavior, product performance, and regional sales contributions.