Reconcile Broker Commission Statement

---
slug: reconcile-broker-commission-statement
name: Reconcile Broker Commission Statement
version: 1.2.0
status: ready_to_publish
owner: revenue-ops
---

# Reconcile Broker Commission Statement

## intent

use this skill when a broker, lender, or internal stakeholder provides a commission statement and you need to verify it against expected loan-level payouts. the skill extracts statement rows, normalizes loan identifiers, compares amounts to expected commissions, flags discrepancies, and produces a detailed summary with follow-up actions. deploy it for monthly statement reviews, pre-payment verification, or investigating short-paid, duplicate, or missing items.

## inputs

**broker commission statement** (required)
- format: PDF, spreadsheet, CSV, or pasted table
- must contain: statement loan ID, borrower name, statement date, and paid amount
- if OCR quality is poor, note confidence level limitation

**expected commission export** (highly recommended)
- source: LOS, CRM, or payments system
- core fields: loan_id, borrower_name, property_address, fund_or_close_date, commission_expected, split_percent, expected_basis_notes
- if unavailable, reconciliation becomes exception-based only

**metadata context** (required for matching)
- broker or payee name
- statement period covered
- gross vs net commission basis (critical; if unclear, mark affected rows `needs_review`)
- whether amounts include processing fees, lender-paid adjustments, tax withholdings, or clawbacks
- matching key priority: (1) loan number, (2) borrower + close/fund date, (3) property address + amount

**compensation plan details** (if nonstandard)
- split commission percentages or allocations
- special adjustments, clawback terms, or fee deductions
- basis for comparison: gross payout vs net-to-broker

**known exceptions or prior unresolved items**
- list any items flagged in prior statements
- document any approved overrides or timing delays

**external connections** (optional but recommended)
- LOS/CRM API: requires read access to loan and commission records; typically env var `LOS_API_KEY` with scope `loans:read, commissions:read`
- payments system API: requires `PAYMENTS_API_KEY` with scope `transactions:read`
- if using APIs, confirm auth tokens are fresh and not expired; implement retry logic for rate limits (typically 429 responses)

## procedure

### step 1: gather and validate context
confirm the following before proceeding:
- statement period covered (e.g., 2024-01-01 to 2024-01-31)
- broker or payee name and ID
- gross vs net commission basis; if unclear, flag this explicitly and mark related rows `needs_review`
- whether amounts include processing fees, admin fees, lender-paid adjustments, or tax withholdings
- expected matching key priority: loan number first, then borrower + close/fund date, then property address + amount
- if the source does not clearly state gross vs net, do not force a match; mark `needs_review` instead

**output:** context validation checklist documented in notes section of final summary

### step 2: parse and normalize both datasets
for broker statement data:
- trim all whitespace from text fields
- normalize loan numbers by removing spaces and dashes; preserve leading zeros where possible
- standardize all dates to ISO 8601 format (YYYY-MM-DD)
- convert currency strings to numeric values (e.g., "$1,234.56" to 1234.56)
- keep original raw values in a notes field if transformation was required
- normalize borrower names conservatively; do not merge clearly different borrowers (e.g., "John Smith" and "J. Smith" require manual review)

for expected commission data:
- apply the same normalization steps
- confirm core fields present: loan_id, borrower_name, property_address, fund_or_close_date, commission_expected, split_percent, expected_basis_notes

**output:** two normalized tables (statement and expected) with matching column structure

### step 3: match rows across datasets
apply matching in this priority order:
1. exact normalized loan ID match
2. same borrower name (normalized) and close/fund date within 7 days
3. same property address and commission amount within small tolerance (0.01)

