This article is relevant if you are building NetSuite Reports using Saved Searches especially if the information is related to financial elements.

Background

During a recent multi-phased NetSuite implementation, one of our younger NetSuite consultants developed a sales-related dashboard for the manager responsible for sales. The information outlined the total number of shipments, total sales and other summaries that were related to activities often considered in Customer Relationship Management (CRM) implementations.  The sales manager worked with the consultant and assessed the summary values were reliable. They did this by reviewing the details of the summarized information and it appeared complete.

A few months passed. Over the course of the business, the sales manager was pleased with the operating results of his sales team as he used the dashboard to help him make regular sales performance assessments.  Subsequently, the sales manager mentioned a specific sales number in a company meeting.  An accounting analyst was in present and felt something was not right…

The analyst checked the manager’s dashboard and discovered the sales information was overstated.  The overstatements were due to some recent credit memos that reversed a portion of sales and then related new invoices entered to resolve them.  The dashboard was actually not reliable because the underlying saved search query was built to key on invoices type transactions.  However, this fact was not understood when it was being assessed for reliability because there were no credit memos in the underlying dataset.

Significant Lessons Learned

When the client brought it to my attention, I was embarrassed.  As he explained the situation, it became immediately clear why the situation happened. Two important next steps were needed:

1. Rebuild Trust: what can we do to rebuild trust with our client? I worked on an offer to help us assess other reports and dashboards used in the environment. We knew that we need to get better verification methods and review practices.
2. Improve Skills: we must become better at producing reliable financial information using NetSuite saved search tools.

Improving NetSuite Saved Search Based Summaries Relative to Financial Statements

The situation above prompted me to think carefully about the philosophy and approaches for producing saved search based summaries especially as it relates to information presented on financial statements. It is important to understand how the NetSuite transaction system is structured in order to produce financial information.

Learn to Think in Business Terms when Building Saved Searches

The opportunity in the NetSuite platform is to combine knowledge in two major discourses: Accounting and Computer Science.

1. Accountants think fundamentally through the general ledger chart of accounts;  it is a result of their tradition.  They also love spreadsheets — but they really unlock their minds when they learn databases.   You don’t need to know databases however to operate NetSuite.  Accountants have widespread agreement on the meaning of assets, liabilities, equity, income, and expenses.
2. Computer Science (systems analysts) folks think fundamentally relationally via table based rows and columns of information. Yet they often lack the background in double-entry accounting.  It thus represents a potential blind spot.
3. Business people think more like accountants and less like systems analysts.  They think in terms of sums of information and generalized business concepts such as Sales, Shipments and Inventory.  Accountants can distinguish Sales from Revenue because they think about earned vs. unearned concepts.  Thus, an accountant has more refinements in business distinctions — and thus they can operate with greater precision.

When it comes to working with NetSuite which at its core is an accounting system, computer science folks will be “that much better” if they learn some fundamental accounting concepts.

Avoid NetSuite Transaction Types in Saved Searches to Think Like an Accountant

The primary flaw in thinking for our young consultant was to organize transactions by type.  Here, he heard the business person wanted Sales and he went to an Invoice Transaction Type.

It is important to emphasize that transaction type searches are dangerous when you want to summarize information; especially financial information. It’s completely understandable that our minds go to transaction type in searches — in our day-to-day work, we are constantly distinguishing transactions based on type.

Learn How to Build Reliable Saved Searches to Produce Financial Information

The following list is meaningful when producing information that business people general reference. Thus, consider these saved search related concepts:

