Complete Business Analysis

Project highlights:

AtliQ is a Computer Peripherals and Hardware manufacturing company which sells its products across the globe. It uses mainly three channels of distribution i.e., Retailer, Direct and Distributor. Since the sales volume is high, they want a Power BI report through which they can measure the health of their business, check the relevant kpis and take action wherever necessary.They want 5 report pages describing Finance, Sales, Marketing, Supply-Chain and Executive dashboard which overall summarized main kpis of all areas.Also, they want to understand their Sales on parameters like Customer, Market, Product Category and Product segment etc.

Data Source:

The dataset contains millions of sales records for the period of Sept. 2017 to Dec. 2021. The main dataset is sourced from MySQL database which is of 0.6 GB. Apart from MySQL, data was also sourced from excel files for Sales Target and Market Share for 2022 FY.

Fiscal year of the company starts from September every year.

For the remaining 2022 Fiscal year forecast data is used. Therefore in date slicer 2022est is used.

Following is the breakup of MySQL database used:

a) DB1: This database contains 4 tables:

dim_customer, dim_product, fact_sales_monthly , fact_forecast_monthly

b) DB2: This database contains 5 tables:

Freight cost, Gross price, Manufacturing cost, Post invoice deductions, Pre invoice deductions

Report Pages and associated KPIs :

1. Finance View: To analyse P & L statements for any customer / product / country or aggregation of the above over any time period and More.

2. Sales View: Analyse the performance of your customer(s) over key metrics like Net Sales, Gross Margin and view the same in profitability / Growth matrix.

3. Marketing View: Analyse the performance of your product(s) over key metrics like Net Sales, Gross Margin and view the same in profitability / Growth matrix.

4. Supply Chain View: Get Forecast Accuracy, Net Error, Absolute error and risk profile for product, segment, category, customer etc.

5. Executive View: A top level dashboard for executives consolidating top insights from all dimensions of business.

Key insights:

Finance view:

1. Steady business growth except there is a decline during Mar-2020.

2. Net sales: In 2019 it was $111.37M, which is increased upto $823.85M in 2021 which is a huge growth.

3. Estimated net sales: in 2022 $3736.17M ( 353.55% increase compared to 2021)

4. Gross margin: $99.42M in 2020 increased to $300.63M in 2021

5. Net profit: remained negative for every years

6. Net sales highest in APAC region for 2022est ($1923.77M) with highest percentage increase in NA region with 474.4%

7. Net sales: Contribution of Networking segment is lowest with $38.43M which is 14.89% lower compared to last year.

Sales view:

1. Amazon contributes highest net sales for 2022est($496.88M) with gross margin of 36.78% though it is 1.39% less than 2021

2. Total discount to Amazon, including deductions is 504.74M(50.39% of gross sales)

3. Novus customer in the Philippines has lowest Gross margin%(21.49%), Nova in EU has lowest net sales(1.71M)

Marketing view:

1. Top selling product :Notebook segment with net sales of $266.49M(2021) and projected $1580.43M for 2022est

2. APAC region contributes 1923.77M of total net sales with India alone accounting for $945.34M

3. Profit is positive in Spain, Norway, Sweden(EU) and Mexico, Chilie(LATAM) for 2022est

Supply chain view:

1. AtliQ-e-store, Amazon, AtliQ Exclusive have highest forecast accuracy( 74.59%, 74.54%, 71.69%) for 2021.

Executive view:

1. Dale is the market leader with a market share of 21.8%(2021) and estimated 22.3%(2022)

2. AtliQ is expected to capture a 5.9% market share for 2022 with the highest share in southern european(16.4%) and India( 13.26%) and lowest in Latin America

3. Highest revenue contributed by PC division which is 61.91%