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.