Atliq Hardware is a leading provider of computer hardware and peripheral devices, specializing in a comprehensive portfolio that includes personal computers, mice, printers, and various accessories. The company operates through a multi-channel distribution strategy, leveraging both traditional retail partnerships with major chains such as Croma and Best Buy, and e-commerce platforms including Flipkart and Amazon.

This business intelligence project focuses on developing advanced analytics capabilities to support data-driven decision-making across sales and financial operations. By integrating multiple data sources and implementing sophisticated analytical frameworks, the project delivers actionable insights that enable strategic planning and operational optimization.

The objective is to design and implement a comprehensive Business Intelligence solution that provides real-time sales and financial analytics for Atliq Hardware. This interactive dashboard and reporting system aims to transform raw transactional data into strategic insights, enabling stakeholders to make informed decisions, identify market opportunities, optimize resource allocation, and develop data-driven strategies for sustained business growth.

1. Customer Performance Analysis - View Report

   • Evaluate customer-wise net sales performance across fiscal years
   • Identify top-performing customers and revenue trends

2. Market Performance vs Target - View Report

   • Compare actual market performance against defined targets
   • Analyze variance and achievement percentages by region

3. Top Products by Quantity - View Report

   • Identify top and bottom 5 products based on quantity sold
   • Understand product demand patterns

4. Top Products by Net Sales Growth - View Report

   • Analyze top 10 products with highest net sales percentage increase from FY2020 TO FY2021
   • Track product growth trajectories

5. Geographic Performance - View Report

   • Evaluate top 5 countries based on net sales performance in fiscal Year 2021
   • Support market expansion strategies

6. Division Performance - View Report

   • Assess division-wise net sales performance
   • Enable strategic resource allocation

1. P&L by Fiscal Year - View Report

   • Comprehensive Profit & Loss analysis across fiscal years
   • Track revenue, costs, and profitability trends

2. P&L by Market - View Report

   • Market-wise Profit & Loss breakdown
   • Identify profitable regions and optimization opportunities

• ✅ Revenue Growth: Achieved significant year-over-year net sales growth of [204.48%] from FY2020 to FY2021
• ✅ Top Performer: [Amazon] emerged as the highest-performing customer with net sales of $[82.09]M in FY2021
• ✅ Product Performance: [AQ Mx NB] showed exceptional growth with [5623.52%] increase in net sales from FY2020 TO FY2021
• ✅ Geographic Expansion: [India] contributed [27%] of total net sales, indicating strong market presence in FY2021
• ✅ Target Achievement: Almost Every Country AtliQ Hardwares Missed the Target in Fiscal Year 2021 in terms of net sales.In [India] AtliQ missed net sales target by [-5.59%] followed by [USA] AtliQ missed net sales target by [-10.44%] and [Poland] AtliQ missed sales target by [-15.35%]
• ✅ Division Success: [Peripherals & Accessories] division demonstrated robust performance with $[338.38]M in net sales in FY2021 followed by [Personal Computer] division with net sales [165.76]M
• ✅ Volume Demand: Top Demanding Products based on quantity sold [AQ Master Wired x1 Ms] with [4.15]M quantity Sold Followed By [AQ Master Wireless x1 Ms] with [4.13]M quantity sold.

• ✅ Gross Margin: Gross Margin is increasing from FY2019 with [36.24]M to FY2021 with [218.16]M despite increasing COGS. Although Gross Margin (%) is declined from FY2019 with [41.43%] to FY2021 with [36.43%]
• ✅ Cost of Good Sold(COGS): Total COGS stood at $[380.71]M in FY2021, representing [63.57%] of net sales
• ✅ Market Profitability(Gross Margin(%)): [Newzealand] demonstrated highest Gross margin percentage of [48.23%] and [Germany] demonstrated lowest Gross Margin Percentage of [26.18%] in FY2021.

// Core Sales Metrics
Net Sales($) = SUM(fact_monthly_sales[net_sales_amount])

