Cash reconciliations or matching, is the term we use to describe situations where no unique transaction reference is available on both sides. Typically there is some form of period, account id or customer id field available (see the discussion on versioning for more on this), however two main complications are common.
Either the value tolerances are material (ie any match is valid within a given percentage limit) or it is not a one-to-one match – mutiple rows on one or both sides can be considered in scope for a given match.
cash-invoices.csv
invoice | amount |
---|---|
567-A | 200 |
345-B | 200 |
123-C | 300 |
789-D | 400 |
cash-payments.csv
transaction_id | amount |
---|---|
9900 | 300 |
9902 | 410 |
9904 | 200 |
9906 | 200 |
Joining these two data sets on the amount only (using more complicated criteria combinations is also possible):
exact-cash-match.csv
invoice | invoice_amount | transaction_amount | transaction_id | |
---|---|---|---|---|
567-A | 200 | 200 | 9904 | |
345-B | 200 | 200 | 9906 | |
123-C | 300 | 300 | 9900 | |
789-D | 400 | |||
410 | 9902 |
The break highlighted in orange, is because of a small difference in amounts (such as those caused by banking payment fees, or exchange rate differences). Adding a tolerance to the matching rule, allows these to be tolerated. In practice the difference may need to be accounted for with an adjustment in an accounting platform, going forward.
tolerance-cash-match.csv
invoice | invoice_amount | transaction_amount | transaction_id | |
---|---|---|---|---|
567-A | 200 | 200 | 9904 | |
345-B | 200 | 200 | 9906 | |
123-C | 300 | 300 | 9900 | |
789-D | 400 | 410 | 9902 |