🚇 DMRC Intelligence Platform

End-to-End Data Analytics & Business Intelligence Dashboard | Delhi Metro Rail Corporation

---

📌 Project Overview

The DMRC Intelligence Platform is a portfolio-grade Business Intelligence and Data Analytics project built using Microsoft Excel and Python.

The project transforms raw financial, passenger, network, and operational datasets of the Delhi Metro Rail Corporation (DMRC) into a centralized analytics platform that supports data-driven decision-making.

The dashboard enables stakeholders to analyze:

• Financial Performance
• Passenger Behaviour
• Network Analytics
• Operational Performance
• Executive-Level KPIs

The final deliverable is a fully interactive Excel dashboard designed to provide actionable insights through visual storytelling and business intelligence reporting.

This was my first complete end-to-end Data Analytics portfolio project, covering the full workflow from data preparation and feature engineering to dashboard design, business analysis, and executive reporting.

---

🎯 Business Problem

Delhi Metro Rail Corporation operates one of India's largest urban transit systems.

Managing large volumes of financial, passenger, and operational data across different departments creates reporting challenges and limits visibility into key performance indicators.

Important business questions include:

• How has revenue evolved over time?
• Is the metro network operating efficiently?
• Which stations generate the highest passenger traffic?
• Which routes experience the greatest demand?
• What operational patterns influence service performance?
• How sustainable is the organization's financial performance?

This project addresses these challenges by consolidating multiple datasets into a single analytical platform.

---

🎯 Project Objectives

• Build a centralized business intelligence dashboard
• Analyze 10 years of financial performance
• Monitor operational efficiency metrics
• Explore passenger travel behaviour
• Evaluate metro network structure and performance
• Generate executive-level insights
• Create a portfolio-ready analytics solution

---

🗂️ Datasets Used

Business Dataset

• 10 Years Financial Data (FY 2015-16 to FY 2024-25)

Passenger Dataset

• 150,000 Passenger Trip Records

Network Dataset

• 228 Metro Stations across 11 Metro Lines

Operations Dataset (GTFS)

• 36 Routes
• 5,438 Trips
• 262 Stops
• 128,434 Stop-Time Records

---

🧹 Data Preparation & Feature Engineering

Data preparation was performed before dashboard development to ensure consistency and analytical accuracy.

Data Cleaning

• Standardized dataset structures
• Removed inconsistencies and duplicates
• Handled missing values
• Validated financial records
• Structured GTFS operational datasets
• Standardized station and route naming conventions

Engineered Metrics

Financial KPIs

• Revenue Growth %
• PAT Margin %
• Revenue per KM
• Revenue per Station
• Revenue per Employee
• Revenue per Coach
• Debt Equity Ratio
• Current Ratio

Passenger KPIs

• Average Fare
• Average Distance
• Fare per KM
• Passenger Distribution
• Ticket Type Analysis

Operational KPIs

• Route Utilization
• Stop Utilization
• Average Stops per Trip
• Network Activity Analysis

---

🏗️ Dashboard Architecture

DMRC Intelligence Platform
│
├── Cover
├── Home
├── Business Analytics
├── Passenger Analytics
├── Network Analytics
├── Executive Insights
├── User Guide
├── Data Dictionary
│
├── Hidden Data Sheets
│   ├── Business Data
│   ├── Passenger Data
│   ├── Network Data
│   └── GTFS Data

Dashboard Navigation Flow

Cover
 ↓
Home
 ↓
Business
 ↓
Passenger
 ↓
Network
 ↓
Insights

---

🛠️ Tools & Technologies

• Microsoft Excel (Dashboard Development)
• Python (Data Processing)
• Pandas & NumPy (Data Analysis)
• Pivot Tables (Aggregation & Reporting)
• Excel Charts (Visualization)
• GTFS Data (Operations Analytics)
• GitHub (Documentation & Portfolio Hosting)

---

📊 Key Performance Indicators

Financial KPIs

• Total Revenue
• Total Expenses
• Operating Surplus
• Profit After Tax (PAT)
• Revenue Growth %
• PAT Margin %

