You unexpectedly see items with 0 quantity and with a value on the Historical Inventory Trial Balance on Microsoft Dynamics GP


Symptoms


When you print the Historical Inventory Trial Balance (HITB) report, you have one or more items that have 0 in the Quantity field but do have an amount in the Value field.

To print the HITB report, on the Reports menu point to Inventory, and then click Activity.  From the Reports drop-down list click Historical IV Trial Balance.

Cause


An item can have 0 quantity but a remaining value if it is an item that is set up with an Average Perpetual valuation method.  Using this method an item is brought into Inventory at the actual cost, however when it is sold it goes out of inventory at the average cost.  If costs fluctuate, there is the potential that when you have sold all of your quantities that a value will still remain.

Here is an example of how this would happen.

1. Create an item with an Average Perpetual valuation method.

2. Enter and post the following increase adjustments for the same item using the same date.

 5 @ $35 = total cost of $175

10 @ $20 = total cost of $200

22 @ $23 = total cost of $506

At this point the HITB report would show that you have Quantity of 37 and Value of $881.

3. The next day sell all 37 of the item.  These would be sold at the average cost of $23.81 each for a total of $880.97.

When you print the HITB report, you will see Quantity of 0 and Value of $0.03.

4. If you review the value of the inventory account in General Ledger, you will see there is also a $0.03 value related to this item there as well. This is because Step 2 debited Inventory for $881 and Step 3 credited Inventory for $880.97.

Resolution


This is expected behavior since the function of the HITB report is to be used to tie out the value in the inventory module to the inventory account in GL.

It is the nature of the Average Perpetual valuation method to bring items in at one cost and sell them at another.  This is not something new that is happening, however, the HITB report provides more visibility to how it can happen.

To remove the remaining value from this report, you would need to use the Adjust Cost Utility and change the Unit Cost of every receipt layer to be the same cost.  Doing so will revalue any outflow transactions that were not posted at that new average cost and create cost adjustment transactions for General Ledger.