more-arw search

Q&A Forum

Consolidated Financial Statements and QuickBooks

Quickbooks Consolidated Financial Statements

Any time-saving tips for simplifying the consolidated financial statements reporting process? In terms of just the software piece of it, I feel like we’ve outgrown QuickBooks. Our holding company is mainly in the construction business.

Answers

Chris Shumate
Title: Accounting Manager
Company: Dominion Development Group, LLC
LinkedIn Profile
(Accounting Manager, Dominion Development Group, LLC) |

Mr/Ms Anonymous - First off I do want to say that if you think you have outgrown QuickBooks, I would say you are correct, and I do not even know your company's financial status. I know people will disagree, but QuickBooks is not great software if you are a company with millions in revenue, and consolidate.

There are better options. I work for a construction company that consolidates. We use Timberline, but we prepare our financials each month on a consolidating spreadsheet. Honestly, I loathe this option, I would like to convince them to move to another software that actually consolidates. Maybe Timberline does, and our company hasn’t learned how to use it correctly. Like many things in construction technology changes slowly, and managers are not apt to adopt.

Do you use a spreadsheet to consolidate the financial reports currently? If not let me know, I can send you a template.

Welles Hatch
Title: Senior Managing Director
Company: Adrian Loring Advisors LLC
LinkedIn Profile
(Senior Managing Director, Adrian Loring Advisors LLC) |

NetSuite and Intacct are the 2 platforms I recommend most often as the place to go after QB. Both are SaaS and manage consolidations. Intacct is G/L only while NetSuite is ERP with job cost, CRM, and customer/vendor portals native.

LESLIE THOMAS
Title: director of finance
Company: RUIYI INC
(director of finance, RUIYI INC) |

I would like to see your consolidation template because we use QB's also but now have a foreign operation I need to consolidate for US purposes and the foreign operation sends me there information in Excel.

Leslie

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

Anonymous, take a look at this free report here on Proformative...

"When Is It Time To Graduate From QuickBooks:"

https://www.proformative.com/whitepapers/when-right-time-graduate-quickbooks

It has some good insights.

Best... Sarah

Patrick Fleck
Title: CFO
Company: AnswerLab, LLC
(CFO, AnswerLab, LLC) |

Chris, you may want to look into using Adaptive Planning for consolidations. Its primary purpose is for budgeting and forecasting, but provided you can download your G/L trial balance into the format needed to upload it into Adaptive Planning, you can very easily use it to do consolidations and other types of roll-ups. In my previous job, I had three corporations, several physical locations, and several service groups. I needed to pretty intelligently set up my G/L chart of accounts to facilitate slicing and dicing the data for roll-up purposes first, but then I was able to mirror that structure exactly in Adaptive Planning. I could then use Adaptive to prepare actual vs. budget comparisons, forecasts broken down by month, etc., all rolled up in a number of different ways: total consolidated, by service type, by legal entity, by physical location. It's a very powerful tool, yet fairly easy to work with.

Topic Expert
Jim Quinlan
Title: CFO, Managing Director
Company: Trinity Group, BlueGold, Genergy, Wellco..
LinkedIn Profile
(CFO, Managing Director, Trinity Group, BlueGold, Genergy, Wellcount) |

Timberline has some very good consultants who can make it do all sorts of things that do not look possible out of the box. These are independent of Timberline, but know it inside and out. It will cost as you pay to fly them in and per hour, but the savings has been, in my cases, worth it.

Anonymous
(Manager) |

Hi Chris,

Yes, we use a spreadsheet and I think "loathe" is the right word. Timberline sounds interesting. Thanks for the tip!

Scott Adams
Title: VP of Operations
Company: Xerva
(VP of Operations, Xerva) |

