Excel workbook previewIntercompany Reconciliation Template
3 free downloads per account. Just your email and a 6-digit code.
About this template
An intercompany reconciliation proves that what one group company says another owes it matches what the other company says it owes. If the two sides do not agree, the eliminations in the consolidation leave a difference, and the group accounts are wrong by that amount. It is needed every month in a group with more than one entity, and auditors test it at year end.
The workbook puts each entity pair on one row, with the balance per the creditor and per the debtor in the transaction currency. Reconciling items are listed on their own sheet and pulled in by formula, so the unexplained difference is live. An elimination matrix shows the balances in USD per both sets of books. The worked rows use Ashcombe Group, an invented company with US, UK and Dutch entities.
Who it is for
The group accountant who cannot consolidate because the UK subsidiary and the parent disagree on what they owe each other, and the auditor wants every difference explained.
What is inside
- Balances sheet: 10 worked entity pairs and 16 blank rows, each with creditor and debtor balance and difference
- Explained and unexplained difference per pair from SUMIFS on the reconciling items
- Status formula: agreed, explained, within threshold or investigate, with colour
- Reconciling Items sheet: 5 worked items and 16 blanks with live age, age band and overdue flag
- FX rates and Settings sheets: example closing rates and a USD threshold in yellow input cells
- Elimination Matrix: creditor by debtor in USD per both sets of books, with the difference to clear
- Summary sheet by status with unexplained USD and open items by age band
How to use it
Enter the period, threshold and closing rates on the Settings and FX rates sheets, then replace the sample pairs.
Files
- XLSXintercompany-reconciliation-template.xlsx20 KB
Opens in Microsoft Word or Excel, Google Docs or Sheets, and LibreOffice. Based on general finance practice, not on one published standard. A template to adapt, not legal, tax or accounting advice.
Questions about this template
How do you reconcile intercompany balances?
List each pair of entities and each type of balance. Take the receivable in the creditor's books and the payable in the debtor's books, in the same currency, and find the difference. Explain each difference with a reconciling item, such as goods in transit or an invoice not yet booked, and correct the books where one side is wrong.
What causes intercompany differences?
The most common causes are timing (an invoice or shipment recorded by one side before the other), cash in transit, charges booked at the wrong amount, entries coded to the wrong counterparty, and exchange differences when one side books in a different currency or at a different rate. Disputed charges also cause differences until the two entities agree.
What threshold should apply to intercompany differences?
The group sets it. This template holds a USD threshold in one input cell, and a pair with an unexplained difference below it is marked within threshold rather than investigate. A threshold keeps the team on the differences that matter, but small differences that repeat every month still point to a process fault.




