Inactive inventory items with Quantity On Hand will cause a discrepancy between Inventory Valuation reports and the totals for your inventory asset account(s) on the Balance Sheet.
In my way of thinking the quickest way to identify these items is by using the Troubleshoot Inventory tool found within the Inventory Section of Client Data Review contained in QuickBooks Accountant and QuickBooks Enterprise Accountant. This tool has a lot of great uses, but it enables you to find inactive inventory items with quantities on hand with the click of a single ‘filter’.
By default the Troubleshoot Inventory tool has all the various ‘filtering options’ checked, I simply can’t use it that way. My personal approach is to uncheck all the filter options, and then select only the CDR filter “Inactive Items with Qty on Hand”.
In this example we see that someone has made the inventory item, ‘Door Frame' inactive even though there is an on-hand quantity of 21 units in stock. With this tool, finding the incorrectly inactivated items is simple, but as professionals we need to understand how improperly deactivated items impact our financial reports and subsidiary ledgers so profoundly.
Regardless of the actual date you make an item inactive QuickBooks simply ‘makes the item inactive’, it doesn’t pay attention to the date you click ‘Item is inactive’. So an inactive item with quantity on hand not only impacts the quantities and values reported in the Inventory Summary Report today, but for all time.
In this example I am using one of the QuickBooks ‘sample company files’ that has set the current date to 12/15/2018. Below is a side-by-side comparison of a Balance Sheet and Inventory Valuation Summary as of 12/31/2017, nearly one year ago (in sample file years). Now as it happens the Door Frame item we identified with the Inventory Troubleshooting tool was made inactive just today (12/15/18).
Looking at the value of our Inventory Asset Account on the Balance Sheet and the Total Asset Value on the Inventory Valuation Summary we can see that our valuation is $252.00 less than the value of our Inventory Asset account. Now if we take the time to multiply out the average cost ($12.00) of those inactive Door Frames by the quantity still on hand (21), we find that the amount is $252.00. In other words the only reason our two reports don’t match is because of the inactive 21 units of that Door Frame item.
We have got to do something, we can't have our two financial reports not matching up. When it comes to Inactive Inventory Items with Quantities still On-hand, you have only two choices for repair.
The first option is to simply reactivate the item by un-checking the ‘Item is Inactive’ checkbox in the Item Edit window. Our Troubleshoot Inventory tool gives us a button to “make selective items inactive”, I don’t know why Intuit hasn’t made that button multi-purposed by turning it into a “make selective items active” when displaying ‘Inactive items with Qty on Hand.’
The second option is to adjust the quantity on hand for the inactive item so as to zero out the quantity. In order to accomplish this repair you can simply click on the Adjust Quantity/Value on Hand link from within the Troubleshoot Inventory tool.
When the Inventory Adjustment window opens, select the appropriate date for the transaction. Typically the best time to perform this type of adjustment is during a physical inventory, but that isn’t always possible. Date the transaction for the appropriate fiscal period recognizing the fact that it will impact your financial statements as of the date of the adjustment.
Use the drop-down arrow in the Adjustment Account field to select the account to use for the adjustment. You should offset the adjustment to either a COGS account for Inventory Adjustments (some accounting professional may prefer an Inventory Quantity Adjustments account) or a ‘COGS – Waste/spoilage’ account.
Now enter the inactive item you want to adjust the quantity for, since the item is inactive you will not find it in the drop-down list, you must type the name of the item into the field exactly as it appears in the Item list. Confirm that you want to use the inactive item on a 'one time' basis.
QuickBooks will display the current quantity on hand, enter 0 in the New Qty field to zero out the inventory. Be certain to click the Save & Close button to record the adjustment.
We have not only identified the improperly inactivated inventory item, but fixed our inventory irregularity. It's simple using the CDR Troubleshoot Inventory tool and it's links.