An end-to-end Excel Business Intelligence (BI) project analyzing AtliQ Hardware’s global sales and profitability performance across multiple fiscal years, customers, and markets.
This project demonstrates how Microsoft Excel can be used as a complete BI solution — integrating Power Query, Power Pivot, and DAX to deliver interactive, data-driven dashboards for real business insights.
- Build an integrated Excel BI system using Power Query, Power Pivot, and DAX.
- Analyze Net Sales, COGS, and Gross Margin % across multiple fiscal years.
- Evaluate market-wise and customer-wise profitability.
- Compare target vs actual performance for global markets.
- Deliver interactive dashboards that support management-level decisions.
- Imported raw sales data from multiple Excel sources.
- Cleaned and standardized datasets (handled blanks, renamed columns, removed duplicates).
- Built a custom Date Table with calculated columns for:
- Fiscal Year
- Fiscal Month
- Month Order
- Month Name
⚙️ Note: Source connections are disabled in this version for portability.
The final Excel model retains all transformations and logic used during data preparation.
Structured a Star Schema model connecting fact and dimension tables:
| Fact Table | Dimension Tables |
|---|---|
Fact_Sales_monthly |
dim_customer, dim_market, dim_product, dim_date |
Relationships were built using:
Customer CodeProduct CodeMarketDate
Created key measures using DAX for analytical flexibility:
- Net Sales =
SUM(Sales[Net_Sales]) - Gross Margin =
[Net Sales] - [COGS] - GM % =
DIVIDE([Gross Margin], [Net Sales], 0) - Net Sales FY21 / FY22 / FY23 via
CALCULATE()and filter context
Four interactive dashboards were created to visualize business performance from different perspectives:
Tracks Net Sales, COGS, and GM% across fiscal months.
Compares profitability across global markets using color-coded heatmaps.
Displays YoY sales growth and contribution of top customers.
Highlights performance gaps between targets and actuals for each region.
- India, USA, and Canada emerged as top markets driving global revenue.
- Gross Margin % remained steady at ~41–42%, indicating operational efficiency.
- Rising COGS affected profitability in certain European regions.
- AtliQ Exclusive and Croma showed exceptional YoY growth (300%+).
| Tool | Purpose |
|---|---|
| Microsoft Excel | Data modeling, analysis, and visualization |
| Power Query | ETL — Data extraction, cleaning, and transformation |
| Power Pivot | Data modeling and establishing relationships |
| DAX | KPI calculations and financial metrics |
| Conditional Formatting & Slicers | Interactive visualization layer |
- Built a complete Excel-based BI architecture (Power Query → Power Pivot → Dashboard).
- Created custom DAX measures for dynamic reporting.
- Applied Star Schema design for optimal model performance.
- Designed executive-level dashboards replicating enterprise BI workflows.
| File | Description |
|---|---|
Excel Project (P & L by months).xlsx |
Fiscal-wise Profit & Loss report |
Excel Project (P & L by markets).xlsx |
Market-wise profitability analysis |
Excel Project (Customer Net Sales Report).xlsx |
Customer performance comparison |
Excel Project (Market Performance vs Target).xlsx |
Regional performance vs targets |
🔗 Download Excel Project (Google Drive)
Star Schema built using fact and dimension relationships for accurate aggregation and time intelligence.
I’m Yash Hooda, an aspiring Data Analyst passionate about transforming raw data into powerful business insights using Excel, Power BI, and SQL.
I aim to bridge data and decision-making with intuitive, visually rich analytics.
📍 Rohtak, Haryana, India
📫 Let's Connect:
LinkedIn • GitHub • Email
⭐ If you found this project insightful, don’t forget to star the repository!
Tags: #Excel #PowerQuery #PowerPivot #DAX #DataAnalytics #BusinessIntelligence #AtliQ #FinanceDashboard