I see questions posed in various forums all the time about QuickBooks ‘data file analysis’, usually asking if there are checklists or other tools that can be used for this. When it comes down to it, there are two basic forms of data analysis, what might be considered a ‘functional’ analysis dealing with area like QuickBooks file set-up, configuration, use and accounting standards conformity. The second deals with ‘technical analysis’ covering areas like data integrity and database integrity (no, they are not the same thing). When it comes to ‘technical analysis’ most QuickBooks users and ProAdvisors are limited to the ‘Verify Data’ utility built into QuickBooks. But when it comes to ‘functional analysis’ I thought I might take time to review the history of past and present methods that have come and gone in an attempt to provide such analyses.
Data File Analysis – Basic Set-up and Use Checklists
For years the Sleeter Group has published their Consultant’s Guide which contains a checklist they refer to as QuickBooks “Data File Analysis”. This checklist contains a number of basic set-up and QuickBooks use indicators. Many of the questions were Yes, No or N/A (Not Applicable), among these were questions like ‘Inventory’ and ‘Payroll’. Some other questions required answers involving specific numerators such as ‘size of file’. A few questions required you to look at reports, for example does the Profit & Loss balance to the Balance Sheet. To my knowledge this checklist is still available within the Sleeter Consultant’s Guide and while it provides a good jumping off point, it is just that, a start when it comes down to conducting a functional analysis of a QuickBooks Company file.
Data File Analysis – Beyond Simple Checklists
Another valuable resource that existed for many years was the creation of Bonnie Nagayama of McWilliams & Associates. When the tool first came out it was a free standing software program that was a very detailed ‘electronic checklist’; after several years of offering the free standing version the software was migrated to an on-line product on an annual subscription basis.
I have written and taught several webinars on using ‘differential diagnosis’ in order to determine the cause of QuickBooks problems affecting data integrity. Well the McWilliams ‘Small Business QuickBooks Diagnostic Tool’ was, what I can best describe as an electronic flow-chart or decision tree. It began with a simple single question to which a yes or no answer was given, based upon that answer another question was posed, and so on and so forth. In many instances questions required more than a simple yes or no, again some report data totals were required in order to evaluate certain parameters and thus determine the validity or invalidity of your QuickBooks data. The results of this decision tree was a report that was suitable to present to your client.
My understanding is that this product is no longer available; however, I was NOT able to confirm that, but I couldn’t find it listed as an available product on the McWilliams & Associated website at the time of this writing either.
Data File Analysis – ‘A fully automated attempt’
Sometime in 2005 or 2006 a software developer by the name of James Cambra produced a program called QDA+ that was designed to run comparative numerics by actually linking to QuickBooks. A set of parameters were configured in the software to examine the validity of QuickBooks data. For example the software automatically compared the Payroll Liability report with the Payroll Liabilities recorded on the Balance Sheet. Many similar indicators resulted in the production of a report documenting those criteria that were either normal or abnormal based upon the parameters.
The fundamental problem with this software was that the developer had such minimal staffing that he couldn’t keep the software current and timely, nor could the software handle set-up configurations that were not QuickBooks standard specific. For example, if you chose to segregate your Payroll Liabilities on the Balance Sheet to have one account for 941 liabilities, and one account for state liabilities, the software simply reported errors in what was actually correct data because it couldn’t handle the set-up variation. After 2 or 3 years this software was removed from the market.
Data File Analysis – ‘Automating specific functions’
Several years ago there was another attempt to automate data file analysis with respect to at least a limited feature set. While intended (in the long run) to be a full-scope analysis tool designed to identify indicators or errors and fraud, ‘Audit My Books’ actually issued their first generation product which performed an automated analysis of QuickBooks based almost exclusively in the area of Accounts Payable. They selected this area because fraud statistics showed that A/P manipulation was more prevalent than was A/R manipulation.
This was an excellent subscription-based tool for its limited capabilities, the ability to select a variety of different parameters to be checked, and set the ‘trigger limits’ allowed for customization that prevented unnecessary and potentially alarming reports being generated, but it took time and experience in order to configure the product properly. That meant consuming valuable resources in time and expense to truly get to the point where a ProAdvisor could reasonably offer a customized level of support or service offering.
As I understand it, the high cost of development for the level of sophistication employed by this tool in terms of algorithms and test models, prevented the tool from expanding significant beyond the initial configuration, and in reality the tool was simply overpriced for the limited functionality it provided. As with the way of the earlier automated tools, Audit My Books also stopped being released.
Data File Analysis – ‘Client Data Review’
In 2009 Intuit released the first version of Client Data Review (CDR), and perhaps as much as other factors, this tool signaled the beginning of the end for other forms of Data File Analysis. A group of ProAdvisors worked hard and long with the development team for this product, to incorporate this feature within ‘Accountant’ versions of QuickBooks Pro/Premier and Enterprise. CDR began life with only a limited number of actual ‘fix it’ tools and ‘analytical features’, but enhancements and additions have been made almost every year since the initial release.
Today Client Data Review is the paramount Client Data Review methodology available to QuickBooks ProAdvisors covering areas, and offering feature-functionality including:
Account Balances
- Troubleshoot Prior Account Balances - this feature displays the differences in balances and suggests adjusting entries to correct the opening balances.
- Reclassify Transactions - allows you to find transactions that may have an incorrect account and reclassify them into the correct account, or reassign the appropriate QuickBooks class.
QuickBooks Lists
- Review List Changes (Chart of Accounts, Items, Fixed Assets and Payroll Items) – this tool lists all changes for the Chart of Accounts, Item List, Payroll Items, and Fixed Asset Items.
Accounts Receivable
- Fix Unapplied Customer Payments and Credits - helps you clean up unlinked A/R transactions.
- Clear Up Undeposited Funds Account – use this tool to link deposits to payments in order to clear up the Undeposited Funds account.
- Write Off Invoices - use this tool to write off a group of invoices.
Accounts Payable
- Fix Unapplied Vendor Payments and Credits - help you clean up unlinked A/P transactions.
Inventory
- Compare Balance Sheet and Inventory Valuation – this tool determines whether the inventory account on the Balance Sheet and the Inventory Valuation Summary match.
- Troubleshoot Inventory – this tool contains several views of potential inventory problems. By changing which items are shown and the inventory criteria, you can see the items in the way that best suits the client's company. The tool’s status columns for End Qty and Neg Qty indicate irregularities in inventory, such as negative quantities or items that are out of balance.
Payroll
- Find Incorrectly Paid Payroll Liabilities – this special report identifies payroll liabilities that have not been paid using the “Pay Payroll Liabilities” feature allowing you to take the appropriate steps to correct improperly made payments.
I have not attempted to include a comprehensive list of all the feature links contained in CDR, simply those feature/function tools that provide not only analysis but repair possibilities. But when it comes to QuickBooks Data File analysis from a file set-up, configuration, use and accounting basis, the Client Data Review feature is just about as ‘state of the art’ as you can find.
Note: This article does not focus on ‘data file analysis’ as it relates to the technical areas of either data integrity or database integrity two areas that are evaluated by the QuickBooks ‘Verify’ utility.