Site menu:

More information per pixel!

Recent Posts

Site search

Categories

In-Cell Variance Charts

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

In financial report we are constantly comparing the actual numbers with our projections using variances.

A quick reminder

Absolute variance = Actual – Budget

Relative variance in % = (Actual – Budget) / Budget

Doing this over a report of even 10 lines requires row by row number comparison, and is not something which can be easily & quickly scanned. Showing the variances in a classical Excel charts is problematic, as you constantly have to go back and forth between the table and the chart to scan exact numbers and the variance tend in the chart.

image

In-Cell variance charts are a compact, data rich and efficient alternative.

image

To create in-cell variance charts you have basically three alternatives:

1. In-Cell Chart Using the REPT Function

Rolf Hichert presents a couple of years ago on his web site (German) a character based approach. The method got quite popular when it was first presented on the  Juice Blog. The main idea is to create the bars repeating a character using the REPT function. The function REPT("●",5) returns "●●●●●". The disadvantage of this approach is that you have to set up all the formulas and that don’t have a continuous scale. E.g. to show 15% you either show "●" or "●●", but there is no way creating fractions of a character. One of the advantages of the rept approach is that the bars are automatically aligned in the grid. When you increase the row height, bars are still perfectly aligned with the numbers.

image

Click here to download the Excel file

2. In-Cell Chart Using MicroCharts

Another smart way to overcome the setup and scale problems of REPT is using MicroCharts. Charts are created from MicroCharts fonts including bars, line segments and pies. A chart is represented as text that is formatted with the MicroCharts fonts. Similar to the rept fuction charts, you can utilize all the rich Excel capabilities for MicroCharts that you would for normal text!:

  • Text alignment
  • Text orientation
  • Font size
  • Conditional formatting
  • Automatic alignment when the row height changes

image

Click here to download the Excel file (You need to install MicroCharts)

Unlike the rept function MicroCharts support continues scales value axis. The chart formatting is very similar to the options with standard Excel charts:

image

Hitesh Patel Winner of 2008 Excel Dashboard Competition used the Micro Bar Charts to visualize variances over areas and territories in a very cleanly presented table in his Pharmaceutical Sales Dashboard .

image

3. In-Cell Charts Aligning a regular Excel CHart in the Grid

A third approach is to use regular Excel bar charts:

image

Click here to download the Excel file

The trick is to set chart area to transparent and to manually align them to the grid. The main problem with this method is that the setup of the chart can be very tedious. You will have to fiddle quite a while till the bars align perfrectly with the grid, and each time you change the row or column height width you have to readjust the chart area:

image

Extra tip: To further enrich your variance report with some historical context, provide the historical performance in an extra column as a sparkline:

image

Graphical Table - Abortion Data 1980 - 2003

Here the history of the Abortion data set

1) Jorge used some U.S. Census Bureau data (original Excel file) to visualize the abortion ratio as a small multiples chart,

2) In Small Multiples - Abortion Data 1980-2003, I have written about Jorge’s panel chart. I like the panel (small multiples) chart, but felt that it can be improved.

3) Jon Peltier posted on Re: Abortion Ratios 1980-2003 and Interactive Multiple Line Chart some nice Excel techniques to create interactive charts to analyze the Abortion data set.

Here is a different view on the data set as a Graphical Table using MicroCharts. The Graphical Table is a hybrid between the panel (small multiples) chart and a plain table of numbers. It nicely integrates the numbers with small charts, so-called sparklines, in a table. The sparklines show you the trend of the abortions over the years and the in-cell bar charts give you a feel for the distribution over the age groups:image

Click here to download the Excel File (you need to install MicroCharts first)

Small Multiples - Abortion Data 1980-2003

Jorge used some U.S. Census Bureau data (original Excel file) to visualize the abortion ratio as a small multiples chart.

I like this chart as I am a big fan of small multiples displays. They help us to understand the nature of multi-dimensional data.

