Editor's Note: If you are wondering where today's "QBO Monday Minute" is, remember, we have made a change for 2023. Liz now will be taking every other week off from her "QBO Monday Minute" features to add a video component to her posts. In addition, we are adding a new "QB Desktop Weekly" feature approximately every other Monday. The new feature will somewhat mimic the "Monday Minute" content. We believe you will find these new features both informative and enjoyable. Thanks for your readership.
In Part 1 of this Troubleshooting Inventory for QuickBooks Desktop series, we examine Client Data Review to help us quickly identify transactions that may cause the Inventory Assets on the Balance Sheet and the Inventory in stock reported on the Inventory Valuation report to be out of balance.
We want to continue with some of the other available tools to help us quickly identify items with negative quantities and, just as quickly, identify inactive items that still have Quantities on Hand.
In addition, we will not only look at these tools for troubleshooting, but repairing these problems within QuickBooks Desktop Accountant and QuickBooks Enterprise Accountant.
In the example below, the Compare Balance Sheet and Inventory Valuation tool shows a significant discrepancy between the Balance Sheet and Inventory Valuation Summary.
In this case, we will use the tool's link to the Transactions using Inventory Asset Account, but not Inventory Items report (shown below.) This example for the QuickBooks file above provides a good illustration of the capabilities of this report.
This report lists all transactions posted to the Inventory Asset account without using inventory items. For example, if the transaction were a check or bill, you could edit the transaction to include the item rather than the inventory account on the expenses tab.
But as in this case, where the transactions are journal entries and bank deposits, it would be necessary to delete or void those transactions and enter an Inventory adjustment for the appropriate item(s) for adjustment.
Another tool for troubleshooting is the Add/Edit Multiple List Entries feature, which is linked from the Compare Balance Sheet and Inventory Valuation tool.
Add/Edit Multiple List Entries allows you to quickly review the Inventory Items and Inventory Assembly Items for proper account configuration, compare Cost and Average Cost and quantities on hand.
In addition, because each column can be used for sorting, this window makes irregularity identification very simple.
- Sort the list by smallest to largest Quantity to identify negative quantities. They appear at the top of the list (shown above).
- Sort the list by smallest to largest Average Cost. Those items with "negative average costs" or an "average cost of zero" will be at the top of the list (shown below).
- You have identified two types of significant errors in your Inventory that must be resolved.
There is one more CDR Inventory tool we must include, and that's Troubleshoot Inventory. A significant feature of this tool is the ability to check for negative Inventory (shown below).
We already have discussed how to identify 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 particular range of dates (options shown in the yellow box above).
The Troubleshoot Inventory tool provides both of these critical capabilities. First, you select whether the tool shows if items had negative quantities at the end of the evaluation period, or if items had negative values at any time during the evaluation period. (Note: The example above depicts data without other optional criteria.)
This enables you to follow up on an item and use item history to determine when an item "went negative" so you can make adjustments.
This tool also enables you to identify inactive items with quantities recorded on hand (an example of this is shown in the red box above), another common cause of inventory valuation differences. This is the exact item in our Inventory Valuation Summary report causing the undetected difference with our Compare Balance Sheet and Inventory Valuation Summary tool.
Remember, this is an all too common cause of inventory valuation irregularity resulting from a QuickBooks user simply making an item inactive even though it still has items a Quantity On Hand. Of course, they could have a good reason for this, perhaps they do not want the item sold any longer, or they do not want the item to be restocked.
Regardless of the reason, the item needs to be repaired for purposes of valuation. Accordingly, once you have identified the problem item(s) using the tool, correcting any inactive item(s) becomes easy.
- Make the item(s) temporarily active
- Post the proper physical inventory adjustment to zero out the item(s)
- Once they are zero, make the item(s) inactivate again
Now that you have corrected it, the Inventory Valuation Summary Report should again tie to the balance sheet inventory account(s) even without having to include "Inactive items with Qty on Hand" in the report's header.
Summary
The tools and related reports available within QuickBooks Desktop Accountant and QuickBooks Enterprise Accountant help us quickly identify transactions that may cause the Inventory Assets appearing on the Balance Sheet and the Inventory in stock reported on the Inventory Valuation report to be out of balance.
These features allow us to review all Inventory Items (Active, Inactive, All, etc.) and Inventory Assembly items to determine their Quantity on Hand and the Average Cost to help identify potential irregularities impacting inventory reporting and valuation.
Our Accountant versions also allow us to quickly identify items with negative quantities for a specific date or the entire period we are analyzing and as quickly identify inactive items that still have Quantities on Hand.
Those who have been using QuickBooks Desktop from the very start can remember when we had to perform year-end inventory assessment, troubleshooting and clean-up without using any of these tools and reports.
Today's QuickBooks ProAdvisors have it so much easier thanks to all of these tools, but most of all thanks to the Intuit QuickBooks Desktop team and the ProAdvisors working with them who developed these tools for the rest of us.
"How sweet they are."
Like what you're reading?
Subscribe to our FREE newsletter and we'll deliver content like this directly to your inbox.