matching rules:
- if multiple expected rows could match one statement row, mark the statement row `needs_review` and document the ambiguity
- if one expected loan appears split across multiple statement rows, aggregate only if statement notes explicitly support split payout; otherwise mark both `needs_review`
- tolerance for rounding-only differences: 0.01 (e.g., 1000.00 vs 1000.01 is a match)
- do not auto-match rows with reversed sign (negative vs positive) unless the statement explicitly indicates a clawback or reversal in notes
- if a statement row has no match, leave the expected_loan_id blank and mark status as `unexpected_statement_item`
- if an expected row has no match, create a row with empty statement fields and mark status as `missing_expected`

**output:** master reconciliation table with statement_loan_id, loan_id (from expected), borrower_name, statement_date, expected_date, and match_method (e.g., "exact loan ID", "borrower + date", "address + amount")

### step 4: compute reconciliation status and variance
for each row, calculate variance and assign status:

variance formula:

variance = net_paid - commission_expected

when clawbacks or fees are separately listed on the statement:
- if the plan pays net (broker receives final amount), compare net_paid to commission_expected
- if payout operations approves gross basis, sum gross commission, subtract fees/clawbacks, then compare to expected
- if basis is unclear, surface both gross and net comparisons in notes and mark `needs_review`

status definitions:
- `match`: statement net aligns to expected within 0.01 tolerance; variance is negligible
- `underpaid`: expected > statement by more than 0.01; broker received less than expected
- `overpaid`: statement > expected by more than 0.01; broker received more than expected
- `missing_expected`: expected item not present on statement; may indicate pending investor purchase or data lag
- `unexpected_statement_item`: statement item has no supported expected match; may be prior-period true-up or data error
- `needs_review`: ambiguous mapping (multiple possible matches), missing key fields (loan ID, borrower name, or date), unclear fee treatment, or unusual adjustments (e.g., manual override without note)

**output:** reconciliation table with columns: statement_loan_id, loan_id, borrower_name, statement_date, expected_date, net_paid, commission_expected, variance, status, notes

### step 5: aggregate totals and identify patterns
sum the following:
- total_statement_amount: net_paid summed across all statement rows
- total_expected_amount: commission_expected summed across all expected rows that matched or were missing
- net_variance: total_statement_amount minus total_expected_amount
- count by status: tally rows for each status category

identify patterns:
- largest variances first (sort by absolute variance descending)
- repeated patterns: e.g., all loans from one branch short by fixed basis points, all loans closed before a certain date overpaid, etc.
- likely data issues: duplicate loan IDs on statement, missing loans on statement, date mismatches (e.g., funding date vs closing date), split mismatches (e.g., expected split 50/50 but statement shows 60/40)
- footer check: if statement includes a total line, verify that sum equals row-level sum; call out any mismatch explicitly

**output:** summary table with broker, statement period, statement total, expected total, net variance, status counts, and list of patterns/issues in priority order

### step 6: document findings and recommended actions
produce a concise summary that includes:
- largest variances and likely reasons (e.g., "loan ABC short by $500; statement notes clawback for early payoff not reflected in expected data")
- any repeated pattern (e.g., "all 12 loans from west branch short by 5 bps; confirm if fee adjustment not yet processed")
- explicit data issues with evidence (e.g., "statement total row shows $45,230 but sum of rows is $45,231; rounding error or missing row?")
- explicit asks needed to resolve open items (e.g., "confirm whether clawback for loan XYZ approved; provide written justification", "provide updated expected export reflecting recent adjustments")

**output:** findings summary with one-liner per item, priority-ordered, plus explicit action items for revenue-ops or broker contact

## decision points

**if gross vs net basis is unclear:**
- do not force a match; mark the row `needs_review`
- surface both gross and net comparisons in the notes field
- request clarification from compensation plan owner before approving payout

**if statement row has no loan ID:**
- attempt match by borrower name + date within 7 days
- if successful, record match_method as "borrower + date"
- if no match, mark `unexpected_statement_item` and note that loan ID is missing from statement

**if expected row is not on statement:**
- if statement period ends within 7 days of expected fund date, mark `missing_expected` with note "likely not yet funded"
- if statement period ends more than 30 days after expected fund date, escalate to revenue-ops with "loan appears to have never funded"

