Data Analyst Interview Case

Complete a timed business-analysis case that combines cleaning, lookups, criteria formulas, PivotTables, charts, QA and executive commentary.

Advanced · 90–120 min

Objectives

Row	A: Txn ID	B: Employee	C: Branch	D: Target	E: Collection	F: DPD	G: Status
2	T001	Amit	Mumbai	50000	62000	0	Active
3	T002	PRIYA	Pune	55000	49000	31	Active
4	T003	Ravi	Nashik	45000	51000	91	NPA
5	T004	Neha	Mumbai	60000	58000	45	PNPA
6	T005	Karan	Pune	52000	68000	0	Active
7	T006	Meena	Nashik	48000	42000	67	NPA

Clean the employee names
Standardize spaces and case without changing IDs or numeric values.
Demonstrate safe cleaning.
Create achievement %
Calculate Collection ÷ Target and format it as a percentage.
Build the core performance KPI.
Create risk buckets
Bucket DPD into 0–30, 31–60, 61–90 and 91+.
Translate raw delinquency into actionable risk.
Build the management summary
Use a PivotTable for Branch × Status with Collection in Values and add achievement context.
Summarize the dataset quickly and accurately.
Find the first management concern
Use both performance and DPD evidence to identify the branch that deserves review.
Make a business judgement.
Deliver the interview answer
Write a three-sentence executive summary: finding, evidence, recommended action.
Communicate analysis concisely.

Interactive practice runs in your browser.