Analyzing General Ledger Transactions

While most accounting and ERP systems provide a plethora of canned reports, there’s always a need for that one “perfect” report that isn’t available. So, there are typically two  choices:

  • Hire a developer to build that report
  • Extract multiple reports into Excel, and using VLOOKUPs and other wizardry, create that perfect report

Wait! There’s another way, which in many cases, is the best choice: Pull all the transactions into the spreadsheet and analyze them with pivot tables.

Let’s take the case of General Ledger transactions. It is possible to extract the transactions into a spreadsheet. Excel 2010 and 2013 can theoretically handle more than a million records. SQL databases can handle much larger numbers of records.

What would you do if you had each transaction available to you in a spreadsheet (with all the columns you wanted)? What is you could easily summarize, filter and drill down into the data (without writing a single line of code)?

Here is what some companies we know do with General Ledger transactions:

  • Analyze trends
  • Compare revenue and expenses with respect to:
    • Customers, Jobs, Products, People, Fiscal Calendars etc.
  • Find outliers and data entry errors
  • Find incorrectly classified transactions
  • Create summaries and be able to drill down to the summary
  • Do what if analysis
  • Use historical data to create forecasts
  • Recalculate foreign exchange transactions using rules not available in the ERP system

 

Most accounting and ERP systems don’t provide a “data dump” of the ledger transactions, so a custom extract is required.

Even if the “data-dump” was available, there are so many choices of columns available, there would still be a need for a custom extract would still be there.

Once you get used to plugging transaction-level data to an Excel pivot table, you are going to find little use for the canned reports available in the ERP system.