This article is relevant if you are looking to explain NetSuite inventory movement by accumulating the quantity and value as transactions happen over time.
Background
Many times, when working with inventory, we need to better understand how the quantity balance or value changed over time. When trying to explain how the quantity balance went negative (underwater), it may be important to diagnose the sequence of events that led to the situation.
In my mind, I categorize these concerns under the topic of inventory movement. Meaning, we need a chronological account for how the balance changes over time with an explanation for the current balance as reported by NetSuite.
Using NetSuite Advanced Saved Searches to Account for Inventory Movement
The key to understanding NetSuite inventory ledger is to know that all item transactions which post to inventory-based general ledger accounts effectively make up the ledger balance. Thus, if we can organize our transactions in the right row order, and then use formulas to add the previous row to the current row, we can effectively produce a cumulative balance. See my article Solving the NetSuite Cumulative Saved Search Tally Challenge.
I previously wrote an article on how this was achieved: How To: NetSuite Stock Inventory Movement and In-Transit.
In this article, I seek to demonstrate further the outputs and provide additional screen shots to illustrate how this is done. I recommend clicking on each image to learn the pattern.
Get NetSuite Database and Accounting Assistance
The article is designed to show that we can indeed get at the information in NetSuite if we learn how to put saved search to work. These concepts are generally comfortable for individuals with database and accounting background. If you are looking for strong support to enhance your NetSuite experience, let’s have a conversation.
See Related Articles
- How to Configure and Optimize the NetSuite Inventory Stock Ledger
- Explain NetSuite’s Last Purchase Price
- Understand NetSuite’s Intraday Inventory Ledger Costing Impacts
- Understand NetSuite System-Generated Accounts
- Explain NetSuite Inventory Quantity, Value and Average Cost Over Time
- Learn How To Craft Better NetSuite Financial Saved Searches
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
This is great, but I noticed that the cumulative amount is not taking into account landed costs on item receipts. It’s being excluded by the Special Account Type criteria. Any thoughts?
So I figured out it’s not the Special Account Type criteria that is keeping those out. Landed costs are InvAsset special account types. It’s the quantity not zero criteria. When I removed that, it worked. But now I’m not entirely sure why that criteria was necessary in the first place.
Now I see that removing the quantity = zero criteria to include landed costs results in cumulative quantity and amount values that are ultimately correct, but get all wonky within the item receipt rows.
You can see what I mean in the screenshot below. The quantity and amount cumulative values correct themselves by the last row item fulfillment shown, but are wrong/out of order until then.
https://cl.ly/2W1D1321091Y
I seem to have gotten the order working correctly by adding a third “sort by” field of “Formula (Text)”. I honestly can’t tell you why that worked other than I had a hunch it might so I gave it a shot.
Sorry to blow up the comments so much, but now that I’ve gone down this rabbit-hole I figure other readers with the same issue may want to know.
My “Formula (Text)” sorting solution improved the sorting situation, but hasn’t completely fixed it. I found an item receipt sequence where the rows are still out of order relative to the cumulative amounts.
Back to the drawing board.
Jeff,
Thanks for working on getting the landed cost dimensions included. I will need to think about this because those elements show up as additional GL lines. This is why I used the criteria of where there is a quantity to make sure we hit the ledger row. Thus, for now, the value column is effectively the PO rate and not the fully burdened cost (less than ideal).
No problem. Thank you for this helpful article!
Why use the Special Account Type criteria vs. Account Type vs. actual account numbers for inventory accounts? I can’t figure out how one designates an account as an InvtAsset special account type in the first place. I only have one account designated that way, which makes me think I should just select it’s account number in the search criteria instead. Any drawback there?
Hi Jeff,
I use the Special Account Type to be sure I am on NetSuite’s inventory account in any account. I don’t though have a hard rule for this.
Marty
Great post Marty. Is there a way to include item quantities sold within kits so the cumulative quantity equals the quantity available? Thanks
Hi Chris,
That is likely hard with a single search. Did you see this article?
https://blog.prolecto.com/2016/07/10/how-to-generate-netsuite-kit-item-members-weighted-price/
We now solve these kinds of situations with our Content Renderer Engine (CRE) as we can meld multiple searches together. But that takes some creativity to get there.
Marty
Hi,
How to retrieve a php save search ?
The function InventoryBalanceSaveSearch does not exist
Hello Nicolas,
I believe you may want to check NetSuite fundamentals here. PHP is not used on the platform. And the Saved Search I demonstrated I crafted to illustrate the power of the technique.
Marty
Hi Marty,
Special Account Type is only filled by Accounts that have been system-generated, correct? in this case, i will probably have to work with standard “account type” and find a proper filtering.
Ingo
Hello Ingo,
I believe you are correct. If the Special Account Type does not give you want you need, use Account Type.
Marty