An Excel-based Operations MIS dashboard that turns raw, messy ticket-level data into a management-ready report — built to practice the exact workflow an MIS/Ops Analyst uses day to day: data cleaning, lookups, SLA tracking, and dashboarding.
Simulates ~260 daily operations tickets (orders, returns, refunds, complaints, queries) across 4 regions and 15 agents, then:
- Cleans inconsistent source data (mixed casing, stray whitespace, duplicate rows)
- Enriches tickets with Agent and Region details via lookups against master sheets
- Calculates Turnaround Time (TAT) per ticket from raw timestamps
- Flags SLA breaches against priority-based thresholds (High: 24h, Medium: 48h, Low: 72h)
- Summarizes results into KPIs, PivotTables, and charts on a single dashboard
| Area | Techniques used |
|---|---|
| Data cleaning | TRIM, UPPER, PROPER, Remove Duplicates |
| Lookups | VLOOKUP, INDEX/MATCH, IFERROR |
| Date/time logic | TAT calculation from datetime differences |
| Business rules | Nested IF for SLA breach classification |
| Data integrity | Data Validation (dropdown lists) |
| Visual QA | Conditional Formatting |
| Reporting | COUNTIF, AVERAGEIF, PivotTables, PivotCharts (bar, pie, line) |
├── Ops_SLA_Dashboard.xlsx # Final workbook — cleaned data + dashboard
├── raw-data/
│ └── raw_tickets_source.xlsx # Original messy dataset (before cleaning)
└── README.md
The source data is intentionally messy — inconsistent text casing, extra whitespace, a few unassigned tickets, and duplicate rows — to mirror what a real system export looks like before any cleanup.
- Open
Ops_SLA_Dashboard.xlsxin Excel. - Start on the Raw_Data sheet to see the cleaned, formula-driven ticket log.
- Check Agent_Master / Region_Master for the lookup reference tables.
- View the Dashboard sheet for KPIs, PivotTables, and charts.
Built as a self-directed learning project to practice real-world MIS/Operations reporting workflows in Excel.
Ayush Dobhal