📊 Financial KPI Dashboard — FinSight Capital NBFC

Portfolio Project 6 · Data Analyst (Finance) · Business Analyst (Finance)

A production-grade financial KPI dashboard built with SQL + Python + Chart.js for a fictional Indian NBFC (Non-Banking Financial Company) called FinSight Capital. Demonstrates real-world NBFC analytics: AUM tracking, NPA monitoring, collection efficiency, NIM computation, and risk metrics — all powered by SQLite queries and an interactive HTML dashboard.

---

🎯 Project Overview

Attribute	Detail
Domain	NBFC / Lending / Financial Analytics
Roles Targeted	Data Analyst (Finance), Business Analyst (Finance)
Skills Demonstrated	SQL (window functions, CTEs, JOINs), Python data engineering, BI dashboard design
Dataset	Synthetic 3-year monthly data (Jan 2022 – Dec 2024)
Database	SQLite (zero-server, runs anywhere)
Dashboard	Single-file HTML — open in browser, no server needed

What this project showcases

• 8 KPIs computed via real SQL queries (not pandas) — showing SQL-first analytics thinking
• NBFC domain fluency: NPA, PCR, CAR, NIM, PAR, DPD buckets, AUM, collection efficiency
• Realistic data storytelling: NPA stress in 2023, recovery in 2024, AUM growth trajectory
• Production dashboard: sticky nav, tabbed layout, Chart.js visualisations, IBM Plex Sans typography

---

📁 Repository Structure

financial-kpi-dashboard/
│
├── src/
│   ├── generate_data.py     # Synthetic data generator → 4 CSV tables
│   ├── sql_queries.py       # 9 SQL query strings (KPI definitions)
│   └── kpi_engine.py        # SQLite runner → kpis.json
│
├── data/                    # Auto-generated CSVs (git-ignored)
│   ├── loan_portfolio.csv
│   ├── collections.csv
│   ├── profit_loss.csv
│   └── risk_metrics.csv
│
├── financial_kpi_dashboard.html   # Interactive dashboard (open in browser)
├── kpis.json                      # KPI output (auto-generated)
├── requirements.txt
├── .gitignore
└── README.md

---

🗄️ Dataset Description

All data is synthetic and generated by src/generate_data.py. No real financial data is used.

Table 1: loan_portfolio

The core table with ~75,000+ rows representing individual loan records across 36 months.

Column	Type	Description
loan_id	INT	Unique loan identifier
month	INT	Month (1–12)
year	INT	Year (2022–2024)
product_type	TEXT	Personal / Business / Home / Vehicle
disbursed_amount	REAL	Original loan amount (₹ Lakhs)
outstanding_amount	REAL	Current outstanding (₹ Lakhs)
status	TEXT	Active / Closed / NPA / Written-off
customer_segment	TEXT	Retail / MSME / Corporate
geography	TEXT	Metro / Tier1 / Tier2 / Rural
interest_rate	REAL	Annual interest rate (%)
emi_amount	REAL	Monthly EMI (₹ Lakhs)
dpd_bucket	INT	Days Past Due: 0 / 30 / 60 / 90 / 180 / 360

Table 2: collections

One row per active/NPA loan per month — tracks amount due vs collected.

Column	Description
loan_id	Loan reference
amount_due	EMI due that month (₹ Lakhs)
amount_collected	Actual amount collected
collection_efficiency	Ratio (0–1)

Table 3: profit_loss

Monthly P&L summary at company level (₹ Crores).

Column	Description
net_interest_income	NII = Interest earned – Interest paid
opex	Operating expenditure
provisions	Loan loss provisions
pat	Profit After Tax
net_margin	PAT / NII × 100

Table 4: risk_metrics

Monthly regulatory and credit risk indicators.

Column	Description
gnpa_pct	Gross NPA %
nnpa_pct	Net NPA %
pcr_pct	Provision Coverage Ratio %
car_pct	Capital Adequacy Ratio %
cost_of_funds	Weighted average cost of borrowing %

---

📐 KPI Definitions & Formulas

KPI 1 — AUM (Assets Under Management)

AUM = Σ Outstanding Amount (Active + NPA loans)

Excludes Closed and Written-off loans. Tracked monthly in ₹ Crores.

KPI 2 — NPA Ratios

Gross NPA % = NPA Outstanding / (Active + NPA Outstanding) × 100
Net NPA %   = (NPA Outstanding – Provisions) / (Active + NPA Outstanding – Provisions) × 100

RBI guideline: GNPA > 5% is considered stress. FinSight peaks at ~6.2% in mid-2023.

KPI 3 — Collection Efficiency

Collection Efficiency % = Σ Amount Collected / Σ Amount Due × 100

Computed at product × geography × year granularity.

KPI 4 — Net Interest Margin (NIM)

NIM (Monthly) = NII / AUM × 100
NIM (Annualised) = NIM (Monthly) × 12

Proxy for lending profitability. A higher NIM with lower NPA indicates strong credit quality.

KPI 5 — Cost-to-Income Ratio

Cost-to-Income = OPEX / NII × 100

Lower is better. Indian NBFCs typically target 35–45%.

KPI 6 — Portfolio at Risk (PAR)

PAR-30 = Outstanding (DPD ≥ 30) / Total Outstanding × 100
PAR-60 = Outstanding (DPD ≥ 60) / Total Outstanding × 100
PAR-90 = Outstanding (DPD ≥ 90) / Total Outstanding × 100

PAR-90 closely correlates with future NPA formation.

KPI 7 — Disbursement Growth

MoM Growth % = (Current Month Disbursement – Prior Month) / Prior Month × 100
YoY Growth % = (Current Month Disbursement – Same Month Prior Year) / Prior Year × 100

KPI 8 — Provision Coverage Ratio (PCR)

PCR % = Cumulative Provisions / Gross NPA Outstanding × 100

RBI mandates minimum PCR of 70% for scheduled commercial banks. NBFCs target 55–70%.

---

🗄️ SQL Query Explanations

All 9 queries are in src/sql_queries.py and run via SQLite.

Query	KPI	Key SQL Concepts
Q1	AUM Trend	GROUP BY, SUM, PRINTF for date formatting
Q2	NPA Ratios	LEFT JOIN, CASE WHEN, NULLIF
Q3	Collection Efficiency	Multi-column GROUP BY, JOIN across 3 keys
Q4	NIM	CTE (WITH clause), derived metric, JOIN
Q5	Cost-to-Income	Simple aggregation, division
Q6	PAR Buckets	Multiple CASE WHEN in single SELECT
Q7	Disbursement Growth	CTE + LAG() window function, nested CTEs
Q8	PCR	Running SUM() window function (ROWS BETWEEN)
Q9	Top 5 Geo NPA	Dual CTE, RANK() window function, LIMIT

Q9 (Bonus) — Top 5 Geographies by NPA Contribution is particularly interview-relevant: it uses dual CTEs with a cross-join for percentage computation, and a RANK() window function for ordering — skills commonly tested in Data Analyst interviews.

---

🛠️ Tech Stack

Component	Technology	Purpose
Data Generation	Python (NumPy, Pandas)	Synthetic NBFC dataset
Database	SQLite 3 (stdlib)	Zero-dependency SQL engine
SQL Queries	SQLite SQL	KPI computation
KPI Engine	Python	Query orchestration, JSON export
Dashboard	HTML + Chart.js 4.4	Interactive visualisations
Typography	IBM Plex Sans / Mono	Professional finance aesthetic
Charts	Chart.js CDN	Line, Bar, Donut chart types

---

🚀 How to Run

Prerequisites

python --version   # 3.9+
pip install -r requirements.txt

Step 1 — Generate synthetic data

python src/generate_data.py

Creates data/loan_portfolio.csv, data/collections.csv, data/profit_loss.csv, data/risk_metrics.csv

Step 2 — Run KPI engine

python src/kpi_engine.py

Loads all CSVs → SQLite → runs 9 SQL queries → exports kpis.json

Step 3 — Open dashboard

