Describe how you would approach a settlement information sheet and a multi-tab Excel workbook to identify where settlement breaks occurred and draft a clear summary of the root causes.
Use the settlement sheet, workbook check results, and root-cause reference data provided below. Return only breaks recorded on the settlement sheet, including breaks without a mapped workbook cause.
Output
- One row per root-cause category, with
root_cause, break_count, total_expected_amount, variance_amount, and settlement_ids.
- Treat a missing actual amount as the full expected amount of variance.
- Sort by
break_count descending, then root_cause ascending.
Output details
- Select only rows whose
sheet_status equals Break.
- Use
Unclassified for breaks without a mapped root cause.
- Calculate non-null variance as the absolute difference between actual and expected amounts.
- Sum expected amounts and variances for every contributing matched record without deduplicating settlement IDs.
- List
settlement_ids in ascending order, separated by , , retaining repeated IDs.