**if one expected loan appears split across multiple statement rows:**
- if statement notes explicitly support split (e.g., "50% to broker A, 50% to broker B"), aggregate and mark match as "split allocation"
- if no explicit note, mark both rows `needs_review` with note "split not documented; requires manual review"

**if statement shows a negative (clawback or reversal):**
- require explicit note support (e.g., "clawback for early payoff per plan terms")
- do not auto-match to expected unless expected also contains a clawback for same loan
- mark as `needs_review` if clawback appears without explanation

**if statement total line does not foot to row-level sum:**
- call this out explicitly in findings: "statement total row shows $X but sum of line items is $Y; $Z discrepancy"
- investigate whether a line item is missing or whether the footer total is a typo
- mark all rows `needs_review` until discrepancy is resolved

**if OCR quality is poor (e.g., scanned PDF with pixelated text):**
- note in summary: "reconciliation confidence is limited due to OCR quality; recommend manual spot-check of top 5 variances"
- mark rows with uncertain data as `needs_review`

**if multiple expected rows could match one statement row:**
- do not pick one; mark the statement row `needs_review`
- document all possible matches in notes: "could match loan A, loan B, or loan C; requires manual review"

**if API call fails (e.g., LOS connection timeout or 429 rate limit):**
- implement exponential backoff and retry up to 3 times with 5-second intervals
- if still failing, fall back to manual expected data input; note in summary: "expected commission data loaded from manual input due to API unavailability"
- if expected data is unavailable entirely, produce exception-based summary only (reconciliation limited to statement analysis and manual research)

## output contract

deliver a reconciliation package with the following components:

**1. reconciled rows table**
- columns: statement_loan_id, loan_id, borrower_name, statement_date, expected_date, net_paid, commission_expected, variance, status, notes
- format: CSV, Excel, or markdown table
- one row per statement item, plus rows for missing expected items
- sorted by status (needs_review first, then underpaid, overpaid, unexpected_statement_item, missing_expected, match)
- within each status group, sort by absolute variance descending

**2. summary section**
- broker name
- statement period (e.g., "2024-01-01 to 2024-01-31")
- statement total (net_paid summed across all statement rows)
- expected total (commission_expected summed across matching + missing expected rows)
- net variance (statement total minus expected total)
- status counts (e.g., "5 match, 2 underpaid, 1 overpaid, 1 missing_expected, 0 unexpected_statement_item, 1 needs_review")

**3. findings section**
- list of patterns, data issues, and largest variances in priority order
- one-liner per finding with evidence and likely reason
- explicit calls for action (e.g., "confirm clawback justification", "provide updated expected export", "investigate duplicate loan ID")

**4. audit trail**
- document any manual adjustments (e.g., "loan ABC matched by address + amount instead of loan ID due to data entry error on statement")
- preserve original raw values and transformation notes for full transparency

**file location:**
- save output as `[broker_name]_commission_reconciliation_[statement_period_start]_[statement_period_end].csv` or `.xlsx`
- include timestamp of reconciliation run in footer
- store in shared finance operations folder with read access for revenue-ops team

## outcome signal

the user knows the skill worked when:

1. **all statement rows are accounted for:** every row on the broker statement appears in the reconciled table with a status assigned; no rows are silently dropped or skipped.

2. **variances are explicit:** any difference between statement and expected amount is surfaced with a status label, variance amount, and reason; the user does not have to hunt for discrepancies.

3. **ambiguities are flagged:** rows that could have multiple matches, contain missing key fields, or show unusual adjustments are marked `needs_review` with a specific reason noted; the user does not have to guess which rows need manual review.

4. **patterns are identified:** if the reconciliation uncovers repeated issues (e.g., all loans from one branch short by the same amount, all early payoffs clawed back without note), the summary calls this out so the user can address root cause instead of item-by-item.

