Back to The Source

Understanding Your Chartfield Design

December 23, 2015 | Josh Dickson

Clients often question their chartfield design. It is a big question comprised of many smaller questions. Some of those questions are best answered with questions. Here is one such answer in the form of a question.

Can you derive a balance sheet and income statement using only your ACCOUNT chartfield? Not every company can. But it pays dividends to have an ACCOUNT list which can produce key financials unsupported by other chartfields.

It can be easy to wind up with a chartfield design which, for instance, requires the combination of DEPTID and ACCOUNT to distinguish between below-the-line expenses and Cost of sales. After all, one of the tenets of data normalization is that a field should have one and only one meaning. With this rule in mind, an accountant designing the chart of accounts (COA) might opt for one single salary expense account. In the same mindset, they might designate the DEPTID to distinguish between cost centers, and organize the cost centers so that a range of them constitute cost of sales. From a general data design standpoint, they’ve done well. From a COA design standpoint, they are in for some challenges.

The following assumes financial statements are nVision reports, although the same challenges would exist with virtually any reporting tool that relies on a tree-like hierarchy of data elements. Consider the following IFRS line items from a Statement of comprehensive income:

  • Revenue
  • Cost of sales
  • Gross profit
  • Other income
  • Distribution costs
  • Administrative expenses
  • Other expenses
  • Finance costs
  • Income tax expense

Even with controls in place such as tree auditing and requiring all values in trees, a check-figure is advisable. Without a check figure, how would one know whether every P&L account is included in the report? So, the nVision layout might include a single nPloded line at the bottom containing every P&L account. Because this figure should agree with the total comprehensive income. But there’s a problem. The Cost of sales line is defined both by ACCOUNT and DEPTID. So much for the check figure. So much for knowing easily whether the report is accurate.

Here is another challenge one faces in this scenario, albeit one specific to nVision. If the tree performance options use selector tables, the combination of chartfields sometimes result in a Cartesian product. Then what should be a thirty second report crawls for hours before finishing. One might avoid the Cartesian product by changing the performance options to static values. This can work. However, static values introduce the risk of creating a SQL statement too large to parse by the database management system. Neither predicament is good.

A robust ACCOUNT list avoids all these problems. How robust? Enough to define every line on your financial statements. One might ask whether this defies good data normalization: whether the ACCOUNT then carries the burden of defining multiple things. From one general perspective, it does define both a category of expense (say, salaries) and something else (whether this is above or below the line e.g. Cost of sales). But within the context of financial reporting, it does not define multiple things. The ACCOUNT defines one thing. It defines the line items on your financial statements.

This is not to say it is a mistake to designate DEPTID values as being Cost of sales. On the contrary, it is rational to do so. The point is, DEPTID does not exist to provide key financial statements. Neither do the other chartfields. Rather, they exist do enable analysis of your data in other dimensions, which is great discussion for another day.

Let’s Chat


Keeping Your System Fit – Data Archiving

I was able to pull up the page in seconds in January.  How come it takes minutes in October? The batch processing window used to be much shorter before; now we are not able to meet the SLA requirements. What’s happening? Remember the help desk calls regarding PeopleSoft application performance issues and DBAs, PeopleSoft Administrators and … continue

Unexpected Emergencies Consume IT Professionals Workday

It is a known fact that IT professionals have to adapt to unexpected technology emergencies. However, did you know that IT professionals spend almost one-third of their average workday identifying, troubleshooting, and resolving these unplanned situations?  “The 1E 2017 IT Incident Response Report," a recent survey of 1,014 IT professionals … continue

Part of the Bigger Story

Find your solution