Best Practices for Modeling the General Ledger in NetSuite

This article is relevant if you are contemplating how to leverage NetSuite’s concept of the general ledger for enhanced reporting and day-to-day use.

Background

Assisting organizations to get on to the NetSuite platform since 2008, we have seen many different situations related to the general ledger.  Prior to my work with NetSuite in 2008, I was educated in accounting and earned my certified public accountant status in the 1990s while I was working at the Big 4 professional services firm, Deloitte & Touche. Before, during and after my career at Deloitte & Touche, I developed my tactical and strategic skills in databases and software development. Consequently, it has become natural for me to think fundamentally about accounting and database concepts as I contemplate business, accounting and reporting considerations and challenges.

During a recent NetSuite implementation, my firm was assisting our client to revamp their chart of accounts.   Under this client’s situation, over the decades, the chart of accounts had grown to support all kinds of business and reporting questions.  The client had invested in developing a reporting data warehouse that translated all the general ledger entries into reports that were distributed to organizational managers.  They had pride in their data warehouse — while I did not want to diminish their pride in their reporting tool, I often wonder if these reporting tools are actually needed when I see the potential with NetSuite.   When my firm was asked to plan the implementation, I did a cursory review of their chart and their management reports. It took a fair amount of time to review their multi-entity reports — I needed accompanying explanations to better understand their business.  I have been told on numerous occasions that I can generally synthesize the essence of a business quickly — especially if the language and distinctions used in conversations are coherent.

As such, I knew that the chart of accounts remodel would take some effort. And it did indeed take some effort to remodel.  During the journey, I realized I had been in the situation a number of times.

Accordingly, as I contemplated writing this article, I realized that there is an opportunity to help both parties educated in accounting and/or computer science to understand NetSuite’s concept for the general ledger and how to model information to gain the benefits of powerful reporting and easier data entry (account coding).

NetSuite’s General Ledger Conceptual Build Up

Many folks not educated in accounting often doubt their ability to understand the general ledger and accounting. At the same time, I find those educated in accounting or bookkeeping often have trouble considering what the general ledger is — at the fundamental level;   If we can get to a shared understanding of the general ledger fundamentals, we have a better chance to build a powerful model.  The general ledger is indeed meant to be modeled or shaped to fit business reporting requirements.  Done well, reporting and data entry all become low-cost to craft.   I invite you to this conversation to help you build your knowledge of the general ledger, in a step-by-step fashion, as offered by NetSuite,  to see how the concepts can be used to your advantage.

General Ledger as a Table of Values for Summarization

To get the conversation going, we need to first start with the concept of a table. A table represents columns and rows and a spreadsheet is a great tool for its visualization. See the table image below.  The important conceptual rules in our table are the following:

1. Accounts Sum to Zero: If you add up all the accounts, they must always equal zero.
2. Debits Equal Credits: the sum of all debits must equal the sum of all credits. Note, that within the NetSuite platform, credits are fundamentally represented by negative numbers in the database. A debit and a credit are really just presentation views of values depending on if they are positive or negative values.  I invite readers to review my 2013 article, The Pluses and Minuses of NetSuite Financial Statement Polarity.

These concepts are also recognized as fundamental in double-entry accounting.

Accounts are Categorized into Types

The purpose of double-entry accounting is to produce an error control practice and to ultimately summarize transactions into meaningful information. To get meaningful information, we build financial statements by categorizing our accounts into different types. Two major financial statements are the balance sheet and the income statement.  We won’t get into these two reports — but importantly, so that NetSuite can produce these reports, they impose a structure on us.  All general ledger transactions must be defined with an account type — these are built into NetSuite and can not be modified.  See below.

When we are introduced to bookkeeping and accounting, we learn about the five major types of accounts: Assets, Liabilities, Equity, Income and Expenses. Below is NetSuite’s list of account types which all fit conceptually in the major ones we learned about in our first-year bookkeeping course. By imposing a structure, NetSuite can ensure their out-of-the-box reports will work. Note, the concept of the Database ID presented below is to help produce a unique string value with no spaces — to avoid spaces in naming structures is a common convention when structuring databases and computer programming.

The Account Number (is Meaningless)

The account number is usually where accounting folks begin to become more rigid in their thinking. In NetSuite, the important concept to understand with the chart of accounts definition is that the account number is just another string value that must be unique.  It does not need to be a number. You can easily create an account number called “1134.clearing” and NetSuite is completely satisfied. At the end of the day, the account number is actually meaningless and not needed. The idea of no account number may be hard for accountants to let go of — especially when coming from other accounting systems that use a concept called account number segmentation to produce meaning. I value the account number only because it is guaranteed to be unique and thus can be used as a database key when passing information to third parties.  For example, outside accounting firms will use the account number to help load information into their analysis systems.