Quantity Sold = SUM(fact_monthly_sales[Qty])

// Cost Metrics
Total Manufacturing Cost ($) = SUM(fact_monthly_sales[manufacturing_cost])

Total Freight Cost ($) = SUM(fact_monthly_sales[freight_cost])

COGS($) = [Total Manufacturing Cost ($)] + [Total Freight Cost ($)]

// Profitability Metrics
Gross Margin ($) = [Net Sales($)] - [COGS($)]

GM (%) = DIVIDE([Gross Margin ($)], [Net Sales($)], 0)

// Year-over-Year Analysis
Net Sales 2019 ($) = CALCULATE([Net Sales($)], dim_date[Fiscal Year] = "2019")

Net Sales 2020 ($) = CALCULATE([Net Sales($)], dim_date[Fiscal Year] = "2020")

Net Sales 2021 ($) = CALCULATE([Net Sales($)], dim_date[Fiscal Year] = "2021")

// Target Analysis
Net Sales Target 2021 ($) = SUM(net_sales_target_2021[ns_target])

2021 - Target = [Net Sales 2021 ($)] - [Net Sales Target 2021 ($)]

2021 - Target (%) = DIVIDE(([Net Sales 2021 ($)] - [Net Sales Target 2021 ($)]), [Net Sales Target 2021 ($)], 0)

// Growth Metrics
YoY Sales Growth (%) 2021 vs 2020 = DIVIDE(([Net Sales 2021 ($)] - [Net Sales 2020 ($)]), [Net Sales 2020 ($)], 0)

Excel-Analytics-Project/
│
├── dataset/
│   ├── dim_customer.csv
│   ├── dim_market.csv
│   ├── dim_product.csv
│   ├── fact_sales_monthly.csv
│   ├── fact_sales_monthly_with_cost.csv
│   └── ns_targets_2021.csv
│
├── excel_analytical_file/
│   └── Sales_Insights_Report.xlsx
│
├── financial_reports/
│   ├── P & L By Fiscal Year Report.pdf
│   └── P & L By Market Report.pdf
│
├── sales_reports/
│   ├── Customer Net Sales Performance Report.pdf
│   ├── Division Net Sales Performance.pdf
│   ├── Market Performance vs Target Report.pdf
│   ├── Top & Bottom 5 Products Based on Quantity Sold Report.pdf
│   ├── Top 10 Products Based on Net Sales Percentage Increase.pdf
│   └── Top 5 Country Based on Net Sales Report.pdf
│
├── screenshots/
│   ├── Atliq_logo_for_dashboard.png
│   └── excel_power_pivot_data_model.png
│
├── .gitignore
└── README.md

• Microsoft Excel 2016 or later (with Power Pivot and Power Query enabled)
• Basic understanding of Excel and data analytics

1. Clone the Repository

   git clone https://github.com/Coderbiswajit24/Excel-Sales-Finance-Analytics.git
   cd Excel-Sales-Finance-Analytics

2. Create a Blank Excel File

   • Open Microsoft Excel
   • Create a new blank workbook

3. Load Data Using Power Query

   • Go to Data tab → Get Data → From File → From Folder
   • Browse and select the downloaded dataset/ folder
   • Click OK to load all CSV files from the folder into Power Query
   • Transform and load the data as per your requirements

4. Alternative Method (If Step 3 Fails)

   • Open the Sales_Insights_Report.xlsx file from the excel_analytical_file/ folder
   • Use this file as a reference to understand the data model and structure
   • You can directly use this file to explore the reports and analytics

5. Explore Reports

   • Navigate through different sheets to view sales and financial analytics
   • Interact with pivot tables and charts for dynamic insights
   • Refresh data connections if needed using Data > Refresh All

Course Credit: This project was developed as part of the Codebasics Excel Course - Excel: Mother of Business Intelligence.

Contributions, issues, and feature requests are welcome!

This project is free to use and does not contain any license.

BISWAJIT SASMAL

Give a ⭐️ if this project helped you!