Wealth management dashboard: clients, flows, and fees

A wealth management dashboard shows which client relationships stayed, where external money moved, and what fee revenue the firm earned. Build those views from the same monthly exports, then compare advisers within client segments. The purpose is managing the advisory business: service capacity, relationship follow-up, and revenue review.

Define the reporting unit before opening the exports

Use households as the relationship unit in this example. A household may have several accounts, so an account closure alone does not count as a lost relationship. Assign each household a stable identifier, a responsible adviser, and a segment at the beginning of the reporting month.

Freeze those opening assignments for the retention comparison. Show adviser transfers separately; otherwise, an internal reassignment looks like a loss for one adviser and a win for another. Use the existing wealth-manager client KPI definitions for the wider scorecard and wealth management client segmentation for segment design.

Keep operational asset labels precise. The SEC's Form ADV instructions define regulatory assets under management through specific inclusion and service criteria. A dashboard's service or billing population should not be labeled regulatory assets under management merely because it contains account balances.

Prepare the monthly rows

Start with an opening and closing household register. Join account cash movements and earned advisory fees to the household identifier, then aggregate each file before combining them. Joining every transaction directly to every fee line would multiply both amounts.

For this illustrative US firm's September 2026 review, the following are the complete input rows. Opening and closing flags are recorded observations: one means an active relationship at that cutoff. Inflows and outflows are positive external cash amounts; internal transfers and market movements are excluded. Fees are earned September revenue after credits, including revenue earned before a departing relationship closed.

Confirm that every household identifier appears once. Unmapped transactions belong in an exception total until resolved; assigning them to a convenient segment hides the gap.

Calculate retention, flows, and fees

Put the table in columns A:H, with headers in row 1. Add net flows in I2 and retained-opening-household flags in J2, then fill down through row 8:

=F2-G2
=D2*E2

Household G contributes flows and fees but is excluded from the opening retention denominator. For a segment summary, put a segment name in L2. These formulas calculate opening households, retained opening households, net flows, and fees respectively:

=SUMIFS($D$2:$D$8,$C$2:$C$8,L2)
=SUMIFS($J$2:$J$8,$C$2:$C$8,L2)
=SUMIFS($I$2:$I$8,$C$2:$C$8,L2)
=SUMIFS($H$2:$H$8,$C$2:$C$8,L2)

Microsoft documents SUMIFS as adding values whose associated rows meet the specified criteria. Change the criteria range from C to B for an adviser view. Covirage is not affiliated with Microsoft.

Relationship retention = Retained opening households / Opening households

External net flows = External inflows - External outflows

Calculate total retention from the counts, =SUM(J2:J8)/SUM(D2:D8). Percentages are rounded to one decimal; dollar amounts are exact. Do not include a new household in the numerator just because it is active at month-end.

Prove the totals and assign the review

The relationship bridge is six opening households minus one lost household plus one new household, giving six closing households. That matches the closing flags. Equal opening and closing counts therefore conceal a lost relationship.

The flow check is $485,000 of inflows minus $375,000 of outflows, giving $110,000 net inflows. Tie both gross amounts to the classified cash export. Fees of $4,750 plus $2,200 plus $1,800 total $8,750 and must tie to the earned-fee ledger for the month.

The adviser view gives Maya $470,000 net inflows and Noah $360,000 net outflows. Maya retained all her opening households; Noah retained two of three. Those facts justify investigating the departing relationship, but they do not prove service failure. The withdrawal may have an ordinary, documented explanation. Use adviser contact recency to guide follow-up and fee margin by client when the question is service economics rather than revenue.

Where it goes wrong

• Counting account closures as household losses. Confirm whether any relationship remains active.
• Treating internal transfers as external flows. Match transfer pairs before aggregating across advisers or segments.
• Using billed cash as earned fees. Advance billing, credits, and collection timing can separate invoices, receipts, and revenue.
• Moving segments midperiod. Keep opening assignments for retention and disclose subsequent reclassifications.
• Reading small groups as a performance ranking. Review the underlying relationships and context before changing responsibilities.

Build the view from your own files in Covirage

Bring a prepared household-level export with adviser assignments, opening and closing flags, external cash movements, and earned fees. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for wealth managers for the supporting industry context.