The Posting Flag

NetSuite’s concept of posting is simple.  It is a true/false flag to indicate if the transaction has a financial impact (posting equals true). Common to many other older school financial systems, there is no real posting routine except to have the posting flag change from false to true. Oftentimes, when approving a transaction, NetSuite is effectively flipping the flag from false to true.

The Accounting Period and Transaction Dates

NetSuite has a concept of accounting periods to organize how to break up time into reportable components. The most common way accountants break up time is by natural calendar using months and years. But all kinds of ways can be used to break up time into reportable segments. Readers may be interested in my article, NetSuite 4-4-5 Accounting Period Definition Consideration.  The most important consideration is that NetSuite uses a transaction date as a lookup value to automatically apply (default) the accounting period definition on the transaction. NetSuite can summarize financial reports by both date or accounting period — this can confuse folks if transactions have transaction dates that are not aligned with accounting periods.

In NetSuite’s general ledger model, all transactions must have an accounting date and accounting period.

NetSuite Dimensions

The major distinguishing general ledger capacity offered by NetSuite is the concept of dimension. The dimension is simply another way to categorize the general ledger accounts, just like the imposed Account Type.  Dimensions open up a new opportunity to think more broadly, more generally, and more fundamentally about accounting transactions to gain powerful reporting.  Before the availability of custom dimensions,  NetSuite offered the following dimensions:

1. Subsidiary: NetSuite uses the subsidiary concept to distinguish companies; every transaction must have a subsidiary value. If you are not using NetSuite’s One World feature, the value of the subsidiary is always 1 and is hidden from view. If you summarize all posting transactions by the subsidiary, the amounts will sum to zero and debits will equal credits.
2. Location: location is generally designed around inventory operations and is most frequently used to represent a physical street address.  Note, that NetSuite imposes a limitation that a location can only exist in a single subsidiary.
3. Department: anything you want it to be. Most folks think of it as functions in the organization such as Sales, Marketing, Operations and Administration.  That’s primarily because the word “Department” has a traditional meaning.
4. Class: like department, the class can be anything you want it to be.

Once you begin to see that department and class can be anything you want it to be, then it is important to model carefully to gain significant reporting benefits. With NetSuite’s new custom dimension capacity, you can now create a large number of dimensions that are not limited to two (department and class). In my mind, I like to use NetSuite’s custom dimensions because they, for the most part, are more flexible than the Department and Class in terms of built-in filtering and sourcing.

Powerful Use of Dimensions / New Thinking for Accountants

It’s in the transition to using dimensions that I frequently run into challenges helping our clients model their businesses.  Our client was using the words “cost center” in the old way of working but there was no clear place this concept was modeled. In their data warehouse and reporting, the concept of Cost Center “530” was a hard-coded reference to a single company and a number of different general ledger account codes that the accounting department “just knew” how to code during data entry work.

In our work with the client, we had to step back and ask them to think more fundamentally about their business. Their language was effectively sloppy. Meaning, people mentioned the words cost center, and division, and department loosely to mean the same thing– if they said the word “Centrifuge”, they knew what they meant to each other.  But I asked them the question, “what is Centrifuge”? In their petroleum-based business, was it a product line, a manufacturing process, or a profit center? As I asked these questions, they too did not have an agreement around the concepts of product lines and divisions and so forth.  As I reviewed their existing financial reports, I would show them all the inconsistencies in the way they were expressing information. The bottom line is that the mental model and resulting structure of their current information systems model was a high cost to use and inflexible.

Using Dimensions as Extensions of the General Ledger

The first order of business is to help the client see how the power of good dimension modeling will simplify their general ledger understanding. Because they had not previously thought carefully about modeling, as the business needed answers to financial questions, they would invent one or more general ledger accounts to capture data. For example, if they wanted to measure the margin on “Hazardous Products”, they crafted the two following accounts:

41245 Hazardous Product Income
51245 Hazardous Products Cost of Sales

They knew that to get the margin on Hazardous Products, they needed an income and cost of sales account. So they created two accounts to take care of this question. Over time, the chart of accounts grew to over 5,000 rows. To create a Hazardous Products margin report, they needed to effectively hard code 41245 Hazardous Product Income less 53345 Hazardous Products Cost of Sales.

NetSuite is fundamentally a structured database.  The modeling goal is to avoid these hard-coded definitions and allow the power of database technology to filter, pivot and summarize easily.  For the accountants, all we are doing is extending the concept of the table previously discussed to include more information.  At this point, we need to define more dimensions to have values that are meaningful to the business. Thus, after much back and forth, we came up with the following:

