I often get requests to merge financial data from multiple sources into Excel to create reports or provide a starting point for analysis. Excel is so popular because it not only provides a broad array or analytical and presentation tools but because it is so flexible. Users are free to create their own powerful presentations and sophisticated analysis.
It is this great flexibility that also creates a significant risk that a small error, in a formula for example, will create very professional looking reports that are wrong, wrong, wrong. Don’t misunderstand me. I am not knocking Excel at all. I think it is an amazing tool. The best way to explain my perspective is by analogy. How about “guns don’t kill people, people kill people”? Nobody says, “his gun shot him in the foot”, right?
Where is all of this leading? How about if we eliminate the risk of human error in our Excel worksheets? While we are at it, let’s turn all of those tedious tasks of creating formulas, charts, and reports into a couple of clicks on a toolbar. We can do this using a programming capability that comes within Excel.
Think about what that these “stored procedures” do to assure that the Excel reports you rely on are correct. When you program a task in Excel’s visual basic the program will always create the formulas, charts, and presentations exactly the same way. This means you write the code once, test it thoroughly to make sure the results are correct, assign it to a toolbar, and forget it.
Be Careful What You Wish For; You Might Increase Your Chances of Relying on Bad Information
It’s not enough to simply envision the output. You need to control for all manual maintenance tasks and potential for user error. Otherwise, you have a really fast and easy report generator creating reports that require a lot of time to manually validate.
I just completed a project in which the automated tasks in Excel included retrieving project related expenses from QuickBooks and merging them with budget data maintained in Excel. With one click the client retrieved the QuickBooks data and created the budget-variance report they wanted.
There was just one really enormous problem. They manually maintained a cross reference between QuickBooks account/class combinations and the budget categories in Excel. In testing the programming and validating the results back to QuickBooks I discovered the cross reference was incomplete. Whether this was an error or intentional, the client had no easy means to assure their report was correct.
I solved this problem by creating several additional supporting reports that gave the client the ability to quickly assure their primary reports were correct and to easily identify updates required to their cross reference table. These additional reports were critical to the client getting what they really wanted.
Identify not just the reports you want but risks of error inherent in creating those reports. Then, you will get the results you really want.