Design Excel visuals for risk results

Q: Design Excel visuals for risk results

This is a Analytics & Experimentation interview question from Citibank for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Analytics & Experimentation interview questions?

Analytics & Experimentation questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master analytics & experimentation interviews.

Question

Excel Dashboard Design: Communicating EL, RWA, and Concentration Risk

Context

You are preparing an Excel dashboard for a credit portfolio review that must clearly communicate:

Expected Loss (EL)
Risk-Weighted Assets (RWA)
Concentration risks across obligors, sectors, regions, and ratings

Assume you have a loan-level dataset with at least these fields: Date, Obligor_ID, Obligor_Name, Sector, Region, Rating, PD, LGD, EAD, EL (or PD/LGD/EAD from which EL can be computed), RWA, Collateral_Type, and Portfolio/Book.

Task

Design a set of Excel visuals (PivotTables, charts, heatmaps) to communicate the portfolio's EL, RWA, and concentration risks. For each visual, specify:

The PivotTable layout (Rows, Columns, Values, Filters) and any slicers.
Measures/calculations used (with formulas if needed).
The chart/formatting applied.
Why this view aids decision-making.

Provide a concise, actionable plan suitable for a technical screen.

Design Excel visuals for risk results

Excel Dashboard Design: Communicating EL, RWA, and Concentration Risk

Context

Task

Solution (Locked)

Comments (0)