This project analyzes sales data sourced from the Superstore dataset (Kaggle). The dataset includes order records from 2014 to 2017, detailing product categories, sub-categories, sales amounts, quantities, discounts, and profit margins.
The objective of this project is to leverage advanced Excel techniques—including Power Query, Power Pivot (Data Modeling and DAX formulas), Macros and VBA, and basic usage of the M language—to clean, analyze, and visualize the dataset. A variety of charts and visualizations have been created to make the insights both visually appealing and easy to comprehend.
- Sales Analysis
Data Cleaning:
Minimal cleaning was required since the dataset contains no missing data or duplicates.
Fixed date formatting using Power Query. Some dates caused errors, which were resolved by adjusting the local date/time format:Right-click column > Using Locale > Change to English (United States).
Insights Generated:
Total Sales Over Years: Grouped data by year (extracted from order dates) using Power Query.
Sales Over Time: Grouped data by quarters within each year to observe seasonal trends.
Sales by Product: Grouped data by Category and Sub-Category to analyze sales distribution across products.
- Profit Analysis
Identified products with negative profit margins using the Profit column.
Created a Profit Margin Percentage column:
Key finding: Regions/products with high discounts often had negative profit margins, while lower discounts corresponded to higher profit margins.
- Customer Analysis
Calculated Order Count for each customer using the COUNTIF function.
Derived key metrics:
Average Order Value (AOV): Total Sales ÷ Order Count.
Customer Lifetime Value (CLV): AOV × Average Order Count.
Identified customers with high CLV and low CLV to guide future strategies and marketing campaigns.
- Efficiency Analysis
Analyzed order delivery efficiency by correlating fulfillment time (days) with ship mode, segment, sales, and profit.
Created a Fulfillment Time (days) column:
Used conditional columns to classify shipments as on-time or delayed, based on average fulfillment times for each shipping mode.
Summarized findings in pivot tables and visualized them to identify patterns.
Deliverables
All analyses are documented in three separate Excel workbooks attached to this repository. These workbooks include:
Data cleaning and transformations.
Visualizations for sales, profit, customer, and efficiency analyses.
Pivot tables and dashboards for actionable insights.
Visualizations Used
Bar Charts for year-over-year sales trends.
Pie Charts to represent category-wise sales distribution.
Line Graphs for quarterly sales trends.
Heatmaps to highlight regional performance and fulfillment efficiency.
This README aims to provide a clear and concise summary of the project, ensuring easy understanding and accessibility for anyone exploring the repository.