I think that we all can agree that in the real world there can be no such thing as negative inventory; likewise, inventory on hand accuracy is vital to any company that uses a computer to track its inventory.
In an accounting/business management software system, however, it is quite common to see a negative quantity on hand. This can be caused by poor inventory accuracy or by ‘timing issues.’ For instance, an item can be issued to production before the purchase order is received in the system for that item.
Potential Problems with Negative InventoryGiven that you have good inventory accuracy, there should be no negative inventory at the end of any day. Negative inventory means your replenishment planning system is using invalid data—which yields incorrect reordering recommendations—and your costing system has no chance of reflecting accurate inventory and COGS values.
In Microsoft Dynamics NAV, we can also think of negative inventory as being negative in one location and positive in another location. For instance, in Location A, the system shows 1 on hand, but in location B, it shows -1 on hand. When we look at the total, the system shows 0 on hand, which is accurate when viewed globally. However, this is still not acceptable and needs to be addressed.
An easy way to prevent negative inventory in Dynamics NAV would be to change the system to never allow negative inventory. This sounds good in theory, but I find that in some companies it would be culture shock to do so. You can imagine if, at the end of a month, quarter, or year, the boss asks why you didn’t make the shipment that would have made the company’s shipping goal, and you answer, “Because the system wouldn’t let me post negative inventory.”
It is best to identify the areas that are causing a negative inventory number to occur and then correct those issues. However, because there can be many reasons for negative inventory numbers, this blog post focuses on identifying negative inventory and determining the cause rather than correcting the issues.
Finding which items have negative inventoryTo discover what items have negative inventory in total is quite easy. In Dynamics NAV, go to the item list and set a filter for Quantity on Hand as less than zero, which will display a list of items showing negative inventory.
To find the items that have a negative inventory in one location only, you will need to access the Item Ledger Entry Table. Follow these steps:
- Go to any item card.
Item Card - Quantity on Hand
- Drill down on the Quantity on Hand. This will bring you to the Item Ledger Entries for that item.
Item Card - Ledger Entries
- To see the Item Ledger Entries for all items, clear the filters set by the system.
Item Ledger Entries - Clear Filters
The system will display a list of all of the Item Ledger Entries for every item.
Item Ledger Entries - Display All
- Now we want to make a list of all of the items with negative inventory, either in total or in one location only. To do this, set a table filter with Positive = NO and Open = YES.
Item Ledger Entries - Filter on Negative
The system will display a list of all Item Ledger Entries that need to be corrected.
Item Ledger Entries - Display Negative Entries
Now that you have identified the negative inventory entries, you can correct them. Keep in mind, however, that inventory values should never reflect a negative value, so it is just as important to correct the root cause of why the inventory went negative in the first place.