Conversations with Meir Bulman

NetSuite Subledgers: Challenges and Opportunities

Thoughtful design of Transactions and Reporting will unlock NetSuite’s power and take the frustration out of Period Close

Introduction: The frustrated controller

Does “frustrated controller” describe you? Accounting Teams are often overworked, overwhelmed and stuck in a spreadsheet morass as they stich together data to explain financial reporting in NetSuite.

If you struggle to interpret your Trial Balance, you are certainly not alone. NetSuite doesn’t have to be this way, though, and actually, its flexible, feature-rich database can dramatically improve your Financial Close experience, if done right. This requires 2 things: Thoughtful Design and Operational Discipline.

This article will demonstrate how design and discipline can produce game-changing Subledger reports in NetSuite, and ultimately, make Period Close painless. (Well, maybe that’s a bit grandiose. Dramatically less painful, though, guaranteed!)

If you are a Controller or Accountant, I invite you to take the below poll to see where you are in relation to your NetSuite utilization and Period Close:

Thoughtful Design in NetSuite: Why?

The adage “Garbage In, Garbage Out” rings true. Often, NetSuite users grapple with what we’ll term as “Nothing In, Nothing Out.” If your transactions lack the structure necessary to articulate the business narrative effectively (memo fields won’t suffice!), generating reports that succinctly summarize balance details for your Trial Balance becomes an elusive goal.

This is one of the reasons we discourage our clients from using Journal Entries. Other Transaction Types more naturally can tell a story about the nature of transactions and then can be summarized more easily in reporting.

NetSuite Subledgers

NetSuite contains built-in Accounts Receivable and Accounts Payable subledgers that explain the GL balances for those types of accounts. A major advantage of NetSuite’s built-in transaction posting approach is that the A/R and A/P Subledger will inherently tie to the GL (see my previous article comparing ERP systems). Customer and Vendors required on standard A/R and A/P transaction types create a natural ability to see balances per entity, and NetSuite has capacities that capture “applied to” and “applying” transactions where A/R credits (e.g. customer payments) are applied against A/R debits (e.g. invoices) and A/P debits (e.g. vendor payments) are applied against A/P credits (e.g. vendor bills). All of this naturally allow for subledgers that accurately describe the balance for the A/R and A/P accounts.

Non-A/R or A/P Ledgers

The problems arise when trying to interpret other (non-A/R or A/P) types of account balances. Consider some of the following accounts:

Accrued Purchases (also known as “Inventory Received Not Billed”): Item Receipts and Vendor Bills impact this account, and there is no inherent “offsetting” or “applying” ability that closes out transactions against each other. Often, companies use manually entered Journal Entries to try to clean up residual balances in this account. Good luck trying to analyze what comprises the GL Balance when you have Journal Entries with no link to POs or Vendors! (See Marty Zigman‘s article “explaining NetSuite’s IRNB account“)
Vendor Prepayment Asset: This is typically an “Other Current Asset” account, and until NetSuite release 2019.2 there was no native transaction to record the debits and credits into this account. Companies often used “check” transactions to record the Debits and Bill Credits to record the Credit and offset A/P. Here again, we lose the ability to aggregate by any useful metric. Even for companies that use NetSuite’s Vendor Prepayment transactions, it is difficult to produce a meaningful report, since the Purchase Order field is not yet exposed in Saved Searches. (This will be the topic of a future article).
Customer Deposit Liabilities: Though NetSuite uses a concept of applied-to/applying transactions for the Customer Deposit account type, it can be challenging to access the right tables to create a Subledger that shows only the “Open” (= unapplied) transactions. See Marty‘s article “Get a NetSuite A/R Aging with Customer Deposits“
Inbound Freight Estimates vs. Actual Amounts: This typically centers around Landed Costs. Companies will capitalize inbound freight to inventory using estimates, and offset (credit) against a freight expense. Then, when actual freight bills arrive, they will debit the freight account for the actual inbound cost. Unless the transactions are crafted wisely, it becomes impossible to construct a report that helps to interpret what comprises the net Inbound Freight expense. See Marty‘s article “Learn NetSuite Landed Costs Fundamentals” to read more about general Landed Cost techniques and to better understand the reporting challenge.

In all of the above scenarios, planning goes a long way in the ability to get meaningful reports directly out of NetSuite. As an example, see below screenshots of a Prepayment Subledger attempt where there are many “floating” transactions:

Prolecto NetSuite Methodology

Each specific subledger reporting situation requires its own article (stay tuned!), but here is a general strategy you can use when facing a difficult-to-interpret GL account:

