Getting the reporting and analysis tools we really want

August 10, 2010

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.

Topics: Management, Blog Posts, Strategic Planning, Accounting & Finance

Robert Guild

Reports U Want

Robert Guild is Microsoft Office application developer who creates user friendly, Excel based, financial reporting and analysis tools that stand alone or integrate with accounting applications and other external data sources for enhanced analysis and reporting. He has been corporate controller for two Houston based oil companies, a developer of commercial oil & gas accounting software for exploration and production companies, and developer of a hospital marketing application installed in over forty hospitals. He is an Advanced QuickBooks Certified ProAdvisor who has conducted CPE QuickBooks classes for CPAs and has provided QuickBooks training and consulting to small business owners. Robert transitioned from controller to software developer by purchasing marketing rights to mini computer based accounting software and converting it for use in desktop computer networks. Since that time he has been creating toolbar driven applications that integrate resources of Excel, Word, Access, Outlook, and external applications such as QuickBooks into a single user interface. He currently has three commerical Excel products that integrate with QuickBooks accounting software to provide interactive cash flow management, enhanced job cost analysis, and enhanced product analysis. His goal and passion for customers is “one click”; to deliver exactly the information they need, in as little time as possible, with as little effort as possible.
Read more articles from Robert Guild

Guide to Business Borrowing

Learn what banks are looking for when they prepare to make loans. Our guide covers what business owners need to know when they prepare to borrow.


Download eBook