We often get asked how to do consolidation modelling with Modano.
This example uses Modano links diagrams to illustrate the key principles for Modano users. However, the same concepts apply to manual Excel modelling. The example files below include both a dynamic (Modano) Excel workbook as well as a standalone Excel file.
Don't Build Pyramids
The key mistake made when building consolidation models is to try and mirror company structures with the flow of information through a workbook. This typically results in a calculation chain something like (see first image):
- Co. A Operational Analysis >>> Co. A Financial Statements >>> Consol. Financial Statements
This approach works well for revenue and those items that are nicely additive across companies. However, it quickly turns into a spiderweb as soon as the (additive) closing cash and retained profits have to be replaced with links to the other consolidated financials, the inter-company transactions need to be removed out, and so on.
The end result is often an overly complex, half-additive, half-consolidated model. This is difficult to understand as even a simple consolidated revenue number has to be traced back through various financial structures in the workbook in order to reach the initial calculations.
1. Model a Single Consolidated Company
The better approach to consolidation modelling is to ignore the cumulative company structures and model the entire consolidated set of financials as if one single entity. This is done in exactly the same way as modelling any other entity, only the operational analysis is more segmented (see second image):
- Co. A & Co. B Operational Analysis >>> Consolidated Financial Statements
This approach keeps all of the calculation chains much shorter and the calculation of the overall consolidated financials far simpler.
But what about the individual companies?
2. EBITDA Statements
Part of the reason why people follow the pyramid approach is that management has requested to see intermediate information, e.g. individual company financial statements.
Our advice at this stage of model development is to keep it simple. Question the need for each piece of disaggregated financial statement presentation. In many cases, an EBITDA statement for Company A might be sufficient, especially if tax, funding, cash, etc. is all handled as a group.
Importantly, from a model design and information flow perspective, this should be created in parallel to the Consolidated Financials (see the third image):
- Co. A Operational Analysis >>> Co. A EBITDA Statement
This approach will keep the calculation chains shorter. It will also enable similar calculation paths to be used for additional Company A items, such as inter-company revenue, which would have been excluded from the Consolidated Financials.
The process for creating an EBITDA Statement is also very straight-forward at this point. Copy your Consol. Income Statement and delete everything below EBITDA, as well as all Co. B data. If you're using bpmModules then duplicate the Consol. Income Statement module, unlink the Co. B modules and delete everything below EBITDA.
Parallel not Series Structures
If an EBIT Statement is required, then capital expenditure and fixed assets will be needed at the company level.
If NPBT analysis is needed down at the company level, then debt and other funding will also be needed at the company level.
If individual company tax is needed then this will have to be created on a standalone basis as well.
And if a Co. A Balance Sheet is ultimately required, then this should all be done in parallel with the consolidated financials. Co. A Trade Debtors can be linked into both the Co. A and Consol. Balance Sheets (noting inter-company issues), the key is to steer clear of 'daisy-chaining' consolidation information via individual company financial statements.
Avoid consolidation pyramids and you'll find things much easier...
Any questions then hit me up. Cheers. J.