How singhsrv/excel_sales_analysis works
Project type and purpose
• Type: A self-contained Excel-based BI solution (Profit & Loss dashboard)
• Purpose: Ingest raw sales data, transform it, build a data model, compute KPIs, and present interactive visuals.
File structure
• Gm subzone.pdf & P & L Year (Markets).pdf – exports of specific report pages
• README.md – describes ETL, modeling, and dashboard features
(No code directories; everything lives inside one .xlsx file.)
Extract architecture details from the README
• ETL using Power Query
• Data modeling in Power Pivot (relationship building)
• Calculations in DAX (measures & calculated columns)
• Dashboard UI with PivotTables/PivotCharts, slicers, conditional formatting, drill-downs
• Export to PDF (static snapshot of dashboard pages)
Main components & their interactions
a. Raw Data Sources
– CSV/Database/Flat files containing ~500K rows of sales
– External lookup tables (e.g., product lines, sub-zones)
b. ETL Layer (Power Query)
– Data connection(s)
– Query steps: filter, clean, transform, merge
– Custom date table generation
↓
c. Data Model (Power Pivot)
– Tables loaded from Power Query
– Relationships (primary/foreign keys)
– Star schema: FactSales at center, dimension tables for Date, Market, Product, SubZone
↓
d. Calculation Layer (DAX)
– Measures: GM%, target deltas, YTD sums, fiscal period tags
– Calculated columns (e.g., fiscal flags)
e. Presentation Layer (Excel Dashboard)
– PivotTables & PivotCharts bound to the data model
– Slicers (year, quarter, market, product) for interactive filtering
– Conditional formatting & drill-down buttons
– Export routines (to PDF files)
Architectural patterns & principles
• Layered BI architecture:
– Ingestion → Transformation → Modeling → Computation → Visualization
• Star schema in Power Pivot for performance
• Push vs. Pull: queries are “pulling” fresh data into PQ/PP on refresh
• Reusability: date table, common measures, template dashboard sheets