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.