Tech Trends

Business Metrics–Static to Dynamic

Written by Chuck Vigeant

The traditional methods of data gathering– especially in the world of compliance reporting – is to provide a report that can be run over and over again, with the same format, and with few options.  In this article, I discuss the evolvement of business metrics, and what it will take to make information quickly available to business decisions makers – at the small and mid-size business level.

I also want to discuss some of my experiences as they relate to the Excel Pivot Table arena.  This is the most powerful and easily accessible tool, yet I contend that many of us ‘don’t get it’.  Want to make a difference to your client?  Want to find another revenue generating stream?  Learn the power of Pivot Tables.

In my earlier article on Business Metrics I discussed the move towards consulting with clients about business outcomes (and away from the QuickBooks/Peachtree consulting of the last 20 years) , the role that business metrics play, and a list of customer related metric measurements.

Metrics and Business Analysis is NOT a static operation

I still have many clients who have come to me, where the owner or manager has looked at the same report for years and years, and has no desire to change their particular recipe for success.

In fact, the most successful business owners with whom I have interacted have had a daily one page summary of the data that allows them to take a pulse of their business, and then use what they see to formulate short term tactics. Suffice it to say, that it took them multiple attempts – even years – to arrive at their final format.

As customers move from the common report to business analytics, we emphasize that data is dynamic – and their operation is dynamic. A business owner may analyze a particular piece of information one week, but then look at something totally different the next – depending upon the situation, strategy, or short term tactics. Many times, delving into one subject area prompts questions about another area.

Accountants do this when looking at a balance sheet, cash flow statement, or profit and loss.

Another example would be to look at the most profitable items, and then turning around to find out which customers buy the most profitable items. Maybe you look at what combination of items a typical customer purchases, or what cross sell strategies come into play.

What prevents us from doing dynamic analysis

IMHO, what has kept most business owners from doing this sort of thing is that it always requires a rocket science degree, or the high costs of a programmer to extract data and create a report. Then they are stuck with fixed output.

There are two things needed in this arena, to make metrics truly usable:

  1. Find tools that allow you to deal with ‘data on the fly’, that don’t require you to have a rocket science degree, and that don’t confine you to ‘packaged analysis’.
  2. Find tools that allow you to move from one area to another without having to re-program or require additional programming costs. Pivot tables are a perfect example of this.

CLEARIFY’s℠ QQube Technology is our first foray into making data available without need the rocket science degree, but our customers constantly challenge us with requests that stretch the confines of our technology vision. When I talk to other vendors that provide reporting or analysis tools like XPanded Reports, or WebKPI, I always nod my head when I hear ‘war stories’ of customers constantly asking for ‘what isn’t in the box’. After 10,000 custom report solutions, tell me about it!

The ideal that QuickBooks provided, was that ‘anyone could use it’, even though, we as accounting professionals and pro advisors knew better. There is a certain expectation that any data analysis can be brought about at the snap of the figure, but trust me, many of us will be trying to perfect this, for the rest of our lives. It is VERY hard to do.

A colleague asked me: “if your tool is so easy to use, why do you need training?” My answer was: “for the same reason that clients need your expertise to deliver QuickBooks”. Report creation, QuickBooks knowledge and data interpretation are still needed for any analysis.

I have been amazed that even the mention of Pivot Tables scares the so called Excel ‘experts’; you can hear a pin drop. Yet Pivot Tables in Excel 2007/2010 are extremely easy to use, given the understanding of 4 or 5 simple concepts.

Sometime, the day will come where you are able to speak into your handheld device about what you want from your data, and it will automatically appear. I hope that I am alive to see that day. But until then…..

‘Old news’ vs. preemptive information

At CLEARIFY℠ we do literally thousands of construction and job costing reports: traditional comparisons, over/under, bonding, line waivers, etc. But the forward thinking customers are those who wish to be warned daily about:

  1. Expenses beyond committed costs.
  2. Warning thresholds for estimated to complete calculations.
  3. Project hours or overtime hours.
  4. WIP based upon time tracking entries

What about inventory forecasting? Wouldn’t you like to know what DAY your Purchase Order opens are expected to arrive? How about being warned when your inventory thresholds are breached? We know when items are sold, but what about items consumed within an assembly? Wouldn’t it be nice to have that information at your fingertips?

This is one more area which has been traditionally expensive because the owner has to hire programmers to setup the base information, but is then left without any flexible options to change warning thresholds on the fly.

As the technology to retrieve data becomes easier for the average user, there will be more requests for preemptive information. At the moment, people may be afraid to ask, because it has been historically expensive to provide, exists in narrow subject areas, or is altogether a separate program.

How do we get from static to dynamic?

