Recently I was at a meeting where the question was asked of a group of ProAdvisors, "how many of you use the CDR tools in Accountant Edition?" I was amazed to see the small percentage of hands raised, I then interrupted the meeting to ask, "how many of you who have used CDR ever use the various Troubleshoot Inventory tools?" I was even further shocked by the yet smaller number of users.
Well one of the things that commonly occurs at 'this end-time of the year' when QuickBooks clean-ups are going on is the troubleshooting of inventory. Instances of where the general ledger inventory account(s) and the inventory valuation do not match are not uncommon, and people are always asking why.
There are a variety of tools that will assist you in identification, troubleshooting and repair of these kinds of problems within the Accountant's Version of QuickBooks and QuickBooks Enterprise Accountant. Two of the major tools to help you our are the Inventory Troubleshooting section of the CDR (Client Data Review) tool, and the Add/Edit Multiple Item List feature. So let's look at how these tools work together to give you improved inventory troubleshooting capabilities.
Client Data Review (CDR) provides several tools to troubleshoot Inventory within the Troubleshoot Inventory window including the Compare Balance Sheet and Inventory Valuation.
The Compare Balance Sheet and Inventory Valuation tool determines whether the inventory account on the Balance Sheet and the Inventory Valuation Summary match. The tool checks the inventory account(s) and Inventory Valuation Summary when it is opened and displays the results.
The Inventory Valuation Summary total should agree as of the same date with the Balance Sheet value for Inventory. The default date for the comparison is the last day of the CDR review period. You can change the date in the As of field and click Refresh to help you narrow down the date when the discrepancy occurred.
A variety of transactions posted directly to the Inventory account will result in a situation in which the Inventory Valuation Summary will not agree to the Inventory Asset balance on your Balance Sheet. One common cause is inventory items with Quantity On Hand that have been made inactive will also cause a discrepancy between the inventory valuation summary report and the totals for the inventory asset account on the Balance Sheet. We will look at this topic in a little greater detail when we discuss the Troubleshoot Inventory tool.
While a green circle indicates that the inventory account on the Balance Sheet and the Inventory Valuation Summary match, a yellow warning triangle (like the one shown in the example above) indicates that there is a discrepancy between the inventory account on the Balance Sheet and the Inventory Valuation Summary. When the tool shows a discrepancy, there are some helpful links available to assist you in troubleshooting the problems.
There are links to the Balance Sheet report and Inventory Valuation Summary as of the selected date. You can change the date, refresh, and click the links to see the Balance Sheet and Inventory Valuation Summary as of the new date.
I have noticed that amounts displayed in the tool may sometimes differ from those appearing on the corresponding reports. This is especially true for Balance Sheet computations. If you have chosen to establish more than one Inventory Asset account, the value shown as the Inventory Asset Account value in the tool window will be the total of all accounts which are tied to actual inventory items (or inventory assembly items). Because improper set-up of items is a frequent cause of discrepancies in these reports, such set-up irregularities may impact the displayed totals in the tool.
The comparison tool also contains a link to a report that shows Transactions using an inventory asset account but not inventory items, like the one shown below.
This report lists all transactions (such as a Journal Entries, Checks or Bills) that were posted to the Inventory Asset account without an inventory item. If it were a check or bill, you could edit the transaction to include the item and not the inventory account on the expenses tab. If the items were a journal entry, then it would be necessary to delete or void the Journal Entry and enter an Inventory adjustment for the appropriate item for the adjustment.
There are additional links that provide access to the Add/Edit Multiple List Entries window which will allow you to easily review the Inventory Items and Inventory Assembly Items to check for proper account configuration, compare cost and average cost, and review quantities on hand. Because each column of the Add/Edit Multiple List Entries can be used for sorting purposes, this window makes it easy to identify irregularities. For example, if you sort the list by Quantity in reverse order (smallest to largest), all of your negative quantities will show-up at the top of the list (as shown above). If you sort the list on Average Cost, also in smallest to largest order, if you find that you have ‘negative’ average costs, or items with zero as average cost, you have found significant errors in your inventory that need to be taken care of.
Another great tool can be found in the Inventory Section of CDR, this is the Troubleshoot Inventory tool. One of the major features of this tool is the ability to check for negative inventory. OK, we just learned how to see negative inventory using the Add/Edit Multiple List Entries window, but that window only displays the ‘current Inventory on Hand’, it does not look at the inventory on hand on a specific date, or over a specific date range.
The Troubleshoot Inventory tool gives you both of these capabilities. You can see if items have negative values as of a specific date (the end of the evaluation period), or you can option to see if items had negative values at any time during the evaluation period). (Note: The above example provides such a depiction without other optional criteria being displayed.) This provides you with the ability to follow-up on an item and then use item history to find exactly when an item ‘went negative’ in order to make adjustments.
This tool also enables you to identify items that are inactive but still have quantities recorded on hand, another common cause of inventory valuation differences. We already discussed the fact that one common cause of inventory valuation irregularities stems from inventory items with Quantity On Hand that have been made inactive. Once you have identified these items using the Troubleshoot inventory tool it become simple enough to correct such inactive items by temporarily making them active again and then posting the proper physical inventory adjustment to zero them out, once that is done you can then inactivate the item, now the related inventory valuation summary report will tie to the balance sheet inventory account(s).
Summary
We have three great tools available to us within the Accountant versions of QuickBooks and QuickBooks Enterprise. The Inventory Asset to Inventory Valuation Comparison tool and it's related troubleshooting reports will help us to quickly identify transactions that may cause the two values to be out-of-balance with each other.
The Add/Edit Multiple List Entries features makes it possible to review Inventory Items (Active, Inactive, All, etc.) as well as Inventory Assembly items, and to sort those lists on any of the available columns most notably the Quantity on Hand, the Average Cost, the Income Account, the COGS (or Expense) Account, and the Asset Account. Each of these sort capabilities helps us to quickly identify potential irregularities that impact inventory reporting and valuation.
The Troubleshoot Inventory tools gives us an easy way to identify items with negative quantities not only on a specific date, but for an entire period we are analyzing. We can also quickly identify inactive items that still have Quantities on Hand.
How any QuickBooks ProAdvisor performing year-end troubleshooting, clean-ups and assessments of Company files involving inventory can accomplish these tasks without making use of these three tools is beyond me. Hopefully now that you are aware of these capabilities you will jump on the band-wagon and add their capabilities to your troubleshooting arsenal.