Home > Excel Dashboards > 

An Excel Tutorial:

Explore Business Performance
Using Small Charts in Excel

When you compare measures of performance to other measures,
you add context that can offer significant insight. We use data
about public companies to illustrate the process.

Several days ago I met a young accountant who works for a very large company. He said that he creates many Excel charts for his department, and prints them one chart per page.

Click to view full size.

When I told him that I prefer reports with at least 20 charts per page, he was stunned.

When I said that his managers would benefit significantly if he used the same approach, he incredulous.

Unfortunately, I've talked with other Excel users who reacted similarly. They find it difficult to understand the power of small charts. Therefore, I thought it would be useful to take a closer look at an Excel dashboard report that uses mini-charts, and to see what information we might be able to glean. 

The Excel report shown here is a new reporting template that I created during the past few weeks. It uses data downloaded from MoneyCentral.MSN.com.

Let's see what information it can tell us.

To be clear, the following analysis is not about investment advice. Instead, it's intended to illustrate the insights you can gain by using small charts and tables to monitor the performance of your own departments, sales offices, divisions, competitors, customers, markets, and so on. By using current data from public companies I can illustrate those benefits more easily.

The Feb 13, 2006 issue of the Wall Street Journal ran an Op-Ed about the financial problems that General Motors has been having. So I decided to use the new Excel dashboard to take a closer look at that company.

When I first looked at GM's dashboard, I saw results that looked pretty grim. But I know little about the automobile industry. So I compared GM's dashboard with one for Ford. Then I added Excel dashboards for Daimler Chrysler, Toyota, and Honda.

Soon, I had five sheets of paper arranged on my desk, five sheets that contained more than 100 Excel-generated figures. By comparing these results, I was quickly able to see how badly GM's performance compares to the rest of the industry.

General Motors

When I first looked at GM's report, these two figures caught my eye.

GM's revenues have been flat for at least four years. But their income has trended downwards since 2003, and dividends have trended upwards.

(In Figure 2, and in most of the figures that follow, the line chart shows data from GM's most-recent quarterly reports, and the area chart shows data from GM's annual reports. For P&L data, like Revenues, I divided annual performance by four to calculate average quarterly performance that we can compare to the quarterly results.)

Ford

I've heard that Ford also is in trouble. So I looked at their performance.

Here, you can see that Ford appears to be in better shape than GM in both figures. Ford's revenue has been rising. And although their income isn't great, at least their dividends and income don't seem to be trending in opposite directions.

General Motors

GM's downward slide in income also is reflected in the two standard measures of profitability shown here.

The Sustainable Growth Rate (SGR) isn't as well known as the ROE. The SGR shows the approximate rate at which a company can grow while keeping its financial structure in balance.

As you can see from GM's Figure 9 above, the company has significant problems with its financial ability to grow. And the trend looks grim.

Ford

In contrast, Ford has been trending in the correct direction.

Admittedly, Ford's performance hasn't been great in recent years. But, at least until the second half of 2005, Ford consistently improved its performance.

Will Ford's downturn in the most-recent quarter continue? Their new financial data should be available soon at MoneyCentral.MSN.com.

General Motors

These were the next two figures to jump out at me.

As you might expect from its declining performance, GM's operating cash flows have plummeted during the past five quarters.

However, GM has one more reason--a significant reason--for its declining cash flows: The measure of their average Days Sales Outstanding in Accounts Receivable (DSO) is approximately 400 days, and this measure has grown worse during each of the past three years.

Ford

In contrast, Ford's cash flow has been better than GM's in recent years, and so has their DSO.

However, I know virtually nothing about the car business. Perhaps these trends are common in that industry.

Therefore, it makes sense to compare GM's performance with that of other car manufacturers.

So I looked at three GM competitors. Unlike GM, I discovered that revenues for Daimler-Chrysler, Toyota, and Honda all have been trending upwards during the past four years. But what about their other measures of performance?

Daimler	Toyota	Honda

The first figure above shows that Daimler has increased its dividend payments significantly in 2004 and 2005. But unlike GM, Daimler continues to earn a profit. In contrast to Daimler, both Toyota and Honda have maintained earnings comfortably above their dividends.

But what about the other competitors' Sustainable Growth Rate? Do the competitors have the financial ability to grow? An ability that GM now lacks?

Daimler	Toyota	Honda

As the above figures illustrate, Daimler's SGR has been trending downwards, which is no surprise in view of their low profits and large dividends in recent years. But both Toyota and Honda have maintained their financial ability to grow.

What about GM's huge measure of Days Sales Outstanding? Do the other competitors have similar collection policies?

Daimler	Toyota	Honda

These figures show that none of these GM competitors must wait a year or more to collect their receivables. Rather than waiting nearly 400 days, in fact, Honda only must wait about 75 days.

If GM were to cut its AR collection time in half, to about 200 days, the company's collection performance still would trail that of its foreign competitors--and by a significant degree. But because GM has more than $220 billion in Accounts Receivable, the company would gain more than $100 billion in cash if it could take this action.

Keep in mind that GM's net operating cash flow, shown in an earlier figure, has averaged only about $5 billion per quarter during the past five quarters. Even for a company the size of General Motors, $100 billion would be a lot of cash.

GM

Finally, the analysts pretty much agree with the conclusions that the charts above imply.

To illustrate, this Excel figure shows how analysts rate GM today. On balance, they advise people to sell their GM stock. Worse, analysts have been growing even more pessimistic as time has passed.

In contrast, the analysts rate all three of GM's foreign competitors more favorably.

Honda

Analysts advise the owners of both Daimler and Toyota stock to hold, and perhaps even to buy a little more stock.

But as this figure shows, the analysts are enthusiastic about Honda. And their enthusiasm for that company has increased slightly during the past three months.

But enough about the auto industry!

I repeat, this article is not about investment advice. And it's not really about the auto industry. Instead, it's intended to give you an idea of the insight you can gain by viewing performance using many small charts.

By comparing different measures for the same entity, and the same measures for different entities, you can learn a great deal about the relative performance of those entities. You can find problems, opportunities, and questions that need further research.

Better yet, you can gain such insight more quickly and easily than you could by studying many pages of columns of numbers!

(Email Comments)