Skip to Content
0
Former Member
Nov 29, 2010 at 04:37 PM

Negative Inventory Adjustments

856 Views

We have a prospect that, for various reasons, must occasionally ship product which hasn't yet been recorded as a receipt. Although we have discussed the disadvantages with them, they cannot entirely avoid negative inventory situations. Their legacy system has an enhancement that makes after-the-fact adjustments when negative inventory is replenished, and they would like a similar capability in SAP Business One. They value inventory with FIFO. It seems that a custom DI-API program might be able to accomodate this need, and I'm hoping for feedback from the community.

When a shipment occurs with an insufficient quantity for an item, the COGS account is updated using the last (now exhausted) FIFO layer cost. The Gross-Profit fields in the document are updated correspondingly. For example, assume you have an item quantity of 2 purchased at $10 plus 2 at $12. You sell and invoice 6 units at $20. Sales revenue is credited $120 and the BP is debited $120. The sales side of the transaction is correct. Inventory is credited $68 (2 at $10 and 4 at $12), and COGS is debited an equal amount. The Inventory Audit report shows a quanity of -2 and a value of -$24.

When a receipt occurs for an item with negative inventory, the COGS-Negative-Inventory account comes into play. Continuing the example above, assume that we enter a receipt for the 2 missing units at $14 ($2 above the last cost). The Inventory account is debited $24 (the 2 missing units at $12), and the COGS-Negative-Inventory is debited $4 (the difference between the anticipated cost of $12 and the actual cost of $14, times the quantity of 2). Unfortunately, no change is made to the Gross-Profit values in the AR Invoice, and the Sales Analysis reports the transaction profit based on the understated cost of $11.33.

After the receipt, the true unit cost is known to be 2(101214)/6 = $14. COGS has already been updated by $68, $4 short of the actual cost. The goal is to increase the AR Invoice Gross-Profit from $68 to reflect the actual total cost of $72, and to move the $4 from COGS-Negative-Inventory to the normal COGS account. Updating the AR Invoice fields and creating a Journal Entry to transfer the difference should put everything into agreement.

Obviously, a number of tables would need to be searched (including OINM) to identify the original sales transaction(s) that might be affected by a receipt of an item in a negative situation. The logic would have to accomodate both cost increases and cost decreases, as well as partial replenishments at multiple costs. The result would not necessarily completely flush the COGS-Negative-Inventory account, since negative situations can be caused by transactions other than sales shipments. Also, while 2007 does not allow changes to the invoice Gross-Profit fields after the document has been added, it seems that 8.8 does. The program would be run on a periodic basis (e.g., nightly) and keep track of which receipts and sales documents have been updated. Once all negative quantities have been updated in a sales invoice, the Gross-Profit report would be correct and in alignment with the COGS account.

I would greatly appreciate any feedback on this concept. Even better, if anyone has done something similar, it would be helpful to know your results.

Thanks in advance!

Dave