Client money events and a monthly new-assets report, drafted for review

Situation

The custodians' raw transaction records are difficult to read as client activity. Tax withholding sits on separate lines, moves between accounts appear as pairs of entries, and the same transfer can appear in more than one report.

Operations kept workbooks by hand to track new money, internal moves and withdrawals. Confirming whether a client had taken a required distribution meant searching raw exports. Each month, the operations manager also typed every line of the new-assets report into a large spreadsheet.

Solution

The work produced two tools. The first turns the custodians' records into a clear list of client events, such as IRA distributions, contributions, charitable distributions, gifts of stock, Roth conversions, rollovers and transfers. Each event is shown at its gross amount and linked to the correct household.

Advisors see these events on each household's timeline, and the year-end charitable distribution workbooks are drafted in the team's own format. The second tool drafts each month's new-assets report and flags the judgment calls for the operations manager to review.

Design decisions

Jeff Hunt built both tools on fixed rules with no AI, because these numbers must come out the same every time. Each event can be traced back to the custodian records it came from, and uncertain items are marked for review.

Outcome

Advisors and operations staff can see each client's money events in one list without searching raw exports. The operations manager can review a drafted report row by row in place of typing every line. Checked against the operations manager's own June report, the draft reproduced every line item to the penny.