Dynamic Report Building – Leveraging Excel VBA

Back To Blog

One optimization we’ve been striving for with all our clients nowadays is dynamic reporting – the goal is to create reports that are future-proof to GL hierarchy changes. Consider the P&L report below:

Note that the Expense accounts (colored orange) are divided into subcategories Compensation, External Labor, and Strategic. These subcategories are then further drilled into for a more granular display of expense accounts.

Let’s say next year, this firm decides to completely revamp their Expense hierarchy – they rename all their expense accounts, add in new ones, delete old ones etc. Suddenly, this report may no longer work!  As a static report, retrieving the ‘External Labor Expense’ accounts only could encounter an error – perhaps it’s been renamed or deleted!  The report doesn’t show information, or potentially doesn’t even run, resulting in frustrated clients and time spent having to revise the report.  This is the scenario our team tries to avoid; reports need to be as flexible as possible, accounting for future variable change.

One method we use to achieve this is by integrating Excel VBA (macros) into our reporting. Instead of retrieving specific accounts, in a specific order, in a specific format, we retrieve all the accounts and apply an excel macro to do the manipulation for us.

Shown below is the same P&L report, only the macro has been removed:

As you can see a lot is missing! There’s missing formatting, spacing, and subtotals.  All of this was accomplished by the excel macro.

The macro ‘reads’ the report, identifies the accounts which are similar (for example, it knows that Salaries, Benefits, Taxes, and Other Expenses are part of Compensation), groups them and applies the correct formatting and totaling.  By delegating this work to the excel macro, we can focus on what information is retrieved.  In this case, we know that if we retrieve all the expense accounts, we can trust the Excel macro to do the detailed work afterwards.

What this means is that in the future, regardless of what happens to the individual accounts or groupings, this report will always retrieve the correct information.  It doesn’t care how the account hierarchy is rearranged or named – whatever the report retrieves, the macro will figure it out afterwards. This results in less time having to re-visit existing reports and revising them to accommodate new changes, meaning less downtime and a smoother budgeting process.

This is only a specific example of how we integrate Excel VBA, but as I said earlier, it is something we are working towards with all our future implementations.  If you have any questions or would like any other information, please feel free to reach out.

Free Ebook!

How to create an engineering companies digital workflow

Create a paperless engineering company with full digital compliance. Download your free ebook now.