Rich tables for high resolution reporting
What is better: tables or charts? That was discussed in the past. Some said a chart is worth a thousand words. I think a picture is worth a thousand words. And most charts need a thousand words to be explained. They are so awful, mostly. Because it is so hard to standardize a chart and there are lots of things that can go wrong. Bissantz loves graphic tables. Me too. Charts are tightly integrated into tables. You have the best of both worlds in one concept. Graphic tables are easy to standardize and you can integrate almost every chart type in a table. Sparklines provide time-series. Horizontal bars are good for almost all types of comparisons. Graphic tables maximize data-to-ink ratio. They are fine grained and with high resolution. The graphical elements provide easy orientation for your eyes. The numbers provide all the details. Typical problems of legends and labeling never arise. I provided some examples in the “Radar chart trap” and in “Small things that make a big difference”. Today, I explain how to create waterfall charts in tables.
A waterfall chart is a variant of a bar chart. It shows how an initial value is changed through other values which lead to a final value. In Excel, you can use invisible columns but this is tricky. Rolf has a nice Excel template for free. Data entry is not so easy. Formatting is very nice.
Jon has programmed an Excel add-in. Formatting is not so easy. Data entry is very nice.
In both cases a chart is the result. If you turn it around it is still readable, perhaps even better.
Turned around it fits perfectly into a graphic table. Subtotals are also easy. This is how Bissantz has done it in DeltaMaster.
It is possible in Excel, too. Just use the REPT function and a few tricks. I show you how in an example Excel sheet.
June 6th, 2007 at 5:50 pm
Bella – What a great idea! I use waterfall charts extensively in my consulting practice, but not the particular graphic table implementation you have employed. I am frequently troubled by the lack of standardization in charts and the expanding memory requirements needed to develop them. Your method not only addresses the standardization issue, but it makes much smaller files. Not only do we need less chart junk, we also need less memory junk on our computers.
I didn’t see, though, how net negative values might be displayed with this specific type of graphic table unless a second column was used for the net negative values. To help out a bit, I expanded the logic of your waterfall table to display net negative values and the cross-over bar needed to transition from positive to negative values. I sent an example of this to your friend, Dr. Roland. Maybe he will share that with other readers here.
Another useful implementation of graphic tables may exist with something called tornado charts, which I also use frequently. If you are not familiar with these, they display the amount of variation an uncertain variable contributes to an objective function, like NPV. It is created by sequentially setting each uncertain variable to its p10 and p90 values while holding all the other variables at their mean (…there are various positions on this, but that is another topic) and recording the effect of the outcome on the objective function. Rank ordering the uncertain variables by absolute delta in the objective function produces the tornado shape. The real information from these charts comes from being able to observe which uncertainty would cause one to regret picking one strategy over another. This leads to value-of-information and value-of-control discussions, which lead to the development of hybrid strategies that employ the best features of all the strategies and mitigate the worst. This requires at least a pairwise comparison of tornados, and that would be difficult to implement properly in a graphic table. However, if a pairwise delta between tornados is considered, this would be easily implemented. Otherwise, the singly charted tornado only displays the relative variation imposed by uncertainties in a single strategy. The file that I sent to Dr. Roland also contains an example of a single tornado graphic table.
Best Regards,
Rob
________________________________________________________________________
Robert D. Brown III
Senior Consultant
Decision Strategies, Inc.
Decision Strategies – Confidence Through Clarity
June 12th, 2007 at 7:00 am
Dr. Roland said to link to my modification of the waterfall (and tornado) charts here: http://home.comcast.net/~robert_d_brown3/images/Waterfall_tornado_In_cell.xls
Best Regards,
Rob
________________________________________________________________________
Robert D. Brown III
Senior Consultant
Decision Strategies, Inc.
Decision Strategies – Confidence Through Clarity