Executive MIS Capstone
End-to-end analyst challenge: turn raw monthly business data into a reconciled management view, choose the right analytical tools, and defend the final decision.
Advanced · 90–120 min
Objectives
- Clean and validate raw MIS data
- Build achievement and risk KPIs
- Choose PivotTable and chart designs
- Explain management actions
- Complete professional QA
Project dataset
| Row | A: Employee | B: Branch | C: Month | D: Target | E: Collection | F: DPD | G: Status |
|---|---|---|---|---|---|---|---|
| 2 | Amit | Mumbai | Apr | 50000 | 62000 | 0 | Active |
| 3 | Priya | Pune | Apr | 55000 | 49000 | 31 | Active |
| 4 | Ravi | Nashik | Apr | 45000 | 51000 | 12 | Active |
| 5 | Neha | Mumbai | May | 60000 | 58000 | 45 | PNPA |
| 6 | Karan | Pune | May | 52000 | 68000 | 0 | Active |
| 7 | Meena | Nashik | May | 48000 | 42000 | 67 | NPA |
| 8 | Amit | Mumbai | Jun | 65000 | 72000 | 5 | Active |
| 9 | Priya | Pune | Jun | 60000 | 57000 | 18 | Active |
| 10 | Ravi | Nashik | Jun | 50000 | 46000 | 91 | NPA |
| 11 | Neha | Mumbai | Jun | 62000 | 70000 | 0 | Active |
Guided checkpoints
Frame the management question
Write the main question as: Where is performance below plan and where is delinquency creating risk?
Translate a vague request into measurable questions.
Validate the source
Inspect headers, numeric types, dates/months and status labels before calculating anything.
Prevent source-quality errors.
Calculate achievement
Build Collection ÷ Target and identify the weakest branch in the sample.
Create and interpret a KPI.
Build the risk view
Bucket DPD into 0–30, 31–60, 61–90 and 91+ and identify high-risk records.
Convert raw delinquency into action categories.
Design the PivotTable
Summarize Collection and Target by Branch and add Status/DPD bucket as analytical context.
Build a flexible management summary.
Choose the chart
Use a comparison chart for branch performance and a separate trend chart when months are involved.
Match visualization to the question.
Write the management decision
State which branch deserves the closest review and why, using both performance and risk evidence.
Turn analysis into a defensible decision.
Prepare for QA
Reconcile the primary monetary total, test an exception and document one assumption before publishing.
Apply professional reporting discipline.
Download sample project .xlsx · All portfolio projects
Interactive practice runs in your browser.