How to Consolidate Financial Statements from Multiple QuickBooks Files

 

Two ways to consolidate are:

EXCEL TRIAL BALANCES

[1] Export all trial balances to Excel, position them on the same tab but each below the previous one, tag add a column to tag each account with the entity name, combine the DR and CR into one DR (CR) column.
[2] Create your financial statements with your desired accounts and headings
[3] On your tab with the trial balances, create a column called Balance Sheet and assign a B/S account from your B/S page by linking to it…for all line items. Add another column for Income Statement Accounts.
Be sure to skip all intercompany accounts.
[4] On your financials, use the SUMIF function to pull from the columns on the T/B tab using your mapping.

Pros: You can easy-to-update, professional-looking consolidated financials.
Con: It takes a while to set up the first time.

 

QUICKBOOKS ENTERPRISE

[1] Make sure the Charts of Accounts are identical across all 3 entities for any accounts that you wish to consolidate. Account number, spelling, parent/sub status.
[2] Also make sure that any intercompany accounts are all on the same line, i.e. if you have an asset in one and a liability in the other, change one of them to an asset before consolidating.
[3] Use the tool in the Reports menu to consolidate.

Pro: Doesn’t take a lot of time.
Cons: You have to set up those intercompany accounts each time so they get zeroed out, and you end up with consolidated financials that are in Excel and are very cheesy-looking.

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.