Jorge asked for suggestions to improve his chart and I came up with these:

  • The abortion ratio is calculated as the number of pregnancies that end in an abortion per 1000 pregnancies. I would put the definition of the ratio in the title or a footnote (Number of abortions per 1,000 live births). Most places where I have seen this data presented tend to do it in this way too, although my preference would be for a % format.
  • I think that the data for Race, Marital Status and Age Group are actually 3 different stories. It took me several scans of the whole chart set to realize this. I would add some white space between the Age group charts, the Race and Martial Status charts, to make it clear that are comparing different variables.
  • Jorge shows an integrated line chart for Marital State and for Race but a small multiples chart for Age Groups. For a more consistent chart reading I would use small multiples charts for all variables.
  • The axes of the Percent Distribution bars are not labeled, and have no scale. This coupled with varying meaning of the color encoding is confusing. The percentage distribution data for the Age < 15 lies around the 1% level and so cannot be seen on this scale even though data does exist. We also cannot encode the way this measure also varies with time over the same period. In fact we miss the fact that for women over 25 years the percentage distribution is actually increasing.
  • I’m not sure if you need to show the all age groups in the Age group charts as light Grey lines. I know that you want the reader to compare the current age group in the context of the other age groups but this exactly what the small multiples are for anyway.

I like Jorge’s idea of integrating the abortion ratio and abortion percentages, but I found having another group of small multiples provides a more consistent view. If we don’t have the percentage distribution numbers then we can easily draw the wrong conclusions about the data.

Here my small multiples chart for the Abortion by Age Groups:

image

 

Update 6/22/2008: Jon Peltier posted on Re: Abortion Ratios 1980-2003 and Interactive Multiple Line Chart some nice Excel techniques to create interactive charts to analyze the Abortion data set.

2008 Excel Dashboard Competition Winners

After much deliberation and debate, we are pleased to announce the winners of the 2008 Excel Dashboard Competition. We were impressed by many of the entries, and thanks to all of you who entered. It’s good to see MicroCharts being put to effective use, and adding value in such a variety of business scenarios and sectors. We had entrants from a broad range of industries including Banking, Insurance, Healthcare, Manufacturing, Oil and Gas and Pharmaceuticals.

The winners are:

1) Wade Stokes - Bank Dashboard

Displaying many disparate Banking Key Performance Indicators, and designed as the basis for the Management review of business performance, it truly achieves More Information per Pixel.

2) Jim Uden – Outpatient Surgery Center Dashboard

Developed for Meridian Surgical Partners, as a one page snapshot for the review and presentation of partnership level business operations and trends.  Jim also includes probably the best associated description of dashboard content and the thought processes involved which we’ve seen.

3) Hitesh Patel - Pharmaceutical Sales Dashboard

Developed by Hitesh Patel and Mike Askew of Data Intelligence, for Bristol Myers Squibb. A key report for the Regional Sales Managers, containing the information required to run the business in terms of cash, growth, share, and competitive performance.

Congratulations to all 3 of our winners. Have a look at our competition page for screenshots and some background on the winning entries. Over the next few weeks we’ll be analyzing the entries in more depth at http://blog.xlcubed.com. We’ll overview each, cover some of the techniques used, and hopefully suggest some further improvements.

More Information per Pixel!

In my last post I suggested some chart selection rules as an alternative to Godin’s Silly Rules for Great Graphs. Jerome commented:

[...] on a slide, you want to convey one message. your graph must NOT carry any information that can be interpreted differently than the point you are trying to make. the corollary is that in virtually all cases, you should display as little data points as possible: 1 if possible, 2 but no more than 3. If you need more than 3 data points, use handouts. [...]

which is very much in line with what Seth Godin said in his famous post about Chart Rules:

No, the reason you put a chart in a presentation is to tell a story. A single story, one story per chart

Why should a presentation display as little data as possible? Why should a slide contain only one chart? I demand More Information Per Pixel. Why not have a data-rich chart in a slide - no, even a couple of charts to support my message?

My friend Rolf Hichert has a totally different design philosophy.

Components of good presentations slides:

  • A clear message
  • A clear title (should be a complete sentence, including units like K$)
  • Each slide to conveys only one message
  • More tables and charts to support the message
  • Choose the right chart type
  • Use arrows, color etc. to highlight the message

Look at this sample taken from Rolf’s web site:

image

The slide has a clear title that conveys one message: "Further positive Development in Frankfurt, Vienna and Graz - Action needed in Lausanne and Linz".

The slide contains small multiple charts to support the message, where Rolf has chosen a line chart to emphasize trends or patterns. The problematic regions mentioned in the title are colored in red; those that made the CEO happy in green.

Chart Rules, As Simple as Possible, But Not Any Simpler!

In chart design it’s good to make things simple, but you certainly should avoid oversimplification. As Einstein said:

"Things should be made as simple as possible, but not any simpler"

Seth Godin presented in his blog The three laws of great graphs:

