Marty Zigman Marty Zigman
Prolecto Labs Accelerator Templates

How to Include Supplier Available On Hand Inventory Values in NetSuite

NetSuite



This article is relevant if you are a NetSuite-driven products distribution company that does not stock inventory on hand but needs to understand and publish supplier inventory available.

Background

For many distribution companies, especially those that sell goods online (eCommerce), the idea is to only take orders where delivery is assured. Said another way, to build and reinforce customer trust, it’s not a good idea to take an order but not be able to deliver in a reasonable amount of time.

One of our clients in the do-it-yourself (DIY) pool equipment supply industry uses a mixture of in-stock warehouse and drop ship fulfillment flows. The product catalog offered on the client’s eCommerce website is deep (i.e., a large catalog of offerings spanning many different categories). Offering esoteric and infrequently sold parts can help attract the kind of customer that hunts around the Internet looking for parts that are hard to find. This deep catalog practice to attract customers to the eCommerce website is not uncommon. Yet, the idea of producing trust to deliver goods is the fundamental opening move to build customer loyalty. The cost to attract the customer to an online retail site needs to now translate into delivery promises so that the acquisition costs produce a long-term profit.

Distribution organizations that will not stock inventory but use a network of suppliers need reliable information about inventory supply availability. The eCommerce shopper generally can care less if the distribution company owns the inventory or not; they care about receiving the goods within the expected receipt date. How can we leverage supplier information as if it is our own to make delivery promises to our customers?

NetSuite’s Inventory Ledger for Vendor Information

The native NetSuite inventory system offers a baseline architecture for stocking inventory. The primary assumption is that the organization owns any inventory on hand (note, for warehousing companies that are holding inventory they do not own, consider my 2023 warehousing article, How To Drive a Warehouse Business Using NetSuite).   We generally need to produce an item receipt transaction to get inventory on hand into NetSuite. This naturally has all the desired consequences of maintaining an inventory value on the balance sheet.  NetSuite goes further and offers up an item-oriented vendor part/price schedule.  This makes sense as it assists in the decision-making for procurement practices.

Yet, we are after having inventory available or on hand for something we do not own. This concept is not modeled in the NetSuite platform.

Extending NetSuite’s Inventory Ledger for Supplier On-Hand Information

Fortunately, the NetSuite platform gives us the capacity to enhance the model to take care of the business requirement. Here is how we solved this challenge for our client.

For our client, we used the term Vendor Managed Inventory or VMI in our solution.  Upon more precise thinking, VMI is a misleading concept for how we solved the challenge. Vendor Managed Inventory is a supply chain management strategy where a supplier manages the inventory at the customer’s location. The inventory is owned either by the customer (VMI without consignment) or the supplier (VMI with consignment) but maintained by the supplier.  Instead, a better term might have been “Supplier Available Inventory on Hand” to express the method we used.

Without respect for what we called it, the solution offers the following:

  1. Extend the NetSuite Vendor Price/Part Schedule: The missing element in NetSuite’s native vendor price schedule is the inventory elements.  Because NetSuite does not allow the extension of that structure, we created a custom table holding all the data attributes we need.  With our table, we could hold multiple suppliers’ information about our unique selling item giving us more capacity to deliver to our customers across the supply chain.  Click the image to see the related structure.
  2. Supplier Available Inventory Feeds – Data Gathering Programs: with a table to hold information, we now need to work with each supplier to determine how we can gather information about their inventory availability at a regular frequency. Naturally, we started working with the client’s largest supplier because we would get the most return from our efforts.  Each supplier offers up different methods for collecting batched (feed) data. Our license-free high-performance tool, expressed in my 2020 article, Fully Automate Complex NetSuite Data Imports, provided a framework to ingest large amounts of inventory on hand data.  Naturally, we captured the latest pricing information along the way to help us in our procurement practices.
  3. Intelligent Available on Hand Lookups: the data supplied by the vendors naturally gets stale the moment you ingest it. Meaning, the trustworthiness of the suppliers’ quantity on hand diminishes as time passes. Thus, we needed to build a confidence factor based on our understanding of the number of times the inventory turns at each supplier and the staleness of the data.
  4. Available Inventory on Hand Feed to eCommerce: we then modified the inventory available on hand that we were feeding to the eCommerce site so it could express reliable “out of stock” information. Based on our confidence level, we could blend our true in-stock inventory on hand (in our warehouse) with supplier inventory available on hand to represent our “virtual” available inventory. We tuned the algorithm as we learned more about what we could trust about each supplier’s performance. The eCommerce system and the shopper knew no difference, but once the order arrived in NetSuite, we could confidently take over the fulfillment narrative.  When we did not have on-hand stock, we would use Drop Ship with updated pricing to the optimized supplier with high confidence for delivery. Note, NetSuite-powered organizations should be very careful with native Drop Ship due to the mismatches in revenue and costs producing poor item-level margin information. Please consider my 2016 article, Solved: NetSuite Drop Ship Purchase Accruals.
  5. Sales Order Driven Fulfillment Lookup Tool: at the beginning of the implementation, where all actors were learning, we developed an inventory available inventory lookup tool driven from the sales order. The idea was to see all the inventory sources for the order.  By providing a confidence factoring filtering method, we could have the system suggest the best approach for fulfillment. With the latest pricing available as well as quantities, our decision-making was better informed.  Selections on this lookup tool then drive the actual order fulfillment strategy on the native sales order by properly selecting location records or preferred vendor and rate information to craft drop-ship purchase orders.  As confidence grew in our practices, we could automate the selections and avoid the use of the lookup tool. Click the image to get a feel for how the tool works.

This model will generally work for all distribution companies that hold these inventory available concerns.

A NetSuite Location Record Workaround Consideration

Exploring the given NetSuite database structures, a workaround approach to holding available inventory quantity would be to use phantom NetSuite locations (or locations/bins) to inject transactions to allow the NetSuite system to offer up inventory available. The idea is that we could dedicate a location record for each vendor. I have seen organizations do this before, especially when they have one or two major suppliers. It can work but the approach is weak for a number of reasons:

  1. Over-Inflated Global Inventory: Inventory on hand across suppliers will over-inflate the global inventory on hand by accumulating quantities across locations.
  2. Financial Impact: getting inventory onto the ledger is going to have financial impacts that are not real. If we are not very careful, even item receipt transactions at Zero value can affect the global inventory on hand value. For the financial statement presentation, the balance sheet will need to be adjusted to remove non-owned inventory.
  3. Transaction Bloat: We will need NetSuite transactions to get vendor inventory on the ledger. But these transactions will bloat the database, require us to exclude these transactions in our day-to-day reporting, and possibly unnecessarily trigger NetSuite’s monthly transaction limits forcing us to a more costly Oracle infrastructure tier.

Drive NetSuite to Produce Competitive Capacities

The above solution demonstrates an important capability that is often not realized by companies seeking to get the promised value from their NetSuite infrastructure investment. By taking a business requirements approach coupled with deep expertise in NetSuite and platform enhancements, we can invent tailored solutions that open competitive capacities.  This kind of capacity is available to those who understand that business software’s real purpose is to make money: to either drive costs down and/or open more revenue resulting in greater profit. It takes both skill and leadership to ultimately deliver on potential.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to tackle your supplier available inventory challenge, 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

About Marty Zigman

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.

Biography • Website • X (Twitter) • Facebook • LinkedIn • YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *