Sales Analytics of AtliQ Hardware

Project Overview

AtliQ Hardware is a multinational company that sells hardware products like mice, printers, monitors, and more. The company's direct customers fall into two categories: e-commerce platforms (such as Amazon and Flipkart) and brick-and-mortar stores (such as Croma and Best Buy). AtliQ operates through three channels:

• Retailer: Croma, Amazon
• Direct Channel: AtliQ E-store, AtliQ Exclusive
• Distributor: Neptune (for international markets)

Project Objective

The primary objective of this report is to analyze the sales performance of AtliQ Hardware from 2019 to 2021, helping the company make informed decisions for growth, find out sales growth trends, and evaluate market performance. The reports are based on AtliQ Hardware's fiscal year (Sep-Aug).

The Report Includes:

1. _[Customer Net Sales Performance]
2. _[Market Performance vs Target]
3. _[Top 10 Products Based on Sales]
4. _[Top 5 and Bottom 5 Products in Quantity Sold]
5. _[Division Level Sales Report]
6. _[New Products Launched in 2021]
7. _[Top 5 Marketplaces](

Methods Performed:

1. ETL (Extract, Transform, Load):

   • Data was extracted from external sources, transformed into the desired format, and then loaded into Excel for further data cleaning and modeling.

2. Data Cleaning:

   • Performed in Power Query Editor. Ensured all columns had headers, replaced some column entries, and converted 'Qty' to absolute values to handle negative numbers.

3. Data Modeling:

   • Connected dimension and fact tables to establish relationships.

4. DAX (Data Analysis Expressions):

   • Created DAX measures for 2019, 2020, and 2021 Net Sales. Calculated Net Sales '21 vs '20 and their percentage growth.

5. Pivot Table and Power Pivot:

   • Utilized Pivot Tables and Power Pivot to create insightful reports.

6. Conditional Formatting:

   • Applied conditional formatting to highlight key figures.

Insights:

1. Top Customers in 2021:

   • Amazon ($82.1M), AtliQ Exclusive ($61.1M), AtliQ e-Store ($53.0M)

2. Market Growth:

   • All markets showed significant growth in 2021 compared to 2020, with India and USA leading the way. However, the estimated targets were not met.

3. Top Product:

   • The highest sales were generated by 'AQ Electron 4 3600 Desktop Processor' ($19.4M).

4. Top and Bottom Products by Quantity:

   • 'AQ Master Wired x1 MS' (4.2M units) and 'AQ Home Allin1 Gen 2' (8.9K units)

5. Division Sales:

   • Peripherals & Accessories had the highest sales in 2021 ($338.4M), followed by PCs ($165.8M) and Networking & Storage ($94.7M).

6. New Product Sales:

   • Sales of 16 newly launched products in 2021 totaled $172.2M, accounting for 29.4% of total sales.

7. Top 5 Marketplaces in 2021:

   • India, USA, South Korea, Canada, and the United Kingdom

Skills Gained:

1. Understanding the ETL process
2. Learning data cleaning and transformation, dataset merging
3. Importance of fiscal year and deriving fiscal year from calendar year
4. Understanding basic business metrics
5. Establishing data model relationships with Power Pivot
6. Creating various Pivot Tables and DAX formulas to calculate required values

Conclusion

The Sales Report for AtliQ Hardware provides valuable insights into sales performance over various years, identifying trends and patterns across different customers and markets. These insights help the management understand customer behavior and market dynamics, leading to data-driven strategic decision-making and fostering the growth of the company.

Combined Report

The combined report for the sales analysis of AtliQ Hardware is attached to this repository. It includes detailed insights and data visualizations that support the findings mentioned above. _[Click here]