For business users of Microsoft Excel Learn to create Excel dashboards

Management Reporting with Charts

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.


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

Several years ago I met a young accountant who worked for a very large company. He said that he created many Excel charts for his department, and printed them one chart per page.

Excel dashboard for General Motors

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 was 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 reporting template that I created several years ago. It uses NASDAQ data and other information from MSN Money.

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 had been having. So a while back I decided to use an 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.

General Motors
Excel chart of quarterly revenues for General Motors Excel chart of income and dividends for General Motors

Ford
Excel chart of quarterly revenues for Ford Motor Company Excel chart of income and dividends for Ford Motor Company

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 compared to the rest of the industry.

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

GM's revenues had been flat for at least four years. But their income had trended downwards since 2003, and dividends had 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 at the time, 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 I could compare to the quarterly results.)

I had heard that Ford also was in trouble. So I also looked at their performance.

In the figure above, you can see that Ford appeared to be in better shape than GM in both figures. Ford's revenue was rising. And although their income wasn't great, at least their dividends and income didn't seem to be trending in opposite directions.

General Motors
Excel chart of Return On Equity for General Motors Excel chart of the Sustainable Growth Rate for General Motors  

Ford
Excel chart of return on equity for Ford Motor Company Excel chart of the systainable growth rate for Ford Motor Company  

GM's downward slide in income also was 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 here, the company had significant problems with its financial ability to grow. And the trend looked grim.

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

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

Would Ford's downturn in the final quarter continue? Their current financial data can tell you.

General Motors
Excel chart of Net Operating Cash Flow for General Motors Excel chart of Days Sales Outstanding for General Motors

Ford
Excel chart of Net Operating Cash Flow for Ford Motor Company Excel chart of Days Sales Outstanding for Ford Motor Company

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

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

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

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

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

Therefore, it made 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 had been trending upwards during the past four years. But what about their other measures of performance?

Daimler Toyota Honda
Excel chart of Income and Dividends for Daimler Excel chart of Income and Dividends for Toyota Excel chart of Income and Dividends for Honda

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

But what about the other competitors' Sustainable Growth Rates? Did the competitors have the financial ability to grow? An ability that GM then lacked?

Daimler Toyota Honda
Excel chart of the Sustainable Growth Rate for Daimler Excel chart of the Sustainable Growth Rate for Toyota Excel chart of the Sustainable Growth Rate for Honda

As the above figures illustrate, Daimler's SGR had reached negative territory, which is no surprise in view of their low profits and large dividends in recent years. But both Toyota and Honda had maintained their financial ability to grow.

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

Daimler Toyota Honda
Excel chart of Days Sales Outstanding for Daimler Excel chart of Days Sales Outstanding for Toyota Excel chart of Days Sales Outstanding for 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 needed to 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 had more than $220 billion in Accounts Receivable, the company would have gained more than $100 billion in cash if it could have taken this action.

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

GM
Excel chart of analyst ratings for General Motors

Honda
Excel chart of analyst ratings for Honda

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

To illustrate, this Excel figure shows how analysts rated GM in 2005. On balance, they advised people to sell their GM stock. Worse, analysts had been growing even more pessimistic over time.

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

Analysts advised 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 were enthusiastic about Honda at the time. And their enthusiasm for that company had increased slightly during the prior 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!

 

Free Excel Dashboards


Charley's SwipeFile charts