Tuesday, March 8, 2022

Inventory Level Search

As I deal with users from outside of accounting & finance teams, I heard many complaints around inventory reports. For accounting & finance people whose main focus may be on the valuation of the inventory and reconciliation, inventory reports that NetSuite provides out of box may be well sufficient.

However, people who are in charge of business units that rely on the inventory throughout multiple locations, they find NetSuite's report not friendly at all, and I personally find this issue especially for operations management or hardware repair environments with large inventory (the specific organization I am referring to held more than $2MM+ worth of inventory in only one of the warehouses).



Also, one interesting issue is that if you are a long-time NetSuite customer, if you inactivated items that had inventory on hand, then it is almost impossible to reconcile quantity on hands to the inventory valuation report (although I believe NetSuite had filed defect and changed behavior maybe around 2015 or 2016, so that when you inactivate an item, all the inventory information is wiped out, instead of preventing users from inactivating items with inventory on hand - what a great workaround).

I am adding a few SQL formulas you can use on your item saved search, which can help you to generate the report you may need. If you are interested in some more examples, please let me know - I will be more than glad to investigate and share the information with you!

But frankly speaking, NetSuite's SuiteAnalytics came along such a long way that you probably don't need to use SQL but use enhanced search fields such as Inventory Details Fields...

I find this usually helpful, because if the inventory was never touching a location, then the quantity is NULL, not 0, so you can easily exclude items that you will never see at a certain location by using below formula as criteria and results:

Formula (Numeric) > not equal to 0 > Formula: NVL(DECODE({inventorylocation},'location name',NVL({location.quantityonhand},0)), 0)

Also, you can use the formula below to see any item that currently has a PO issued:

Formula (Numeric) > not equal to 0 > Formula: NVL(DECODE({inventorylocation},'location name',NVL({location.quantityonorder},0)), 0)

Of course, you will need to adjust results, and creating the results page may be more painful than it may sound. We will simply have to create each column for each location using Formula (Text) fields.

Formula (Numeric) > not equal to 0 > Formula: NVL(DECODE({inventorylocation},'location name',NVL({location.quantityonhand},0)), 0)

To make it more interesting, you can also add multiple formula fields to get results that you need:

Formula (Numeric) > Formula: NVL(DECODE({inventorylocation},'Location 1 Name',NVL({locationquantityonhand},0)),0) > Custom Label: Location 1 Quantity on Hand
Formula (Numeric) > Formula: NVL(DECODE({inventorylocation},'Location 1 Name',NVL({locationquantityonorder},0)),0) > Custom Label: Location 1 Quantity on Order
Formula (Numeric) > Formula: NVL(DECODE({inventorylocation},'Location 2 Name',NVL({locationquantityonhand},0)),0) > Custom Label: Location 2 Quantity on Hand
Formula (Numeric) > Formula: NVL(DECODE({inventorylocation},'Location 2 Name',NVL({locationquantityonorder},0)),0) > Custom Label: Location 2 Quantity on Order

Or, if you are a supply chain manager and would like to review the reorder points or preferred stock level, you may replace {locationquantityonorder} variable with {locationreorderpoint} or {locationpreferrestocklevel}.

So, here is my question back to you. If you are an operations director - or maybe a consultant or administrator helping out the operations director - how would you build the search so that your current stock and POs are enough to the orders outstanding?

I used to have a solution at my previous employer but not anymore due to the lack of test data to play with... but I will post my answer below. If this formula does not work, please let me know and I'll work in the sandbox get the test data generated and review the formula myself!







MY ANSWER:
Once you built the criteria you need, below is the formula I will use:

Formula (Numeric) > Formula: (NVL(DECODE({inventorylocation},'Location 1 Name',NVL({locationquantityonhand},0)),0) +  NVL(DECODE({inventorylocation},'Location 1 Name',NVL({locationquantityonhand},0)),0)) - (NVL(DECODE({inventorylocation},'Location 1 Name',NVL({locationquantitycommitted},0)),0) + NVL(DECODE({inventorylocation},'Location 1 Name',NVL({locationquantitybackordered},0)),0)) 

Then I will go to Highlighting tab, add the exact same formula to the condition to flag any items that may run into inventory issues!



I hope this post helps you with inventory reporting issues you may have at your organization. What are some of the other key challenges you encounter from day-to-day usage of NetSuite? I'm waiting to hear from you!

No comments: