Accounts receivable reconciliation template for a multi-currency close

Use this accounts receivable reconciliation template to match the total of your open customer invoices to the receivables control account at month-end. Start with the same posting cutoff and scope in both reports. Keep invoice currencies visible for follow-up and compare ledger-currency amounts only after confirming how exchange revaluation appears in each report.
Upload a case

Key facts

A balance can agree at company level while two customer accounts are wrong. Keep an invoice-level exception list alongside the control-account total so that a matching total does not hide offsetting entries.

Start with three sheets

Sheet Copy these columns Use it to
Open invoices Entity, customer, invoice ID, invoice currency, invoice amount, credit applied, cash applied, open balance, ledger amount, last posting date Check the invoice detail against the totals in the same open-invoice export
Control account Entity, ledger currency, account code, posting cutoff, opening balance, period entries, closing balance Match the closing balance to your general ledger report
Exceptions Item ID, customer, invoice currency, ledger amount, reason, source reference, owner, next check, cleared date Explain differences and track corrections or next-period entries

Export both reports as of the same cutoff. Where entities share a reporting currency, reconcile each entity first. Within the open-invoice sheet, total each transaction currency separately. For every ledger comparison, use the ledger amounts exported by your accounting system. Avoid converting a month-end invoice list at a single rate if the ledger already reflects different booking and revaluation entries.

Compare the control-account balance with the ledger value of the open-invoice export and write down the sign convention. In this example a positive difference means that the general ledger is higher. This worksheet bridges an invoice-only export to the control account. Include a receipt or other entry in the bridge only when it posts to that control account and the open-invoice export excludes it. Classify each difference by cause, such as a posting cutoff, account mapping or exchange revaluation. Give every unresolved line an owner and a next check.

Worked example

A controller closes on 31 October. The invoice-level report shows open balances of USD 70,000 and EUR 20,000. The exported ledger value of the EUR invoice group is USD 22,000. Together these groups give USD 92,000 of receivables in the open-invoice report. The general ledger control account shows USD 93,250, an initial difference of USD 1,250.

Reconciliation line Ledger amount
Open-invoice ledger total USD 92,000
Unapplied customer receipt included in the control account −USD 750
Documented month-end FX revaluation booked to the control account +USD 2,000
Expected control-account balance USD 93,250
Actual control-account balance USD 93,250
Difference after the bridge USD 0

In this example, both the unapplied receipt and the FX journal post to the same control account and sit outside the invoice export. Check the actual postings and report scope in your accounting system. The receipt is linked to the customer, but the buyer has yet to confirm which invoice it pays. Keep it on the exception list until you can support the allocation.

A zero difference with documented bridge items completes this tie-out. Carry outstanding disputes and unresolved allocations forward with an owner and next date. Pause reminders on invoices that an unresolved customer receipt could cover until you verify the allocation. If you hand an overdue invoice to a collector, provide the supported open balance after credits and payments.

Turn the close into an action list

Send unresolved allocations to cash application. Use the receivables aging audit to prioritize overdue invoices once balances are verified. If a documented business invoice remains overdue after follow-up, use the unpaid invoice escalation guide to choose the next step.

Sources

RELATED ARTICLES

Illustration of a free e-book representing the 'Free International Debt Collection Guide.' This guide offers proven debt collection techniques. Get your free copy now.
FREE GUIDE

Download our free debt collection guide

Practical steps to get unpaid invoices paid, at home and abroad. Download our debt collection guide for free.

Thank you! The guide will land in your inbox shortly
Oops! Something went wrong while submitting the form.