This article is relevant if you are using NetSuite’s inventory ledger and you need to reconcile to a warehouse or third party logistics (3PL) on-hand quantity.
Background
It is becoming more common for me to work with clients that seek to use NetSuite to maintain a perpetual inventory ledger but work with a third party logistics (3PL) organization to support all the warehouse operations. In these cases, my firm is engaged to design the integration with their third party’s warehouse management system (WMS). Readers may be interested in my 2018 article, NetSuite Warehouse Management Systems Integration Patterns.
The integration is one matter, but the actual reliability of the information depends heavily on the practices between all the parties. It’s important to develop an internal control environment to help operations managers assess the reliability of the inventory ledger information in NetSuite. To do this, the best practice is to regularly compare inventory on-hand between systems.
General Technique to Compare NetSuite Inventory to External Inventory Systems
In general, to assess if the NetSuite inventory ledger is in sync with the external inventory ledger, we seek to determine if the value of on-hand quantities are equal. Most folks will export on-hand information from NetSuite, load it into a spreadsheet, and then take a spreadsheet of the third party’s inventory on-hand and use a vlookup formula to “join” the information so that a formula comparison can be done to see if the quantities differ.
With that pattern in mind, we can use the power of NetSuite’s flexible database engine to build a table to hold the third party’s inventory quantities on-hand and then link this to NetSuite’s item database. We then can use the power of saved search to join the values and compare inventory quantities to find the differences.
Set Up a Custom NetSuite Table to Hold Inventory On-Hand
The following simple table structure can be used to hold the third party information:
- Item: we need to link the third party item to NetSuite’s item. The assumption is that the item name is exactly equal.
- On-Hand: we need a numeric field to hold the third party’s inventory on-hand. While this can be an integer, a decimal may be appropriate for fractional requirements.
Because inventory management databases are transaction-oriented, we generally want to “snapshot” (take a picture) of the third party inventory on-hand values at an agreed-upon time so that we can understand what their ledger indicates is on-hand. This can then be used to review NetSuite’s on-hand values at the same agreed-upon time.
However, our simple table can be enhanced so that it is easier to work with. Consider the following extra fields:
- Date: this is the date for which the inventory on-hand is represented. Without this date value, we need to effectively purge the table each time we need to use it so that we can fill it with fresh snapshot data and not have old values mislead us.
- Location: we may need some type of location information that is related to NetSuite location (or bin) information to help us cross-reference values.
While we can delete information from the custom table, it’s easier to use NetSuite’s built-in tools (e.g., CSV import or our Record Import Export Manager tool) to insert more and more data. Plus, having the inventory on-hand date from the third party over time can allow for new time-based information reporting possibilities.
Timing for Inventory Reconciliation
When attempting to compare two systems, we need to find a time when we believe both systems are not processing inventory transactions. For example, for one of our clients, Sundays represented the best day because all the previous day inventory movement should be complete and recorded and there were no shipping and receiving activities supplied on Sundays. Likewise, all of the related item receipts and item fulfillments would have been recorded in NetSuite by Sunday and thus the two systems should be conceptually aligned. Of course, in any situation, it’s important to make an assessment of when records should be stable enough to allow for a meaningful comparison.
Inventory Comparison Considerations
Since NetSuite is a real-time perpetual ERP system, we need to be aware of the way it holds information so we can compare the inventory on-hand.
- By Item: the easiest to understand is to connect by each item which tells us what our on-hand inventory value is, by location, right now. This technique does not allow us to go back in time to see what our inventory on-hand value was. This technique can work if we compare when there is no inventory movement activity in the database. In our example, this technique works when we run the report on Sunday but stops working as soon as the inventory activity starts again on Monday. Consequently, we need to get the report produced on Sunday — else the information will be out of phase.
- By Transaction Summary: the more powerful, yet more demanding approach, is to roll up all inventory related transactions up to a particular point in time so that the comparison is in the right timeframes. See my article, Explain NetSuite Inventory Quantity, Value and Average Cost Over Time, to learn how get to a particular balance on a specific date.
Click on related images to understand how we connected our client’s NetSuite item database to a third party logistics on-hand table called “XB”. In this saved search definition, I offer up some techniques that illustrate more advanced Saved Search capacities:
- Compare Inventory Location: by linking inventory location IDs, we could get more flexible criteria filtering.
- NetSuite “Inventory Location” with Location On-Hand: NetSuite’s Saved search technology can be confusing when doing an item search. The Inventory Location field, not the Location field, is the real requirement needed to anchor the on-hand value. Without it, you get an exploded view of on-hand values across all locations.
- Inventory on-Hand Comparisons: with the two on-hand values, you can use simple math to find those items whose differences are not equal to zero. These are the discrepancies. When working with the transaction summary, we use an advanced Criteria Summary search to only focus on these values where there are differences ignoring everything that is as expected.
Solving for Inventory On-Hand Discrepancies
Once we have a search that helps us see we have differences, we then can perform the investigative work to discover why the ledgers are off. All kinds of considerations may come forth in this discovery; depending on how each party acts with inventory and the information system, the reliability of information is subject to logical interpretations about the possibilities in comparison. For the purposes of this article, not much more should be said. However, what I have observed is the practice and care for these inventory values often reveal that operations managers and their respective peers in third party companies can usually work to enhance their inventory procedures; and this is the point — to develop more trustworthiness in all coordination affairs.
Work with NetSuite Professionals with Strong Control Practices
In my mind, the power of the NetSuite platform reveals itself in our ability to craft custom objects to help us hold information while we use the saved search technology to look up and aggregate. With the platform, we can fully automate the consumption of the inventory on-hand and generate a report to distribute to managers on a regular basis. This can help organizations understand if they are under control.
If you found this article meaningful, feel free to receive notifications of new articles as I publish them. If you have a challenging Netsuite inventory situation, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
