Home   >  Business Tools > 

Use Excel's Unique Power
To Raise Manager Alerts

Excel can analyze and present data from any number of sources
in one report. This makes it the ideal program to uncover strategic
and operational problems that are invisible to other software.

When you're up to your neck in alligators, the old saying goes, it's hard to remember that your original intention was to drain the swamp.

Unfortunately, in business, this observation is as relevant today as it was many decades ago when it first became popular. Today, managers still are surprised by serious business problems even though...

• our information systems contain much more data,
• our management theories are much more advanced,
• our knowledge workers are much better educated,
• our computers are much more powerful, and,
• our laws governing business conduct are much more pervasive.

One reason that alligators are so common in business is that we have so few tools designed to find and destroy them. We lack the logical tools, and we lack the software tools, other than Excel.

My article, Bain's Management Tools, provides an extensive list of logical tools that mangers commonly use to monitor and improve business performance. The list includes Balanced Scorecards, 6-Sigma, Economic Value-Added Analysis, and dozens of others. But none of the tools provides a systematic answer to the critical question: Where are things starting to go wrong?

Mere Error Reporting Is Not Sufficient

It's true that most serious business software provides error reports. But  these reports provide trivial information from a management perspective. The software typically alerts us to problems with individual transactions, but it seldom alerts our managers to fundamental business problems.

For example, your General Ledger software doesn't compare your Key Performance Indicators to those of your competitors, and then report areas where your company is weak. It doesn't report journal entries that probably are incorrect. 

Your GL doesn't warn you about a deteriorating cash position. It doesn't alert you when a risky percentage of your sales comes from one customer or industry, or when key customers begin to buy from others, or when certain products start to lose their appeal.

Your software doesn't perform these tasks for several reasons.

• Your internal software contains little or no external data; and it obviously can't report data that it doesn't contain.
   
• Most commercial software is designed to work only with data under its control. That is, a General Ledger system from one vendor typically can't report manufacturing data maintained by another vendor's program, nor data in competing General Ledger product used by a newly acquired division.
   
• Your business is different. You don't have problems that others have, but you do have problems that others don't. Therefore, any off-the-shelf early warning system probably would be a waste of money.
   
• Your business is changing constantly. Problems you didn't know about yesterday may be critical tomorrow. You have no time to wait for programmers with their long development schedules.
   
• Alligators can come from any direction, at any time, for any reason. Unlike off-the-shelf business software, business problems have an infinite scope.

In short, neither your General Ledger nor most other business software can serve as an early warning system.

The Beginnings of a Solution

Years ago I worked as a cost accounting manager at a Hewlett-Packard division. One day I discovered and corrected a multi-million-dollar error that co-worker had made.

When I explained the error, he wasn't convinced. I therefore wrote a program that looked for similar errors at each month-end. Several months later my program reported a very large, very bone-headed error by the same person. But after I told him about my error-finding program, and explained the negative consequences of a third occurrence, his performance improved.

After that experience, I wrote a new error-finding program whenever I discovered a new type of error in our data. And then I ran a batch of those programs at the end of each month. This growing collection of error-finding programs gave me the power to ratchet down errors, ratchet up the quality of our financial information, and reduce year-end audit adjustments.

The key to this success was that I, a knowledge worker, wrote those programs. I understood finance and statistics; I had access to massive amounts of accounting and operating data; and I understood what the data meant. This allowed me to create (often by trial and error) a variety of ways to find each new instance of the various kinds of problems that I discovered. Such success never would have been possible if I had been forced to rely on a programmer.

Introducing the Manager's Early Warning System

Today's business challenges are much greater than those I faced as a young cost accountant. Even so, the three-step process that I used to uncover booking errors can work today in a much broader context. For convenience, I call this the Manager's Early Warning System (MEWS).

Step One is to surface a developing or potential business problem. This could be a marketing problem, an operating one, a financial one, whatever. Often, you take this step when a new alligator starts to gnaw on your ankle.

Step Two often is the most difficult and costly step. You must find internal and/or external data that can help you to monitor the problem, evaluate its severity, and uncover related problems.

Although this step can be a difficult one, it also can be a rewarding one. Often, data that helps you to monitor a potentially serious problem also can help you to solve the problem.

For example, a competitor database could help you to monitor your market share...by product by region by type of customer, etc. This would allow reports to alert you when your market share falls by product by region, etc.

That same data also could help you to uncover market opportunities, which you otherwise might have missed. It could help you to evaluate your pricing policies. It could help you to better understand your competitors' strategies. And so on.

Step Three typically is the most fun for Excel users. You create reports and analyses that monitor the data and report the status of the various measures.

For example, if you consider the market-share performance of all products in all regions, one chart could report what percentage of these fell during the past six months. There could be trouble ahead if that percentage of weak product-region sales results continues to grow, even if your total market share appears to be strong.

Although Excel users can find this step professionally rewarding, they also could find that it condemns them to Spreadsheet Hell. One way to avoid the hellish aspects of such management reporting is to store the data for your MEWS reports in an Excel-friendly OLAP.

Excel is the Key to Early Warning Systems

Whether you store your MEWS data in an Excel-friendly OLAP, or in dozens of Excel spreadsheets, Excel is the key. This is because no other product provides the power to do so much analysis with so much diverse data in so many displays in a few simple reports.

To illustrate, suppose you create a Competitor Report, which has many figures. And suppose that one of these is a simple chart that compares your company's revenue per employee to those of your largest publicly traded competitors.

For your own company's data, you could have one row with monthly revenue from the General Ledger, and a second row with headcounts from a Human Resources report. A third row, with Excel formulas, could easily calculate Sales Per Employee from these two series of numbers.

For your competitor's data, you could enter two similar rows of quarterly data per competitor, perhaps using data from Hoovers.com or Yahoo.com. Simple formulas could calculate the quarterly ratios and present them in three-month blocks, a design that easily allows you to compare the quarterly data with your internal monthly data.

Finally, you could add a chart of these monthly ratios to your Competitor Report.

Excel is the only product I know that gives knowledge workers the ability to create such rich reports. No programming is needed. And no programmers.