Consolidated Balance Sheet - Debt and Equity Links

Hi Nick,

This is a great question, I love a bit of consolidation modeling!

At the core of every consolidation is whether you're doing a genuine consolidation or simply doing an aggregation. Here's a basic explanation of the difference:

1. A consolidation sums up the non-financial and taxation components of the different entities (e.g. revenue, expenses, working capital, assets, etc.) and then independently does the financing and tax (e.g. corporate tax, debt, equity, etc.) based on the resulting profit and cash flows.
2. An aggregation simply involves summing up all of the data within the different sets of financial statements.

From the information you've provided, it looks like you're doing an aggregation, as a consolidation can only have one ordinary equity piece as an entity can only have one ordinary equity (i.e. common stock) class of capital. Hence, if all you want to do is aggregate your 4 sets of financial statements, you will need to convert all total-only link in rows (e.g. corporate tax, ordinary equity, etc.) into category blocks (ideally in independent categories groups) and link in the 4 rows of data for each row. For example, you would delete the ordinary equity row in the balance sheet and replace it with a category block (in a new categories group called 'Ordinary Equity Closing Balances') with the same link in (e.g. the 'Ordinary Equity Closing Balance' link in), a lot like we've already done for the 'Other Equity' category block. Here's how this might look:

You would then do this for all total-only links in and then link in the 4 rows from each underlying corresponding financial statement set.

An aggregation like this is relatively easy, as it's simply a summation of multiple financial statements.

For a consolidation, you would insert a 5th ordinary equity module (and corporate tax module, etc.) and enter ordinary equity assumptions for the consolidation entity independently of the underlying entities. You could take this approach as a hack/workaround to your issue, and plug the assumptions with formulas referencing the 4 precedent equity module outputs, but this really isn't clean or best practice so I'd go the above approach instead.

Hope this helps!