Citibank·Data Scientist·Technical Phone Screen·Senior

Senior

Jul 2026

Summary

Citibank data scientist interview with a pretty involved Excel-based case question around credit risk visualization. One question, technical, and it required more domain fluency than I expected going in.

Questions Asked (1)

Using Excel, walk through how you would build visuals to communicate portfolio expected loss, risk-weighted assets, and concentration risk. What specific PivotTables, charts, or heatmaps would you create, what fields and measures would you use, and why do those choices actually help decision-makers?

Product Analytics & MetricsData ModelingTechnical Trade-offs

Author's notes

I knew the EL and RWA concepts well enough but the 'why does this help decision-making' part is where I got a bit handwavy.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the data model and key metrics (expected loss, RWA, concentration), then describe how you would structure the data in Excel using Power Pivot or PivotTables, and finally outline the specific visuals (charts, heatmaps) that effectively communicate each risk aspect to decision-makers. Emphasize that the choice of visuals is driven by the need to highlight trends, outliers, and concentrations for actionable insights.

Pro tip: Use dynamic named ranges and slicers to make the dashboard interactive, and always include a 'data quality' check step to ensure accuracy—this shows you understand that reliable insights depend on clean data.

1. Understand the Data and Metrics

Identify the necessary data fields (e.g., exposure, PD, LGD, EAD, risk weights, sector, rating) and define the calculations for expected loss (PD*LGD*EAD) and RWA (EAD*risk weight). Ensure you know the granularity and relationships.

2. Structure Data for Analysis

Load data into Excel's Data Model or create a flat table, then build PivotTables to aggregate metrics by dimensions like sector, rating, and region. Use measures in Power Pivot for dynamic calculations.

3. Design Visuals for Each Risk Aspect

For expected loss: use a bar chart by sector or a treemap. For RWA: use a stacked column chart by rating or a waterfall. For concentration: create a heatmap (using conditional formatting) of exposure by sector and rating, or a scatter plot of exposure vs. PD.

4. Enhance Interactivity and Clarity

Add slicers for time periods, sectors, or ratings to allow decision-makers to drill down. Use clear titles, data labels, and color scales to make insights immediately apparent.

5. Validate and Iterate

Cross-check totals with source data, ensure formulas are correct, and gather feedback from stakeholders to refine visuals for maximum decision impact.

Key Points to Mention

Use of Power Pivot and DAX measures for dynamic calculations of expected loss and RWA.
PivotTables to aggregate data by key dimensions such as sector, rating, and region.
Heatmap via conditional formatting to highlight concentration risk (e.g., high exposure in a single sector).
Charts: bar chart for expected loss by sector, stacked column for RWA composition, scatter plot for risk-return analysis.
Slicers and timelines for interactivity, enabling decision-makers to filter and drill down.
Importance of data quality checks and validation to ensure reliable outputs.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.