AtliQ Hardware is a company which supplies computer hardware and peripherals to its respective customers like Amazon, Flipkart, Neptune, Croma, AtliQ e stores through different channels including direct, retailer and distribution. It needs insights about its sales and finance data.
In this project I made finance and sales analytics report for AtliQ Hardware to help them make informed finance decisions, come up with better negotitations and offering insights into key metrics and trends.
With the aim to solve this problem and build data analytics skills I picked excel and used its advanced analytics features like power pivot, power query, measures and DAX to come up with insightful reports.
Process:
- Performed data cleaning and transformation in Power Query, created useful calculated columns
- Loaded the data to data model and formed relationships between tables
- Used Power Pivot and DAX to create meaures and calculated fields for the required reports
- Created Customer Performance Report
- Conducted a comprehensive comparison between Market Performance vs Target
- Found answers to business questions like Top 10 perfoming products
-
Purpose of sales analytics: Empower businesses to monitor and evaluate their sales activities and performance.
-
Importance of analyzing sales data: Identify sales patterns and track key performance indicators (KPIs).
-
Role of reports: Determine effective customer discounts, facilitate negotiations with consumers, and identify potential business expansion opportunities in promising countries.
- Created Profit and Loss (P&L) reports by Fiscal Year & Months
- Created Profit and Loss (P&L) reports by Markets
- Generated GM% by quartes (subzone)
Project Priority and Scenario Planning:
- Created Project Priority Matrix to help teams, product managers and users to prioritize their projects in an effective manner
- Made a scenario planning tool to help AtliQ in planning and deciding best discount rates for their customers
-
Purpose of sales analytics: Evaluation of financial performance, support decision-making, and facilitate communication with stakeholders.
-
Importance of analyzing Finance data: Aid in benchmarking against industry peers and previous periods Foundation for budgeting and forecasting.
-
Role of reports: Align financial planning with strategic goals Instill confidence in the organization's financial outlook.
- Proficiency in ETL methodology (Extract, Transform, Load).
- Skills to generate a date table using Power Query.
- Ability to derive fiscal months and quarters.
- Establishing data model relationships with Power Pivot.
- Proficiency in incorporating supplementary data into an existing data model.
- Utilizing DAX to create calculated columns.
- Refined understanding of Sales & Finance Reports
- Designing user-centric reports with empathy in mind.
- Optimization of report generation through meticulous fine-tuning.
- Developing a systematic approach to devising a report building plan.