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:

1. 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.
2. 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.
3. 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.