QuickBooks Data File Analysis With the Client Data Review Tool

The tool has some compelling features, such as the following:

– A customizable master control screen with a checklist-style view that provides access to tools and reports used by the reviewing accountant. This master control screen tracks the “status” of each review task (not started, in progress, completed, or not applicable) and allows you to keep “task notes” as you perform the data review.

– A “freeze” feature that takes a snapshot of your prior-period reviewed balances so that the next time you review the file, the balances for each account (as of the prior period date) are compared to the stored balances. If CDR finds a difference, it recommends a journal entry to restore your original balances.

– Several tools are provided that allow you to detect problems (or verify accuracy) of transactions in the client data file. These tools provide review at the account level for any account in the general ledger, as well as tools to verify sales tax, inventory, payroll, and “links” between transactions. Incorrect links are often the cause of inaccurate cash basis financial statements. For example, when a payment is applied to an invoice, QuickBooks creates a link between the two transactions. If, for example, you find an incorrect “link” between a payment and an invoice (e.g., if the payment is applied to the wrong invoice), this could cause problems on the cash basis financial statements.

– In addition to tracking changes to transactions, as provided by the Audit Trail, the CDR tool tracks changes to the QuickBooks lists including additions, deletions, merges, and edits to list items. This includes the Chart of Accounts, the Item list, the Fixed Asset Item list and the Payroll Item list.

Note: You can also access the CDR tool in any QuickBooks Pro or Premier, or QuickBooks Enterprise Solutions, by using an External Accountant User. However, this feature is only available when you are working in a client’s working file (.QBW file). The feature is not available when working with the Accountant’s Copy of the client’s data file.

Starting a Client Data Review

The CDR tool (shown in Figure 1) is launched from the Accountant menu in both QuickBooks Premier Accountant and QuickBooks Enterprise Solutions Accountant.

INSERT FIGURE 1

Figure 1 Client Data Review

Here are a few of the more interesting and useful tasks you can accomplish with the CDR tool:

Troubleshooting Account Balances

The Troubleshooting Account Balances (as shown in Figure 2) displays the current account balances and the “frozen” balances from the last review. Any differences are highlighted in the Difference column. You can set the view to Only show accounts with different balances.

You can create a new Transaction Change Report, listing those changes since the last review period, which allows you to drilldown to the specific transaction(s) creating the difference.

If CDR finds a difference, a recommended journal entry to restore your original balances is prepared for your review. The journal entry can be modified, if needed, prior to saving so that you remain in control. Some re-work of the journal entry may be needed if both Accounts Receivable and Accounts Payable have differences. This is because of the QuickBooks limitation that disallows an adjustment to both Accounts Receivable and Accounts Payable in the same journal entry.

The Last Review Balances are updated after the completion of each review. These balances cannot be changed by clients.

INSERT FIGURE 2

Figure 2 QuickBooks identifies differences between your reviewed balances and current data

Batch Reclassify

Very often, an accountant will have to reclassify a number of transactions posted by the client. Before the CDR tool existed, the concept of mass reclassifications was simply impossible. You had to edit each transaction, make the change and save it. Or if that was too much work, you had to use an adjusting journal entry to move the total from one account to another. In some ways that’s good practice, but being able to fix each original transaction gives you much better reporting.

The Reclassify Transactions tool allows you to do mass reclassifications in one screen.

Once the Reclassify Transactions window opens (see Figure 3), you select the vendor, the date range, the range of transactions (including those that can be reclassified and those which cannot, such as item-based transactions), and whether or not journal entries should be included. To do the mass reclassification, just select the transactions, enter the new account and/or class, and click Reclassify.

INSERT FIGURE 3

Figure 3 Reclassify Transactions options

Tracking Changes to QuickBooks Lists

The CDR Review List Changes feature allows you to view and track changes (additions, deletions, merges and edits) to the Chart of Accounts, Items, Fixed Asset Items and Payroll Items.

INSERT FIGURE 4

Figure 4 Review List Changes

Fix Unapplied Payments and Credits in AR and AP

Often credit memos or payment transactions get created, but for one reason or another they don’t get applied to the correct invoice. The net totals in the GL and on Aging Summary reports are correct, but when looking at the Open Invoices report, both transactions, the credit memo (or unapplied payment), and the open invoice will still be listed.

You can use the new CDR clean-up feature to apply the open credit or unapplied payment to the open invoice as shown in 5.

INSERT FIGURE 5

Figure 5 Apply open customer credits with open invoices

The window will only show those customers with open credits or payments that need to be applied. You can apply them one at a time or select a customer on the left and auto apply to all transactions.

Similar to the unapplied payments and credits in AR, you often find vendor bills and credits that are not properly linked. The Fix Unapplied Vendor Payments and Credits feature works just like those for AR.

Clear Up Undeposited Funds Account

When a client creates a payment transaction but does not properly include that payment in a deposit, the result is a growing balance in the Undeposited Funds account.

The Client Data Review provides a one-screen view (see Figure 6) of all customer payments that are included in the Undeposited Funds balance and any Deposits that are assigned to that customer. You can associate the payment transaction(s) to the deposits as necessary, and the result will be that the payment is no longer left hanging in the Undeposited Funds account.

INSERT FIGURE 6

Figure 6 Fix client data when payments were not included on a Make Deposit

Find Incorrectly Recorded Sales Tax and Payroll Tax Payments

There are several other tools that allow you to quickly identify data-entry errors such as incorrectly recorded sales tax and payroll tax payments. In previous QuickBooks versions, finding these types of errors was a lengthy process and involved preparing multiple reports.

Batch Write-Off

The Batch Write-Off tool does exactly what it says, allowing you to write off uncollectable invoices in one step.

INSERT FIGURE 7

Figure 7 Write Off Invoices feature in Client Data Review

The Write Off Invoices window (see Figure 8) indicates all invoices that are aged beyond a certain number of days. In addition, the list can be refined for smaller balances that are not worth pursuing to collection. The transaction type can be filtered for invoices, finance charges and statement charges … or all three. The date of the write-off, the write-off account, and (if desired) the class are specified in this window.

INSERT FIGURE 8

Figure 8 Choose the open receivables to write off

Marking a Review “Complete”

After reviewing the data for a specific accounting period, you can finalize the review by clicking Mark Review Complete. Marking a review complete will transfer your reviewed balances to the Last Review Balances column in the Troubleshooting Account Balances task, and it will prompt you to print a report of the review (see Figure 9).

Then when you start a new review, you can open the prior review or continue to a new review period.

INSERT FIGURE 9

Figure 9 Final report of Client Data Review activity

Overall, I think you’ll find the CDR tool to be quite useful for cleaning up the client data file. There are several things I wish it would allow us to do such as make corrections to item-based transactions, or to find and reclassify transactions coded to balance sheet accounts, but even without those features, the tool may save you hours compared to what it took to clean up data in previous versions.

In general, think of the tool as a useful way of doing many cleanup tasks, but you’ll probably find many common errors that cannot be detected or fixed with the tool. So don’t get too confident that everything’s clean just because you used the Client Data Review tool. In my QuickBooks Consultant’s Reference Guide, I dedicate about 100 pages to troubleshooting and fixing common errors. This tool makes many, but not all, of those processes much faster and easier.