1. One Story
2. No Bar Charts
3. Motion

Effective chart design rules are simple, but reducing it to this set of 3 rules certainly is an over-oversimplification.

Particularly rule 2 is flawed. Seth details rule 2:

"NO BAR CHARTS
Bar charts are dramatically overrated, primarily because they’re the first choice in many graphing programs.

The problem with bar charts is that they should either be line/area charts (when graphing a change over time, like unemployment rates) or they should be a simple pie chart (when comparing two or three items at the same scale).

Jorge, Kaiser and Jon already wrote some critical posts about this rule, where Jon suggested to replace rule 2 with

Choose Chart Types Intelligently

We are working tightly together with Stephen Few on a new product that helps business users creating effective charts with Excel and are therefore we are quite familiar with Stephen’s design principles.

Its an easy to learn set of rules

1. Determine the relationship you want to display

Relationship Sample

Value Comparison

Sales in different regions

Ranking

Best selling products

Time-Series

Sales in the last 12 months

Part-to-Whole

Market shares

Deviation

Revenue Actual vs Budget in the last 12 months

Distribution

Support response times

Correlation

Relationship between employee’s heights in inches and their salary

 

2. Determine if you want to emphasize individual values or the overall pattern
3. Determine the chart type

Relationship Encoding Method  

Value Comparison

Bars and Columns

image

Ranking

Bars and Columns

image

Time-Series

Lines to emphasize the overall trends or pattern

image

 

Points connected by lines to slightly emphasize individual values

image

 

Columns to emphasize and support comparisons between individual values

image

Part-to-Whole

Bars and Columns

image

Deviation

Lines to emphasize the overall shape of the data

image

 

Points connected by lines to slightly emphasize individual data points

image

 

Bars and Columns to emphasize individual values

image

Distribution

Columns to emphasize individual values

image

 

Lines to emphasize the overall shape of he data

image

Correlation

Points and a trend line in the form of a scatter plot

image

 

Armed with this set of rules you would rule out Seth’s pie chart, and use the bar chart in the appropriated business context.

Hermann Grids, An optical Illusion Best Avoided

An interesting optical illusion is the so-called Hermann Grid illusion: the effect of seeing gray dots at the intersections of a black grid on a white background or a white grid on a black background.

image_thumb3

 

 

 

 

While it’s an interesting optical illusion, it’s something we should avoid in management reporting:

image_thumb16

 

 

 

 

 

 

 

 

 

Tables formatted with medium or thick black or gray borders tend to produce Hermann Grids. Just scan the table above and you should see the gray dots in the grid intersections.

To avoid this unpleasant and distracting effect, and to maximize the data-ink ratio follow this simple but very effective table design rule:

  • Avoid using dark and heavy grids
  • Use light gray grids instead

image_thumb19

 

 

 

 

 

 

 

 

 

Above is the same table with light gray borders. This eliminates the Hermann Grid illusion and – by de-emphasizing the grid –  puts more emphasis on the numbers.

Here are some images I found on Google Image Search that show how popular it is to put your data behind Hermann Grids:

image_thumb8

 

 

 

 

 

 

 

 

 

 

image_thumb11

 

 

 

 

 

 

 

 

 

 

So I hope you are with me – get rid of the heavy grids and free your data!

Color in Motion

A very, very cool Flash Movie about Color in Motion. An interactive Experience of Color Communication and Color Symbolism of Claudia Cortes.

clip_image002

 

 

 

 

Enjoy !

Win Lose Charts - English Premier League 2007 - 2008

With hindsight, it’s easy to look at it with hindsight” - Glen Hoddle

Little did we suspect midway through the season that the Premier League would turn into a 2 horse race. Poor starts by both Man Utd. and Chelsea were transformed into a consistent run of good form which would extend the drama to the very last day of the season.

With one game left to play, the top two were level on points with Manchester having a superior goal difference. If Chelsea could achieve a better result than Manchester, then they would clinch the league in the final game…

The English Premier league currently has 20 teams requiring a total of 380 games per season. The results of sporting leagues are usually displayed in a league table format. During the course of a season we see teams occupy different table positions. The standard table format however leaves out the important historic story of the league.

This is how soccerstats.com shows the final league table.

image

 

 

 

 

 

 

 

 

 

 

 

 

 

The final 8 games are summarized as a colors encoded Win Lose chart. The table found at the web site above provides helpful links to each team’s performance and a snapshot in time regarding table position for each month of the league. It is also possible to rank the teams with respect to their home or away performance and overall is a very powerful tool for analyzing statistics of the league. Some historical information is also given with the results for the last 8 games being displayed in the last column.

