1. column
  2. TECHNOLOGY Q&A

Accounting and ERP systems: A look inside drillable financial statements

By J. Carlton Collins, CPA

Please note: This item is from our archives and was published in 2017. It is provided for historical reference. The content may be out of date and links may no longer function.

Related

TOPICS

Q. Is there an inexpensive, easy-to-use, third-party add-on solution for achieving drillable financial statements when your small business accounting system does not offer that functionality?

A. I am a big fan of drillable financial statements and reports as they enable readers to drill up, drill down, or drill around to instantly view supporting or related data. Drillable data is not only easy to navigate, it also provides a stellar audit trail sure to make any auditor smile. While many popular accounting and enterprise resource planning (ERP) systems offer drillable data, an example of a highly drillable product is Microsoft Dynamics NAV, which is used below to demonstrate this type of functionality. As a starting point, the screen image below shows the Dynamics NAV Customer Card. Clicking the down arrow next to the $0.00 balance amount for the customer (Cannon Group PLC) drills down to a listing of the underlying Customer Ledger Entries. In this example, we can see that even though the customer currently has an outstanding balance of $0, there have been numerous sales and receipts that all net to $0.

techqa-1


To drill down further, select any of the customer ledger entries ($-52,169.68 in this example) and click the Navigate button (near the lower-right corner of the Customer Ledger Entries listing) to display the Navigate dialog box (pictured at bottom left) summarizing the transaction’s various line items posted in the accounting system. In this example, the $52,169.68 transaction was posted in the books in 11 places, as follows:

  1. Once as a sales invoice (to record revenue).
  2. Six times as a general ledger (G/L) entry:
    1. To deplete (or reserve) the inventory item;
    2. To record the customer’s account receivable amount; and
    3. To set up state, county, city, and local sales tax liabilities.
  3. Four times as a tax entry (to record state, county, city, and local sales tax expenses).

From here, we can drill deeper by highlighting the Posted Sales Invoice line item and pressing the Show button to drill down to the actual Posted Sales Invoice, as pictured above. In this example, we see that the customer purchased an ANTWERPConference Table for $647.80.

techqa-4


From this Posted Sales Invoice, Dynamics NAV enables users to select the ANTWERP Conference Table and press the Navigate button to drill into the inventory module—a process often referred to as “drilling around” because, rather than drilling deeper into the same data table, the user is “linking to” and “drilling down” into a completely different table of data (the inventory data table in this example). This action displays both the table’s Item Statistics and Image screens. The statistics screen includes the amount of sales, cost of sales, profit, and profit percentages for the conference table item by day, week, month, year, and life-to-date (by using the scroll tool located at the bottom of the Item Statistics screen).

techqa-table


This type of drillability is impressive when you consider it started from the customer’s outstanding balance shown on the Customer Card. Dynamics NAV also enables users to drill upward toward summary information by clicking the up arrows located next to the values. In addition, Dynamics NAV includes the following drilling tools, which are included with the standard product:

  • PowerDrill tool (demonstrated above): Allows users to navigate transactions down to the originating documents and to related modules, as well as allowing users to build custom lookups for future use.
  • PowerSeek tool: Allows users to search for data by criteria (such as a specific amount or text string) and can sort the data based on any transaction data field.
  • PowerFilter tool: Allows users to build queries combining multiple fields of transaction data. For example, a CPA might build a query to summarize customers for specific states, cities, ZIP codes, territories, and salespersons to perform a test of transaction work in conjunction with a financial audit. A CPA could also use this tool to slice or stratify reports by departments, projects, dates, and other parameters.
  • TrendScape tool: Allows users to display numbers on a lifetime, annual, quarterly, monthly, weekly, or even daily basis to help detect trends. This tool also allows users to define specific sets of periods to analyze the results of promotions or special offerings, or the impact of major economic events.

Unfortunately, there are no simple, inexpensive third-party add-on products that easily add data-drilling capabilities to an accounting or ERP system that doesn’t already offer this type of functionality. This capability must be built into the accounting or ERP system’s database, tables, or programming code, and the system’s complete data set is needed to provide full drillability. While it is technically possible to import accounting or ERP system data into sophisticated tools that include drillability (such as Clear Analytics, IBM Cognos Business Intelligence, Oracle Hyperion Planning, Sisense, and others) the costs and/or complexity of these business intelligence solutions are typically beyond the reach of many small businesses. Also, while it may be technically possible to export accounting data to Excel and build drillability into the data set using the grouping, subtotaling, pivoting, or other tools, the extensive amounts of programming and time required are rarely justifiable.

Advertisement

The most reasonable way to employ drillable functionality is to purchase an accounting or ERP system that has this functionality already built in. A sample listing of some popular accounting and ERP systems that include at least some levels of drillability is shown below.

Examples of accounting and ERP systems with drillability

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

Advertisement