Lots of our clients hate their QuickBooks but the alternatives were too expensive to make the jump to (ex. NetSuite), so we at Xerva consolidate QuickBooks files for reporting using a data warehouse for our clients. We can pull from any number of files in any editions of QuickBooks (Desktop or Online) and can pull any of the reports (the built-in consolidation options are limited here). Since we're a data warehousing company we also can combine your QuickBooks data with any other data sources (Budgeting softwares is a common QB combo to help show actual vs budget without all the manual integration work). Hope we can help some who's run into the "loathing" of QB. Reach out to us at https://www.xerva.com/

(Agent, JKS Solutions, Inc.) |

Quickbooks is not designed for multiple entities. You can of course consolidate manually separate QB companies.

It might be time to look at another system that is designed for multiple entities, like Intacct. There are videos on the website so you can learn more. It is AICPA recommended. Although depending on size of company something else may be more applicable.

Sandor Lenner
Title: CPA
Company: Susan Missal Lenner, P.A.
(CPA, Susan Missal Lenner, P.A. ) |

Assuming your are satisied with your construction reporting aspect of your business and you need to create consolidated financial statements, consider trying the consolidating feature in QB. However, company files must all be on the same version of QuickBooks Enterprise Solutions.
So if you have QuickBooks Enterprise Solutions, it makes sense to give it a try. I recently used it to consolidate 5 companies with eliminations. Tip - Either you can set up a subsidiary and use that subsidiary to post your intercompany transactions thereto or you can line up your intercompany accounts to self eliminate. Just to be clear, you need to invest some time to make sure your chart of accounts accross all companies are consistent, but once you go past the learning curve, the consolidation and elimination process is literally a few keystrokes and it looks just like the old fashion Excel spreadsheet columnar format. And most importantly, it works.

Roman Delino
Title: Account Executive
Company: Qvinci Software
(Account Executive, Qvinci Software) |

I would suggest googling "Consolidating multiple quickbooks files or entities." You will find that there are several solutions that can automate this reporting process when dealing with Quickbooks desktop, or online, or both. There is one solution (i will not mention names) that allows for consolidated or combined reporting across multiple entities, with different charts of accounts.

Lori Hammond
Title: CPA/Business Owner/Sr Mgr
Company: 8N Business Solutions and CBIZ, Inc.
(CPA/Business Owner/Sr Mgr, 8N Business Solutions and CBIZ, Inc.) |

I agree with Sandor. We have a client with 7 entities and we use QuickBooks Enterprise (QBES) which is designed for multiple entities. We have also set up a separate company file called Eliminating for those accounts that don't match up exactly (such as Due To and Due From for the Intercompany balances). Just include your Eliminating company as the last one in the consolidation process and your consolidated spreadsheet is built for you!

Topic Expert
Bob Scarborough
Title: CEO
Company: Tensoft, Inc.
(CEO, Tensoft, Inc.) |

Multi-national operations is usually one of those requirements that pushes companies to the next level of ERP/Financial Systems. Most of the mid-market systems support multi-national operations to some extent.

Usually multi-national operations requires more than the ability to consolidate (FASB 52 for multiple currencies). It requires the ability to support inter-company transactions (including inter-company payables and receivables), the ability to support local reporting requirements as well as consolidated reporting requirements, along with the ability to support multiple currency rates (spot, mid-point, end of month, etc) and history.

We support the Microsoft Dynamics line (also available as a Cloud application) - so there are plenty of options out there for creating consolidated financial statements and much more.

Bob Scarborough

Gerry Anderson
Title: President and Founder
Company: Logicon Solutions
LinkedIn Profile
(President and Founder, Logicon Solutions) |

There are a couple of tools to use to make your consolidations work. The key concepts are a data warehouse to dump your trial balances, a mapping engine to ensure you map the various chart of accounts to the 'consolidation accounts' and a place to keep your journal entries. We have used 'off the shelf' systems (cloud and non-cloud) as well as build custom soluions for various clients. The question I ask is how much do you want to spend and how much 'customization' or complexity do you have in your consolidation process. We have seen some as low as $5,000 and ones over $80,000. Contact me if you want more details.

Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