# Option A: just open the file
open financial_kpi_dashboard.html

# Option B: serve locally (avoids any CORS with fetch)
python -m http.server 8080
# Then visit: http://localhost:8080/financial_kpi_dashboard.html

Live Demo

Note: The dashboard includes a fallback synthetic data generator in JavaScript — it will render fully even without running kpi_engine.py, making it easy to demo standalone.

---

📊 Dashboard Features

Tab	Content
Overview	9 KPI cards + AUM trend + NPA overview + Disbursement MoM
Portfolio & AUM	Full disbursement bar chart + PAR trend + DPD buckets + Segment/Geo donut
NPA & Risk	Full NPA timeline + PCR trend + CAR compliance + Geo NPA table
Collections	CE by product + CE by geography + Monthly CE trend
P&L / NIM	P&L stacked bar + NIM + CTI + PAT + Cost of Funds
SQL Queries	All 9 SQL queries with syntax highlighting + copy button

---

💼 Interview Talking Points (Data Analyst Roles)

"Walk me through this project."

"I built an end-to-end financial analytics pipeline for a fictional NBFC. I started by designing a realistic 4-table schema — loan_portfolio, collections, P&L, and risk_metrics — then wrote 9 SQL queries covering every key NBFC KPI: AUM, NPA ratios, collection efficiency, NIM, cost-to-income, PAR buckets, disbursement growth, and PCR. The SQL uses CTEs, window functions like LAG() and RANK(), and conditional aggregation with CASE WHEN. The Python engine loads everything into SQLite, runs the queries, and exports JSON to an interactive Chart.js dashboard."

"Why SQLite over pandas for the KPIs?"

"The goal was to demonstrate SQL skills — a core requirement for Data Analyst roles. Pandas could do these computations, but SQL is the universal language for financial reporting in enterprise environments. Using SQLite means the queries are portable to PostgreSQL, Redshift, or Snowflake with minimal changes."

"How did you handle the NPA trend storytelling?"

"I engineered the data to tell a realistic NBFC stress narrative: GNPA rises from ~3.2% in early 2022 to ~6.2% by mid-2023 (mimicking post-COVID stress and rate hike impact), then recovers to ~4.4% by end-2024 as collections improved and write-offs resolved the legacy book. PAR-30/60/90 buckets follow the same arc."

"What's the business impact of the Top 5 Geo NPA query?"

"Geographic NPA concentration is critical for NBFC risk management. If Metro geographies disproportionately drive NPA despite having higher AUM, that signals underwriting or collection issues specific to that segment. The RANK() window function makes it easy to surface this without subqueries."

"What would you add with more time?"

"I'd add vintage analysis (cohort-based NPA formation by disbursement quarter), roll-rate analysis (DPD bucket migration), and a stressed NIM scenario tool. On the tech side, I'd migrate to dbt + Snowflake for the SQL layer and use Plotly Dash or Streamlit for the dashboard."

---

📚 NBFC Glossary

Term	Full Form	Definition
AUM	Assets Under Management	Total outstanding loan book
NPA	Non-Performing Asset	Loan with >90 DPD; RBI classification
GNPA	Gross NPA	NPA before provisions
NNPA	Net NPA	NPA after deducting provisions
PCR	Provision Coverage Ratio	Provisions / Gross NPA
CAR	Capital Adequacy Ratio	Capital / Risk-weighted assets (RBI min: 15%)
NIM	Net Interest Margin	NII / AUM — profitability metric
PAR	Portfolio at Risk	Outstanding with DPD ≥ threshold
DPD	Days Past Due	Days since EMI missed
CTI	Cost-to-Income Ratio	OPEX / NII
NII	Net Interest Income	Interest earned – interest paid
MSME	Micro Small Medium Enterprise	RBI-defined borrower segment

---

👤 Author

Kishore U.

• GitHub: github.com/ukishore33
• LinkedIn: linkedin.com/in/kishore-techie
• Mobile: 6303308133

---

FinSight Capital is a fictional company. All data is synthetically generated for portfolio demonstration purposes.