[Susan Lambert]

Hi all!

I’m not sure if it is possible to proiduce a report that will show me the obsolete and slow moving stock using NetSuite.

If anybody knows how or where this report can be produced I will be one very grateful lady.

Cheers

Sue
This is a cached copy. Click here to see the original post.

[Olivier Gagnon NC]

RE: Obsolete & Slow moving stock report

There’s an Inventory Turnover report under Reports->Inventory.

If that doesn’t do the trick you should be able to build a saved search to show what you want. Could make a transaction saved search, sum the qty being fulfilled, and set a filter to spot what you define as slow-moving.

[AlanW]

I have not come across nor have been able to develop any meaningful search or report that provides the right information for effective management of slow moving or non moving stock. I will also be grateful to find out if any member of the User Group has got a good solution.

[al3xicon]

AlanW – how do you define slow- or non-moving stock? Generally I think that most companies vary in this definition, so the best bet is, once you determine what it means to you for inventory to be slow-moving, then build a saved search identifying those items as Olivier Gagnon NC mentions. In the past I have come up with various kinds of saved searches for clients, for multiple purposes. You could generate a ratio/index of total current value on hand to total average monthly value moved over the last 365 days, and items with a high ratio number/index could be identified as slow movers. If you don’t necessarily care about differences in inventory values / carrying costs, then simply identifying which items haven’t had a sale in the last X days may be sufficient. There are many factors that would be specific to your business, I imagine.

[AlanW]

Hi Al3. With Non-Moving Stock, on our previous system, we ran time based determinations of non moving stock, usually last 3 months, 6 months, one year, and two years. We run each time base separately, depending on what actions we intend to take as follow up action. So the variable in the report will be the time period we need to work on. With that in mind, the fields we need to report on are, Subsidiary (no heirarchy), Class, Item Code, Item Sales Description, Not sold between dates – “Filter”, Quantity in Stock, Average Value per Item, Last purchase price, Total Value (of each item) in Stock, Date item last sold, Date item last receipted or adjusted into stock, Preferred Vendor.

Sorted by class then Item Sales Description

Subtotal by Total Value by Class

With Slow-Moving Stock, we need to pull the same fields in the report as above, but a further field which generates a ratio/index of total current value on hand to total average monthly value moved over the last 365 days {or last XXX days}, and items with a high ratio number/index could be identified as slow movers. This generated field could be included within the Non Moving Stock report.

Is this possible?

[al3xicon]

AlanW it is possible, however it sounds like you’ll want to start creating some custom item fields that store some information like “last receipt or adjustment date” etc, so that you can leverage those in your searches and not have to use search joins all the time.

The slow-moving stock report leveraging the ratio is a good idea – that’s what we are using as well. I would caution you, however, that considering “movement” you may need to look at multiple transaction type – if you use Kits, particularly, but also if you use Assembly Items. Do you use either of these? Also how many Items do you have?

[AlanW]

Hi Al3. Is using search joins problematic? How do you create a search join as this is an area I fall short when designing a search? We do not have kits and only a handful of assembly items. We have around 5,000 inventory items.

[al3xicon]

AlanW – using search joins to report on transaction detail when building an item search is problematic. This is mainly because of how NetSuite performs the searches – essentially, for each item you have (5,000), there are potentially multiple transaction lines every day – this could mean hundreds in a given accounting period (more or less depending on how frequently you sell goods).

That easily then explodes to 5,000 x 100 results (making some assumptions on order frequency) in a given accounting period. Let’s say your periods are months-of-year and you want to assess the last 365 days (12 months) to generate an average – now you have 5,000 x 100 x 12, or 6M results. NetSuite saved searches tend to choke on numbers like this.

Now, you can filter out transaction lines by limiting the transaction type in your search criteria, by saying Transaction : Type = Cash Sale, Invoice (, Credit Memo, Cash Refund if you care about the impact of returns/credits). However, this will exclude any items where there are NO transactions to join to, so you would not see items that haven’t sold yet, which could be considered a problem. Any further limiting via joins will also further limit your result set. At best what you’re looking for, really, is to just produce a list of all your items and then place your summary joins in the Results section. This will take a long time to run, which can be frustrating to users, especially if they’re hoping to use Available Filters to dynamically change criteria and run the report several times in a day in the UI.

