# mohdakif919/sales-and-finance-insights architecture

> Architecture diagram of the GitHub repository mohdakif919/sales-and-finance-insights, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-06-22). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/mohdakif919/sales-and-finance-insights
- Repository: https://github.com/mohdakif919/sales-and-finance-insights

## Overview

##### 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.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Data Ingestion Layer
    subgraph "Data Ingestion"
        DS1["Sales Transactions: Sales Report - 1.xlsx"]:::data
        DS2["Sales Transactions: Sales Report - 2.xlsx"]:::data
        DS3["Financial Dataset: Finance Report.xlsx"]:::data
        README["README.md"]:::doc
    end

    %% ETL & Data Modeling Layer
    subgraph "ETL & Data Model"
        PQ1["Power Query Transform & Data Model (Sales1)"]:::etl
        PQ2["Power Query Transform & Data Model (Sales2)"]:::etl
        PQ3["Power Query Transform & Data Model (Finance)"]:::etl
        Model["Data Model (Star Schema)"]:::model
    end

    %% Analytics Layer
    DAX["DAX Measures (Net Sales, COGS, Gross Margin, GM%)"]:::analytics

    %% Presentation Layer
    subgraph "Presentation Layer"
        subgraph "Sales Analytics"
            PivotS["Pivot Tables & Conditional Formatting"]:::presentation
        end
        subgraph "Finance Analytics"
            PivotF["Pivot Tables & Conditional Formatting"]:::presentation
        end
    end

    %% Export Layer
    subgraph "Export Layer"
        subgraph "Sales PDF Reports"
            PDF1["Customer Net Sales Performance.pdf"]:::export
            PDF2["Division Level.pdf"]:::export
            PDF3["Market Performance Vs Target.pdf"]:::export
            PDF4["New Products (2021).pdf"]:::export
            PDF5["Top & Bottom 5 Products Sold.pdf"]:::export
            PDF6["Top 10 Products.pdf"]:::export
            PDF7["Top 5 Countries (Net Sales 2021).pdf"]:::export
        end
        subgraph "Finance PDF Reports"
            FP1["GM % By Quarters (sub_zone).pdf"]:::export
            FP2["P&L (Fiscal Month).pdf"]:::export
            FP3["P&L (Fiscal Year & Market).pdf"]:::export
            FP4["P&L (Fiscal Year).pdf"]:::export
            FP5["P&L (Market).pdf"]:::export
        end
        subgraph "Final Workbooks"
            FW1["Sales Report - 1.xlsx"]:::export
            FW2["Sales Report - 2.xlsx"]:::export
            FW3["Finance Report.xlsx"]:::export
        end
    end

    %% Data Flows
    DS1 --> PQ1
    DS2 --> PQ2
    DS3 --> PQ3
    README --> PQ1
    README --> PQ2
    README --> PQ3

    PQ1 --> Model
    PQ2 --> Model
    PQ3 --> Model

    Model --> DAX

    DAX --> PivotS
    DAX --> PivotF

    PivotS --> PDF1
    PivotS --> PDF2
    PivotS --> PDF3
    PivotS --> PDF4
    PivotS --> PDF5
    PivotS --> PDF6
    PivotS --> PDF7

    PivotF --> FP1
    PivotF --> FP2
    PivotF --> FP3
    PivotF --> FP4
    PivotF --> FP5

    PQ1 --> FW1
    PQ2 --> FW2
    PQ3 --> FW3

    %% Click Events
    click DS1 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Sales Report - 1.xlsx"
    click DS2 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Sales Report - 2.xlsx"
    click DS3 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Finance Report.xlsx"
    click PQ1 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Sales Report - 1.xlsx"
    click PQ2 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Sales Report - 2.xlsx"
    click PQ3 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Finance Report.xlsx"
    click PDF1 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - Customer Net Sales Performace.pdf"
    click PDF2 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - Division Level.pdf"
    click PDF3 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - Market Performace Vs Target.pdf"
    click PDF4 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - New Products (2021).pdf"
    click PDF5 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - Top & Bottom 5 Products Sold.pdf"
    click PDF6 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - Top 10 Products.pdf"
    click PDF7 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Report - Top 5 Countries (Net Sales 2021).pdf"
    click FP1 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Report - GM % By Quarters (sub_zone).pdf"
    click FP2 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Report - P&L (Fiscal Month).pdf"
    click FP3 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Report - P&L (Fiscal Year & Market).pdf"
    click FP4 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Report - P&L (Fiscal Year).pdf"
    click FP5 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Report - P&L (Market).pdf"
    click FW1 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Sales Report - 1.xlsx"
    click FW2 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Sales Analytics/AtliQ Hardwares Sales Report - 2.xlsx"
    click FW3 "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/Finance Analytics/AtliQ Hardwares Finance Report.xlsx"
    click README "https://github.com/mohdakif919/sales-and-finance-insights/blob/main/README.md"

    %% Styles
    classDef data fill:#cce5ff,stroke:#2b6cb0,color:#2b6cb0;
    classDef etl fill:#ffebcc,stroke:#d69e2e,color:#ad5900;
    classDef model fill:#d6f5d6,stroke:#2f855a,color:#276749;
    classDef analytics fill:#e5ccff,stroke:#6b46c1,color:#44337a;
    classDef presentation fill:#f2f2f2,stroke:#718096,color:#2d3748;
    classDef export fill:#edf2f7,stroke:#4a5568,color:#1a202c;
    classDef doc fill:#f0fff4,stroke:#38a169,color:#22543d;
```
