This article is relevant if you would like to understand how to explain NetSuite’s inventory quantity, valuation, and average cost using running balances over time.
Background
Back in 2015, I wrote an article to help solve the challenge of tallying information using NetSuite Saved Search. From that article, in 2018, I wrote an article, Learn How To Accumulate NetSuite Inventory Balances via Saved Search. These articles lay down the technical foundation to explain how to accumulate values, or running balances, over multiple records.
In a recent client implementation, a client was having trouble understanding how their average costs were dramatically changing from the start to the end of the accounting period. To answer this question, I offered that if we can see average costs change every time there was an item movement, we could spot the culprit transactions.
Generated Item Stock Ledger Movement Explanation
I went to work to produce the stock ledger that would explain the current balances on the inventory ledger by location. The key to this is to understand that NetSuite is keying on the item’s respective inventory general ledger account; if you accumulate all transactions that affect that balance sheet account, you can explain the current balance (or any balance up to a point in time).
Using the knowledge in that previous article, I enhanced the searches to include an average cost component. Now you can see the average cost value as each new transaction comes into play. It then becomes easy to spot a significant change.
Get the Inventory Stock Ledger Saved Search
Given the value of this search in inventory situations, I bundled it up to make it available to the customer community. The bundle will automatically create an inventory stock ledger sublist for your inventory items. It also provides the related saved search with an item and location filter allowing you to selectively run queries ad hoc.
The bundle is available to all NetSuite customers by making a request and providing me your NetSuite Account ID. Naturally, if you need help to refine the search and solve interesting challenges, my firm is here to help. 
If you found this article meaningful, sign up to receive notifications of new articles as I post them. I promise not to send you unsolicited emails. If you would like to get more out of your NetSuite investment, let’s have a conversation.
See Related Articles
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
Was seeking help on how to retrieve the average cost per location on each line?
Perhaps you can elaborate more on your objective.
Marty
i have tried to create a saved search to calculate de GMROI KPI. but i find that I don know how to evaluate total inventory cost for an item at a diferent date than today.
of course this is possible using a inventory valuation report. but in a saved search i want to create a formula to calculate things like average inventory amount for a period of time and average inventory cost, thus i need to evaluate the field {item.totalvalue} and {item.quantity} at specific dates (start of perior and end of period). but i dont know how to write this on a formula.
Can you help me?
Hello Dario,
We give away the saved search for NetSuite end-users who just ask. If you study the saved search, you should be able to get the information on the specific dates. It’s a more advanced use of the Saved Search. If you need our help, reach out to me here:
https://www.prolecto.com/contact-us/
Marty
That would be great!, can I have the saved search to study it? 🙂
I wrote on the contact us section also.
Best regards.
Thanks Dario,
I see we have connected via https://www.prolecto.com/contact-us/ and I have provided the software. Best!
Marty
Hi Marty! Will this work in combination with Landed Cost?
Hello Gea,
Yes, because landed costs increases the value of the inventory on hand and thus will get averaged. It is just one of the values that comes back from the search.
Marty
Hey Martry,
Would love to try this out! How do i get access. Thanks!
TM
Please reach out to us at the following link and supply your NetSuite account ID so we can provision the tools:
https://www.prolecto.com/contact-us/
Marty