Consolidation Excel template (free download)
This free Excel workbook consolidates a small group: it translates five companies' revenue and costs into one currency, removes the intercompany fees, totals the group profit and splits out the non-controlling interest (minority interest). It downloads with no form. A spreadsheet like this suits a few companies, and stops being enough as accounts, currencies and months multiply.
Many groups begin consolidating in Excel, and for a few companies it is a reasonable way to start. This workbook is the layout we would use for a small group: inputs on three sheets, one sheet that does the arithmetic, and checks that show zero when the numbers agree. It has no macros.
It opens with the Zenvara group already filled in, so you can see the answer before you replace anything. The group figures it produces are 5,956 of revenue, 4,940 of costs and a net profit of 1,016.
Who it suits
The template fits a parent with a handful of subsidiaries, one reporting currency, intercompany items in one or two accounts, and one person who owns the file. It is also a good way to explain consolidation to a new colleague, because every number can be followed by clicking into the cell that produces it.
What is in the workbook
| Sheet | What it holds |
|---|---|
| Read me | What the workbook does, how to use it and what it leaves out. |
| Trial balances | Each company's revenue and costs in its own currency (thousands), the intercompany fees each has booked, and the group's ownership of it. |
| Rates | The average rate for each currency, with a clear statement of whether it is local currency per dollar or dollars per local unit. |
| Eliminations | The two sides of the intercompany fees, translated and compared, and the amount removed: 420. |
| Consolidation | A column per company, the total, the eliminations and the group, plus the split of profit between minority shareholders and owners, and four checks. |
How to use it
- Open the Trial balances sheet. Replace the company names and currencies, and type each company's revenue and costs for the month, in thousands of its own currency. Input cells have blue text on a cream background.
- Enter the intercompany fees each company has booked, in the two right-hand columns. Revenue and costs should already include them.
- Enter the ownership percentage of each company. A wholly owned company is 100%.
- Open Rates. Type the average rate for the month for each currency and choose how it is quoted. The workbook then divides or multiplies for you: 83.50 rupees to the dollar is divided, 1.27 dollars to the pound is multiplied.
- Open Eliminations. Check that the difference between the two sides is zero. If it is not, the smaller side is removed and the difference is left for you to chase.
- Read the group on Consolidation. All four checks at the bottom should show 0.
What the four checks mean
At the bottom of the Consolidation sheet, four cells should each show zero.
- Eliminated revenue equals eliminated costs.
- Group profit equals the profit of the five companies added together, since the fees come out of both sides.
- The minority's share plus the owners' share equals the group profit.
- The two sides of the intercompany fees agree. If this one is not zero, the companies have booked different amounts, and the difference needs chasing.
The Zenvara figures it starts with
Each translated figure is rounded to whole thousands before it is added, so every column and row adds up exactly.
| Companies added together | Eliminations | Group | |
|---|---|---|---|
| Revenue | 6,376 | (420) | 5,956 |
| Costs | 5,360 | (420) | 4,940 |
| Net profit | 1,016 | 0 | 1,016 |
| Of which minority shareholders | 27 | ||
| Of which owners | 989 |
The minority share is the 20% of Zenvara FZ-LLC's profit of 134 that belongs to the non-controlling interest (minority interest): 26.8, shown as 27. The guides on intercompany eliminations and non-controlling interest explain the two steps.
Tips when you adapt it
- Type only on the first three sheets. Do not overtype the formulas on Consolidation and Eliminations, or the checks cannot protect you.
- Make sure the fees are included in the revenue and costs you enter. They are shown separately only so they can be removed.
- Enter amounts in thousands in every currency. Mixing units is the commonest reason a total looks wrong.
- Save a new copy for each month and add the month to the file name.
Where a spreadsheet stops being enough
The workbook is deliberately small: one month, one profit and loss, five companies. A real group's workbook grows in directions that cost time and carry risk.
- The balance sheet. Closing rates, historical rates for equity and a translation reserve that has to be carried forward every month. One wrong opening balance and it never balances again.
- Different charts of accounts. Each new account in a company needs to be found and placed on the group line. Nothing in a spreadsheet stops the month when one is missed.
- Every month is a new copy. Formulas get overwritten, links to last month's file break, and nobody can say which version went to the board.
- Intercompany at scale. Five items is easy. Fifty pairs of balances in four currencies is a day's work, every month.
- History and locks. A workbook cannot show what changed in a number or that a month is closed.
- Drill-down. A group figure that cannot be opened to the company, the account and the entry behind it takes an hour to explain.
When two or three of these are costing time each month, the group has outgrown the template. The eight steps of consolidation do not change, but they can be held in a system that remembers the mapping, applies the rates, compares intercompany and records every change. The month-end close checklist shows the calendar those steps fit into.
General information about consolidation, not accounting or legal advice for your group.