Reconciliation Break Matching
Match the internal book to the broker statement, classify every break by type and catch the one-sided items in both directions.
The scenario
The morning reconciliation between your internal book and the broker statement hasn't tied out. Most lines match, but a few don't — a price that's off, a quantity that doesn't agree, a trade the broker has never heard of, and one the broker shows that you don't. The desk needs each break classified, the one-sided items caught in both directions, and a clean summary it can hand over.
Where this shows up
Matching two records, classifying breaks and catching one-sided items is the core reconciliation take-home for operations and product-control roles at firms of this type.
Firms such as State Street, BNY, J.P. Morgan.
DeskPrep is not affiliated with, endorsed by, or sponsored by any named firm. Firm names are used for illustrative, educational purposes only and do not imply that these materials are official assessments of, or are connected with, those firms.
Task brief
# Reconciliation Break Matching **Role relevance:** Reconciliations / product-control operations take-home. **Estimated time:** 50 minutes · **Difficulty:** Intermediate · **Format:** Excel (.xlsx) ## What you are given - `reconciliation_break_matching_starter.xlsx` — a **Brief** tab, a **Formula Help** tab, an **Internal** book and a **Broker** statement. Amber cells are yours to complete. ## What you must deliver 1. Pull the broker's quantity and price onto each internal row with a **lookup**, handling rows the broker doesn't have 2. Classify each row: **Matched / Qty break / Price break** (within tolerance) **/ Missing at broker** 3. Count the **broker-only** items that have no internal trade 4. A *breaks-by-type* chart and a one-line note on what you'd chase first
Your tasks
- 01Pull the broker's quantity and price onto each internal row with a lookup, handling rows the broker doesn't have.
- 02Classify each row: Matched, Qty break, Price break (within an explicit tolerance), or Missing at broker.
- 03Catch the other direction too — count the broker-only items that have no internal trade.
- 04Summarise breaks by type, chart them, and note which break you'd chase first and why.
How you're assessed
The full points-based mark scheme is included with the pack.
What you'll learn
- Why a reconciliation is two-sided — a clean recon proves both books agree, not just that your rows have a partner.
- How a matching key plus a tolerance turns raw differences into classified breaks.
- Why the missing and broker-only items are the riskiest breaks on the sheet.