How mohdakif919/sales-and-finance-insights works
Project type and purpose
• This is a data‐analytics/reporting project built entirely in Microsoft Excel.
• It produces two major reporting modules: Sales Analytics and Finance Analytics.
• The goal is to ingest raw sales and financial data, clean and transform it, build a data model, compute metrics (DAX), and generate reports (Pivot Tables & PDF/Excel exports).
File structure
• Top level contains two folders: “Sales Analytics” and “Finance Analytics,” plus a README.
• Each folder holds source Excel files (.xlsx) and rendered reports (.pdf).
• There are no code, service or deployment directories—this is an Excel‐centric workbook system.
README for insights
• Tools and features: Power Query (ETL), Power Pivot (data modeling), DAX (calculated measures), Pivot Tables, Conditional Formatting.
• Key metrics: Net Sales, COGS, Gross Margin, GM%.
• Output formats: Printable PDF summaries and reusable Excel workbooks.
Main components
a. Data Sources
– Raw transaction tables (Sales Reports 1 & 2.xlsx)
– Financial datasets (AtliQ Hardwares Finance Report.xlsx)
b. ETL Layer (Power Query)
– Data cleansing and transformation steps
– Joins, lookups, date tables
c. Data Model (Power Pivot)
– Star schema or relationship graph
– Fact tables (sales, finance) and dimension tables (time, customer, product, market)
d. Analytics Layer (DAX Measures)
– Net Sales, COGS, Gross Margin, GM%
e. Presentation Layer
– Pivot Tables for Sales Analytics
– Pivot Tables for Finance Analytics
– Conditional Formatting rules for highlighting KPIs
f. Export Layer
– PDF exports for each report view
– Final Excel workbooks for stakeholder distribution
Architectural patterns and principles
• ETL→Data Warehouse→BI Visualization layered approach.
• Separation of concerns: raw data, transformation logic, data modeling, calculation, and presentation.
• Reusable components: a single centralized data model powering multiple report views.
– Break the project into these layers and subcomponents.
– Include notes on which Excel features are used at each step.
– Keep it detailed but avoid extraneous steps—focus on the five or six core components above.