When the Inventory Valuation Report Doesn’t Agree with Your Balance Sheet – Find it and fix it fast

There are two types of errors that can cause the valuation report total to be different from the balance sheet. What follows are the errors and how to correct them.

First, let’s look at what I am talking about. As you can see below, the balance sheet shows the value of inventory to be $21,754.31 while the Inventory Valuation Summary report whos the value to be $21,566.50.



The first type of error involves entries that were made to the inventory account through a journal entry, via the expense tab on the “Write Checks”, “Enter Credit Card Charges”, “Enter bills” forms, or from the “Adjustment Account” dropdown list in the “Adjust Quantity/Value on Hand” form. Below are the forms from which you should NOT make entries to inventory. Notice that the work “Account” is next to each selection that says “Inventory Asset”



Below are the forms from which you SHOULD make entries to inventory. Notice that each of these has the label “item” next to them.



The second type of error occurs when a user makes an inventory item that still has value inactive. Never make an inventory item inactive until you have adjusted the quantity on hand and value to zero using the “Adjust Quantity/Value on Hand” form.



Finding the Problem

Run a custom transaction detail report with the filters, Date range="All" and Account="Inventory Asset"
From the columns to display select Type, date, Num, Memo, Item, and Amount
Total by “Item Detail.



Scroll down to the bottom until you see a line that says “Total Inventory”.
If this amount matches the total in your your “Inventory Valuation Report”, you have the first type of problem, non-item level entries made to the inventory account.
If this amount does not match the total in your “Inventory Valuation Report”, you have the second type of problem. Someone has made one or more inventory items with value inactive.

Fixing the Non Item Entry to Inventory Problem

You should see a category “No item” below the “Total Inventory” line. Scroll down to the “Total No Item” row to see the amount of the adjusting entry you will need to make to zero out the “No Item” total. You will need to look at the individual “No Item” entries to decide what the offset account should be but the most likely candidates are “Cost of Goods Sold” and “Inventory Gains and Losses”.

Fixing the Deactivated Inventory Item Problem

From the QuickBooks menu, select “File/Utilities/Export/List to IIF files”. Check “Item List” and click OK. Save file with a name and location you can access from within Excel. This is a tab delimited file you can open in Excel.



Delete all rows above the row with “!INVITEM” in the first column. These are the column titles I refer to in the selection for sorting. Format them as bold tp make them easy to identify as headers and not data.

Sort the data on “Hidden” in descending order and on the second column labelled “QNTY” in descending order. In order to make your work easier, hide all columns other than “NAME”, “DESC”, the second “QNTY”, and “HIDDEN”. You will now see any items with a non-zero value in the “QNTY” and a “Y” in the hidden column.



You will need to activate them in QuickBooks, use the “Adjust Quantity/Value on Hand” form to change the quantities and values for each to zero, and then again deactivate them.

Robert Guild is certified QuickBooks ProAdvisor in Austin, TX who conducts CPE courses for CPAs and individual training and group classes to QuickBooks users. His company at www.QBCoach.biz, maintains a sixteen-station QuickBooks lab, providing hands-on training. You can contact him directly at rguild@QBCoach.biz or follow him on twitter at QBPro