When someone is setting up a new company file in QuickBooks a common complaint I see is that they are getting an incorrect quantity on hand for inventory items, or an incorrect average cost for the items. Today I’ll explain why this usually happens, and give you a little tip on how to correct the problem.
When you add a new inventory part or assembly you are allowed to enter an “on hand” quantity, and an associated “total value”, as shown here.
Note that you cannot change these values if you are editing an existing item. The only time that you can directly enter this information in this window is if you are adding an item.
Behind the scenes QuickBooks is creating an inventory adjustment transaction when you enter values here, something that isn’t obvious. This adjustment is taking the on hand quantity and total value that you entered and calculating the average cost for the item. I’ve seen several problems with this:
- When setting up a new company the user may also enter outstanding bills and payments, and with those bills they may enter a received quantity of the items. This may be a duplication of some of the items entered in the new item window.
- Many times the user will not know the accurate value of the quantity entered. QuickBooks is using this value to create the average cost of the item, which affects both the inventory asset account as well as cost of goods sold (COGS) when the items are sold later. Often I see the user entering a zero value here.
How to Correct the Problem
If the quantity is wrong most people will notice this fairly quickly, seeing the incorrect balance in the item list or an inventory report. If the value is wrong it might not be noticed right away. The problem becomes more apparent when you see that COGS values are not being updated correctly. The problem is, if you start looking at the transactions you have entered, such as invoices and receipts, you can’t see where the problem comes from.
You can’t go back to the item list and correct this in the Edit Item window, because once the item is added to the list QuickBooks won’t let you change the average cost or the quantity on hand in this window.
The quickest way to pin this down is to run a QuickReport for the item. In the item list, select the item and right click on it to open the context menu. At the bottom of the list you will see the QuickReport. Select this, and change the date range to “All” to make sure that it shows you all of the transactions for this item.
You will see the inventory adjustment that was created by adding the quantity on hand in the Edit Item window. Note that it has a memo saying Opening Balance.
To correct this, double click on the inventory adjust transaction that is listed in the report. This will open an Inventory Adjustment window.
Scroll down until you find the item, and you will see the quantity and value that were entered. Note that this transaction posted to your Opening Balance Equity account.
You can correct this by either deleting the transaction (press ctrl-d in Windows versions), or editing the quantity and/or value as is appropriate.
BE CAREFUL when doing this – changing this will affect your balance sheet and possibly your profit/loss statement. If the adjustment occurred in a prior period you may be changing values for periods where you have already generated financial statements and tax returns.