It really depends on how you expect to use the search.

So, I created some custom fields and populate them nightly with any changes from the previous day’s transactions via script, to track the past 365 days sales revenue, as well as the last sale date and anything else I need to quickly access via search. Most of this information you could easily enough also store via workflow. There are patterns to help get this data stored if it is desired.

Another option is taking raw data and using some form of BI (Business Analytics) software outside of NetSuite (or even Excel).

Then again, if you don’t mind long load times or timeouts, or you are able to limit your search criteria some other way so not every one of your 5,000 items are included on every run, then you will likely be able to get around many of the problems.

The bottom line is that there are many approaches to take on reports like this. I typically take a trial-and-error approach to seeing what works, starting with the approach that has the lowest overhead/impact on NS performance/technical debt (don’t store more data/run more scripts than you have to, don’t customize if you don’t have to). We have BI software that we use, so I will go out to that if there is a more complex reporting requirement.

I hope this helps. I’m happy to chat about it some more.

[AlanW]

Hi. Thanks for your detailed response. It will make it easier if you know the essential elements on what I would like to report on for inventory items that are slow or non moving, or have excess stocking:

FIELDS

Class (Item)

Sales Description

Item Number

Sold by (Each/Length/Roll etc.)

Current Inventory Quantity

Last Purchase Price per Item

Average Cost per item

Total Inventory Value per item

Total Quantity currently on Purchase Orders per item

Total Quantity currently on Sales Orders per item

Last receipt date

Last Sale Date

SORTS AND FILTERS

Sorted by Class

Subtotal by of items inventory values by Class

Variable period (from date and to date) over which to report the data

Filtered to report only stock that has not sold in last x months (or x days) {Probably last 180 days}, AND/OR has “xx days stock in inventory” (can be annual turnover rate equivalent) of more than 90 days

Preferably days in inventory based on item sales and item quantities in inventory rather than item costs, (i.e. COS for period/divided by Ave Value in Stock)

Is this possible? Hope so! Would be much appreciated if you have the solution for this.

Alan

[al3xicon]

AlanW – I can say with confidence that that sort of complex search/report is something that can be easily done within NetSuite. I would use BI software to do that sort of complex analysis, or else I would make several different saved searches, each one focusing on a different piece of data you wish to look at, and then either look at them separately, or export and combine them in Excel.

Some good news:

– At least for “Total Quantity currently on Purchase Orders per Item”, the “On Order” field should get you what you need.

– For “Total Quantity currently on Sales Orders per item”, assuming you mean only open sales orders, I believe a combination of “Quantity Committed” and “Quantity Backordered” can help get you there (though items can be committed to other kinds of transactions like work orders and transfer orders).

– Last Purchase Price, Average Cost and Total Value are all columns that are stock NetSuite and can just be pulled.

For the rest, you’d really need to start creating some custom fields and writing scripts to populate them daily or something along those lines.

[AlanW]

Hi. Thanks for the feedback again. What BI software do you utilize? It is only Open Sales Orders that are required with the combination of Quantity Committed and Quantity Backordered. I haven’t been trained in scripting so that is a problem (and a frustration) for me. With regards scripting, what do you think is the best source of training?

[al3xicon]

AlanW – we use Sisense. Right now, the best source of training seems to be NetSuite’s training courses, which are pricey and you need to come in already knowing Javascript / general programming concepts (ideally, anyway). You may be best off hiring a freelance NS developer and paying them for a few hours of work to get this done.

---

AlanW replied on 11/20/2016, 04:15 AM: Thank you for all your comments which have been very helpful

[MNeedhamRyonet]

I just created a search that shows the last time an item was used on a Work Order but you could possibly use it to show the last time an item was on a sales order as well

Item Search

Filter

Transation: Type is Work Order

Results

Name Summary Type Group

Transaction : Transaction Number Summary Type Maximum When Ordered By Field Transaction : Date Created

Transaction : Transaction: Date Created Summary Type Maxium When Ordered By Field Transaction : Date Created

I threw in On hand maximum and a like to the item since this was a summary search but if you change the type to Sales Order or Invoice you should get the last time an item was sold.

---

AlanW replied on 11/20/2016, 04:15 AM: Thanks for your input. Will use this in my search

[Author]

Posts