Start with determining what the reporting needs are. Ask questions such as, what would be a useful metric by which we would want to measure the account a balance, if we could. Often this is an entity or a planning transaction such a Purchase Order and Sales Order. Determine a “unifying element”, meaning, a data point around which the balances ought to be organized.
Next, ask yourself, what information would need to be captured on transactions in order to support the reporting need. Think through how transactions could be shaped to ensure that the right type of data is consistently captured.
Finally, decide what the reporting technique should be, depending on the nature of the data required and on how to present it. Saved Searches are not always powerful enough, and we at Prolecto have created other options for reports that need to reach deeper. See Marty’s article about our Content Renderer “Content Renderer Engine 2.0 with NetSuite SuiteQL” and this one about a powerful SQL reporting tool “Render NetSuite SQL Queries Like Saved Searches“

Operational Discipline in NetSuite

What does “Operational Discipline” mean? This is a crucial piece to getting subledger reporting right. Even when the NetSuite system is designed along the lines of the above methodology, if users do not enter the data that aids subledger analysis, the benefits are lost.

Of course, a well designed system will include mandatory fields and controls to ensure compliance. Yet even the best designed system cannot always prevent users from getting around controls intended to organize transactions. For example, Admin users (or others) can always enter Journal Entries without sufficient information, if they are unaware of the bigger company objectives. For this reason, we often include control reports and alerts so that users can react quickly to transactions that potentially can undermine any custom subledger reports.

Conclusion

I hope that this article was useful in demonstrating how to approach accounting problems from a “business-first” perspective. Through ingenuity and NetSuite expertise, we can move Accounting teams from being reactive and “clean-up” focused to proactive and forward-thinking. If you would like to find out how these and other solutions can work for you, please reach out!

I’ve been fortunate to study powerful tactics from Marty Zigman and others at Prolecto, and am always learning more. If you would like to learn about opportunities working at Prolecto, please contact us!

Please stay tuned for forthcoming articles to learn about some specific solutions to the NetSuite Subledger challenge.

Quick Tip: NetSuite “Overwrite Sublists” for Journal Entry CSV Imports

Hat tip to my colleague Boban for this one!

CSV transaction imports in NetSuite offer an option to “overwrite sublists” when you are updating transactions. This is a useful way to avoid trying to target specific transaction lines to be updated. See SuiteAnswers article 31848 for more details on how to use this feature.

Using the feature on Journal Entries (or custom transaction types of the “Journal” style) is documented in SuiteAnswers article 105561, but there seems to be an additional step that I haven’t yet been able to find documented:

If you attempt to update a Journal Entry and select “Overwrite Sublists” as described in the above-referenced articles, the CSV import sometimes fails with the following error:

“This record contains duplicated key or keys. Please correct it before next update.”

After much frustration and failed troubleshooting attempts, I reached out to my teammates at Prolecto, and Boban gave me the following advice:

“Add another column to the file and call it ‘line id’, and leave that column blank in the file. Map it to “Line Id” in the Lines mapping.“

This worked perfectly! The surprising part about that is that according to SuiteAnswers 105561, Journal Entries cannot be updated at the specific line level, and the only option is to overwrite sublists. Here’s the wording:

“Note: Updating individual lines/Journal Lines in general for Journal Entry can only be done with the Overwrite Sublist Preference= True during CSV Import.“

I have not been able to determine if the blank line id fix is always needed, or if there was something unique in my import or environment that required this.

NetSuite Currency Considerations

Understand Advanced Currency Management in NetSuite

Introduction

Currency concerns in NetSuite can be a confusing topic. The confusion is compounded for users of prior ERP/Accounting software that may use a different approach than NetSuite when it comes to managing currencies. I’ve written before on this topic, and as I continue to learn from my clients’ situations, my knowledge deepens, and I would like to share another iteration of the topic with my dear blog readers.

In this article, I review and expand on NetSuite’s approach to currency management, and I created an 11-minute video posted at the end of this article which takes the viewer through the entire process of moving from a foreign currency transaction to consolidating foreign currency subsidiaries.

Common Currency Problem in NetSuite

We often get asked the following question, in various forms:

“I have a [Currency A] transaction in a [Currency B] Subsidiary. My consolidated reporting is in [Currency A]. Shouldn’t the [Currency A] amount in the consolidated report match the original transaction amount?”

To answer this question, we need to understand the relationship between currencies and exchange rates in NetSuite, and which Exchange Rates drive how transactions are represented both in the General Ledger and in Consolidated Reporting. In my earlier article, “Understand NetSuite’s Currency Translation”, I explain the different rate tables and how they drive reporting and valuation, and that article provides some additional backdrop to the solution to the question posed above.

Transaction Currency, Base Currency, and Consolidated Currency in NetSuite

3 distinctions must be understood when evaluating currency impacts in NetSuite OneWorld reports:

Conversion to General Ledger: This is the process NetSuite uses to convert the transaction currency to the Ledger Base Currency, AKA “Functional Currency”. An example would be the US Dollar impact on the GL for the Euro amount of a customer invoice where the customer’s currency in NetSuite is Euro and the subsidiary’s base currency is USD.
Currency Revaluation: A month-end process to markup or mark down the Base Currency value of a non-base currency balance. Typically, this process is done on Receivables, Payables, and Bank Balances (in short, any account that is expected to be converted to actual foreign currency cash, or actually consists of foreign currency cash). As the exchange rates change, there needs to be a revaluation to reflect the true current base currency amount that the foreign currency balance represents.
Currency Translation: The NetSuite built-in function to consolidate subsidiary financial statements into a parent subsidiary’s currency. An example would be a child subsidiary whose base (functional) currency is US Dollars that is owned by a parent-subsidiary whose base currency is Euro. When running consolidated reports, the entire USD ledger of the child must be translated into Euro to allow uniform, consistent reporting.

Exchange Rate Tables in NetSuite

The exchange rates that NetSuite uses to generate Currency Revaluation (point 2. above) are held in a different table than the rates used to perform Currency Translation for Consolidation (point 3. above).

Rates used in Revaluation: These rates are found in the “Currency Exchange Rates” table, which can be found at Lists > Accounting > Currency Exchange Rates. The table stores daily rates, and the one used for Currency Revaluation at period end would be the rate of the last day of the relevant period.

Rates used in Translation: These rates are found at Lists > Accounting > Consolidated Exchange Rates. The rates used depend on settings on the Account record and need to be updated each period via the Period Close checklist or by clicking the “calculate” button as in the below screenshot.

Response to the Common NetSuite Currency Question

Returning to the question posed above, we now have the main elements needed to respond:

The short answer to the question is “It depends“.

Mainly, it depends on which “rate type” is selected on the Account record and on whether the Account is revalued each month. These are among the core concepts discussed in the article referenced above.

The below short video is a review of those concepts and provides an explanation of whether and when Consolidated reports will produce the same result as source transactions in the same currency:

Conclusion

At Prolecto Resources, Inc., we bring together a wealth of accounting knowledge and extensive experience with NetSuite. Our team comprises skilled accountants who truly grasp the significance of specialized expertise in NetSuite. We are dedicated to utilizing our multi-dimensional knowledge to gain a profound understanding of our clients’ needs and challenges. If you’re interested in learning more about working with Prolecto, feel free to reach out to us!!

Saved Search Text Filtering

Do you want a filter on Saved Search results to use Keyword typing instead of a Drop Down? Often, drop-down filters are hard to use in Saved Searches, especially if the list is long. Here are 2 ways to change the filter to support text typing:

If the filter is a “joinable” record (e.g. the Customer Name on a Transaction Saved Search): Instead of using the primary field, join the record and use a “name” or similar field.

If the filter is not a “joinable” record (e.g. the Transaction Status field): First, in the Criteria create a Formula (Text) criterion. Next, add “Formula (Text)” as a filter (give it a useful name). The formula should contain the internal id of the field that you want to use as a filter.

Voila! You then can type text in the Filter you have created, rather than use a drop down. (Hint: The “%” sign in NetSuite is a wildcard character).

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:

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.

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.

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:

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.

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.

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!

NetSuite SuiteQL A/R and Customer Deposit Fields

Ever get confused about which fields do what when creating A/R – oriented SuiteQL reports? Here’s a table that help organize the fields and their meanings:

Transaction Types	Table	Field	Meaning
All Types	transactionLine	foreignamount	Transaction amount in transaction currency
All Types	transactionAccountingLine	amount	Transaction amount in GL base currency
Invoice	transaction	foreignAmountPaid	Total paid amount, in the transaction currency
Invoice	transaction	foreignAmountUnpaid	Total unpaid (open) amount, in the transaction currency
Customer Deposit, Payment, Credit Memo	transaction	foreignPaymentAmountUnused	Total unapplied (open) amount in the transaction currency
Customer Deposit, Deposit Application, Payment, Credit Memo	transaction	foreignPaymentAmountUsed	Total applied amount in the transaction currency
Invoice, Journal Entry	transactionLine	foreignAmountPaid	Total paid amount, in the transaction currency (note: JE only if Dr to A/R)
Invoice, Journal Entry	transactionLine	foreignAmountUnpaid	Total unpaid amount, in the transaction currency (note: JE only if Dr to A/R)
Customer Deposit, Deposit Application, Payment, Credit Memo, Journal Entry	transactionLine	foreignPaymentAmountUsed	Total applied amount in the transaction currency (note: JE only if Cr to A/R)
Customer Deposit, Payment, Credit Memo, Journal Entry	transactionLine	foreignPaymentAmountUnused	Total unapplied (open) amount in the transaction currency (note: JE only if Cr to A/R)
Invoice, Journal Entry	transactionAccountingLine	amountPaid	The amount “settled” against a Debit to A/R, in GL base currency (note: JE only if Dr to A/R)
Invoice, Journal Entry	transactionAccountingLine	amountUnpaid	Total unpaid amount, in GL base currency (note: JE only if Dr to A/R)
Customer Deposit, Deposit Application, Payment, Credit Memo, Journal Entry	transactionAccountingLine	paymentAmountUsed	Total applied amount in GL base currency (note: JE only if Cr to A/R)
Customer Deposit, Payment, Credit Memo, Journal Entry	transactionAccountingLine	paymentAmountUnused	Total unapplied (open) amount in GL base currency (note: JE only if Cr to A/R)

Create Impactful Customer Statements in NetSuite by Leveraging the power of SuiteQL

Learn the background of how Accounts Receivable balances are brought together using SuiteQL

Custom Customer Statements

As described in this article: Extend NetSuite Customer Statements, from Marty Zigman, one of the popular tools we offer is the ability to produce enhanced customer statements. There are 2 components used in the solution:

Prolecto’s Content Rendering Engine (“CRE”): A versatile tool to create and distribute external-facing content such as customer forms, special reports, and Vendor Remittance statements. The content can be distributed as PDF or HTML embedded in an email, and either automatically sent or via a suitelet. This tool is extremely powerful, and we often use it as part of holistic business process improvements.
SuiteQL queries: Since early 2021 the ability to use SQL to bring together far-reaching data into one report in NetSuite has been a game changer.

The latest version of the CRE tool leverages SQL queries as the “inputs” for the content shown in a PDF or email body.

In this article I will describe the background of the components needed to produce a Customer Statement and the structure in NetSuite that allow us to bring the data together in understanding a customer’s balance.

NetSuite Accounts Receivable Patterns

Accounts that are of the Account Type “Accounts Receivable” (and “Accounts Payable”) have special treatment in NetSuite. Unlike other account types, when a transaction impacts a GL Account that is of one of these types, it can be “settled”. In other words, in addition to a simple running total of a balance based on debits and credits, specific transactions can be applied to each other. When entering a customer payment and “applying” it to unpaid invoices, NetSuite preserves a link between the transactions. At first glace this may be obvious, but there are some important ramifications that might not be intuitive:

Presentation of balance aging on A/R or A/P reports depend on settlement of invoices. Even if a customer or vendor balance is zero, there may be results showing on a standard Aging report as positives and negatives, if transactions are not “settled” against each other.
Reporting tools in NetSuite can show values based on “settlement”. Certain transaction types will have values as “open” or “unpaid” balances, and other transactions types may have “amount applied” or “amount unapplied”.
Currency revaluation works differently on “settled” transactions vs. open transactions, if the underlying transactions are in a foreign currency as compared to the Subsidiary’s base currency.

Customer Deposits

One of the important improvements that we can provide is the ability to include Customer Deposit balances (prepayments) in a customer’s open balance. Being able to present open Customer Deposit balances in a similar way as open Invoices and open Credit Memos is challenging.

Customer Deposits in NetSuite (prepayments from customers) have special treatment for A/R reporting purposes. They do not impact Accounts Receivable, rather they result in a credit to a liability account, “Customer Deposits”. Yet, they represent an important part of the Accounts Receivable collection cycle. NetSuite allows the application of Customer Deposits to invoices, via a transaction called “Deposit Application” which acts as a reclassing entry to move the credit out of Customer Deposits liability accounts into an Accounts Receivable account so that it can settle an invoice or other A/R balance.

Journal Entries

Generally, Journal Entries should be avoided when attempting to impact Accounts Receivable. If there is no “name” on the line, there will be no way to settle the A/R balance. If a name exists on the JE line that is impacting A/R, it can be settled against any other A/R transaction. Another challenge with Journal Entries impacting A/R is that not all of the fields that are available on Invoices or Credit Memos are available on Journal Entries in Saved Search or SuiteQL query reporting. The table at the end of this article will show which fields are available for Journal Entry balances.

Reporting Approaches

There are really 2 ways to produce analysis of customer balances using SuiteQL tools.

Balance Approach: It would be straightforward to produce a simple analysis of the customer balances using SQL. We could query the transaction impacts in either the base currency or the transactional currency by pulling in the below fields:

SELECT tra.amount,

trl.foreignamount

FROM Transaction Tran

INNER JOIN Transactionline TRL ON (Tran.id = TRL.transaction)

INNER JOIN Transactionaccountingline TRA ON (Tran.id = TRA.transaction AND TRL.id = TRA.transactionline)

Aging Approach: If we want to get Aging buckets, we need to know not just the “debits and credits” side of transactions, but also the amounts still open on each transaction. For example, if an invoice date was 60 days ago, and a partial payment was made 30 days ago, we do not want to see a debit in the 60 column for the full invoice amount and a credit in the 30 day column for the full payment amount. Obviously, we want only the unpaid balance of that invoice in the 60 day bucket.

Generally, companies would be more interested in the second approach. We thus need to find which fields to pull in a query to provide the “Open Balances”. The tricky part here relates to Transactions Types and the concepts of what NetSuite considers an “Open” balance. The below table shows the tables, field mapping, and meanings of each available field:

Transaction Types	Table	Field	Meaning
All Types	transactionLine	foreignamount	Transaction amount in transaction currency
All Types	transactionAccountingLine	amount	Transaction amount in GL base currency
Invoice	transaction	foreignAmountPaid	Total paid amount, in the transaction currency
Invoice	transaction	foreignAmountUnpaid	Total unpaid (open) amount, in the transaction currency
Customer Deposit, Payment, Credit Memo	transaction	foreignPaymentAmountUnused	Total unapplied (open) amount in the transaction currency
Customer Deposit, Deposit Application, Payment, Credit Memo	transaction	foreignPaymentAmountUsed	Total applied amount in the transaction currency
Invoice, Journal Entry	transactionLine	foreignAmountPaid	Total paid amount, in the transaction currency (note: JE only if Dr to A/R)
Invoice, Journal Entry	transactionLine	foreignAmountUnpaid	Total unpaid amount, in the transaction currency (note: JE only if Dr to A/R)
Customer Deposit, Deposit Application, Payment, Credit Memo, Journal Entry	transactionLine	foreignPaymentAmountUsed	Total applied amount in the transaction currency (note: JE only if Cr to A/R)
Customer Deposit, Payment, Credit Memo, Journal Entry	transactionLine	foreignPaymentAmountUnused	Total unapplied (open) amount in the transaction currency (note: JE only if Cr to A/R)
Invoice, Journal Entry	transactionAccountingLine	amountPaid	The amount “settled” against a Debit to A/R, in GL base currency (note: JE only if Dr to A/R)
Invoice, Journal Entry	transactionAccountingLine	amountUnpaid	Total unpaid amount, in GL base currency (note: JE only if Dr to A/R)
Customer Deposit, Deposit Application, Payment, Credit Memo, Journal Entry	transactionAccountingLine	paymentAmountUsed	Total applied amount in GL base currency (note: JE only if Cr to A/R)
Customer Deposit, Payment, Credit Memo, Journal Entry	transactionAccountingLine	paymentAmountUnused	Total unapplied (open) amount in GL base currency (note: JE only if Cr to A/R)

Observations

Observe the following general guidelines in the above table:

The “Transaction” table fields related to open/unapplied amounts are not available for Journal Entries
The “transactionLine” and “TransactionAccountingLine” table fields are available for Journal entries, and the specific fields available depend on whether there is a credit or debit to A/R
“transactionAccountingLine” table fields only show the Base Currency (GL) amounts, and the other two tables’ fields only show the original transaction currency amounts

Conclusion

Once we understand the fields and tables that are available, we can use SQL arguments to pull in the necessary field values to craft the balances and aging buckets. I worked together with members of the incredibly talented team at Prolecto to get the queries wired up to the CRE tool to produce the below output:

If you would like to learn about working with us, or would like to hear how we can put our tools to work for you, please feel free to reach out!

Intercompany Account Balance Troubleshooting

Techniques for getting NetSuite to correctly auto-eliminate intercompany balances

Auto-Elimination: Background

NetSuite’s Automatic Intercompany Elimination feature can really ease the burden for multi-subsidiary period close. The tool finds balances to eliminate based on a transaction line field called “eliminate”, and reverses any impact of those lines into an “Elimination” subsidiary. I’ve recently written an article describing this feature and how to analyze amounts that were eliminated. Here is a list of some resources that may be helpful in understanding how the tool works:

My Previous Article: Reconcile NetSuite Auto-Elimination Amounts

SuiteAnswers 20121: Intercompany Elimination Overview

SuiteAnswers 64447: Running Intercompany Elimination

SuiteAnswers 64444: Key Points for Running Intercompany Elimination

Challenge: Clean Up Historical Intercompany Amounts

We recently were contacted by a client that was having difficulty understanding why intercompany account balances were not clearing out each period even though they were using the Auto-Elimination feature in NetSuite. After some analysis using SuiteQL queries to search for fields not available in Saved Searches, we were able to get the root of the problem and fix it, so that going forward the balances should clear out completely without manual work.

Solution (in short): reverse, re-import, revalue, eliminate

The causes of the non-eliminating amounts were:

Transactions that did not have the “Eliminate” checkbox “flagged” for elimination
Currency revaluations in the intercompany accounts, likewise not flagged for elimination
Manual Journal Entries into elimination subsidiaries that couldn’t always offset foreign subsidiaries’ intercompany amounts (due to currency translation).

In order to fix the problem, we needed to do the following:

Find the sources of Currency Revaluation transactions that were not flagged for Elimination, and reverse the impacts in both the Base Currencies and Foreign currencies
Re-import those balances, using the same exchange rates, but this time, flagged for elimination.
Find the remaining non-flagged for elimination amounts and do the same (reverse and re-import, flagged for elimination)
Run Currency Revaluation for the first available open period, verify that no Currency Revaluations remained that impacted intercompany accounts not flagged for elimination
Reverse the impact of any manually-booked elimination entries
Run Auto-elimination; verify zero balance in the consolidated financial statements for the intercompany accounts

Now let’s take a look under the hood to understand how the “eliminate flag” works, and why certain intercompany accounts won’t just automatically eliminate:

Intercompany Elimination and Account Types

One of the key considerations in running Auto-Elimination is the different treatment of Elimination based on Account Type. The Account Type selected on the GL Account record is a consequential decision to make when each account is set up, because the Account Type has a significant impact on all sorts of transactions and processing in NetSuite, and the Intercompany Elimination process is one example.

In order to flag transaction lines for Elimination, the “ELIMINATE INTERCOMPANY TRANSACTIONS” checkbox field must be checked on the account record. Yet, even when this is checked, the behavior of Auto-Elimination based on that flag will still depend on the Account Type. Here is a table of differences to Auto-Elimination based on Account Type:

Account Type → ——————————- Behavior ↓	Accounts Receivable/ Accounts Payable	Income / COGS / Expense / Equity	Other Assets / Other Liabilities (Current or Long-Term)
“Eliminate” can be selected or deselected at the Transaction Line level?	No – tagged by default, cannot be “un-tagged”	Yes	Yes
Forces a “Representing Subsidiary” entity as Customer/Vendor?	Yes	No entity required	No entity required
Links Elimination Entries back to Source Transactions?	Yes	Yes	No
Elimination JE is based on Summary Amounts / Specific Transactions?	Depends on “Journal Entry Summarization” Preference	Depends on “Journal Entry Summarization” Preference	Always Summarized
Elimination JE respects original Class, Department, Location?	Yes	Yes	No
Elimination JE Auto-Reverses in Next Period	Yes	No	Yes

Intercompany “Other Asset” or “Other Liability” accounts

Often, companies will opt not to use an Accounts Receivable or Accounts Payable type of account for Intercompany balances. Sometimes, this is to maintain natural netting of amounts when a subsidiary has both payables and receivables to/from a different subsidiary. (Side note: NetSuite’s Intercompany Netting feature accommodates this requirement and is worth considering). Using non-A/R and non-A/P accounts for Intercompany presents some challenges with period end automatic elimination. As can be seen from the chart above, non-A/R and non-A/P accounts need the “Eliminate” flag checked on Transaction Lines in order to auto-eliminate. Thus, if the practice of flagging transaction lines was not in place historically, it can be quite challenging to fix so that balances in these accounts are eliminated in consolidated financial reports, as was observed in the client situation described above.

Additional Complication – Currency Revaluation and Elimination Flag

Another interesting observation of the Elimination flag is that it exists on Currency Revaluations, although there doesn’t seem to be a way to expose it on the currency revaluation transaction forms. The determinant of whether the field will be checked on Currency Revaluations depends on the source transactions that are subject to Currency Revaluation. If the source transactions in a non-base currency are flagged for elimination, the currency revaluation will likewise be flagged for elimination. (For additional info regarding Currency Revaluation, see my previous article, “Understand NetSuite Currency Translation”).

Best Practices

When encountering accounting cleanup projects such as this one, I like to help clients think of how to best avoid errors and costly manual work. When it comes to Intercompany Elimination, if a non-A/R, non-A/P account is used, it would be good to create a simple script or workflow that forces the user to check the “eliminate” flag before saving. Normally, a saved search alert would be a good, low-tech solution, but in this case the field is not available in searches, so a little more development work would be needed to stay in control. If you would like to learn more about best practices for Intercompany Consolidation, feel free to reach out!

NetSuite Fixed Asset Reconciler Bundle

Announcing a Fixed Asset supplementary bundle that can diagnose and help resolve discrepancies

NetSuite Fixed Asset Common Questions

Over the past few years, I have had the privilege of helping clients understand and use NetSuite’s Fixed Asset Management tool (“FAM”). I’ve been approached with support requests over time, and begin to see some recurring patterns in the nature of these requests. Here are some of the common challenges posed by clients in trying to understand their Fixed Asset numbers:

Why do the Asset Net Book Values not tie out to the balances in the Fixed Asset ledger accounts?
How can I be sure that the system-generated Depreciation Journal Entries are correct?
Where can I find a breakdown of the Assets that were Depreciated in the system-generated Depreciation Journal Entries?
Why do the native Fixed Asset reports offered as part of the FAM module not tie to saved searches driven by Fixed Asset records?
We adjusted Depreciation Journals, yet the FAM system reports are still incorrect. Why?

FAM Records

I’ve written previously about NetSuite’s Fixed Asset Management tool (“FAM”), and the below previous articles describe some of the foundational record structure of the module.

https://meir.prolecto.com/2022/05/30/finally-trace-fixed-asset-depreciation-journal-entries-back-to-underlying-assets/

https://meir.prolecto.com/2019/12/29/fixed-asset-management/

Many of the challenges with using NetSuite’s Fixed Asset Management tool stem from the way that FAM differs from general subledgers like Accounts Receivables / Accounts Payable. The various records that the FAM module contains do not directly impact the ledger, rather they support and drive automation that in turn creates transactions. This implies a risk, since an entry can theoretically be booked to the GL that doesn’t match what shows in the FAM module.

What Can Go Wrong?

Here are some specific scenarios that cause FAM reports to fall out of sync:

Historical mid-life assets imported did not include special records for accumulated depreciation to-date
Journal Entries were manually booked to Fixed Asset accounts, without corresponding entries in the module
Asset records or Depreciation History Records were manually modified or deleted after Depreciation was posted
Initial Acquisition records are missing or do not have a date

Announcement! Fixed Asset Module Reconciler

After engaging in several repeat troubleshooting projects helping clients understand their Fixed Asset reporting in NetSuite, I realized that it would be helpful to create a supplementary system that could help clients quickly diagnose and fix problems with their Fixed Asset environment. With support from the Prolecto Team, I’ve created a new bundle that is a complete toolkit to locate any problems with Fixed Asset records. The flagship report in the bundle is a SuiteQL Query that shows the breakdown of assets depreciated on a Journal Entry for depreciation generated by the FAM module, as described in my previous article.

Example: Journals Not Linked to FAM

Here’s an example of a common problem: Journal entries booked manually for depreciation. These Journals have no linkage to FAM, thus generally they would throw off the reporting. The saved search to locate these is relatively simple if you understand the record structure in NetSuite. There are no direct links from assets to depreciation journals, but there is an indirect link through a record called “BG Summary Record”. A saved search that find Journals for Depreciation that are missing a link to a BG Summary record will reveal these entries:

Conclusion

My very first project as a NetSuite consultant was a Fixed Asset implementation project. It is very gratifying to be able to provide what I believe is a very valuable resource to the community. Like all Prolecto bundles, the FAM Reconciler is free to our clients. If you would like to hear more about getting more transparency into your Fixed Asset technology, or for all NetSuite related projects, please reach out! We would be glad to hear from you.

Reconcile NetSuite Auto-Elimination Amounts

Get clarity on hard-to-find NetSuite elimination entry sources to ease consolidation

Consolidation and Elimination Entries

NetSuite OneWorld offers some really powerful tools to make consolidated reporting simple. A key feature offered is the “Automate Elimination Entries” tool. The concept is straightforward: Intercompany transactions that occur during the period are “flagged” for eventual elimination, and at month end, when initiated, NetSuite will find all transaction lines that have been flagged, and reverse the impact of these transaction lines to a designated “Elimination” Subsidiary by booking special Journal Entries automatically.

Trace Elimination Entries to Source Transactions

Automatically-generated Elimination entries in most instances get tagged on each line with a link back to the source transaction.

These links to the source transaction are in the form of URLs that when clicked, open up new transaction windows as popups. It is not currently possible to reach the “source” transactions from Elimination Journal Entries in Saved Searches or SuiteQL Queries, though the field is available via script as can be seen below (hat tip to Michoel Chaikin for his useful NetSuite Field Explorer).

NetSuite Elimination Entries missing “Source”

Although, as mentioned, the Source transactions cannot be accessed via Saved Search or Query, there is a native report that shows detail from the source transactions. It is called “Intercompany Elimination” and can be accessed via Reports > Financial. However, there are some Elimination Entries that will not get tagged with a “source” transaction and will likewise not appear on the native “Intercompany Elimination” report. This is documented in SuiteAnswers article 34241 and in SuiteAnswers article 106926. As stated in those articles, Balance Sheet accounts other than Accounts Receivable and Accounts Payable (and Equity – not mentioned in the article) will not have a link to the source transactions on the Elimination Journals. This means that for these types of accounts, it is even more difficult to reconcile transactions that should have eliminated and what actually posted automatically to the Elimination Subsidiaries.

Additionally, it is difficult to properly reconcile amounts even those in accounts that do get tagged with “Source”, since the native “Intercompany Elimination” report only goes one way – it shows the Intercompany Elimination entries that were auto-created, and traces back to the original source transactions. There doesn’t seem to be a standard way to start with entries subject to elimination and test whether they were all successfully eliminated.

“Eliminate” flag on transactions

Although I mentioned above that the “source” field cannot be accessed via SuiteQL queries, there is a way to find the “sources” of Elimination Journal Entries, and even when there is no “source” link as described above. There is a field called “eliminate” on all NS transactions, though it often is hidden on the forms. This field is a checkbox that drives the Automatic Elimination process. If we can search all transactions that have this flag checked, we can see the amounts that should have eliminated, and that can be a basis of comparison to the actual eliminated amounts.

SuiteQL Strikes Again!

Although the Eliminate flag is not available in Saved Search, it is available in SuiteQL queries. SuiteQL is a rapidly growing resource for reporting in NetSuite, and several tools have emerged to help the user create reports that have depth and breadth in the database. See my previous articles that discuss ways that SuiteQL can be leveraged. See also Marty Zigman‘s blog article about a custom “renderer” tool that greatly expands the use of this type of reporting.

Here is a sample basic Query that shows transactions that are flagged for Elimination. Carefully note the “FROM” argument and the way the Joins are built; this is the way to bring together all of the elements of a transaction. Also note that the Elimination flag is “tl.eliminate”, which means that the field lives in the transactionLine table (tl is the alias for that table):

SELECT
t.type
,BUILTIN.DF(t.tranid) transaction
,t.trandate
,tl.eliminate
,BUILTIN.DF(tl.subsidiary) subsidiary
,tal.account account_internal_id
,BUILTIN.DF(tal.account) account
,tal.amount
,tal.debit
,tal.credit
,tal.exchangerate
,BUILTIN.DF(t.currency) currency
,BUILTIN.DF(tl.entity) name
,t.createddate

FROM
transaction t INNER JOIN transactionline tl ON t.id = tl.transaction INNER JOIN transactionaccountingline tal ON tal.transaction = t.id AND tal.transactionline = tl.id INNER JOIN subsidiary s ON s.id = tl.subsidiary

WHERE

tl.eliminate = 'T'
AND
t.trandate <= '06/30/2022'

ORDER BY
BUILTIN.DF(tl.subsidiary)
,t.id DESC

Conclusion

I have found that the above query gives the most value when viewed with and compared to other reports, such as the “Intercompany Elimination” report described above, or supplementary saved searches, such as the one suggested in SuiteAnswers article 34241.

NetSuite knowledge tends to build on itself. A whole world of reporting possibilities opened up once I learned the SQL reporting techniques in NetSuite. If you would like to hear more about expanding your analytics horizons, please reach out!