This article is relevant if you are seeking to understand how NetSuite’s inventory ledger accounts for cost changes for different transactions on the same day.

Background

We are often invited to client situations to help accountants and inventory analysts explain the quantity and costing mysteries in NetSuite’s inventory ledger. To facilitate the process, my firm created a saved search that can be attached to an item sublist that will tell the analyst the inventory story transaction-by-transaction.

See these related articles:

• 2016: How To: NetSuite Stock Inventory Movement and In-Transit
• 2018: Learn How To Accumulate NetSuite Inventory Balances via Saved Search
• 2020: Explain NetSuite Inventory Quantity, Value and Average Cost Over Time

This article goes further to help explain the notorious challenges of explaining what is happening when NetSuite creates a “Cost of Sales Adjustment” during negative inventory situations and how to understand that in a same-day transaction-by-transaction context.

NetSuite Cost of Sales Adjustment for Negative Underwater Inventory

When we work with our clients during implementation, we emphasize that negative inventory situations mean that the costing information NetSuite provides is unreliable. It’s one of the matters we must address in helping our clients ensure they have reliable item margins (see 2018 article, Learn how to Reliably Measure NetSuite Gross Profit and Margin). We work with our clients to avoid the situation in the first place by helping them enhance their internal record-keeping practices and properly use item commitments.

Yet, the world is not perfect. When an item is underwater (i.e., negative inventory), as a subsequent transaction that has a cost is recorded, NetSuite uses the opportunity to balance the negative inventory ledger toward the positive and must use a plug value to get the cost right. It can be difficult to understand what is happening unless you can see transaction impacts with full clarity.

Understanding NetSuite’s Intraday Inventory Ledger Impacts

While we provide our Prolecto Stock Ledger search to any NetSuite end-user who makes a request, for analysts that are working on their own searches and explanations, it is important to understand the transaction impact order of operations.

We created a saved search sort column that helps get all the transactions lined up so that we can expose the cumulative impact on quantity and cost. Here is the algorithm as recorded in the text formula result column definition.

/* note, this code is an example only to help demonstrate the sequencing */ 
TO_CHAR({trandate}, 'YYYYMMDD') || 
/* Sequence below based on Answer Id: 28147 [see below]. A value of 9 = unmapped/something wrong */ 
DECODE({typecode}, 
        'InvWksht', '1', /* Assume first-of-day since until Enhancement 585465 is resolved */
        'VendBill', '2', 
        'ItemRcpt', DECODE({createdfrom.typecode}, 
                'PurchOrd', '2',
                'TrnfrOrd', '3',
                'RtnAuth', '6',
                '9'
        ),
        'InvAdjst', (CASE WHEN {quantity} >= 0 THEN '2' ELSE '5' END),
        'Transfer', '3',
        'InvTrnfr', '3',
        'Build', '3',
        'ItemShip', DECODE({createdfrom.typecode}, 
                'TrnfrOrd', '3',
                'VendAuth', '4',
                'SalesOrd', '5',
                '9'
        ),
        'VendCred', '4', /* Not covered in Answer Id: 28147 but confirmed by NS support as the right mapping */
        'Unbuild', '4', 
        'CustInvc', '5', 
        'CashSale', '5', 
        'CashRfnd', '6', /* Not covered in Answer Id: 28147 but confirmed by NS support as the right mapping */
        'CustCred', '6',
        '9'
)
|| TO_CHAR({internalid}, '0000000009')

Click on related images to see full size.

Consideration for NetSuite Inventory Worksheets

One challenge is that NetSuite Inventory Worksheets give the end-user an opportunity to indicate if the impact should be at the start of the day or the end of the day. Unfortunately, this field is not exposed to saved searches and thus, without resorting to a custom field, we have to make some assumptions about the sorting order of that transaction. If your policy for the use of this field is consistent, then a simple refinement of the algorithm is all that may be needed.

Note, NetSuite has an Enhancement 585465 which is to be able to pull up the Transaction Order field in an Inventory Worksheet Saved Search. I recommend voting on it so we can see this field and properly account for it.

Get the Prolecto Stock Ledger to Explain Intra-day Inventory Balances

For NetSuite end-users seeking to gain better insight into NetSuite’s inventory movements, the Prolecto Stock Ledger is available simply by making a request.

Our philosophy is to build artifacts, documents, algorithms, and applications that illustrate our prowess to innovate and bend the NetSuite platform to our will.  We are transparent in that we should be bringing the best of our talent and all previous accomplishments to situations to get the job done.  It’s no wonder that we are frequently asked to tackle complex concerns where others remain perplexed.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like to request the Prolecto Stock Ledger or would like to express your inventory challenges to experts, 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