For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #30

These "rainbow charts" or "calc plots" rely on a band of lines to display three results rather than merely two results


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

A Swipe Files subscriber recently sent me images of several "ROA Graphs" from the book, "Managing by the Numbers" by Kremer, Rizzuto, and Case. I was glad he did, because I first created charts like this with Visicalc, the first spreadsheet program. So creating them with Excel was somewhat nostalgic.

Over the years, I've sometimes called them rainbow charts, because they include a band of lines typically curved like an upside-down rainbow. I've also called them calc plots, because I've used them to calculate loan payments and other time-value-of-money information.

The rainbow chart on the left shows Appleís measure for Return On Assets over the past ten years. Its current ROA value of about 22% is shown by the black dotís position in the rainbow.

The ROA (Profits/Assets) is the product of Profit Margin (Profits/Sales) and Assets Turnover (Sales/Assets). So Apple achieved this result by improving their profit margin from essentially zero at its low to 24% currently. But their asset turnover in recent years was essentially constant at about .90.

Charley's Swipe File #30

The chart on the right shows the current values for the Dow 30 Companies. Notice that if Microsoft (MSFT), with a profit margin of 33%, could achieve the Wal-Mart (WMT) asset turnover ratio (2.34), Microsoft would have an ROA of nearly 80%.

Rainbow charts arenít limited to financial issues. They can display any performance determined by a formula that uses two variables to yield a key result.

Excel Issues

Rainbow charts rely on several key tricks to create the rainbow, label its lines, and then label and position the actual results.

The first trick is to chart the rainbow lines using...

(Continued in the documentation.)

Order Details

Excel Versions:

Excel 2007 and after, for either Windows or Mac

Availability:

Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.

Guarantee:

One full year, unconditional.

Currency:

All prices are in US Dollar currency.

Licensing:

Two copies: One for work plus one for home.

Credit Cards:

Credit Cards
 

Only $14.95 USD

New Excel