5. **next actions are clear:** the findings section lists explicit asks for the broker or internal team (e.g., "provide written justification for $X clawback", "confirm whether fee adjustment has been processed"); the user can hand these off without rework.

6. **totals reconcile or discrepancies are explained:** the summary total for the statement matches the sum of statement rows, or the discrepancy is called out explicitly; the user can trust the math.

7. **audit trail is complete:** any manual adjustment or data transformation is documented so that if the user needs to re-reconcile in a future period or audit this work, the record is clear.

---

## suggested output format

### reconciled rows
| statement_loan_id | loan_id | borrower_name | statement_date | expected_date | net_paid | commission_expected | variance | status | notes |
|---|---|---|---|---|---:|---:|---:|---|---|
| 2024-001 | 2024-001 | Smith, John | 2024-01-15 | 2024-01-12 | 2500.00 | 2500.00 | 0.00 | match | exact loan ID |
| 2024-002 | 2024-002 | Jones, Sarah | 2024-01-20 | 2024-01-19 | 1800.00 | 2000.00 | -200.00 | underpaid | variance exceeds tolerance; investigate |

### summary
- **broker:** example mortgage corp
- **statement period:** 2024-01-01 to 2024-01-31
- **statement total:** $45,230.50
- **expected total:** $46,800.25
- **net variance:** -$1,569.75 (statement short)
- **status counts:** 18 match, 2 underpaid, 0 overpaid, 1 missing_expected, 0 unexpected_statement_item, 1 needs_review
- **key findings:**
  - loan 2024-015 short by $500; statement notes clawback for early payoff not reflected in expected data; confirm clawback approval
  - loan 2024-018 missing from statement despite expected close date 2024-01-25; likely pending investor purchase; monitor for next statement
  - loan 2024-022 marked needs_review; statement shows split 60/40 but expected reflects 50/50; clarify allocation before payout
- **next actions:**
  - revenue-ops to confirm clawback for loan 2024-015 per compensation plan
  - contact broker to confirm status of loan 2024-018
  - request updated expected export reflecting loan 2024-022 split adjustment

---

## guardrails

- never assume compensation plan terms not supplied in the source material; if a term is unclear, surface it and mark affected rows `needs_review`
- never hide ambiguous matches; mark them explicitly and document all possible matches in notes
- preserve an audit trail in notes for all manual adjustments, data transformations, and match decisions
- if OCR quality is poor (scanned PDF with pixelated or unclear text), state in summary that reconciliation confidence is limited; recommend manual spot-check of top variances
- if statement total line does not foot to row-level sums, call this out explicitly and do not proceed to approval without resolution
- if expected data is unavailable, produce exception-based summary only; clearly note that reconciliation is limited to statement analysis and manual research
- never merge clearly different borrowers (e.g., "John Smith" and "J. Smith") without manual review; conservative normalization is safer than aggressive matching

---

## edge cases

**negative rows and reversals:**
- negative rows may be reversals or clawbacks; require note support (e.g., "clawback for early payoff per plan terms")
- if a negative row lacks explanation, mark `needs_review` and escalate

**combined statements:**
- broker statements may include prior-period true-ups or adjustments; if the statement period overlaps multiple months, ask for split by month or clarify which expected period to use for comparison

**split commissions:**
- if a loan involves split commission (e.g., 50% to broker A, 50% to broker B), require explicit note support before aggregating
- if expected reflects one broker but statement shows split, mark `needs_review` and request clarification

**date mismatches:**
- broker statements sometimes show funded date while expected export uses closing date; allow 7-day tolerance for matching and document the date source in notes

**missing loans on statement:**
- a missing loan on the statement may still be pending investor purchase; mention timing risk in findings if statement period ends within 14 days of expected fund date
- if missing more than 30 days after expected fund date, escalate to revenue-ops

**footer discrepancies:**
- if statement includes a total line that does not match row