Negative inventory in inventory accounting
Allowing physical negative inventory may have undesirable consequences in inventory accounting, especially if the inventory costing principle is Actual and the valuation method is either FIFO or Weighted average.
Most of the issues that are related to physical negative inventory can be mitigated by using the correct configuration and maintenance of data.
Example: Why is the cost out of sync?
The following table lists the required setup for the Item model groups.
| Item | Inventory model | Physical negative inventory | Latest cost price | Active planned cost |
| A | FIFO | Yes | Yes | No |
The purchase from the supplier always takes place at a unit cost of 7,500.00.
The following table lists the events as they occur, in chronological order.
| Financial date | Reference | Receipt | Issue | Quantity | Cost amount |
| 10/6/2017 | Sales order 01 | Sold |
-3.00 |
||
| 10/6/2017 | Purchase order 01 | Purchased |
2.00 |
15,000.00 |
|
| 10/6/2017 | Sales order 02 | Sold |
-3.00 |
-22,500.00 |
|
| 10/6/2017 | Purchase order 02 | Purchased |
1.00 |
7,500.00 |
|
| 10/6/2017 | Sales order 03 | Sold |
-3.00 |
-22,500.00 |
|
| 10/6/2017 | Purchase order 03 | Purchased |
3.00 |
22,500.00 |
|
| 10/6/2017 | Purchase order 04 | Purchased |
2.00 |
15,000.00 |
|
| 10/6/2017 | Purchase order 05 | Purchased |
3.00 |
22,500.00 |
|
| 10/6/2017 | Sales order 04 | Sold |
-1.00 |
-18,750.00 |
The system starts issuing from inventory at cost per unit of 18,750.00 even though the cost of purchase has not exceeded 7,500.00.
Why does this happen?
In order to explain this in better detail, let’s add a few more columns on the rightmost side, in green. These new columns represent the inventory balance after posting the specific transaction. The inventory balance is also known as InventSum.
| Financial date | Reference | Receipt | Issue | Quantity | Cost amount | Quantity | Value | Avg. unit cost |
| 10/6/2017 | Sales order 01 | Sold |
-3.00 |
1) |
-3.00 |
$0.00 |
$0.00 |
|
| 10/6/2017 | Purchase order 01 | Purchased |
2.00 |
15,000.00 |
-1.00 |
$15,000.00 |
-$15,000.00 |
|
| 10/6/2017 | Sales order 02 | Sold |
-3.00 |
-22,500.00 2) |
-4.00 |
-$7,500.00 |
$1,875.00 |
|
| 10/6/2017 | Purchase order 02 | Purchased |
1.00 |
7,500.00 |
-3.00 |
$0.00 |
$0.00 |
|
| 10/6/2017 | Sales order 03 | Sold |
-3.00 |
-22,500.00 3) |
-6.00 |
-$22,500.00 |
$3,750.00 |
|
| 10/6/2017 | Purchase order 03 | Purchased |
3.00 |
22,500.00 |
-3.00 |
$0.00 |
$0.00 |
|
| 10/6/2017 | Purchase order 04 | Purchased |
2.00 |
15,000.00 |
-1.00 |
$15,000.00 |
-$15,000.00 |
|
| 10/6/2017 | Purchase order 05 | Purchased |
3.00 |
22,500.00 |
2.00 |
$37,500.00 |
$18,750.00 |
|
| 10/6/2017 | Sales order 04 | Sold |
-1.00 |
-18,750.00 4) |
1.00 |
$18,750.00 |
$18,750.00 |
Notes:
- The Cost per unit is 0.00. When the balance is negative, the system looks for a fallback cost to apply.
- First, the system looks for an active cost. This fails.
- Second, the system looks for cost set up in the Cost price field in the item master record. The cost is equal to 00.
- The Cost per unit is 7,500.00. The balance is still negative. The system looks for a fallback cost to apply.
- The system looks for an active cost. This succeeds.
- The latest cost price was set to Yes on the item record. The prior Purchase order unit cost of 7,500.00 has now become the active cost.
- The same condition applies as in number 2.
- The Cost per unit is 18,750.00. If the balance is positive at the time of posting the issue transaction, the system applies the average cost of the balance.
- Average cost is calculated as: 37,500.00 / 2.00 = 18,750.00
The issue is that the inventory balance of 1 piece at 18,750.00 is overvalued because the item has never been purchased at a cost higher than 7,500.00. The reason for this overvaluation is that the first issue transaction leaves inventory at a cost per unit of 0.00. The wrong cost estimation will continue to ripple through the following transactions.
The only way to get the inventory balance back in sync is to run either the Recalculation or Inventory close jobs.
| Financial date | Reference | Receipt | Issue | Quantity | Cost amount | Qty | Value | Avg. Unit cost |
| 10/6/2017 | Sales order 01 | Sold |
-3.00 |
-3.00 |
$0.00 |
$0.00 |
||
| 10/6/2017 | Purchase order 01 | Purchased |
2.00 |
15,000.00 |
-1.00 |
$15,000.00 |
-$15,000.00 |
|
| 10/6/2017 | Sales order 02 | Sold |
-3.00 |
-22,500.00 |
-4.00 |
-$7,500.00 |
$1,875.00 |
|
| 10/6/2017 | Purchase order 02 | Purchased |
1.00 |
7,500.00 |
-3.00 |
$0.00 |
$0.00 |
|
| 10/6/2017 | Sales order 03 | Sold |
-3.00 |
-22,500.00 |
-6.00 |
-$22,500.00 |
$3,750.00 |
|
| 10/6/2017 | Purchase order 03 | Purchased |
3.00 |
22,500.00 |
-3.00 |
$0.00 |
$0.00 |
|
| 10/6/2017 | Purchase order 04 | Purchased |
2.00 |
15,000.00 |
-1.00 |
$15,000.00 |
-$15,000.00 |
|
| 10/6/2017 | Purchase order 05 | Purchased |
3.00 |
22,500.00 |
2.00 |
$37,500.00 |
$18,750.00 |
|
| 10/6/2017 | Sales order 04 | Sold |
-1.00 |
-18,750.00 |
1.00 |
$18,750.00 |
$18,750.00 |
|
| 30/6/2017 | Sales order 01 |
-22,500.00 |
||||||
| 30/6/2017 | Sales order 04 |
11,250.00 |
||||||
|
1.00 |
7,500.00 |
The Inventory close will apply the selected Inventory model, in which case is FIFO, and then adjust the cost on the issue transactions accordingly.
Note: If the inventory balance is negative when executing the inventory close, the balance will not be adjusted. A full adjustment can only occur when the balance is positive, and enough receipts exist that can adjust the issues.
Conclusion
By default, all items should have an active cost. If you plan to allow temporary physical negative inventory, which is a valid scenario in certain business, it’s essential to apply an active cost before creating any transactions on the item.