In our boot camps, and training materials for SAP® Crystal Reports, we lay out the standard operating procedures for creating reports: Getting a picture of the report, ensuring the correct order of the requested fields, determining the source of the data, creating a list of groupings, e.g. sales rep then customer then invoice, format requirements, etc. etc.

Crystal Reports is still the best tool for creating pinpoint format reports, and when you don’t have to figure out where the data is, it is fairly easy to use. But dynamic analysis is not its forte. You can insert many filters, and conditional groupings, but that requires an advanced knowledge of Crystal Reports.

I have staked much of our risk on the fact that Excel is still the tool of choice for data analysis. Formatting is not always as pretty as it is with Crystal, but for analyzing on the fly, it is still the best – hands down. If I want to look at information by vendor by item, but then wish to view the same metrics in a different order, it is a 3 second drag and drop operation in a pivot table; no programming, no thinking.

When I worked on the Business Analysis project for Intuit, and helped put into place the building blocks for the Business Objects tools, I saw the possibilities of dynamic analysis that so many had asked for. Drag and drop capability – but with a ton of options.

When we as a company decided to make our QQube technology ‘Excel friendly’, pivot tables where not the primary focus. However, once I began learning more and more about the possibilities, I wondered where I had been the last ten years.

I wonder how many of us know that what we need is right in front of us!

Dealing with data in Excel

There is also one more hindrance to adopting Excel as a true data analysis tool: dealing with dynamic data.

Historically, we use Excel to link information in one cell, or range of cells, to another cell. Dynamic data that ‘talks’ directly to a database is one set of contiguous rows and columns; there are NO blank columns or rows in the middle of a dynamic range of data; rules of adding calculated fields and columns have a slight twist. THIS is foreign to the average Excel user, and one more reason to scare people off.

How to learn the skill of working with Excel pivot tables

This is still a scarcely populated frontier. And while there are many You Tube videos, a few books, and Microsoft tutorials, the material deals with simple topics, and leaves much to be desired.

I would visit websites like where there are podcasts, videos, You Tube snippets, and all sorts of training materials.

This summer, Michelle Long and myself will also provide Pivot Table training for dealing with QuickBooks data.

Always looking for new business models

I encourage all those practitioners who wish to add value to their services, to get involved with Excel, Pivot Tables, and business metrics. You will positively affect business outcomes, and this skill will give you a definite advantage in the market place. It is NOT as prevalent as you might think.

Save pagePDF pageEmail pagePrint page

About the author

Chuck Vigeant

Chuck Vigeant is the Managing Partner of CLEARIFY, LP and is known as the “grandfather” of Crystal Reports and Business Intelligence for QuickBooks. He is the principal architect behind QQube™, the ground-breaking data warehouse technology for QuickBooks, and his company is the largest provider of Business Analytics and custom management reporting systems for a variety of small business products.

There is one common thread from Chuck’s days as a controller in the late ’70s, through his three decades as an accounting technology specialist, through his stint as a data analyst and schema architect for Intuit – and, yes, even as a QuickBooks ProAdvisor: his passion to make it easier to grab data from everyday software applications and formulate it into usable information from which people can make more educated decisions. He has received awards from the Austin Entrepreneur’s Association; was a keynote speaker at the National Association for Professional Saleswomen; taught business curriculums at local community colleges; and spoken at dozens of Intuit functions, CPA chapters, and various technology conferences. He now limits his speaking to special engagements.

Chuck is a member emeritus of the prestigious Intuit Accountant and Advisor Council, holds a bachelor’s degree in business and public administration from the University of Hartford and a Master of Education from the University of North Texas.


  • A GREAT article about a topic that more and more people are becomming interested in….

    Another excellent resource is the textbook “Business Analysis with QuickBooks” by Conrad Carlberg, PhD, published by Wiley in 2009 (which I was ‘honored’ to serve as Technical Editor for). Conrad writes in a literary style that is easy to read and yet technical enough to meet the demands of most ‘sophisticated’ QuickBooks users. [No, I am not attempting to be self-serving as I do NOT receive any royalties off of this book.]

    About 50% of the text is devoted to using the resources inside of QuickBooks to analyze data, and the other 50% is how to review QB Data by exporting the data to Excel via either the standard QB Export method or the SDK. Forty (plus) pages (almost 10)% of this text cover the use of Pivot Tables as they specifically relate to various QuickBooks data and reports.

    William “Bill” Murphy – Oklahoma City

    • William – I bought a copy of that book and agree it is a very good resource for those interested in learning a lot more about getting data out of QuickBooks and applying it to their business. That said, it does require a certain level of technical skills to achieve results.

Leave a Comment