For business users of Microsoft Excel Learn to create Excel dashboards

Management Reporting

Five Ways to Improve Business Insight in Your Excel Reports

Here's how to help your readers gain more insight from your Excel reports...courtesy of the Harvard Business Review.


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

Is there a systematic way that you can help your readers to gain more insight from your Excel reports? There just might be.

The November, 2014, issue of the Harvard Business Review included the article, “Managing Yourself, Where to Look for Insight.” The article by Mohanbir Sawhney and Sanjay Khosla suggested seven strategies for improving insight in your organization.

Although the article didn’t address spreadsheets or dashboards specifically, five of the authors’ strategies can help your managers to gain greater business insight from your Excel reports. If you’d like to experiment with these ideas, you can download my sample Excel dashboard workbook here.

Strategy 1: Look for Anomalies

The authors suggest that managers examine deviations from the norm to raise useful questions about business performance. That is, managers should ask themselves what unexpectedly high or low results might suggest about their business processes.

This Excel dashboard makes it easier to identify those unexpected results...

Excel dashboard showing unexpected results.

Here, the lines show actual performance and the light-blue areas show targeted performance. And in the following examples that show measures where no targets exist—as in the displays of economic data—the line and light-blue area in each figure both display the same actual measures.

To illustrate the authors’ point, when managers see this dashboard, they might ask a variety of questions. 

For example, Shanghai and Bozeman had similar trends, and both exceeded their targets. What did those two diverse units do to achieve their good results?

Bangkok was on a similar trend until January, but then performance fell like a rock. What changed? Are similar changes coming to other units? How can Bangkok’s sliding performance be reversed?

The Montréal unit had uniquely poor performance during the first half of the year. And much of that trend was forecasted. Is the unit’s poor performance a harbinger of future bad performance of the other units? Or was the performance due to unique circumstances that can be reversed?

Strategy 2: Look for Confluence

The authors suggest that you look at external indicators for confluence—a situation in which two things come together or happen at the same time...as in the confluence of the Missouri and Mississippi rivers.

They suggest this technique as a way to uncover opportunities in the marketplace. But you also can rely on confluence to alert you to danger ahead, as this example illustrates...

Excel dashboard showing confluence.

Here, for example, the revenues for the Dampville Unit are right on target, as shown in Figure 1. But will they stay that way?

Probably not, because key external indicators are screaming that there’s danger ahead.

Specifically...

...Figure 2 shows that the average number of hours worked in the city of Dampville is declining.

...Figure 3 shows that their average hourly earnings is falling.

...Figure 4 shows that the value of their homes is tanking.

So in view of these declining economic indicators for Dampville, can falling revenues for the region be far behind?

(Although Dampville doesn’t actually exist, you can find similar information about large cities in the United States in the data for Metropolitan Statistical Areas (MSAs), at: http://research.stlouisfed.org/fred2/.)

Excel dashboards are particularly useful for this kind of analysis, because they offer a systematic way to display relevant measures using any available data…data from the Web, from data providers, from printed sources, and so on.

Strategy 3: Look for Frustrations

Life’s irritations are a terrific source of new-product ideas, the authors tell us. But if those pain points exist in your organization, they also can be a major source of problems.

One way to anticipate the problems is to report any measures you can find that can offer hints about those frustrations, as this dashboard illustrates...

Excel dashboard showing frustrations.

Here, for example, the report of Frustration Indicators for employees suggests that worker frustrations have been decreasing in recent months. We reach this conclusion because sick days, resignations, overtime hours, and HR complaints all are down.

Similarly, frustration indicators for customers might include measures of requests for refunds, the number of canceled orders, the number and length of calls on the telephone support line, the callers’ wait time on the support line, the number of requests to speak to a supervisor on the support line, the trend in the percentage of repeat orders from existing customers, and so on.

Some of your frustration indicators for employees, customers, and other stakeholders will be available from your business intelligence system. But many will come from other sources…both formal and informal. However, because this is an Excel report, your dashboard can easily display whatever relevant data you can accumulate.

Strategy 4: Look for Extremities

Every set of measures has leading and trailing results. The authors advise managers to learn what they can from the performance of those extreme results.

Generally speaking, this advice makes sense. But when you try to apply the advice, you’ll immediately face the question: What type of calculation should you use as the basis for ranking the items?

One useful way is to rank the growth rates of the underlying performance. By doing so, you can rank both large and small entities on the same scale, as this dashboard shows...

Excel dashboard hightlighting extremities.

Here, for example, the dashboard shows the four products that have had the worst average growth rates in sales over the prior 13 months. Similarly, a Best Trends dashboard could show the other extreme.

To calculate the average growth rate, which is displayed in the title of each figure, I used the PAGR method explained in How to Calculate BOTH Types of Compound Growth Rates in Excel

Specifically, I used this formula to calculate the growth rate for each product…

=LOGEST(ActualData)-1

…where the cell named ActualData contained the 13 months of actual performance for a figure, as returned from an Excel data workbook.

And I used this formula to calculate each figure’s title text…

=ProductName&" ("&TEXT(Growth,"0%")&"/mo)"

…where the cell named ProductName contained text like “Carriage Bolts” and the cell named Growth contained the LOGEST formula shown above.

Strategy 5. Look for Analogies

Finally, the authors suggest that you borrow good ideas from other industries and organizations. Innovation, the authors remind us, is not about bringing something new into the world, it’s about usefully applying something new to a situation, no matter the purpose for which the method was invented.

Additionally, there are many smart people in the world who face problems and opportunities somewhat similar to yours. The way they report those issues could give you excellent ideas for your own Excel reports.

Unfortunately, Excel users seldom are able to share their reports with others, because their reports show company-private data. But there’s a simple solution to this problem, which this figure illustrates...

Excel dashboards showing disguised data.

Here, the original figure at the left displays monthly revenues for the System 7-2A product. This revenue information certainly is company-private.

But the disguised figure at the right hides that company-private data in five ways while displaying the report format and the underlying pattern of the original results…

  1. It changes the name of the company.
  2. It changes the subtitle to a line that displays your contact information.
  3. It scales the original results by a randomly chosen number.
  4. It displays a made-up product name.
  5. It shows an image of the report, not the actual report workbook. By taking this step, we hide all company-private information that the report workbook contains.

With the first four changes applied to all figures in a dashboard workbook, and with the disguised report distributed as an image or a PDF file, you could exchange reporting ideas with Excel users in other organizations and industries with no chance that they will learn company-private information.

You also could include the dashboard with your resume, of course.

To learn how to disguise and distribute your reports in a similar way, see How to Disguise Your Company-Private Excel Dashboards So You Can Show Them to Others .

If you want to experiment with these ideas using a simple Excel dashboard report, you can download the free dashboard workbook here.




Tags: #excel, #chart, #excel dashboard, #excel reports, #Harvard Business Review, #reporting
Free Excel Dashboards


Charley's SwipeFile charts