What goes in a bank reconciliation dashboard?

A bank reconciliation dashboard compares statement activity with ledger cash at account and date grain. Its job is to expose unmatched, duplicated, delayed, or incorrectly coded records, not to replace the controlled reconciliation process.

Which cards belong on this dashboard?

• Statement balance vs. ledger balance
• Matched and unmatched amount
• Unmatched transactions by age
• Deposits in transit and outstanding payments
• Duplicate match candidates
• Bank fees and interest not posted
• Accounts reconciled through date
• Data-feed freshness and missing days

What data does this dashboard need?

• Bank statement lines with stable IDs and running balance
• Ledger cash journal lines and clearing references
• Match table with status, rule, confidence, and reviewer
• Account mapping between bank and GL
• Reconciliation period, sign-off state, and owner

How do you build it?

1. Sync the source systems into a database and retain raw IDs, timestamps, status, and currency.
2. Build reconciled models at the business grain this dashboard requires.
3. Create one saved question per card and certify the shared models and definitions.
4. Add dashboard filters for Entity, bank account, currency, match status, age, statement date.
5. Show refresh time and the latest complete or reconciled period.

Example card SQL

SELECT
  CASE
    WHEN CURRENT_DATE - statement_date <= 2 THEN '0-2 days'
    WHEN CURRENT_DATE - statement_date <= 7 THEN '3-7 days'
    WHEN CURRENT_DATE - statement_date <= 30 THEN '8-30 days'
    ELSE '30+ days'
  END AS age_bucket,
  COUNT(*) AS unmatched_transactions,
  SUM(ABS(reporting_currency_amount)) AS unmatched_amount
FROM modeled_bank_reconciliation
WHERE match_status = 'UNMATCHED'
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - statement_date);

Related

Dashboards

Integrations

Metrics

Analytics

FAQ