Extreme Makeover: Chart Edition – July 19

As every week I end up receiving at lest one junkchart specimen, I’m starting the Extreme Makeover: Chart Edition series. I’ll show the old chart, the proposed improvement, and why I believe the old one doesn’t work. I’d love to hear your comments as well.

Here it goes:

And the makeover:

Problems:

• Vertical labels: Even when the list of items is long and is tempting to use columns to use the width of the screen versus the height to show the items, it forces the audience to turn their heads to read the labels (or to not read them at all)

• Clutter: Does your audience need to know to the dollar the GDP in each region? The new chart uses ‘000s. People will remember more easily data they get interested in. Will someone interested in the Shangdong province remember the GPD per capita is $3,162? More chance they will recall it was like 3.2 thousand, right?
• More clutter: Once you label the bars/columns, why keeping the axis? Also, the arrow to highlight the 9x difference adds clutter to the chart
• Lack of labels/legends/source: The original chart has an average line, which is evident only on close inspection because it is not labeled. Also, the columns are grouped in colors, without legends, so a reader, like me, is left clueless about their meaning. Finally, every chart using data must show its source (I’d have noted one if I knew where the original came from)

In charts, Simplicity is King: I’m assuming in the context of the presentation it was necessary to include every province by name.
“Is this cart needed? Is this level of detail needed?” That’s a question you should ask yourself before including any chart. The answer is completely situation-specific. What is the key message to communicate? Who is the audience? What will they look for from their specific viewpoint?

So, what do you think? How would you further improve my proposal?

El Juego de la Cerveza – Systems Dynamics

One of the activities conducted during the System Dynamics class in Colombia was the Beer Game – El Juego de la Cerveza

It allows students to experience first-hand how structure of business organizations impact their behavior, to some extent irrespectively of how skilled their managers may be. In the game, a 3 tier distribution system delivers beer from a factory to consumers. Only the retailer knows the consumer demand. As information and goods propagates through the chain with delays, oscillation occurs.

Students play competitively, as a pool of money collected from them at the start of the game will be awarded to the winners. A very entertaining and active dynamic develops during the 4 hour exercise.

The Systems Dynamics Society offers kits for playing the beer game, that I recommend. They include everything you need to run the game, as well as videos of Professor Sterman running a session.

I have attended many of his sessions, both as student and observer – my employer sponsors the Beer Game during incoming MBA orientation at MIT. Professor Craig Kirkwood at Arizona State University also has very good materials and hints.

I decided to run with a low-budget version that uses beans, pieces of paper and boards printed on common plotter paper. Everything is in Spanish language. I believe publishing the materials here may be useful to colleagues in Latin America and other Spanish-speaking countries.

Here is the board for the game, and here are the slides

For editable versions of these files, please contact me directly.

Transmilenio – a world-class example of appropriate technology

It is refreshing to see when developed countries take note and learn from the success stories of developing countries. In particular, Transmilenio, Bogota’s Rapid Transit System, is an example of what E.F. Schumacher called “Appropriate Technology”

Michael Bloomberg, New York City’s mayor writes on The Economist

…we drew on the experiences of Berlin for our renewable-energy and green-roof policies; Hong Kong, Shanghai and Delhi for our innovative transit improvements; Copenhagen for our pedestrian and cycling upgrades; Chicago and Los Angeles for our plan to plant 1m more trees; Amsterdam and Tokyo for our transit-oriented development policies; and Bogotá for our plans for Bus Rapid Transit

Harmonic Averages

This is a short note to talk about Harmonic Averages. Most people are familiar with Weighted Averages, as they are a valuable tool for aggregation. For instance, with the data below, the average profitability (~1735) can be easily calculated using weighted averages.

Avg_Profit = (1000*1200 + 200*300 + 500*2500 + 10*600 + 100*300) / (1000 + 200 + 500 + 10 + 100)

or

Avg_Profit = SUMPRODUCT(UnitsSold,ProfitPerUnit)/SUM(UnitsSold)

I’m using Excel notation, and assuming it is clear from the context that UnitsSold is a range that covers the second column, for all models, etc.

A less known way of averaging are Harmonic Averages. It is relevant when the data to aggregate is actually a ratio whose denominator is proportional to the weighting factor. A typical case is miles per gallon (MPG) for a bunch of vehicles. Gas consumption is directly proportional to the number of units.

Let’s add some MPG data to the table above.

Using Weighted Averages for an inverse ratio like MPG is plain wrong (24.3 MPG is NOT the average fuel economy)

The right thing is to use Harmonic Average:

Harm_Avg_MPG = (1000 + 200 + 500 + 10 + 100) / (1000/22.5 + 200/15.0 + 500/32.0 + 10/12.0 + 100/24.0)

As Excel doesn’t have a similar function to SUMPRODUCT for adding 1000/22.5, 200/15.0, etc. I will not use Excel notation, but plain math notation:

UPDATED formula

If you have to deal with Harmonic Averages, you may find interesting this note on how to do PivotTable Multidimensional Analysis with Harmonic Averages. There’s a similar one for Weighted Averages as well.

Let me know what you think.