How to Fetch FIFO Cost in NetSuite Based on a Date

This blog post details the process of fetching FIFO cost in NetSuite based on a particular date. 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 sett 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 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 transaction 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.

Need more help? MIBAR offers expert NetSuite support. Contact us today to learn more.