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
Consolidated Financial Statements and QuickBooks
Answers
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
Do you use a spreadsheet to consolidate the financial reports currently? If not let me know, I can send you a template.
5
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.
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
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
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.
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.
Hi Chris,
Yes, we use a spreadsheet and I think "loathe" is the right word. Timberline sounds interesting. Thanks for the tip!
1
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/
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.
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
1
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.
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!
Multi-national
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
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.
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.
1
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!
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
It works great for migrating history to new systems, reporting monthly activity to home offices, working in Excel... Google TBX Trial Balance Exporter
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
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.
1
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.
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.
Intacct and NetSuite both have job cost
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.
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
1
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
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.
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.
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.
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.
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.
You can use QuickBooks to consolidate statements, but it involves careful bookkeeping. Under some circumstances, you'll need an upgrade or use of a third-party program to import the financial data into QuickBooks.
Filed Under:
Accounting