Thursday 7 August 2014

Managing Negative Inventory in Microsoft Dynamics NAV

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 Inventory

Given 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 inventory

To 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:
  1. Go to any item card.
Item Card - Quantity on Hand
Item Card - Quantity on Hand
  1. Drill down on the Quantity on Hand. This will bring you to the Item Ledger Entries for that item.
 Item Card - Ledger Entries
Item Card - Ledger Entries 
  1. To see the Item Ledger Entries for all items, clear the filters set by the system.
Item Ledger Entries - Clear Filters
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
Item Ledger Entries - Display All
  1. 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
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
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.

Regards,
Sathish

No comments:

Post a Comment