1. Transactions: NetSuite has generalized different transaction types, such as an invoice, or a cash receipt, into a single transaction search.  This means that we can easily get at information across different transaction types in order to summarize without difficult joins. The structure is great news because it is often our opening saved search attack angle for any valued business metric.  Work on becoming highly competent at understanding that search angle and its related joins.
2. Management vs. Financial Information: NetSuite has made it easy to distinguish management information from financial information.  By using the Posting flag saved search criteria: Posting = Yes means Financial; Posting = No means Management. Understand that financial means earned — and earned, in general, means there is a consequence.  Consequences matter in business.
3. Dynamic General Ledger: While we can not see what NetSuite’s database looks like behind the scenes, Saved Search gives a mental model to work with.  Thus, there does not appear to be a general ledger in the old school sense (a record structure that is posted from another set of records). Instead, all transaction elements that have the simple posting = true flag set effectively mean they are on the general ledger. Readers may want to reference my article, How to Produce a NetSuite Trial Balance with Saved Searches.
4. Financial Balance Sheet Accounts; Search Based on Inception: If you want to explain a Financial balance sheet number, you will need a transaction posting search that has no start date but has an end date.   In contrast, Income statement numbers are derived within the accounting year.
5. Transaction Dates versus Accounting Periods: Be careful when working with financial information to remember that they are fundamentally expressed by the Accounting Period time dimension. While accountants often speak about a month, a quarter and a year end in terms of calendar distinctions, the database for financial statements is held together with the Accounting Period table. The accounting period is stamped on every posting transaction (although, I have seen instances via data migrations where the accounting period was not stamped). Depending on the NetSuite configuration, transaction dates may be outside of the accounting period. It’s easy to trip up on this.
6. Subledgers are a Function of Account Types and Special Account Types: A balance sheet account should always tie out to the sum of the detail. Due to NetSuite’s dynamic general ledger, Saved Search can be used to explain amounts expressed via balance sheet accounts.  Learn NetSuite’s Special Account Types and Account Types so you can avoid keying on a specific account number; doing so makes your saved search brittle and less flexible to adapt as the chart of accounts changes over time.
7. Be Careful with Inactive Accounts: NetSuite uses a concept of marking records as inactive as a way to “get them out of the way” for your searches. By default, searches have the inactive flag = false turned on. However, make sure you indicate that inactive accounts be referenced so that you do not miss transaction detail.
8. Understand Foreign Currency Amount: A transaction is anchored in the subsidiary (base, local or functional) currency. Every transaction is priced. The price for the local currency is simply 1 (one). Thus, the transaction Amount field is derived by multiplying the Foreign Exchange Amount to the Foreign Exchange Rate. Be sure to set the Consolated Exchange Rate result setting to “None” so that the search uses the exchange rate on the actual transaction and not the consolidated exchange rate table.  See How to Produce a NetSuite Trial Balance with Saved Searches for an example.
9. Avoid Transaction Type: Finally, with all the preceding suggestions, if you are going to output a financial number on any search (or custom dashboard), avoid the use of referencing the transaction type. This one major suggestion drives one’s thinking to be more fundamental and generalized — and will then catch all the different transactions that may come into play comprising a financial balance.

Reference Previous Articles that Leverage Financial Saved Searches

Readers may be interested in previous articles that I have written that use these concepts above. These can be helpful to teach concepts such as tallying up a balance.

1. How to Produce a NetSuite Trial Balance with Saved Searches
2. Explaining NetSuite’s Inventory Received Not Billed Account
3. How To: NetSuite Stock Inventory Movement and In-Transit
4. Learn How To Accumulate NetSuite Inventory Balances via Saved Search
5. Lost Netsuite Accounts on Financial Statements?
6. The Pluses and Minuses of NetSuite Financial Statement Polarity

Build Reliable NetSuite Dashboards, Reports and Saved Searches

Even with the thinking above, it is important to assess the reliability of crafted information that will drive decision making. Generally, if you can tie out saved search information to a built-in financial report, this is good. It’s also important to have others look at information to see if they too can tie it out. Sometimes, just by changing your NetSuite login role, the database may return different results.

My hope is that this article helps you write better NetSuite searches while it increases your sensitivity for the issues that can come up if we don’t tie out information between other trusted sources.  If you found this article valuable, feel free to sign up for notifications of new articles. If you would like to discuss help with your NetSuite financial saved searches, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top 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 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

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