Currently, NetSuite does not have an option to directly show the FIFO cost for an item (and also bin if required). The saved search below also helps to find out the stock of an item as of a date along with FIFO costing.
Here’s how to create a transaction-based saved search and set following criteria within STANDARD.
- Posting is TRUE
- Quantity is not empty
- Date within 01/01/2019 until 01/31/2019 (can be fetched for other date range by adding DATE in available Filters)
- Formula numeric: case when {account.id} = {item.assetaccount.id} then 1 else 0 end
- Formula numeric: case when {location.id}={item.inventorylocation.id} then 1 else 0 end
Note: once the results are displayed, you can eliminate zero stock by using the SUMMARY Filters. If you would like to only see negative stock, then use the Summary filter and set the sum of quantity to less than zero.
In the results tab,
- Add column ITEM and GROUP it.
- Add column QTY and SUM it
- Add column Location and group it (so that we can visualize by item and its location)
- Add column TransactionBin (if you use BINS it will be useful).
- Add formula column as below and let it be round({amount} / NULLIF({quantity},0),5). Let the summary function have Maximum value.
Then, you can navigate to the Available Filters section to add any columns that may be useful to filter further. Some columns that may be useful are Location, Date, Item, Bin number etc.
The above-saved search will run for each transaction by item matching the criteria as well as the parameter range specified in AvailableFilters.
For example, input the date range from Jan 1st to Jan 31, 2019, and it will identify all the items that have transactions within that date range. The date value within available filters can be used to find out the stock as of the date range as well as FIFO cost.
With the above-saved search, it is possible to find out FIFO cost as well as stock count. The output can be exported to excel for further analysis. The stock status would help managers to plan for procurement.