Chuck's Reporting Corner April 2008

IMPOSSIBLE AND DIFFICULT REPORTS

I have been known to say, “We can pull anything out of QuickBooks – including the kitchen sink”. In reality, this is only true for about 90% of the data available in QuickBooks, and for about 80% of the report requests we receive. In this month’s article, I will discuss the caveats of a few of the most requested reports we receive at our organization.

It has been my experience that we must involve ourselves in the day-to-day QuickBooks operations for about 70% of the clients. In other words, we have hundreds of report shells, but we might have 30 that would work on a generic QuickBooks file. This is due to the myriad of ways that someone may enter data and encapsulate their daily operational workflow.

I am going to discuss three report areas in this article:

• Balance Sheet by Class
• Profit by Vendor
• Commissions on Profit

Balance Sheet by Class

This report is simply impossible. Let's look at a bill transaction to exemplify why: If you have two line items or account postings on the same bill, QuickBooks does not split the accounts payable into two pieces to record the A/P portion for each class. Instead, you would have a debit to Class 1 on the first line, a debit to Class 2 on the second line, and a credit to A/P without a class.

To take it one-step further, even if QuickBooks could separate A/P into two classes, it would have “transferred” that class to the Bank Account when the bill is paid. In other words, it would have to maintain the integrity of the class entry as it moved from one balance sheet account to another.

Note: It is possible to create a custom report for A/R and A/P by class, because we don’t have to worry about carrying forward the integrity of the class from A/P or A/R.

Profit by Vendor

This one is only possible in the narrowest of circumstances, for the following reason: QuickBooks does not expose a “table” that delineates the precise details of each item i.e. the exact cost or purchase of that one item.

Some database experts claimed that they have reproduced this item “table” by tying together – from the beginning of time - what the QuickBooks (SDK) Software Development Kit has exposed; however, I am quick to point out two things:

1. What about the beginning inventory balances? There is no associated vendor when entering those beginning balances.
2. What if the client has condensed earlier transactions in their file? The transaction item detail is lost in the transition to journal entries.

In general, there is no link in QuickBooks between a particular line item on an A/P transaction and the same line item being sold on an A/R transaction.

In the QuickBooks Graphical User Interface (GUI), it is possible to invoice the customer the items that are entered in A/P and marked as billable – but “underneath the hood” that link is destroyed once it has been invoiced.

Commissions on Profit

This report is fairly simple for inventory and assembly items, because the QuickBooks SDK exposes the actual cost apportioned to the G/L at the time the A/R transaction is posted. But for other types of items, this becomes more difficult for the facts mentioned above: there is no link between the specific A/P item and the corresponding A/R item.

Commission reports based on the profitability of items which are not inventory and assembly, is similar in nature to the Profit and Loss by Job report in QuickBooks; however most clients request that the costs be directly related to the invoices sold (or fully paid), as opposed to a specific date range. If the invoices and purchases were in the same requested time period, we would have no problem; but this is not a reasonable operational procedure for most businesses.

If the items were unique, it also might be possible; but again, for most business the description would be unique – not the item name.

For this case, the general pattern for success is to create some manual link between the A/R and A/P transaction, i.e. putting the Bill number in an invoice custom field, or putting the invoice number on the memo of the Bill. We have seen dozens of different methods to get this report to work correctly.

Summary

I have given you several common examples of some of the more difficult custom reporting areas in QuickBooks. Some are not possible at all; some are possible with certain caveats. Yes, most of the data can be extracted – but it takes both patience and imagination to make it work for the client.