Adjusting inventory on hand and inventory values
We frequently have to adjust item quantities and values. At least once per year after physical inventory, we have to adjust quantities on almost every item. Often we find that the balance sheet value of an inventory item is correct but the value shown on the Inventory Value Report is different by some amount and needs to be corrected to match the balance sheet. Here are my methods for making these adjustments – gained from hours of trial and error and days of total time with Quickbooks phone support.
Here’s an actual example from our winery accounts. On 12/31 (year end) we counted 1,209 bottles of 2002 Meritage wine and showed a balance sheet value of $13,472.45 (see note 1). Before adjustment, the Inventory Item Valuation report for 12/31 (see note 2) shows 1,302 bottles at an average cost of $7.40 each, and a value of $9,641.12 (see note 3).
We have two problems:
A. We have 93 bottles of “shrinkage” during the year (due to wine used for sample tasting that was not recorded properly). We want to write off the appropriate amount: $7.40 x 93 = $688.20 to samples expense, adjusting the inventory value and correcting the quantity in the Quickbooks (QB) item database.
B. After adjusting for shrinkage, we want to correct the item’s average cost and calculated inventory value so it reports correctly on the Inventory Valuation Summary (the calculated value will be the same as the balance sheet value). The higher actual inventory value was due to an allocation of additional winemaking cost to the item’s inventory balance sheet account via a general ledger entry during the year.
Solve problem A.: Writing off shrinkage – use Adjust Quantity/Value on Hand:
Solve problem B.: Correct Avg. Cost and calculated Inventory Value without changing balance sheet inventory value – use Adjust Quantity/Value on Hand:
Steps 1, 2, and 3 are the same as in A. above.
1. From the Item List, right click on the item and select Adjust Quantity/Value on Hand. Or from the Vendors menu, select Inventory Activities -> Adjust Quantity/Value on Hand.
2. Click the Value Adjustment box.
Change the date to the date you want the adjustment to take effect –
4. In the Adjustment Account field selecting the item’s inventory account from your chart of accounts. This tricks QB into leaving the account value unchanged but results in a recalculation of the Avg. Cost. QB will complain with an “Income or Expense expected warning message – just press OK to close the message.
5. Select the item to adjust in the list, i.e., “02Meritage”.
In the “New Inventory Value” field enter the item’s inventory
account value shown on your chart of accounts for the correct date,
Check your work by going back to the Inventory Valuation Summary, clicking
on the item, and checking adjustments for the correct date,
Using these two methods, you can make any adjustments necessary to an inventory item’s calculated value and average cost, as well as synchronize the calculated value to its balance sheet asset value.
The item edit window contains an entry field for “Cost” that is separate from the read-only “Ave. cost” value shown at the bottom of the window. As far as I can tell, QB does not use the user-entered “Cost” value for anything.
A quirky thing about the Adjust Quantity/Values on Hand feature is that when you create an adjustment, it asks for the New Quantity and the New Value. This is really handy if you're correcting inventory with a physical count and an actual book (balance sheet account) balance. You just enter the physical count quantity and the book balance for the appropriate date. But, if you go back to the same adjustment after it is completed, QB shows you the difference between the old and new qty and value. You can change these numbers but now you'll need your calculator. Even worse, QB forbids you from entering a negative number in the value difference field. This makes it impossible to change the $ amount of shrinkage. I've found the best approach if you need to make a change to a prior adjustment is to delete the original adjustment then re-enter a corrected one.
If all you're trying to accomplish is to change a physical count and correct the balance, usually the best approach is to open the Adjustment dialog window and do this: