NetSuite Inventory Received Not Billed / Accrued Purchases Analysis

Use advanced Saved Search techniques to interpret balances in hard-to-understand General Ledger Accounts

Inventory Receiving and Billing in NetSuite

In 2017, Marty Zigman wrote an article describing how to explain the Inventory Received Not Billed account. Below is a brief summary of the problem and solution described in that article. This will serve as an introduction to the topic of today’s article, which is essentially an expansion of Marty’s solution.

When NetSuite’s “Advanced Receiving” feature is on (Setup > Enable Features > Transactions > Shipping & Receiving), NetSuite automatically adds an account to record the Purchasing activity of incoming Inventory prior to the recording of the formal vendor bill. This account is named “Inventory Received Not Billed”, and in some environments it is named “Accrued Purchases” (I have not yet been able to determine why the name is sometimes one or the other). For simplicity, in this article I will refer to the account as “IRNB”.

The IRNB account is used as a temporary liability to account for the fact that when inventory is received, the company’s assets increase, but a formal obligation to pay the vendor has not yet been created with a vendor bill. When the vendor bill is entered, the IRNB liability is reclassified to a formal Accounts Payable liability, which can then be paid.

IRNB Debits and Credits

In general, Item Receipts will Debit Inventory and Credit IRNB for the cost of the items based on the Purchase Order price. Vendor Bills against the same Purchase Order for the items received will generally Debit IRNB and Credit Accounts Payable based on the Vendor’s price on the Bill. Variances between Item Receipts and Vendor Bills will remain in the IRNB account unless acted on. See the “T-Account” analysis below for an example:

3 Way Match & Vendor Bill Variance Journals

Mature businesses have solid 3-way-match processes to detect and resolve discrepancies that are the result of errors. Yet, there often still will be variances that need to be written off at month-end for discrepancies that are deemed immaterial. The best practice is to use NetSuite’s Vendor Bill Variance tool to write these off. Please see my previous article on this topic for full treatment of this tool. See the below “T-account” illustration including Vendor Bill Variance Journals:

IRNB Balance Subledger

Companies should want to understand the detail of what comprised the balance in the IRNB account, to understand the true financial status of Purchase Order-related transactions, and to assess any variances between receipts and bills. Marty’s 2017 article describes how to create a saved search that does this. The 2 critical parts to the saved search are:

1) Use “Special Account Type = RecvNotBill” as the main criteria for interpreting the balances (see Marty’s 2016 article “NetSuite Special Account Types Reference”)

2) Group results by “Created From” to summarized balances in the account by the Purchase Orders of Item Receipts, Bills and Variance Journals

Limitations of using “Created From” to group by Purchase Order

The above-described saved search works in most environments, but consider the following 3 limitations that require a different approach in the saved search:

1. Multi-Bill Purchase Orders: Clients that use the bulk billing tool “Bill Purchase Orders” have the ability to generate 1 Vendor Bill per common Vendor, across multiple Purchase Orders. When this occurs, the native “created from” field in Saved Searches will only show the 1st PO that the bill was created from, even though at the line level there are actually multiple “created from” POs.

2. Bills Created from Item Receipts: As of the 2021.1 release, NetSuite allows one to create Vendor Bills directly from Item Receipts. When this occurs, there actually are 2 “created from” transactions that exist on the Vendor Bills: The Purchase Order and the Item Receipt. This results in duplicate lines in the basic IRNB analysis search crafted by Marty.

3. Ownership Transfer transactions: When Inbound Shipment and Ownership Transfer transactions are used, the transactions that impact IRNB are actually not the Item Receipts that eventually get generated, rather it is the Ownership Transfer transactions that impact IRNB. This further complicates the analysis, since the “created from” field which points to the PO is hard to access. The “created from” on Ownership Transfers is not associated to the IRNB-impacting line, thus the simple IRNB search will not properly group the Ownership Transfers with a PO.

NetSuite IRNB Subledger 2.0

Here are the techniques used in an advanced Saved Search that accommodates the above-described problems:

1. Multi-PO Bills: Instead of using “Created From” in the results, use a formula to selectively use “applied to” when the transaction type is a Vendor Bill. See screenshot below. Note: This approach will also require a different approach for getting a drill down detail, since “Formula (Text)” groupings don’t support a detail view. More on this further below in this article.

2. Bills created from Item Receipts: To get around this issue, again we can use selective logic, to limit the “applied to” transaction type on the bills to only show Purchase Orders. It’s important to understand that we are not filtering the transaction types of the actual result set, rather we are filtering the joined Purchase Order references on the Vendor Bills to avoid duplicate results. Note the Formula (Numeric) logic and 0 vs. 1 dynamic. This is a good technique to avoid the “Use Expressions” options in the Criteria tab of saved searches.

3. Ownership Transfer transactions: In order to address the issue with “Created From” on Ownership Transfers, we had to broaden our criteria for finding the transactions we needed beyond just using “Special Account Type = RecvNotBill” as the sole criteria. However, we needed a reliable way to ensure that the Saved Search remained faithful to the GL Balance in the IRNB account. After testing, we determined that Ownership Transfer lines that impact “Inventory in Transit” would offset IRNB impacts on the same transaction. Thus, we could safely change our criteria so that on Ownership Transfers we look at the Inventory in Transit account, and this produces a result that ties out to IRNB.

Using Hyperlinks in Saved Search to produce a “Drill-Down”

The above solutions replace simple “created from” grouping with custom formulas that get to the correct associated Purchase Orders based on each transaction type and linkage. This also means we need to artificially reconstruct a drill down functionality.

This can be achieved by first creating a separate detailed saved search, and then using a feature to create a hyperlink output in the results of the summary search, linking to the detailed search url. The ability to create Hyperlinks in Saved Search results is described in this 2012 article by Marty Zigman “Custom Hyperlink Outputs in NetSuite Saved Searches”.

The trick here is to use conditional logic within the Hyperlink formula so that detailed report results are filtered by the correct associated Purchase Order. See screenshots below. (Note that I have removed some of the url in the screenshot below to make it simpler to understand, but actually there is a longer string of characters needed).

Conclusion

It certainly was gratifying to be able to use creative saved search methods to produce a report that is simple to interpret and that ties back reliably to the General Ledger account. Readers should also refer back to my earlier article describing how to properly handle variances between Receiving and Billing using NetSuite’s Vendor Bill Variance tool.

If you would like to learn more about how to create unique custom Subldeger-style reporting to support your accounting functions, please reach out!