How To Combine Multiple QuickBooks Files For Reporting

You’re a CEO. You want to know the answer to a simple financial question, e.g. last month’s revenue. However, if your company is of decent size, it’s probable that your accounting team uses multiple QuickBooks company files to record your data. This could be for several reasons:

  • Your company has several legal entities, perhaps through acquisitions, and you maintain separate books for each legal entity.
  • Your company is large enough that you would exceed the transaction limits in a single QuickBooks file.
  • Your accounting team did a historical migration or change of processes, and now uses a new file.

Any of these would result in having multiple QuickBooks files – which is the case for about half of InsightSquared’s customers.

Here’s the problem: if you have multiple QuickBooks files, you cannot use QuickBooks to get consolidated reporting across all of them. You also cannot merge them via QuickBooks.

Here are 3 possible solutions.

1. Upgrade to QuickBooks Enterprise Edition

intuit quickbook
Quickbooks Enterprise Solutions is the highest edition in Intuit’s product suite. Pricing starts at $999 with +$575 per additional user. Right away that’s one disadvantage of this strategy: it’s more than 4x the cost of Quickbooks Pro.

QuickBooks Enterprise can only combine data to produce the following reports:

  1. Balance Sheet Standard
  2. Balance Sheet Summary
  3. P&L Standard
  4. Statement of Cash Flows
  5. Trial Balance
  6. P&L by Class

Here’s the problem: what if you want to do other types of financial analysis on the combined company data? For example, what if you want to know which customers are late in paying you (Accounts Receivable) across all parts of your company?

Upgrading to QuickBooks Enterprise Solutions only gets you a very limited set of cross-entity reporting. If you want robust reporting, Intuit recommends you move on to solution #2 below…

2. Export to Excel

excel
Most Intuit customers, until now, will have to do this option.

  1. Export the raw data from a company file into a worksheet in Microsoft Excel.
  2. Repeat #1 for each company file. Intuit recommends you put each company file into a separate sheet.
  3. Then, combine the worksheets into a single consolidated worksheet and run analysis on that. We recommend that you have an extra index column indicating the source company file for later troubleshooting in case the merge doesn’t happen perfectly.

Of course, this approach has a boatload of problems:

  • You are manually assembling and manipulating the data, which creates the chance of a human error.
  • Excel has row limits. Depending on what you are exporting (all transactions?) you may hit these limits when you assemble across all entities. Even if you don’t hit the row limits you may hit your computer’s memory limits.
  • Manually refreshing data is cumbersome. Every time you want to look at company financials you need to bother your accountant to export and re-assemble the data. Thus you can either suffer old data, or waste a lot of time on manual refreshes.
  • Where’s the insight? This export process will generate a lot of data, but very raw data. You then need to assemble it, do the addition and subtractions, do the date range calculations, etc. Even then, you may not have truly insightful reports that help you run your business better.

All of the above reasons are true of reporting in general. Excel is good for ad-hoc analysis but is not good at ongoing reporting.

If you want consistent, reliable, ongoing reporting of Financial data across multiple entities, Intuit recommends #3 below…

3. Combine in an Analytics Application

In 2011-2012 Intuit launched its Marketplace for 3rd-party software. Apps in this marketplace can (with your authorization) do things with your sync’d financial data.

In a Marketplace search for “combine company files,” there is one application that will combine several files into one. However, it only provides combined P&L and Balance Sheet reports. This is cheaper than buying QB Enterprise Solutions but even more limited — you don’t get combined data for any other financial reporting you might need.

Another approach is to use an analytics platform like InsightSquared that can read multiple company files when compiling financial analysis.

InsightSquared is reading data from five simultaneous company files and combining their data as if they were a single company. This happens automatically. This means that, for example, when you see analysis of your Accounts Receivable over time, it is whole-company analysis because it includes all your data:

[image source_type=”attachment_id” source_value=”33153″ width=”640″ height=”353″] [image source_type=”attachment_id” source_value=”34758″ width=”632″ height=”250″ align=”center” quality=”100″ link=”http://learn.insightsquared.com/excel-ninja-cheat-sheet?blog_source=organic&blog_medium=blog&blog_campaign=Excel-ninjas”] [contentblock id=18 img=html.png]