Monthly sales reconciliation
Owner: Wickford Workshop finance coordinator.
Why: Reconcile a monthly sales export to an independently prepared ledger.
Data: Original fictional invoice text and ledger amounts, with duplicate invoices and unmatched records.
Done: Invoice, Region, Net, LedgerNet and Gap for matched invoices, with Gap = Net - LedgerNet.
Wickford Workshop's finance coordinator receives monthly invoice rows as text. The ledger uses typed amounts, and the two exports don't agree on every invoice. The coordinator needs a clean comparison before discussing any difference with the sales team. Duplicate invoice rows and padded invoice keys must be dealt with before matching the files.
Work toward a small handoff table that shows each matched invoice, its sales amount, its ledger amount and the signed gap. Keep credits and zero invoices in the calculation. The ledger and sales-only exceptions remain inspectable on Join's other outputs. This project checks the comparison rather than assuming that a balanced overall total proves each invoice matched. The hidden month changes the invoices and discrepancies, so your workflow must keep the rules dynamic.
input: {"source":"main"}
formula: {"field":"Invoice","expression":"Trim([Invoice])","type":"V_WString"}
Worked example: A padded invoice identifier will not match its ledger key.