How do I consolidate multiple Xero organisations with AI
Xero multi-entity reporting is one of the most common reasons finance teams end up back in a spreadsheet. Xero works on one rule: one organisation, one set of books. That is fine until the business becomes a group. A holding company, a trading company, a property company, perhaps a subsidiary in Ireland or Australia, each in its own Xero file with its own chart of accounts and sometimes its own currency. Then the board asks "how did the group do last quarter?" and someone spends two days exporting, pasting and stripping out intercompany recharges by hand.
This guide is for finance leads and owners running two or more Xero organisations who want consolidated management figures without a consolidation spreadsheet. It covers why group reporting in Xero is hard, how Tugger treats multiple Xero files, and three reusable prompts you can run in Claude, ChatGPT or Gemini against your own data.
Why consolidated reporting in Xero is harder than it looks
The difficulty is rarely the arithmetic. It is consistency. Four issues cause most of the pain.
Charts of accounts drift apart. The trading company calls it "200 Sales". The subsidiary set up two years later calls it "4000 Revenue: Services". Unless someone maintains a mapping, the same income lands in different places. Each account carries a Code, Name, Type (REVENUE, DIRECTCOSTS, OVERHEADS and so on), Class and ReportingCode, and if your accountant has set up consolidation groupings they appear as field and value pairs against each account code. Those fields give the AI a common language across files.
Intercompany transactions inflate the totals. When the management company invoices the trading company for shared services, one file shows revenue and the other shows a cost. Add them together and the group appears to have earned money from itself. Those invoices need identifying and removing, which means the AI has to know which contacts are other group entities.
Currencies and year ends differ. Each organisation record holds its BaseCurrency, CountryCode and financial year end. A UK parent with a March year end and an Australian subsidiary with a June year end cannot be summed as they are. Invoices carry a CurrencyCode and CurrencyRate, so values can be translated, but only if you say which rate to use.
Revenue needs defining properly. Xero stores sales invoices (Type ACCREC) separately from customer credit notes (Type ACCRECCREDIT). Summing the Total column includes VAT. A reliable revenue figure uses SubTotal, includes only AUTHORISED and PAID invoices, and subtracts credit notes over the same period. Get this wrong in one file and the whole consolidation is wrong.
A spreadsheet can handle all four, but only through someone's careful manual effort every month. The aim here is to write those rules down once, in a prompt, and let the AI apply them every time.
How Tugger handles multiple Xero organisations
Tugger syncs each Xero organisation into a managed data warehouse on a schedule, and its built-in MCP server lets Claude, ChatGPT and Gemini query that warehouse directly. If MCP is new to you, our guide to MCP servers explains it in one page. The connection is read-only: the AI queries the warehouse, never your Xero files.
When you connect several Xero organisations, each one becomes its own integration. The AI sees the list (for example "Holdings Ltd", "Trading Ltd", "Property Ltd", "Harbour Pty Ltd") along with when each last synced. Three things then matter:
- Parallel queries. The same question runs against every organisation in one step, with results kept separate, so you see each entity side by side before anything is added together.
- Cross-organisation matching. Where a contact in one file corresponds to a supplier in another, the AI can match on a shared field such as TaxNumber, CompanyNumber or EmailAddress.
- Schema awareness. The AI reads the column descriptions alongside the data. It knows ACCPAY rows are purchase bills rather than revenue, that AmountCredited is a settlement figure, and that trial balance rows are snapshots tied to a date.
That last point is the difference between asking a chatbot about a CSV and asking an AI connected to your ledger. The first guesses what columns mean. The second is told.
Prompt 1: The group entity health check
Before consolidating anything, confirm the files are comparable. This check regularly uncovers problems that would otherwise surface halfway through month end.
Role: You are a group financial controller preparing a monthly consolidation.
Context: Use the XeroOrganisations, XeroAccounts and XeroTrialBalanceRows data provided via Tugger MCP for every connected Xero organisation. Treat each Xero integration as a separate legal entity in the group.
Task: For each organisation, report its Name, LegalName, BaseCurrency, CountryCode, financial year end (FinancialYearEndDay and FinancialYearEndMonth), PeriodLockDate, the most recent TrialBalanceDate available, and the last sync time. Then compare the charts of accounts: list every active account (Status = 'ACTIVE') with Class = 'REVENUE' or Class = 'EXPENSE', grouped by Type, and flag any Type that exists in some organisations but not others. Flag any organisation whose PeriodLockDate is earlier than the end of last month, because its figures for last month may still change.
Output: Section 1, one row per organisation. Section 2, a table of account Types with a column per organisation showing the number of active accounts. Section 3, a short list of issues to resolve before consolidating, in priority order.
Why it works. A consolidation is only as reliable as its least-prepared entity. Lock dates tell you which books are closed. Base currencies and year ends tell you whether translation or period alignment is needed. Comparing account types by organisation shows where one company books costs somewhere the others do not.
Prompt 2: Consolidated revenue by entity
This is the question the board actually asks. It is also where a naive prompt goes wrong in instructive ways.
The first attempt
"What was total revenue across all my Xero companies last quarter?"
The AI will return a number. It will probably be wrong, for reasons that are obvious once you see them. It may sum Total rather than SubTotal, so VAT is included. It may include draft invoices or leave out credit notes. It adds pounds, euros and Australian dollars together as if they were the same currency. Every management recharge between group companies counts as revenue. And "last quarter" is ambiguous when entities have different financial years.
None of this is the AI being careless. The question did not specify the rules, so it filled the gaps with reasonable guesses.
The refined prompt
Role: You are a group management accountant preparing consolidated revenue for the board pack.
Context: Use the XeroInvoices, XeroCreditNotes, XeroContacts and XeroOrganisations data provided via Tugger MCP for every connected Xero organisation. The group entities are Holdings Ltd, Trading Ltd, Property Ltd and Harbour Pty Ltd. Any contact whose Name matches one of these entities is an intercompany counterparty. The group reporting currency is GBP.
Task: For the calendar quarter 1 July to 30 September, calculate revenue for each organisation as follows.
- Sum SubTotal from XeroInvoices where Type = 'ACCREC', Status is 'AUTHORISED' or 'PAID', and Date falls inside the quarter.
- Subtract the sum of SubTotal from XeroCreditNotes where Type = 'ACCRECCREDIT', with the same Status filter and the same date range.
- Separately calculate the intercompany portion: the same net figure restricted to invoices and credit notes whose contact (ContactContactID joined to XeroContacts.ContactID) is a group entity.
- Report external revenue as net revenue minus intercompany revenue.
- For any organisation whose BaseCurrency is not GBP, convert using the average CurrencyRate on that organisation's GBP invoices for the quarter and state the rate used. If no GBP invoices exist, ask me for a rate instead of guessing.
Output: A table with one row per organisation and columns for Base Currency, Gross Invoiced, Credit Notes, Net Revenue, Intercompany Revenue, External Revenue (local currency), Rate Used, and External Revenue (GBP). Add a group total row for the GBP column only. Finish with three bullet points explaining the largest movements in intercompany revenue compared with the previous quarter.
Why the refinement works
Every change closes a gap the first attempt left open. The revenue definition is explicit, so anyone reading the prompt can see how the number was built. Intercompany is named, not inferred; if you keep a contact group in Xero for group companies, you can point the AI at XeroContactGroups instead of listing names. Currency handling is stated, including what to do when data is missing, which stops the AI quietly inventing an exchange rate. The period is a date range rather than a phrase. And the output is shaped for the audience: separating gross, credits and intercompany lets a finance director check the logic line by line rather than trusting a single total.
Save this prompt. Next quarter you change the dates and nothing else.
Prompt 3: Intercompany balance reconciliation
The third recurring pain is proving that what one company says it is owed matches what the other company says it owes. When those balances disagree, the consolidation will not balance, and someone spends an afternoon finding the missing invoice.
Role: You are a group accountant reconciling intercompany balances before month-end consolidation.
Context: Use the XeroInvoices and XeroContacts data provided via Tugger MCP for every connected Xero organisation. The group entities are Holdings Ltd, Trading Ltd, Property Ltd and Harbour Pty Ltd.
Task: For every pair of group entities, compare the outstanding receivable one entity shows against the other with the outstanding payable the second entity shows against the first. Use AmountDue on XeroInvoices with Status = 'AUTHORISED' and AmountDue not equal to zero as the source of truth: ACCREC invoices in the creditor entity and ACCPAY bills in the debtor entity. Where the balances differ by more than 10 GBP, list the individual invoices on each side, matching them by InvoiceNumber and Reference where possible, and identify which appear on one side only.
Output: Section 1, a matrix of entity pairs showing Receivable Balance, Payable Balance and Difference. Section 2, for each pair with a difference, a table of unmatched invoices with Entity, InvoiceNumber, Reference, Date, AmountDue and a suggested cause (bill not yet entered, invoice dated in a different period, or currency difference).
Why it works. Intercompany mismatches are nearly always one of three things: a bill not yet entered in the paying company, a timing difference across month end, or a currency difference. Asking for unmatched invoices rather than just the variance points the team straight at the fix. Matching on InvoiceNumber and Reference mirrors what an accountant does by hand, because most groups quote the sales invoice number on the corresponding bill.
A fourth question: group cash
Once the three prompts above are working, group cash is the natural next step. Bank accounts in each organisation are identifiable in XeroAccounts (Type = 'BANK'), and their balances appear in XeroTrialBalanceRows for the latest TrialBalanceDate. Ask the AI to list every bank account across the group with its latest balance, translated into your reporting currency, and to flag unreconciled items using IsReconciled on XeroBankTransactions. Treasury conversations get shorter when everyone can see the whole position in one table.
What this approach is, and what it is not
This is management reporting. It answers the questions a leadership team asks during the month: how is the group trading, where is the cash, which intercompany balances need tidying up. It replaces hours of spreadsheet work with a repeatable set of prompts.
It is not a statutory consolidation. Group accounts for Companies House, minority interests, goodwill and formal translation reserves still belong with your accountant and your year-end process. The prompts above make that process easier, because intercompany balances have been reconciled monthly rather than annually, but they do not replace it.
| Approach | Good at | Limitation |
|---|---|---|
| Consolidation spreadsheet | Flexible, no new tools | Fragile, depends on whoever built it, rebuilt every month |
| Dedicated consolidation app | Statutory consolidation, fixed group reports | Answers only the questions its reports were designed for |
| Power BI or Tableau | Fixed dashboards once the data is modelled | New questions still need someone to build a report |
| AI connected through Tugger MCP | Ad-hoc questions across every entity, same rules every time | Management reporting, not statutory accounts |
An AI connected through Tugger sits alongside the other three. It is most useful when the question is new, specific, or needed today. Tugger also feeds Power BI and Tableau from the same warehouse, so fixed group dashboards and ad-hoc questions run off one set of synced data.
Making it repeatable for the finance team
The biggest gain comes from standardising the rules. Put your group definitions (entity names, reporting currency, revenue definition, intercompany treatment) in a short instruction file and load it into a Claude Project or a ChatGPT custom GPT. Every prompt your team runs then starts from the same assumptions, and the numbers match no matter who asks.
A practical monthly routine looks like this:
- Day one of close: run the entity health check and resolve any issues it lists.
- Day two: run the intercompany reconciliation and clear the differences.
- Day three: run consolidated revenue and cash, then drop the tables into the board pack.
What used to be two days of exporting becomes a morning of reviewing.
Related guides
- How to connect Xero to Claude
- Tugger vs Xero's own Claude integration
- What is an MCP server?
- Why Claude needs a data warehouse
- How to connect QuickBooks to Claude (for groups with entities on more than one platform)
Get started
If your group runs on several Xero files and consolidation still lives in a spreadsheet, connect each organisation to Tugger and run the entity health check against your own data. It is the simplest way to see whether this approach fits your group. Tugger is UK-based and ISO 27001:2022 certified, and it connects 70+ systems including Xero, HubSpot, Simpro and Harvest, no code, no faff.