Efficiency KPIs

• Revenue per KM
• Revenue per Station
• Revenue per Employee
• Revenue per Coach

Passenger KPIs

• Total Passengers
• Average Fare
• Average Distance
• Fare per KM

Network KPIs

• Network Length
• Total Stations
• Interchange Stations
• Station Density

Operations KPIs

• Total Routes
• Total Trips
• Total Stops
• Average Stops per Trip

---

🖼️ Dashboard Screenshots

🏠 Home Dashboard

Centralized KPI overview providing quick access to financial, passenger, network, and operational performance metrics.

---

💼 Business Analytics Dashboard

Comprehensive financial analysis including Revenue, Expenses, PAT, Growth Trends, and profitability metrics.

---

👥 Passenger Analytics Dashboard

Passenger travel analysis covering routes, stations, ticket types, fare distribution, and demand patterns.

---

🚉 Network Analytics Dashboard

Network performance dashboard focused on metro line distribution, station coverage, operational activity, and infrastructure insights.

---

📈 Executive Insights Dashboard

Strategic recommendations and executive-level insights derived from financial, passenger, and operational analytics.

---

💡 Key Insights

Financial Performance

• Revenue increased from ₹4.35L Lakh in FY 2015-16 to ₹8.15L Lakh in FY 2024-25.
• Revenue declined significantly during FY 2020-21 due to COVID-19 disruptions but recovered in subsequent years.
• Profit After Tax (PAT) remained negative due to high depreciation and financing costs despite positive operating performance.

Passenger Analytics

• Rajiv Chowk emerged as the highest-traffic station across multiple passenger metrics.
• Smart Card users represented the largest share of passenger transactions.

Network Analytics

• Blue Line is the largest metro corridor by station coverage.
• The network spans 394.25 KM with 228 stations across 11 metro lines.

Operational Analytics

• Kashmere Gate recorded the highest stop utilization across GTFS operational data.
• Route utilization analysis identified key service corridors with the highest operational activity.

---

📁 Repository Structure

DMRC-Intelligence-Platform
│
├── Dashboard/
│   └── DMRC_Intelligence_Dashboard.xlsx
│
├── Data/
│   ├── Business_Feature_Engineered.xlsx
│   ├── Passenger_Feature_Engineered.xlsx
│   ├── Network_Analytics_Master.xlsx
│   └── Operations_Analytics_Master.xlsx
│
├── Documentation/
│   ├── User_Guide.pdf
│   ├── Data_Dictionary.pdf
│   └── Project_Report.pdf
│
├── Screenshots/
│   ├── 01_Home.png
│   ├── 02_Business.png
│   ├── 03_Passenger.png
│   ├── 04_Network.png
│   └── 05_Insights.png
│
└── README.md

---

🚀 Future Enhancements

• Power BI Migration
• Automated Data Refresh Pipeline
• Revenue Forecasting Models
• Passenger Demand Prediction
• Geographic Heatmaps
• Real-Time Performance Monitoring
• Route-Level Profitability Analysis

---

📚 Learning Outcomes

This project strengthened practical skills in:

• Data Cleaning
• Data Analysis
• Dashboard Development
• Business Intelligence Reporting
• KPI Design
• Data Storytelling
• Excel Dashboard Design
• Transportation Analytics

This was my first complete end-to-end Data Analytics dashboard project, covering the full workflow from data preparation and feature engineering to dashboard design, business analysis, and executive reporting.

---

📋 Project Information

• Project Type: Individual Portfolio Project
• Duration: 1 Week
• Domain: Transportation Analytics
• Tools Used: Excel, Python
• Level: Beginner to Intermediate
• Status: Completed

---

👤 Author

Aakash

Data Analytics Enthusiast | Excel Dashboard Developer | Business Intelligence Learner

📧 Email: aakashimportant15@gmail.com 🐙 GitHub: https://github.com/aakashgit09 📍Project Repository: https://github.com/aakashimportant15-max/DMRC-Intelligence-Platform

---

⭐ If you found this project useful, consider starring the repository.