There are 2 straightforward ways to consolidate using QuickBooks:
(1) The Enterprise Solutions edition includes a consolidation capability.
(2) Use a few Excel functions and the new "Update" link between Excel and QuickBooks to create a reusable consolidation tool.

Both of the above are straightforward if you have deep expertise with the software or are working with someone who does.

Maybe you're outgrowing QuickBooks, maybe not, but in my experience decision-makers have wasted 15K or more + annual maintenance fees on upgrading when they just needed a little consulting to get more out of what they already have.

Roman Delino
Title: Account Executive
Company: Qvinci Software
(Account Executive, Qvinci Software) |

Jaime, there is a third way as well, by enlisting a solution that works alongside Quickbooks and gives your clients consolidation on an automated basis. You should give us a call!

Sara Laidlaw
Title: President
Company: Accounting Services Bureau Inc
LinkedIn Profile
(President, Accounting Services Bureau Inc) |

You asked about tools to assist with consolidations. I may have one solution.
There are two important features missing in QuickBooks when exporting a Trial Balance. You can not export a NET CHANGE or a Trial Balance by CLASS CODE.

Many entities use the class codes for departments or locations. I'm an accountant with a large QuickBooks client base and I had to have this, so I hired a programmer to write a utility for me. It only works on the desktop version of QuickBooks.
It works great for migrating history to new systems, reporting monthly activity to home offices, working in Excel... Google TBX Trial Balance Exporter

Topic Expert
Bob Stenz
Title: Controller
Company: Silicon Valley start-up
(Controller, Silicon Valley start-up) |

Repeating a previous posting on Proformative on 7/3/13 in a separate thread as follows:
I got by with eleven entities in a single instance of QuickBooks Enterprise. I added two digits to the front of my GL accounts for each entity. I had the local accountants map their trial balances to my GL, convert the currency, and create a monthly consolidating journal entry which I would upload into QB. Once the process was worked out, the consolidation for these entities went really well (less than 1 hour a month, excluding those entities mentioned in my NOTE) and we had world-wide reporting with our ERP system. NOTE: 6 of the entities were consolidated in Excel and then booked as a single entity into QB. This was the most difficult part and would have been made much easier if I could have booked each entity separately in QB. Unfortunately, these 6 entities were controlled by an overseas CFO that didn't know anything about consolidations (and eliminations) and much work was needed each month to ready the consolidating journal entry.

SUPPLEMENT: I'll add that doing the consolidation in QB was much more efficient than doing so in Excel. In this way, everyone using QB (we used QB Enterprise) saw a world-wide view. NOTE: I did not use the QB Enterprise consolidation features and I didn't want to work with multiple QB instances.

Timothy Ogden
Title: President/CFO
Company: Joseph Elon, LLC
(President/CFO, Joseph Elon, LLC) |

Glad to see someone else using this creative solution when having to work with QB! I finally converted everything over to Sage 50 Quantum, as it has built-in capabilities to allow you to maintain departmentalized financials within a company file, as well as consolidated financials among separate company files. Perhaps your organization might want to give it a detailed look. I've been using Sage products for a number of years now. I'm currently running Sage 50 Quantum 2014 and I love it.

