Dynamic Data & Visualisation
Description
Task Title: Build a Comprehensive Financial Risk Analysis Dashboard
Scenario Description
Your organization’s risk management team requires a Power BI dashboard to analyze
financial risks across multiple dimensions. The dashboard should monitor credit
risk, market risk, and operational risk while providing insights into high-risk
areas. You will integrate financial data, apply advanced DAX for calculations, and
create interactive visuals to assist stakeholders in making risk-informed
decisions.
Objective
Create a fully functional Financial Risk Analysis Dashboard with the following
capabilities:
Credit Risk:
Track overdue loans, default rates, and credit exposure by client and sector.
Market Risk:
Analyze portfolio value fluctuations, Value at Risk (VaR), and currency risk.
Operational Risk:
Visualize operational losses, incidents by category, and trends over time.
Risk Scoring:
Implement a risk scoring system to rank clients, portfolios, or operations by risk
level.
Requirements
1. Data Sources
Loan Portfolio Dataset:
Client ID, Loan Amount, Interest Rate, Payment Status, Credit Score, and Industry
Sector.
Market Data:
Stock Prices, Portfolio Allocations, Currency Exchange Rates, and VaR data.
Operational Loss Dataset:
Incident Category, Date, Loss Amount, and Region.
Economic Indicators:
Inflation Rates, GDP Growth, and Interest Rates (can be downloaded from public
sources like the World Bank or IMF).
2. Data Integration and Modeling
Consolidate data into a star schema:
Fact Tables: Loan Performance, Market Performance, Operational Losses.
Dimension Tables: Clients, Time, Sector, and Region.
DAX Calculations:
Credit Default Rate = (Defaults / Total Loans) * 100
Portfolio Value Change = (Ending Value - Starting Value) / Starting Value
Incident Severity = IF(LossAmount > 50000, "High", IF(LossAmount > 10000, "Medium",
"Low"))
3. Visualization Requirements
Credit Risk Section:
Heatmap showing loan performance by sector and region.
Bar chart for overdue loans by client category.
Risk scoring cards (e.g., High, Medium, Low Risk).
Market Risk Section:
Line chart for portfolio value fluctuations.
Waterfall chart for Value at Risk (VaR) changes.
Map visual for currency risk by region.
Operational Risk Section:
Donut chart for incidents by category.
Trendline for operational losses over time.
Filters and Slicers:
Date Range
Region
Industry Sector
4. Advanced Features
Scenario Analysis:
Create a "What-If Parameter" to simulate changes in interest rates and observe the
impact on loan defaults.
Risk Alerts:
Use conditional formatting to flag clients with credit scores below 600 or loans
overdue by more than 90 days.
Anomaly Detection:
Implement DAX measures to identify outliers in operational losses.
Forecasting:
Use Power BI’s Forecast feature to predict market trends for the next 6 months.
Challenge Points
Data Simulation:
If real data is unavailable, create a synthetic dataset with realistic attributes
for testing.
Custom Visuals:
Use custom visuals such as "Gantt Chart" for incident timelines or "Radar Chart"
for risk scoring.
Performance Optimization:
Optimize data models to ensure dashboard loads within 3 seconds.
Submission Guidelines
Submit the Power BI .pbix file with a detailed explanation of the data model and
DAX measures.
Provide a link to the published dashboard in Power BI Service with shared access.
Attach documentation explaining assumptions, data sources, and risk scoring
methodologies.
Evaluation Criteria
Accuracy: Correctness of risk calculations and visualizations.
Complexity: Use of advanced DAX, forecasting, and scenario analysis.
Usability: Dashboard interactivity and clarity.
Performance: Dashboard responsiveness and refresh time.
