Accounting Geek Alert! How to create a consolidation template

How to Create a Consolidation Template
[1] As a one-time exercise, create the financial statements the way you want to see them as a final product. Formatting, line items, headers.
[2] Export the trial balances of each company to Excel. In one separate tab but in the same workbook as your financial statements, paste them one above the next on the same tab.
[3] Create a column called DR (CR) with a formula that takes whatever is in the Debit columns and subtracts whatever is in the Credit column.
[4] Create a column called B/S Account and link to the desired line item on the Balance Sheet. I recommend using absolute references to save time. Remember that unless you have a Net Income line item on your B/S, you’ll want to map all revenue, COGS, and Expenses accounts to Retained Earnings in this column.
[5] Create a column called P&L Account and link to the desired line item on the Profit & Loss statement (appropriate accounts only)
[6] Don’t map the intercompany accounts or other accounts that need to be eliminated.
[7] On the face of the financial statements, where the numbers should show up, use the SUMIF function to pull the figures from the Trial Balance tab. Reference the entire column (i.e. ‘Trial Balance’!D:D) rather than a limited range of cells.
[8] Create an Error Trapping tab and enter formulas to confirm the obvious, such as Total Assets per B/S, Total Liabilities + Equity per B/S, Variance = 0.
Special note: If you want to present financials that are rounded to the nearest whole number, then in step [3] above, use the ROUND function to do so, and add a line item to each Trial Balance called Rounding, add a formula to balance the Trial Balance for that entity, and map it to an account of your choice. I have learned to choose A/P.
When it’s time to run financials again, just export a fresh Trial Balance. Make sure the T/B structure hasn’t changed (or insert/delete the occasional row) and then simply copy and paste the new amounts over the old ones.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.