Introduction
A multinational retail company managed cash across more than 40 bank accounts in over 10 countries. The treasury team needed a single, reliable daily view of cash and liquidity, without hours of manual consolidation.
Business challenge
- Over 40 bank accounts across more than 10 countries
- No centralized daily view of cash positions
- Limited visibility into liquidity and foreign exchange exposure
- Bank statements arriving in different formats, consolidated manually
- No real-time cash flow view by currency, and heavy reliance on Excel
Requirements
- Consolidate daily balances from every bank account
- Handle different statement formats and local currencies
- Standardize reporting in GBP and EUR
- Give leadership real-time cash and liquidity visibility
The solution
Data preparation in Power Query
- One query per bank statement format, appended into a single model
- Only the latest transaction per account per day kept as the closing balance
- A rolling date table that carries forward the last known balance on days without transactions
Calculations in DAX
- Currency conversion through measures and exchange rate tables
- Daily and weekly liquidity metrics, FX exposure and account-level summaries
- Filters by currency, country, business unit and bank
Dashboard and KPIs
- Current day vs last day balance, and daily and monthly trend comparisons
- Balances by country, business unit, bank and local currency
- Account-level tracking for the Head of Treasury and VP of Finance
Outcome
- Real-time cash visibility across all global bank accounts
- Hours of manual reconciliation work saved
- Faster decisions with less manual input
- Less dependency on individual team members
- More confidence in liquidity and FX risk management
Tools used
- Power BI
- Power Query
- DAX


