This article is relevant if you are working on a NetSuite-driven inventory stock ledger report to help explain item-level costing and quantity changes.
Background
I have written a number of articles designed to help the NetSuite community better understand NetSuite’s inventory ledger. Consider these articles:
- 2022: Understand NetSuite’s Intraday Inventory Ledger Costing Impacts
- 2020: Explain NetSuite Inventory Quantity, Value and Average Cost Over Time
- 2018: Learn How To Accumulate NetSuite Inventory Balances via Saved Search
- 2016: How To: NetSuite Stock Inventory Movement and In-Transit
I have published a free download for all NetSuite end-customers that supplies the inventory stock ledger saved search and sublist.
At the time of this writing, the stock ledger tools have been installed almost by 200 different clients. One NetSuite administrator reached out to me to understand better how NetSuite average costing works by item-based serial numbers. In the dialogue, I could see that we needed a conference call to help listen to their concerns and confirm our saved search could address his questions.
Refining the Stock Ledger Configuration to Specific Account Requirements
During the discussion, which is demonstrated in the video below, it became clear to me that I needed to write this article to help the general community understand other important aspects of the NetSuite inventory ledger. Further, this article could then help NetSuite administrators dial in the saved search against their accounts.
Saved Search Criteria
The saved search makes an assumption about how to identify NetSuite inventory accounts. NetSuite offers a “Special Account Type” called “InvtAsset” which stands for Inventory Asset. When you first install NetSuite, a default inventory account is created for you, and it is marked as a special inventory asset account. Yet, you can create more Current Asset general ledger accounts for tracking inventory, and this Special Account Type value may not be set. It appears that the underlying nature of NetSuite does not need to have InvtAsset set for proper accounting — it’s only there for reference.
Thus, it may be necessary to remove the Special Account Type criteria and simply reference the Inventory Accounts used to track your inventory.
Click the related image to see an illustration.
Date Related Information
The inventory stock ledger works because it rebuilds the transactional storyline from inception to today. In order to do this, it is important that all the related transactions are in scope from the start of time.
Some Administrators would like to tell a story from a specific point in time. You can not start at a point in time because we need since inception information, but you can end at a point in time to determine what the cost and quantity were at that date. Thus, the only date criteria you can add is “On or Before” a date so that all the transactions from inception are contemplated.
Stock Ledger Sorting
A special formula field takes into account the date and contemplates the intra-day transactional activities as discussed specifically in my 2022 article, Understand NetSuite’s Intraday Inventory Ledger Costing Impacts. Thus sorting, as supplied by the search, should be left alone so that we emulate what the NetSuite inventory costing engine is producing to get the same explicit and revealed results.
Including Lot and Serial Numbers
The lot and serial number structures can be included with some refinements. It requires modifying the values for the quantity factor, as we are summarizing now on the “Inventory Number” sublist. The video below discusses this.
Watch Video for How to Configure and Optimize the NetSuite Inventory Stock Ledger
The video below (click to open in new window) [17:11] is with a NetSuite administrator as well as his fellow inventory analyst and accountant, who are trying to answer questions about serial number costing. The discussion is relevant to NetSuite administrators who are wondering why their stock ledger is not returning results or who would like a better explanation of how the saved search works.
Specialized Tools to Drive Inventory Questions
This stock ledger is a great resource for learning about what is going on with NetSuite inventory quantity and values over time. The most common concern I hear is what is “Cost of Sale Adjustments” which is indicative of Negative or Underwater inventory. This saved search can help diagnose what internal practices are causing this.
Naturally, many other questions come for which we have developed specialized tools. Consider these articles:
- 2023: Explain NetSuite’s Last Purchase Price
- 2020: Distinguish between NetSuite Inventory Aging and Activity Reports
- 2018: Solving the NetSuite Aged Inventory Challenge
- 2013: NetSuite Kit Items and Serial Numbers
For example, I can imagine a tool that supports answering a question that an accountant could ask, “What inventory items have changed in value this accounting period for which vary by a specific percentage?”. NetSuite’s saved search alone can’t answer such questions. But tools, such as our Query Renderer can.
A major way we add value to a support relationship is to give these tools to our clients without a license charge. All that we ask is for you to make a request. Most certainly, we help our clients by leveraging our existing algorithms to solve their specific requirements.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like the Inventory Stock ledger or need help with your inventory-related 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
Marty thanks for sharing that video. Seems like the client kept thinking they could see the cost for a specific serial number but they didn’t understand that they chose average costing not specific identification costing. You pointed that out that the weighted avg has to include all items. So then as a good consultant I would challenge why are you enduring all the pain of serialized inventory but then not taking advantage of specific identification costing? hat seems stupid decision to me. They want to switch to group average which will require creating all new items so why not switch to specific identification? And I always setup all items as group average with 1 location in the group (which is same as avg costing) just to lay the foundation in case they do want more locations in the group later.
Hello Nick,
I always thought of Serial Number items as specific identification for costing. But I since learned that you can indeed choose average costing (or even standard). Thus, for someone that just wants to track the serial numbers for logistics, but less for costing, it appears you can go with average. The same is true for Lot Numbered items.
The configuration for Group Average with one location is forward thinking. Thank you.
Marty