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:

 

  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.
  3. Change the date to the date you want the adjustment to take effect – 12/31/06 in our example.
  4. Choose an expense account to write off the cost of the shrinkage by clicking on the Adjustment Account field and selecting the account from your chart of accounts.  We used “Expenses G & A:Samples” in our example, but may differ on your balance sheet.
  5. Select the item to adjust in the list, i.e., “O2Meritage”.
  6. Enter the “new quantity”, 1,209 in our example.  QB will fill in the new inventory value: $8,952.50.  You may want to enter the reason for the adjustment in the Memo field, i.e., “Samples used during the year”.  More than one item can be adjusted by entering the new quantity for each additional item.  Press “Save and Close” 
  7. Check your work by going back to the Inventory Valuation Summary, clicking on the item, and checking adjustments for the correct date, 12/31/06.  Double click on this adjustment you just made and you can see the number of items changed by the adjustment, 93, and the amount of the expense, $688.62 (this amount is slightly more than the $688.20 we expected due to rounding).  Checking in the Chart of Accounts, we see that the Samples Expense account has been increased by $688.62 on 12/31/06.  And finally check the item’s inventory account to see that the account has be decreased by $688.62 on 12/31/06 from $13,472,45 to $12,783,83 

 

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.

3.      Change the date to the date you want the adjustment to take effect – 12/31/06 in our example.

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”.

6.      In the “New Inventory Value” field enter the item’s inventory account value shown on your chart of accounts for the correct date, 12/31/06 and $12,783.83 for our example.  You may want to enter the reason for the adjustment in the Memo field, i.e., “Adjust Avg. Cost”.  Press “Save and Close”

7.      Check your work by going back to the Inventory Valuation Summary, clicking on the item, and checking adjustments for the correct date, 12/31/06.  You will see the correct quantity, 1209, and Inventory Value, $12,783.83 and a new calculated Avg. Cost, $10.57.  Checking the item’s inventory account to see that the account has been increased and decreased by the same amount, $3,831.33 on 12/31/06 leaving the 12/31/06 balance at $12,783,83

 

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. 

 

Notes:

  1. How does the balance sheet value relate to the calculated value? To get balance sheet inventory value (which may be different from the calculated value on the Inventory Valuation Summary), select Chart of Accounts from the Lists menu, then double click on the current asset inventory account you’ve assigned to the item.  This opens the register to the inventory account.  Scroll to the first date on or before 12/31/06.  This balance shown is used for the year-end Balance Sheet, not the value on the Inventory Valuation Summary.

 

  1. How do I get the calculated Inventory Item Value for the date I want? Often it is a few days or weeks between the physical inventory date and the date you’re making the adjustments, i.e., you’re adjusting inventory on 2/20/07 but the inventory was taken 1/2/2007 for an inventory date of 12/31/06.  To get the inventory information for 12/31/06 before you make adjustments, create the Inventory Valuation Summary report.  Then change the date to 12/31/06.  It will show the quantity, average cost and calculated value for 12/31/06 of all items. 

 

  1. Why do I get a slightly different calculated inventory value when I multiply the inventory value times the quantity on a calculator? You might notice that 1,302 bottles times $7.40/bottle equal $9,634.80, not $9,641.12.  QB actually calculates the average cost per bottle to 5 decimal places when you create a new item or adjust the value of an existing item.  Double click the item in the Item List to see the full five-decimal-place average cost of $7.40455 in the Item Edit window.  But wait, 1,302 bottles x $7.40455 = $9,640.72 not $9,641.12.  Quick books calculates its avg. cost when the item is first entered which in our example was months before 12/31/06.  Probably small rounding errors on CGS on sales invoices have added up to the difference.

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.

ADDENDUM:

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:

  1. In the date field, enter the date you want the change to take effect.
  2. In the Account field, enter the item's Inventory Account (as in B.4. above).
  3. Check the Value Adjustment box.
  4. Scroll to the item you want to adjust and enter the correct quantity in the New Qty field.
  5. Enter the correct value of the inventory in the New Value field. Close.
  6. If your book value and the calculated values were the same, you're done. QB will show the correct numbers for the item on the Item Value Summary report.
  7. If the book value was too high or low (because you lost or gained items to inventory), You can finish up with one more step - a simple journal entry in the Chart of Accounts fixes it. For example, if inventory shrunk $1,000, create an AJE with a debut to your Shrinkage Expense account of $1,000 and a credit of $1,000 to the Inventory account.
Good Luck,
Martin Rex
2/26/07