How to Produce a NetSuite Trial Balance with Saved Searches

This article is relevant if you are seeking to produce a Trial Balance with NetSuite Saved Searches.

Background

Many times during our client engagements, we need to work with general ledger account balance data for various analysis or systems integration work. ¬†Often times, NetSuite reports are not exactly what we need. ¬† We may need to display a set of balances on a form. ¬†Many times, a common request I hear is to produce a comparative balance sheet. ¬†The current NetSuite report tools can produce a comparative balance sheet but the column definitions need to be hard coded making them harder to use. ¬† ¬†Ideally, columns would be dynamic based on today’s date.

In these cases where you need more dynamic control, going to Saved Search can be very helpful. ¬†However, advanced saved searches using NetSuite’s underlying Oracle functions can take time to learn,

Trial Balance by Saved Search Illustration

To help the accounting community learn how NetSuite can produce financial numbers, it is important to remember that there is no general ledger as one might think in a traditional sense. ¬†Instead, all balances are generated through the use of transaction details summarized. ¬†This is why NetSuite is a real time system with no need to update the general ledger — it’s always current! ¬†The key to producing financial numbers is to remember these rules:

  1. Balance Sheet: Sum all transaction amounts since inception to the date you want your balance.
  2. Income Statement: Sum all transaction amounts from the beginning of the fiscal period to the date you want your balance.
  3. Retained Earnings: Sum all income statement transaction amounts since inception to the last day of the previous fiscal period.

To help NetSuite accounting minding individuals understand how to produce these balances, the Trial Balance is a relatively simple financial report; yet it is somewhat challenging to produce with a Saved Search.  Hence, it is a good opportunity to see how to build one here.  We get started by defining a Saved Search of type Transaction.

Key Trial Balance Saved Search Criteria and Result Definition

I assume you have basic saved search skills. ¬†So let’s go.

Set the criteria on a Transaction type Saved Search:

  1. Posting = True
  2. Account.InternalID Not Null

Next, define the result sets.  For the most part, I use summary based formula fields.  See screen shot for the definition.

The key value to produce is the balance which is defined as “NVL({debitamount},0)-NVL({creditamount},0)”. ¬† Although not necessary to know here, you may be interested in this article about the polarity of the amount field if you do a lot of NetSuite financial work. ¬†See The Pluses and Minuses of NetSuite Financial Statement Polarity.¬†This NetSuite Saved Search makes good examples for some advanced searching concepts:

  1. Classifying Accounts as “Balance Sheet” or “Income Statement” using conditional logic
  2. Two techniques to Extract out the Year based on the Accounting Period End Date and using it to qualify amounts
See the two images for definition and the output of the result set.  The key is that the summary should foot to zero.

Get the Saved Search Bundle

If you are a NetSuite end customer, you are welcome to the bundle. ¬†My intention is to build a library of financial based NetSuite saved searches as we solve client concerns. ¬†With no obligation, send me a request with your account ID and I will make it available to download. ¬† If you are in a situation and need a professional that understands accounting and NetSuite advanced Saved Searches, let’s have a conversation.

 

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , , , | Category: Accounting, NetSuite, Reporting | 2 Comments

2 Comments

  1. Heidi Jensen
    Posted November 6, 2017 at 2:19 pm | Permalink

    I’m trying to build a report (whether it’s a report builder or Saved Search) to show Beginning Balance, Debits, Credits, and Ending Balance for a given year. Your saved search is close but not exactly what I’m looking for. I need to be able to select the date ranges rather than just using the system date.
    Please help!

  2. Posted November 11, 2017 at 4:17 pm | Permalink

    Hi Heidi,

    The trick to this is to use case statement formulas. You must start from inception and then aggregate all the way to the beginning balance. Did you download our bundle so that you can study how I wrote it? Send me a note here to make a request for it.

    Marty

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>