Why doesn't my intercompany check show zero?

One side of a transfer is missing, entered with the wrong sign, or recorded in a different month. Filter Cash flows on Intercompany and pair the rows up.

Can I add a fourth entity?

Insert a column before Group on Summary, type the entity name in row 1, copy the formulas across and extend the Group SUM.

Can I report several months?

Add the month as a third SUMIFS condition, or copy the Summary sheet once per month and change the month it filters on.

Cash flow consolidation template

By the Analistable team · Updated 2026-10-10 · 3 min read

A free workbook for small groups: paste every entity's cash movements into one Cash flows sheet, add opening balances, and the Summary shows each category by entity, the group total, net cash flow, closing cash and a check that intercompany transfers net to zero.

Part of our guide: How to consolidate data in Excel

What the template does

Group cash reporting usually means three or four entity spreadsheets with slightly different layouts. This template asks each entity for the same four columns — Entity, Month, Category, Amount — and builds the group view with SUMIFS. Intercompany transfers are kept as their own category so the summary can prove they cancel out, which is the most common reason a consolidated total comes out too high.

Sheet-by-sheet guide

Sheets in cash-flow-consolidation-template.xlsx
Sheet	Columns	You fill in?
Instructions	Step, What to do	No
Cash flows	Entity, Month, Category, Amount, Description	Yes
Opening balances	Entity, Opening cash	Yes
Summary	Line, one column per entity, Group	Entity names in row 1 and category names in column A

How to fill it in

Enter inflows as positive and outflows as negative amounts.
Use the same five categories everywhere: Customer receipts, Supplier payments, Payroll, Tax, Intercompany. Add rows on Summary if you need more.
Record both sides of every intercompany transfer: the payer as a negative, the receiver as a positive.
Put each entity's opening cash for the month on Opening balances.

How the formulas work

Each category and entity cell is a SUMIFS keyed on the entity name in row 1 and the category in column A:

=SUMIFS('Cash flows'!$D$2:$D$300,'Cash flows'!$A$2:$A$300,B$1,'Cash flows'!$C$2:$C$300,$A2)

Opening cash is looked up per entity:

=IFERROR(INDEX('Opening balances'!$B$2:$B$50,MATCH(B$1,'Opening balances'!$A$2:$A$50,0)),0)

And the intercompany check reads the group total of the Intercompany row:

=IF(ROUND(E6,2)=0,"Intercompany nets to zero","Check intercompany")

Worked example

Three entities for October 2026 produce this Summary:

Summary sheet with the sample data
Line	UK Ltd	IE Ltd	Shop Ltd	Group
Customer receipts	84000	36500	22400	142900
Supplier payments	-31500	-14200	-9600	-55300
Payroll	-28000	-12800	-7400	-48200
Tax	-6200	-2100	-1300	-9600
Intercompany	-10000	7000	3000	0
Net cash flow	8300	14400	7100	29800
Opening cash	120000	45000	18500	183500
Closing cash	128300	59400	25600	213300

UK Ltd sent 10,000 to IE Ltd, which passed 3,000 on to Shop Ltd. Each entity shows its own side, but the group column is zero, so the check reads 'Intercompany nets to zero'.

Limits and when to move on

The template assumes one currency and one month at a time. For multi-currency groups, convert amounts before pasting or add a rate column. If entities send full bank exports rather than categorised lines, first combine the bank statements into one sheet and categorise them. For P&L rather than cash, see combining franchise P&L and budget files. Once you have many entities or months, Power Query or Analistable (which reads the files in your browser and answers questions such as 'closing cash by entity for each month') saves the copy-paste.

This is a management view of cash, not a statutory cash flow statement.

Frequently asked questions

Why doesn't my intercompany check show zero?: One side of a transfer is missing, entered with the wrong sign, or recorded in a different month. Filter Cash flows on Intercompany and pair the rows up.
Can I add a fourth entity?: Insert a column before Group on Summary, type the entity name in row 1, copy the formulas across and extend the Group SUM.
Can I report several months?: Add the month as a third SUMIFS condition, or copy the Summary sheet once per month and change the month it filters on.