John Pustell
Title: VP and Controller
Company: TRI Ventures Inc (formerly known as Aque..
(VP and Controller, TRI Ventures Inc (formerly known as Aquent Inc)) |

We have gone the old fashioned route - Microsoft Dynamics and FRx (soon to switch to MRW for reports). We consolidate over 20 ledgers quickly and easily each month and we distribute customized Financial Statements to 75 managers using an internet portal. The cost is easily 20% (or even less) of what the Cloud systems (Netsuite/Intacct) want. (We keep looking but the cost advantage holds up.) Altico Advisors and Vic Rosenberg are two consultants who helped us make this system sing.

Deb Morrin
Title: President
Company: Kase Orion CFO
(President, Kase Orion CFO) |

Intacct and NetSuite both have job cost accounting functionality and provide consolidations of different entity consolidation. They are both excellent and good choice for graduating from QuickBooks. Anytime part of the accounting is being done "off line" to accommodate QuickBooks, it is time to move on!

John Beck
Title: CFO Consultant
Company: BeckCFO
(CFO Consultant, BeckCFO) |

Sandor Lenner, thanks. I had heard QBEnterprise did consolidations but had not personally done one. People seem too quick to leave QB without knowing the expanded capabilities of QBEnterprise plus its many 3rd party applications for specific needs.

James Myers
Title: Director
Company: FP&A Strategy Consulting
(Director, FP&A Strategy Consulting) |

Anonymous - I've just completed a similar consulting role for a retail company that had 4 sets of accounts in Quickbooks and they wanted to create a consolidated view as the basis for their budget and future cash flow forecast. They are moving to a new ERP in the next 6 months and needed a cheap interim solution. I created the consolidation in MS Access that can easily be updated and the output is an Excel file that has all their data all in one place. I also created summary fields, simplified their chart of accounts (that was different in all entities) and consolidated their store structure, without making any changes in Quickbooks. I created a reconciliation in the Database, to ensure accuracy and the result was a very simple excel spreadsheet covering the last 2 years of data. I would be happy to share the details with you

Stephen McEwan
Title: Principal
Company: PRISM Consulting, Director - Advisory S..
(Principal, PRISM Consulting, Director - Advisory Services) |

Hi James, I'm going through the same situation. Client is moving to SAGE in 4 months, but needs consolidated cashflow position now. Currently using quickbooks. Are you able to share your experience. Thanks so much

Mark Matheny
Title: VP - FInancial Planning and Analysis
Company: Novolex (formerly Hilex Poly)
(VP - FInancial Planning and Analysis, Novolex (formerly Hilex Poly)) |

We use a series of upload "flat" files into a Microsoft Office PivotTable. We then download the PivotTables to Excel and create financial statements. Once set up, the Excel files will be attached to the data source and we just Refresh the Excel file.

Timothy Ogden
Title: President/CFO
Company: Joseph Elon, LLC
(President/CFO, Joseph Elon, LLC) |

As a member of the Sage 50 Accountants Network, I'd say you've definitely outgrown QB and should consider the benefits of Sage 50. I currently use Quantum 2014. Sage 50 Premium editions (and higher) have many features, including departmentalized and consolidated financial reporting with just a few click.

Timothy Ogden
Title: President/CFO
Company: Joseph Elon, LLC
(President/CFO, Joseph Elon, LLC) |

Perhaps the detailed response to another pertinent question will also be of benefit to help you answer this question. It can be found here: https://www.proformative.com/questions/sage-50-vs-quickbooks

I do hope this helps you.

Lynn MacDonald
Title: Executive Director of Finance & Administ..
Company: St. Mary's College
(Executive Director of Finance & Administration, St. Mary's College) |

QuickBooks Enterprise is built for consolidations. I've done international consolidations here without too much difficulty. If all your companies aren't already in a QB database but are in excel, you could pop that information into a new column once QB has done the consolidation.

Mark Smith
Title: V.P. of Finance
Company: Arrowhead Health Centers
(V.P. of Finance, Arrowhead Health Centers) |

Time-saving tips? Regardless of the software used or a company's industry, one principle that simplifies the consolidation process in most cases is to minimize the number of intercompany GL accounts (Due to / Due from).

If there are fees between companies, consider using a single GL account for both revenues and expenses, instead of having accounts for intercompany revenue in addition to accounts for intercompany expenses. If there are balances between companies, look at using a single intercompany account for both liabilities and assets.

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.

QuickBooks Quicker - 24 Courses Available