4
Feb 18
QuickBooks Troubleshooting: Finding Inventory With Zero Quantity On Hand And A Inventory Value That Is Not Zero
When you have worked with QuickBooks for as many years as I have, 16 years, you are bound to run into some of QuickBooks quirks and have to figure out how to troubleshoot and resolve them. One of the quirks I have run into is that you can have inventory with zero quantity on hand but that still has a inventory value that is not zero.
From my experience this type of issue happens when making a inventory adjustment that moves inventory from one inventory item to another (for example when moving a single unit item into a multipack item). If you do not make inventory adjustments that move inventory from one inventory item to another you probably will not have this issue, however if you do the procedure below will allow you to find any problem inventory items.
To find inventory items affected by this issue you will want to open QuckBooks and go to Inventory -> Reports -> Inventory Valuation Summary and run the report. Below is a screenshot of two of our inventory items that have zero quantity on hand but have a inventory value.
QuickBooks inventory valuation report showing two items that have a quantity on hand of zero and a inventory value that is not zero.
You could scroll through the inventory valuation report trying to spot each these problem inventory items, however unless have only handful of inventory items it would get tedious and you would almost certainly miss one or two of the problem inventory items.
The easiest option to ensure you find all your problem inventory items is to export the report to Excel, since you can filter the inventory items to show only the ones you are looking for. If you have a large number of inventory items you may want to export the report to a CSV file and then open the CSV file in Excel (It took about 13 minutes to export our 4,000 inventory items to Excel).
Export QuickBooks inventory valuation report to Excel.
Once you have the report in Excel you can filter the On Hand column to show items that have zero quantity on hand and then filter the Asset Value column to show only items that have a non zero inventory value.
QuickBooks inventory valuation report in Excel and filtered to show inventory that has 0 quantity on hand and a valuation that is not 0.
One way to fix the issue would be to drill into the inventory valuation detail and find the inventory adjustment that caused the issue. In the screenshot below I can see there was a adjustment that made the inventory negative on 5/04/2017 and clicking into that adjustment shows me that we moved 10 non existent units out of the inventory item and into another inventory item. To resolve the issue I could edit the inventory adjustment and change it to move inventory from the correct item.
QuickBooks inventory valuation detail showing the non zero inventory value.
The other way to correct the issue would be to enter a inventory value adjustment to rest the inventory value to zero. Fixing the original adjustment would be the preferred method, however in this case since I found the issue almost 8 months later I chose to use inventory value adjustment.
QuickBooks inventory adjustment to reset the value to zero.
This will fix issues that can be found using the inventory valuation report, however it will not catch them all thanks to another QuickBooks quirk where inactive inventory items are not included in the inventory valuation report. This issue can lead the total inventory value reported on your inventory valuation report to be different from the value reported on your balance sheet and I will cover how to troubleshoot that issue in a future post.
