Report Consolidation in QuickBooks Enterprise – Part 1
One of the most common tasks I perform for my clients is consolidating or combining multiple QuickBooks® files to create a combined set of financial statements. Most of my clients have QuickBooks Enterprise, and that feature is built-in!
However, I do have some clients that do not use QuickBooks Enterprise, opting to use QuickBooks Pro/Premier or QuickBooks Online instead. But, when they do want to consolidate reports, we resort to two options:
- Export the reports to excel, and combine them manually (which requires some extensive work with getting all chart of accounts to match and some data manipulation in excel), which could be a task that takes between one to four hours.
- Use one of the many third-party apps that are design for consolidation, such as FathomHQ, Spotlight Reporting, Qvinci, QQUBE, Bison Analytics and Infor F9.
But, let’s focus on how to go about combining multiple QuickBooks Enterprise company files. First, keep in mind the following important caveats to be prepared for:
- Make your chart of accounts in the several company files as nearly identical as you can, making combined reports much easier to read.
- Accounts will ONLY be combined if they are identical. (Have the same name, same type and are at the same hierarchical level in each report. However, the good news is that they are not case sensitive.)
- Accounts that will NOT be combined:
- Identical Account names with different account numbers; for example "6115 – Utilities" will be a different account than "6778 – Utilities."
- One account with an account number, and one without an account number. For example, "6200 – RENT" will be a different account than "RENT."
- Accounts at hierarchy different levels, such as an "Office Supplies" account, are not combined with an "Office Supplies" sub-account of "Office Expenses."
- Name differences due to Spelling. For example, "telephone" is not combined with "telephones."
- Accounts are listed by type. Within each type, accounts are listed in the order of what has been done in the first selected company file. Subsequent accounts from the other company files are appended in the order they are encountered.
- You may be asked to log into your files and switch them to multi-user mode to accommodate the combination routine, which means you need to sit in front of the computer and enter the login credentials until all companies have been loaded. After the last one is loaded, and the excel combination is happening, then you can get up and get some coffee.
After you have done the preparation work in your chart of accounts, and made sure you had admin level access to all companies, you can follow these steps in QuickBooks Enterprise, with at least one of the companies open:
- From the Reports menu, select Combine Reports from Multiple Companies.
- Click Add Files, locate the other company file and click Open. Repeat for additional files.
- In the Select reports for combining section, select the reports for which you want to see combined information.
- Complete the From and To date range fields to set the report date range.
- Select a report basis.
- Click Combine Reports in Excel. A Microsoft Excel spreadsheet will open with the combined information.
This entire process is described on this video: https://youtu.be/IR4qCLCCAaI.