Learn how SQL Capacities open up the possibility of highly dynamic A/R and A/P aging reports in NetSuite
Introduction: NetSuite Aging report limitations
Accounts Receivable and Accounts Payable Aging reports stand prominently among the vital metrics crucial to business operations. While the standard NetSuite A/R and A/P aging reports may suffice for some, they harbor two limitations that often catch newcomers to NetSuite by surprise, diminishing their overall effectiveness.
- As documented in SuiteAnswers Article 37804, surprisingly, Aging reports cannot be filtered reliably to specific GL-Account. This is because the filter options on the native reports are limited to either Main Line account or Line Account, but both cannot be combined. Here is a quoted excerpt from that article:
Currently, A/R Aging Report has no ability to filter Account and Account (Line) at the same time. These two (2) criteria are important in ensuring to get the same balance per A/R Account. Account filter exposes transactions with Main Line (e.g. Invoice) while Account (Line) filter exposes all other transactions (e.g. Payment and Journal).
- Customer Deposit balances and Vendor Prepayment balances cannot be included in the native A/R and A/P Aging reports. This is a common request that NetSuite users have, since for collection purposes or for Payable management purposes, seeing only open A/R and A/P is an incomplete picture if we don’t also see prepayment/deposit balances.
Since the introduction of SuiteQL in 2019, and with the development of SQL reporting tools such as the Prolecto SQL utility and the Prolecto Query Renderer, we have been able to completely break out of limitations in native reporting. Here too, with help from my colleague Boban D., we’re been able to craft aging reports that can be reliably filtered by specific GL accounts, and can include Customer Deposit or Vendor Prepayment amounts, with great flexibility in how the details are organized. See the below screenshots for samples of what is possible:
NetSuite Aging Fundamentals
There are two concepts that underlie Accounts Receivable and Payable agings:
- General Ledger to Subledger relationship: An Aging report provides an explanation to “what is in” the main Account Receivable or Accounts Payable General Ledger. The Entity (Customer or Vendor) is the obvious necessary level of detail to properly express the nature of A/R and A/P balances, and therefore each entity’s balance constitutes its Subledger. This aspect of Aging reports is expressed in the row dimension of the reports.
- Aging Bands or “Buckets”: Aging reports provide interpretation of the quality of the open Asset or Liability that A/R and A/P represent. This is expressed in the column dimension of the reports, and each column represents a time metric; typically, 30 days. The ability to present open balances in an age band rests upon the concept of “settling” a transaction so that Debits and Credits are “applied” against each other properly to present the net balance in the correct Age Band.
Expressed differently, Aging reports are more than just an aggregate of Debits and Credits grouped by Customer/Vendor. They also tell a time-related story about the transaction balances that are “open”, which is based on the way that specific Credits and Debits are associated to each other.
NetSuite “Open Balance” Concept
The term “open balance” in an invoice doesn’t merely indicate that the total payments made by the customer haven’t matched the invoice amount. It’s a more nuanced concept, often posing a significant challenge in creating custom reports. Even if a customer’s overall net balance stands at zero, they can still have unpaid invoices and unapplied payments.
In NetSuite, when a Customer Payment or Vendor Payment is recorded, two key operations usually occur within the database: firstly, a debit or credit is recorded in the Accounts Payable (A/P) or Accounts Receivable (A/R) corresponding to the payment amount. Secondly, this transaction is allocated against specific invoices or bills as chosen in the sublists. Furthermore, any existing unapplied Credit Memos, Vendor Bill Credits, or similar transactions can also be “applied” to chosen invoices or bills during a Customer Payment or Vendor Payment transaction.
NetSuite Saved Search users will likely be familiar with the “Applying Transaction” and “Applied To Transaction” options that can be exposed in Saved Search to identify how transactions were settled against each other.
Challenge with NetSuite’s Open Balance Approach
It is easy enough to create custom reporting to show a net balance owed by a customer or owed to a vendor as of any given point in time. In a saved search, it is simply a matter of keying on “Account Type” and filtering the output to a specific date range or accounting period range. If we want to also show Customer Deposit balances we can add something called the “Special Account Type” and filter to the customer deposit option. For Vendor Prepayments, we would need to use the actual GL account used, since NetSuite as of yet does not have any indication on account records for Vendor Prepayment assets.
For a sample saved search that uses this approach, see Marty Zigman‘s 2021 article “Get a NetSuite Accounts Receivable with Customer Deposit Aging”
However, when it comes to creating Aging Bands, it is more challenging. The difficulty is grouping the debits and credits together in the way they are applied to each other so that the open balances appear in the right aging buckets, since we can’t just use transaction amounts, rather, we need to know the specific debit transactions and how they were applied to specific credits, and vice versa.
Two Approaches in NetSuite to Solve
1) Simple Approach using Open Balance Fields
NetSuite contains essential fields that display the open balances for invoices and vendor bills, as well as the unapplied balances for customer payments, credit memos, and vendor bill payments. These fields can be used to ensure that open balances are accurately categorized into time buckets. For a comprehensive list of these field names and the tables where they are located, refer to my earlier article, NetSuite SuiteQL A/R and Customer Deposit Fields.
Using these “open balance” oriented fields, we can construct Aging reports that can be filtered to specific GL accounts and we can include Vendor Prepayment and Customer Deposit accounts.
It’s important to note that these fields only reflect the real-time open balances, and therefore using these fields to construct Aging Reports will not work if reporting on dates in the past are needed.
2) Complex Approach using linked tables
To create tailored Aging reports with SuiteQL for as-of date reporting, we utilize the nextTransactionLineLink table in NetSuite. This table sheds light on the interconnections between various transactions, revealing the application of Accounts Receivable (A/R) and Accounts Payable (A/P) transactions to one another. By selectively including linked transactions in our calculations, we can accurately determine the current “open” balances, enabling effective as-of date reporting. The image below illustrates our method for joining tables to compute these balances:
Our approach involves identifying open debits (like unpaid invoices in A/R) and open credits (such as unapplied credit memos or payments in A/R). We address each of these scenarios independently and then combine the results using UNION statements in SQL to compile a comprehensive report. A unique challenge arises with Customer Deposit balances, as their link to Deposit Applications is not represented in the nextTransactionLineLink table in the same manner as the links between Payments and Invoices. However, we successfully navigated this by calculating the total amounts applied to invoices and adjusting the Customer Deposit amounts accordingly. Below is a diagram showing how Customer Deposit open balances were handled:
Conclusion
Like all of our accelerator bundles, our SuiteQL-based Query Renderer is free to our clients, and the above article describes how the tool has solved one of many previously intractable reporting challenges with native NetSuite reporting.
It certainly was gratifying to crack the nut on custom A/R and A/P Agings with account selection and prepayment/deposit functionality. If you would like to hear how we might be able to help your finance team gain clarity in Subledger reporting, please feel free to reach out!