Reconcile broker commission statements against expected loan-level payouts, flag variances, and provide a detailed summary with recommended follow-up actions.
--- 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 ## Summary Use this skill when a broker, lender, or internal stakeholder provides a commission statement and needs it checked against expected loan-level payouts. The skill extracts statement rows, normalizes loan identifiers, compares amounts to expected commissions, flags discrepancies, and produces a concise reconciliation summary with follow-up actions. ## When to use - Monthly or ad hoc broker commission statement review - Pre-payment verification before approving payout - Investigating short-paid, duplicate, or missing commission items - Validating clawbacks, split commissions, or post-close adjustments ## Inputs Provide as many of the following as available: - Broker commission statement (PDF, spreadsheet, CSV, or pasted table) - Expected commission export from LOS/CRM/payments system - Broker name and statement period - Compensation plan details if nonstandard - Known exceptions or prior unresolved items ## Required tools - File reading/parsing - Spreadsheet/table manipulation - Basic arithmetic and row-level comparison - Ability to produce a structured summary ## Output Deliver: 1. A reconciled table with one row per statement item 2. Status for each row: `match`, `underpaid`, `overpaid`, `missing_expected`, `unexpected_statement_item`, `needs_review` 3. Variance amount and likely reason 4. Totals summary: - total statement amount - total expected amount - net variance - count by status 5. Recommended next actions ## Procedure ### 1) Gather context Confirm: - statement period covered - broker/payee name - gross vs net commission basis - whether amounts include processing/admin fees, lender-paid adjustments, or tax withholdings - expected matching key priority: 1. loan number 2. borrower + close/fund date 3. property address + amount If the source does not clearly state gross vs net, mark affected rows `needs_review` instead of forcing a match. ### 2) Parse and normalize data For both statement data and expected data: - Trim whitespace from all text fields - Normalize loan numbers by removing spaces/dashes while preserving leading zeros where possible - Standardize dates to ISO format - Convert currency strings to numeric values - Keep original raw values in notes if transformation was required - Normalize borrower names conservatively; do not merge clearly different borrowers Expected core fields: - loan_id - borrower_name - property_address - fund_or_close_date - commission_expected - split_percent - expected_basis_notes Statement core fields: - statement_loan_id - borrower_name - statement_date - commission_paid - fee_adjustment - clawback_amount - net_paid - statement_notes ### 3) Match rows Match in this order: 1. Exact normalized loan ID 2. Same borrower and close/fund date within 7 days 3. Same property address and commission amount within small tolerance Rules: - If multiple expected rows could match one statement row, mark `needs_review` - If one expected loan appears split across multiple statement rows, aggregate only if notes clearly support split payout - Tolerance for rounding-only differences: 0.01 - Do not auto-match rows with reversed sign unless statement explicitly indicates clawback/reversal ### 4) Compute reconciliation status Use: - `match`: statement net aligns to expected within tolerance - `underpaid`: expected > statement by more than tolerance - `overpaid`: statement > expected by more than tolerance - `missing_expected`: expected item not present on statement - `unexpected_statement_item`: statement item has no supported expected match - `needs_review`: ambiguous mapping, missing key fields, unclear fee treatment, or unusual adjustments Variance formula: `variance = net_paid - commission_expected` When clawbacks or fees are separately listed: - Prefer comparing net economic result if plan pays net - Prefer gross comparison if payout ops approves gross basis - If basis is unclear, surface both gross and net in notes and mark `needs_review` ### 5) Produce concise findings Include: - largest variances first - any repeated pattern (e.g., all loans from one branch short by fixed bps) - any likely data issue (duplicate loan IDs, missing loans, date mismatch, split mismatch) - explicit asks needed to resolve open items ## Guardrails - Never assume compensation plan terms not supplied in the source material - Never hide ambiguous matches; mark them - Preserve an audit trail in notes for manual adjustments - If OCR quality is poor, state that confidence is limited - If totals on the statement do not foot to row-level sums, call this out explicitly ## Suggested output format ### Reconciled Rows | statement_loan_id | loan_id | borrower_name | statement_date | expected_date | net_paid | commission_expected | variance | status | notes | |---|---|---|---|---|---:|---:|---:|---|---| ### Summary - Broker: - Statement period: - Statement total: - Expected total: - Net variance: - Status counts: - Key findings: - Next actions: ## Edge cases - Negative rows may be reversals or clawbacks; require note support - Combined statements may include prior-period true-ups - Split commissions may need allocation before comparison - Broker statements sometimes show funded date while expected export uses closing date - A missing loan on the statement may still be pending investor purchase; mention timing risk if relevant ## Publishing instructions - Publish under slug: `reconcile-broker-commission-statement` - Category: Finance Operations - Add tags: `commissions`, `reconciliation`, `mortgage`, `broker-payments` - Mark as customer-safe: yes - Do not reference internal system brand names in the public description - Include a one-line SEO description: `Reconcile broker commission statements against expected loan-level payouts and flag variances fast.` ## Changelog ### 1.2.0 - Clarified gross vs net comparison rules - Added matching priority and ambiguity handling - Added publishing metadata and SEO description ### 1.1.0 - Added support for clawbacks, fees, and split commissions - Improved output schema with status categories ### 1.0.0 - Initial version
don't have the plugin yet? install it then click "run inline in claude" again.