1. Region: managers would work with the production of goods across different manufacturing locations. We organized each location into groups called regions. For example, one region was called South and another was called North East. A manager was assigned that region and then information could be summarized to address each managers’ regional concern.
2. Product & Service: In this implementation, we were gathering information from other “business operating systems” for data import.  Thus, we had no real product database. We elected to create a dimension called Products & Services which held values that all business managers would understand. This can be tricky in NetSuite because it offers an item concept.  Items are the traditional area to model products and services. Yet, for our reporting requirements, we crafted this new dimension without using items to allow us to create Products & Services reports based on the values defined.

Avoid Crafting New General Ledger Accounts

The opening in powerful dimension modeling is to realize you do not need to craft new general ledger accounts as the business evolves. Instead, if you have modeled the business functions well with dimensions, when a new product or service is introduced, you add a new value to the Products & Services dimension list — this effectively opens the general ledger for more reporting.

Getting back to our margin discussion, the client ended up creating the following general ledger accounts to help with margin reporting which would not need to change:

40000 Products and Services Income
50000 Products and Services Cost of Goods Sold

Margin is now 40000 Products and Services Income less 50000 Products and Services Cost of Goods Sold but without respect to any other consideration.

(Note the careful reading of NetSuite’s imposed structure illustrates that we could define Margin as NetSuite Account Type “Income” less NetSuite Account Type “Cost of Goods Sold” without respect to account name or number.)

Then, using NetSuite’s concept of dimensions, we can now do the following:

1. Filter: we could filter our transactions for the value “Hazardous” and we would have a result of Hazardous Product & Services Margin.
2. Column: instead of filtering, NetSuite’s income statement report writer allows you to select which dimension you like to report as a column. This makes the report wide and is very similar to spreadsheet pivot.

Click on images to get a feel for how the report writer can be used.

Finally, although I am emphasizing the use of dimensions, there is a trade-off between its use and crafting traditional account structures.  This takes some back and forth to recognize where to model information — we want to use dimensions especially when they can apply their value to be meaningful across many different general ledger accounts.

Using Parent / Child Information

Another important concept offered by NetSuite is the use of parent/child records to express hierarchies.  NetSuite offers parent/child record concepts throughout its database model and these can be used to more powerfully express information.  For example, we may have multiple banks to hold our cash.  We can use parent/child on general ledger accounts to express these on the balance sheet as follows (the colon distinguishes the parent from the child):

1001 Cash : Bank of America : Operating Cash
1002 Cash : Bank of America : Money Market
1003 Cash : JP Morgan Chase : Electronic Payments
1004 Cash : JP Morgan Chase : Certificate of Deposits

In this case, when we use general ledger account 1001, we are actually getting three pieces of information: a) Cash; b) Bank of America and c) Operating Cash. We can easily now summarize information to see how much cash is available at Bank of America.

We can also use parent / child concepts in our dimension work.   In the case of our Products and Services dimension, the client wanted to track Hazardous as follows:

Transportation : Internal Truck : Hazardous

Thus, they can now roll up information using the parent/child and make more meaningful reports without any additional effort.   They tag a transaction with Hazardous and they get the value of Internal Truck and Transportation as well.  See the related image to see how the parent / child dimension now shows as a columnar report hierarchy.

Planning for Modeling The General Ledger

In my 30+ years of experience working with a wide array of clients in financial systems implementations, I have found this modeling exercise sometimes challenging for our clients.  Sometimes, our clients do not see the benefit — they are so used to the old ways that they don’t see the limitations in their current practices.  In addition, I have found that the exercise often brings forth other business important questions — we often need to get more executives involved so they can work on their language and distinctions to come up with a new capacity and common way to measure their own business operations.  This process can be slow to gain agreement among larger audiences — thus, it is always important to gain project sponsorship at the highest level when producing NetSuite implementations.

In all, it takes time to realize the benefit of the modeling and this must be weighed against the project effort and investment available for getting the project complete. Modeling well is the kind of effort that is difficult to do later — because if we don’t have time today, in all likelihood, we won’t have time tomorrow.  And once we have transactions recorded against values that are not modeled well, it’s a larger job to restructure because we may need to unwind our poor upfront structures — and this is why so many organizations jump into a data warehouse — because they try to fix the problem “downstream” in the reporting tools.  Why embrace a powerful NetSuite ERP system unless we can get the major value from the platform?   The platform is constrained unless we do the real work to conceptualize the business into the system with good modeling.

My hope is that this article helps accountants and computer science-based folks understand the NetSuite general ledger better. I believe a major difference in getting value out of the NetSuite platform is the work done in the planning and modeling.  The idea of a central database to run the entire business can really only be realized if we plan and structure NetSuite well to reflect the actual business operations upfront.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them.  If you are having the challenge to model your business well on the NetSuite platform, let’s have a conversation.

Be Sociable, Share!

• 
• 

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