For business users of Microsoft Excel Free guides and templates
Home >  Excel Reports > 

Reporting Strategies

Create a Manager Early-Warning System in Excel

Excel can analyze data from many sources in one workbook. This makes it the ideal program to alert managers to problems that other software can't see.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

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 about Bain's management tools provides an extensive list of logical tools that managers 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 Enough

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 probably doesn't report journal entries that might be 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 a 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. When problems surface, you need to track them NOW!
  • Your smart knowledge workers and their managers can discover alligators of any size, coming 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 your Business Intelligence software, nor most other business software can support a 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 accounting error that a co-worker had made.

When I explained the error, he didn't seem to be convinced. I therefore wrote a program that looked for similar errors at the end of each month.

Several months later my program reported a similar, 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, he stopped making that particular type of error.

Because of that experience, I developed a three-step routine. Whenever I discovered a new type of error in our data...

1. I corrected the error.
2. I wrote a program that looked for other errors like the first error.
3. I added the program to a batch of error-finding programs that I ran during each month's accounting close.

That 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 significantly.

The key to this success was that I was a knowledge worker who could program.

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 kinds of problems I had discovered. Such success never would have been possible if I'd been forced to rely on some other programmer.

Create Your Own 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 errors our data can work today in a much broader context. I call this the Manager's Early Warning System (MEWS).

Step One is to surface a developing or potential business problem. Yes, this could be a problem with your data. But it also could be a marketing problem, an operating one, a financial one, whatever.

Step Two often is the most difficult and costly step. When a new alligator starts to gnaw on your ankle, ou must find internal and/or external data that can help you to monitor the one problem, evaluate its severity, and uncover similar problems elsewhere.

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 database about your competitors' performance could help you to monitor your market 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 analytical reports that monitor your data and that raise alerts when your formulas show that something is out of use a technical term.

Although Excel users can find this step professionally rewarding, they also could find that analyizing many different sources of data each period could condemn them to Spreadsheet Hell. One way to avoid the hellish aspects of this work is to store the data for your MEWS analytical reporting in an Excel-friendly OLAP.

This would allow all your analytical reports to raise alerts automatically whenever you open a MEWS workbook.

Excel is the Key to Early Warning Systems

Whether you store your MEWS data in an Excel-friendly OLAP, or in an Excel database, Excel is the key. This is because no other product gives knowledge workers like you the power to do so much analysis, with such a wide variety of data, in so many analytical reports—so easily.

To illustrate, you could raise a Competitor Alert by comparing key internal measures with measures of similar data for publicly traded competitors. The data might come from,,, or other similar sources. Your workbook would raise an alert only if your formulas detect a problem. 

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...

...Just an Excel-friendly OLAP to contain your data!


Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts