Free template · Excel

Intercompany reconciliation template

What it is

This free Excel template compares what each group company records against its sister companies. It lists every pair by counterparty and transaction currency, shows the receivable, the payable and the difference, and records the reason, owner and due date for anything that does not agree. It has one sheet for each month and a filled example.

What’s inside

• One sheet per month, so last month's open items are the first thing you see.
• Pairs by counterparty and transaction currency.
• Receivable, payable and the difference for every pair, worked out by formula.
• A reason for each difference from a pick list: timing, rate, coding, dispute or agrees.
• An owner and a due date against every open item, and a summary of differences by reason.
• An example month, filled in, so you can see how it reads.

How to use it

1. Copy the month’s sheet and put the month-end date at the top. Use the same cut-off for every company.
2. Enter each company’s receivable and payable by counterpartyCounterpartyThe other group company in an intercompany balance or transaction.Open the glossary entry, in the group currency. Show the currency of the transaction beside each pair.
3. Read the difference column. For every row that is not zero, choose a reason, name an owner and set a due date.
4. Eliminate the agreed amount in your consolidation, then carry the open rows into next month’s sheet and review them first.

Who is it for?

Finance controllers and group accountants who reconcile intercompany balances in Excel, for groups of about 2 to 50 companies, on any accounting system. It also suits accounting firms that close a group for a client. Once a group has more than a handful of pairs, a spreadsheet gets slow to keep right, and that is when Finconsol compares the pairs for you. The method behind the sheet is in why intercompany balances never agree.

Questions

What is an intercompany reconciliation?

It is the monthly comparison of what one group company says it is owed, or owes, with what its counterparty has booked. Both sides must agree before the balance is eliminated in the group accounts. Differences usually come from timing, exchange rates, coding or a dispute.

How often should intercompany balances be reconciled?

Every month, before the group accounts are built, using the same cut-off date for every company. Leaving it to the quarter or the year-end lets small differences pile up and makes each one harder to trace.

Which currency should I compare in?

Compare each pair in the currency of the transaction to see whether both sides booked the same thing, then in the group currency to see what a rate difference is worth. The example rows are in GBP, the group presentation currency, with the transaction currency beside each pair.