August 5, 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 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.
- Drill down on the Quantity on Hand. This will bring you to the Item Ledger Entries for that item.
- To see the Item Ledger Entries for all items, clear the filters set by the system.
- 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.
- Login Error: Communication protocol mismatch between client and server
- Creating a Date Table in Power BI
- How to Make Measures Total Correctly in Power BI Tables
- The Microsoft Technology Stack – What It Is and Why You Should Care
- The Top Eight KPIs Retailers Should Be Tracking (with Formulas) for Your Retail KPI Dashboard