Using Excel, design visuals to communicate portfolio EL, RWA, and concentration risks. Specify which PivotTables/charts/heatmaps you would create, the fields and measures used, and why these choices aid decision‑making.

Design Excel visuals for risk results evaluates metric design, causal reasoning, experiment setup, diagnostics, SQL/statistical checks, and recommendations in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.

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.

What difficulty level is this interview question?

This is a medium difficulty Analytics & Experimentation question, commonly asked during Technical Screen rounds at Citibank.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Citibank during technical interviews.

Design Excel visuals for risk results | Citibank Interview Question

Design Excel visuals for risk results

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.

Constraints & Assumptions

Preserve the scope, facts, inputs, and requested outputs from the prompt above.
If the prompt leaves a detail unspecified, state a reasonable assumption before relying on it.
Keep the answer interview-ready: concise enough to present, but concrete enough to implement or evaluate.

Clarifying Questions to Ask

Clarify the business objective, unit of analysis, time window, exposure definition, and primary metric.
State assumptions about instrumentation, randomization, sample size, and data quality.
Separate descriptive analysis from causal claims.

What a Strong Answer Covers

A metric framework with primary, guardrail, and diagnostic metrics.
A credible analysis or experiment design with clear assumptions and bias checks.
SQL/statistical logic for segmentation, variance, confidence, and data validation where relevant.
An actionable recommendation that explains trade-offs and next steps.

Follow-up Questions

What sanity checks would you run before trusting the result?
How would you handle novelty effects, seasonality, or selection bias?
What decision would you make if metrics disagree?

Design Excel visuals for risk results

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

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.

Constraints & Assumptions

Preserve the scope, facts, inputs, and requested outputs from the prompt above.
If the prompt leaves a detail unspecified, state a reasonable assumption before relying on it.
Keep the answer interview-ready: concise enough to present, but concrete enough to implement or evaluate.

Clarifying Questions to Ask

Clarify the business objective, unit of analysis, time window, exposure definition, and primary metric.
State assumptions about instrumentation, randomization, sample size, and data quality.
Separate descriptive analysis from causal claims.

What a Strong Answer Covers

A metric framework with primary, guardrail, and diagnostic metrics.
A credible analysis or experiment design with clear assumptions and bias checks.
SQL/statistical logic for segmentation, variance, confidence, and data validation where relevant.
An actionable recommendation that explains trade-offs and next steps.

Follow-up Questions

What sanity checks would you run before trusting the result?
How would you handle novelty effects, seasonality, or selection bias?
What decision would you make if metrics disagree?

Design Excel visuals for risk results

Quick Overview

Design Excel visuals for risk results

Design Excel visuals for risk results

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

Context

Task

Constraints & Assumptions

Clarifying Questions to Ask

What a Strong Answer Covers

Follow-up Questions

Write your answer

Design Excel visuals for risk results

Quick Overview

Design Excel visuals for risk results

Design Excel visuals for risk results

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

Context

Task

Constraints & Assumptions

Clarifying Questions to Ask

What a Strong Answer Covers

Follow-up Questions

Write your answer