Statistics are like miniskirts; they give you good ideas but hide the important things.” - Ebbe Skovdahl

However, there are a number of things about the table in that I think could be improved and all of them are centred on the “Last 8” column. The table author is attempting to describe win-lose information graphically and has dedicated approximately one quarter of the width of the table to it. With a quarter of the table dedicated to it, we should expect more than 8 out of 38 games to be described. The author has chosen to invent a chart type which encodes the results using colored squares with more empty space than data. The whole season can easily fit into this width if we choose an appropriate display technique such as a win-lose chart.

My least favorite aspect of the “Last 8” column is the non-standard use of a 1 dimensional plot. I come from a physical science background and so I am used to diagrams telling me certain things in certain ways. One of these things is (in the absence of an axis or some other visual guide) that time should go from left to right! In this 1D plot the knowledge of which way time is going is fundamental. The links at the top of the same table used to break the table down into months, go in chronological order from left to right. This sets up my brain to expect the rest of the table to behave in the same way. So why don’t the last 8 games do the same? Unless you actually have some knowledge about how the season ended, you might not actually realize that time is going from right to left. I happened to know that Liverpool and Arsenal didn’t lose their final games of the season. This made me double check what the table was actually telling me. Without some knowledge of how the Premiership ended I would have interpreted the information presented wrongly. The fact that time is going from right to left isn’t wrong. The fact that the rest of the table is telling me to expect it to go from left to right is.

“Well, Clive, it’s all about the two M’s. Movement and positioning” - Ron Atkinson

So what improvements could we make to the standard table format to get more information into the table? To tell the story of the 2007-2008 league we need to include the historical context.

The Excel table below uses sparklines to summarizes the season for each team in terms of relative positions in the table and a win lose chart for the entire season.

image

 

Now we get a feeling for the true drama that occurred during the season. The Position column shows how the each teams position changed over the course of the league. Those of us that followed the 2007-2008 Premiership will be able to look at the performance curves and remember the situation for a given time. For example:

  • The lowest point of the season for Man Utd ,in every sense, was when they lost to Man City
  • The departure of Jose Mourinho after 8 games was a catalyst for change in Chelsea’s fortunes

“I never make predictions, and I never will” - Paul Gascoigne

It is interesting to directly compare the performance of teams together. Adding an interactive Bumps Chart in allows us to see how Man Utd and Chelsea faired over the season. To compare two team click the sparklines in the ranking table or click the data label on the Bumps Charts.

image

 

In the next post I will talk about the techniques used to produce the ineractive ranking table within Excel. Until then why not check out a live web version, published from Excel to our website.

Effective Management Reports? Interview with Rolf Hichert

Professor Rolf Hichert is the foremost specialist for information design for financial professionals in the German speaking world. His seminars have been attended by thousands of CFO’s, financial controllers in Germany, Austria, Switzerland and the UK. Recently my friend Martin from INTALIGN had the pleasure to interview Professor Dr. Rolf Hichert about his view on what makes an effective management report:

Professor Hichert, your extensive research claims management reports are often ineffective and largely misunderstood, mainly because they are simply never read. Yet we all continue to create these complex documents. Why is that?

Hichert: Reading management reports is enormously time-consuming, in a time when our corporate culture is particularly time-poor. Concise messages are buried, or missing altogether, phraseologies can be confusing, and notation is often not uniform. Frequently, those who do understand the reports have had prior knowledge of its contents, so they are simply reinforcing what they already know. Financial controllers in particular are frequently frustrated by what they perceive to be a lack of understanding, and interest in their reports, despite the tremendous amount of work they may have put into creating a very comprehensive document. I liken the experience to a newspaper editor, who writes a long, in-depth story and then complains about lack of interest by his readers.

What is the main objective of a management report?

Hichert: Reports need to convey a comprehensive message, otherwise they function merely as a statistic or a reference book, comparable to a telephone directory. “To report” means that the creator of the report has taken a certain position and has something of value or novel to say. This may be in the form of statements, explanations, conclusions or recommendations. So, according to this definition, many management reports are not actually reports at all, but merely an exercise in pontification.

Who should be recipients of reports?

Hichert: Structured reports are usually directed to the executive level, the managing directors, and board members. We’re all contributing to information overload however, and there is a considerable increase in the tendency to now supply these reports to middle management, and even trickle them down to all the company’s employees. Other business partners such as banks and investors also have access or are supplied with reports on a regular basis.

We hear a lot of managers complain about the volume of management reports – is that a common problem?

Hichert: Criticism about the extent and thickness of management reports probably dates back to the first ever management report itself. I come across many companies in which senior management are buried under monthly reports containing over 100 pages, an unsurmountable monthly feat to read. And then there are organizations where reports contain only 10 pages or less. The reasons for the extent of management reports are varied; if a report is targeted at a large diverse group for example, it inevitably becomes more extensive as it has to cover a wide variety of needs. In addition, volume may vary depending upon the objective of the report – wether it is to provide an overview, or to give full and complete details. I believe that the question of validity centres less around the extent of a report, and more around the structure itself - is it easy to read and does it follow clear, consistent structures?

Do you then recommend using more charts in a management report?

Hichert: We live in a visual world, where a picture is worth 1,000 words. Pictures are much quicker and clearer to describe complex facts, which might otherwise require substantial wording. It is important to note though, that we can over-use charts as well. Many management reports use charts to visualize numbers that could easily be described in two brief sentences or less. If I want to refer to an export portion of 50% for example, I can easily do this in one sentence, I really don’t need to waste space on a pie chart that depicts only two halves. Such ‘business charts’ serve primarily for ‘optical loosening up’ reports that otherwise might only contain tables and texts. Financial analysts typically complain to me that “my boss is a numbers man, he doesn’t like charts, he prefers tables.” If you look at the quality of charts produced, you can understand this notion. Typically these charts have very low ‘information density’ and are weak illustrations, with no clear message, ‘cut off’ axes, and lack a consistent concept of notation and design structures.

How important is the inclusion of strategic aspects into management reports?

Hichert: Naturally, the structure and contents of management reports should be as aligned as possible to the company’s overall targets. The now popular introduction of a Balanced Scorecard into an organization, with the objective of aligning operations with corporate strategies, offers the ideal opportunity to rethink and improve corporate reporting systems.

What are your thoughts on packaging reports  ‘decoratively’?

Hichert: We now have easy access to creative programs which render all of us amateur graphic designers. Too often, though the necessary knowledge about basic information design principles is missing. CD (Corporate Design) guidelines, that are in principle important to unify content typically  don’t address those either. From our research, we know that the simpler the structure, the easier the report is to understand. Our work shows that such graphic elements as colored backgrounds, decorative pictures, pseudo-3-D-display, shades, frames or other design facets which may be inserted without meaning, should be considered as noise. Rather than add to a report, these features ultimately reduce the quality and the message of a report. Decorations that are unnecessary additions can dilute and crowd out the message. The over-use of color is the most common source of error. Color should only be used if it has an assigned meaning. One shouldn’t for example, expect that the reader will understand the use of red and green as traffic light colors, indicating stop and go on projects, if these colors are also used in other areas of the report for purely decorative purposes.

What is your recommendation in regards to how to deal with the display of variances between actuals and targets or plans?

Hichert: Typically, the major portion of a management report should demonstrate substantial deviations between targets and current actual values. This includes different forms of deviations, such as, for example, between previous years or even more importantly, corporate numbers versus industry benchmarks. If deviations are important, then they should also be concisely represented and emphasized through colors, arrows or frames. The more important the deviation, the more the emphasis must be marked. Professional report guidelines should ensure that equally relevant deviations are equally marked and represented. And it should apply not only to charts, but to tables and texts as well. This principle should always be applied to any reports in an organization, in a consistent and uniform manner.

So, the format of reports should be standardized?

Hichert: We strongly recommend employing a consistent uniform design concept which can be easily understood and interpreted without confusion. The key sign of a quality report is its ability to convey a message and explain facts in a clear and simple manner. Its objective is not to be an object of beauty. Today, we rarely see organizations that have mapped out clear guidelines and rules for scaling, usage of color, when to use what chart types, tables or texts. But consider the road map which universally utilizes a single color scheme; a river is always blue, the scale is always on each side, and north is always at the top. Whether in Australia, or Africa, the rules of the map remain the same. With management reports, it‘s usually left to the creator whether turnover figures are shown in blue columns or green lines – rendering it difficult guesswork for the executive who is forced to interpret and understand it. To be fair, cartographers needed many hundred years to develop visualization of roads, cities and to unite standards - so the financial controllers still have some time…