Tornado Charts in Excel 2007 Update

This is an update to my previous post on tornado charts.

As reader Shaun pointed out, the instructions were created under Excel 2003. Let’s refresh them to Excel 2007.

There is no need for any external add-ins for making good-looking tornado charts. And it takes only 5 steps. Let’s start with data in a table like this:

1. Select the data, excluding the Delta column
2. On the Insert ribbon, choose Bar. Pick Clustered Bar (first one in the 2-D section)
3. Right-click on the horizontal axis, choose Format Axis. On the bottom of the Axis Options pane look for Vertical Axis crosses:, choose the Axis value radio button, and type your base case value (7 in the example)
4. Without closing the window, choose the vertical axis of the chart. Again on Axis Options, check the box Categories in reverse order, and set the Axis Labels menu to Low
5. Without closing the window, click on any of the bars, which should bring the Series Options pane. Move the slider Series Overlap completely to the right (Overlapped).

That’s it! Now you can format the chart as you like. The final result should look something like this:

Final Tornado Chart

Keep reading to learn more, to watch a screencast, or download the Excel file

(more…)

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.

Market partition – Mekko chart in Excel, no add-ins

Mekko charts are two dimensional graphs that analyze how data is partitioned against two variables, the X and Y axes. The width of the columns is proportional to data represented by the columns. Individual segment height is a percentage of the respective bar total value.

Yet another in-cell Excel bar chart technique

Two improvements over the technique described by Juice Analytics and posted in Lifehacker: better resolution and solid-looking bars that show better at different font sizes.

If your values are integers in a range 0-9 or so, you can use the REPT formula as presented there, and perhaps you like the dashed type bars, so the formula as shown would work perfectly for you. If not, keep reading.
(more…)

Math on the simplified market adoption s-curve for Excel

I’ve got a number of questions on the simplified Excel s-curve formula I published some time ago, so here are more details for those interested in the math behind it. The previous posting focused on how business analysts sometimes need to model market adoption, and provided a simple and easy to maintain formula to do so in Excel.

The formula =saturation/(1 + 81^((hypergrowth + takeover/2 – year)/takeover)) suggested for Excel is a simplification of the formula for a sigmoid function (See the Wikipedia article)

The graphic below shows the shape of both functions is identical. The saturation parameter just scales the function to a desired value, instead of going from 0 to 1. The factor 81 on the Excel formula determines how “sharp” the curve is, in this particular case, reaching 0.1 at the period hypergrowth and 0.9 at hypergrowth + takeover. Note that 81^x can be re-written as e^(ln(81)*x), so whatever factor is used there is simply going to affect the shape by compressing or expanding it horizontally.

This is how the scaling factor can be computed. Let’s say we want the penetration to be 5% at the period specified by hypergrowth. We can work out the solution off the second function. We need to solve for 1/(1+e^(-x) == 0.05, which gives x=-2.94444. Since the function is symmetrical, we also know for x=2.94444 P(x) == 0.95.

Since factor^((hypergrowth + takeover/2 – year)/takeover)) can be re-written as e^(ln(factor)*(hypergrowth + takeover/2 – year)/takeover)), we can solve ln(factor)*(hypergrowth + takeover/2 – (hypergrowth + takeover))/takeover == 2.94444. Reducing all the math, we arrive to
1/(1 + e^(-0.5*ln(factor))) == 0.95, and factor would be 361. If the desired penetration at hypergrowth is 20%, then we solve 1/(1 + e^(-0.5*ln(factor))) == 0.80, leading to factor == 16

Junkchart and pistongeekery on the same page!

The sites I refer to when I talk about charting, data analysis and good (and bad) charting techniques are normally quite different from the ones where I satisfy my gearhead lust with the news from the latest car models in the market. This time the guys at motortrend did it for me in their article “Finding the best-handling car in the US”: both fixes in just one page

Look at the following chart: well, it’s easy to tell the winner on their tests is the yellow line (if you are curious, the Porsche 911GT3). But can you tell from the green and red lines in the middle of the pack which one is better?

If you go to their gallery, you’ll see many other charts that fight fiercely for the top place in the Junk Chart museum. Just a couple of samples below

Spider charts may be useful, but only in very limited cases. Leave the webs to the guy in the custome, enjoy the movie, and stick to other chart types in your professional reports. You would be better off. Your audience will get it faster.

Read on to see a proposal on how this chart could be better.

(more…)

Modeling market adoption in Excel with a simplified s-curve

UPDATE 10/31/2007: Here is a screencast for this model

Often business analysts need to model the adoption of a new product or service for financial planning. There are several approaches, but a common one is the s-curve (see Wikipedia article). Here is a simple implementation in Excel that can be easily added to your spreadsheets. It reduces all the math to just three parameters:

• saturation – What is the maximum expected penetration after the product becomes mainstream? i.e. what is the value that the top of the s-curve will reach?
• start of fast growth – By this year, the penetration will be 10% of the saturation value, and it will start to grow rapidly. 10% was an arbitrary choice to simplify the model, and by doing some math you could change the formula to any value. It is a reasonable choice in most cases. We’ll call this parameter hypergrowth
• takeover time – How long it will take for the product to “catch on”? – The operational assumption in the formula is that this number of years after the start of fast growth, the product would have reached 90% of the saturation value and will start to slow down. Again, 90% is an arbitrary value I chose.

The s-curve model focuses in the early phases of the product lifecycle, until maturity is reached. Penetration decay is NOT covered by this model.

The formula for each year’s penetration would simply be:
=saturation/(1+81^((hypergrowth+takeover/2-year)/takeover))

See it in action:

In the sample spreadsheet above, look at cell B8 where you can see the formula in use. It is the same for all row 8.

saturation, hypergrowth and takeover are names defined for the parameters on rows 2 to 5 (you use names in your models instead of plain cell references, don’t you?)

Very simple, easy to maintain, light on calculation times… happy market adoption modeling!

PS: The chart shown is NeoOffice, an open source alternative to Excel for Macintosh users, based on OpenOffice

Some more JunkChart

Martin Theus posts here an example of poor charting or “junk charts”. I’ve had the opportunity to work with many organizations as a management consultant, and it’s sad to report that is more the norm than the exception to see charts like that one in the workplace.

Feeding my bad habit of thinking anytime anytime I see a chart how would I restate it to make it cleaner, here is how I would do it. On top, the original chart, below a proposed improvement.

The key questions, whenever one draws a chart, are “Who is my audience?” and “What is the message I want to communicate to them?”. For such chart, I would imagine the audience are the users of a software system for which we are reporting the different sources of errors”, and the underlying message, most likely, is what are the most relevant sources, so we can fix them.

In the original chart, transparency, 3D and color are used, but they are not adding any new information. The pieces of the pie do not show any meaningful order, either. The audience will have to look at the callouts to see which category corresponds to which piece of the pie, and their work is going to be harder by having to follow the callout lines that in some cases converge.

What makes the second chart better?

• The improved chart uses sorting as a way to help the audience. The largest sources of errors appear first.
• Once the data is sorted, it uses the Pareto principle to focus on the main sources of errors, removing from the audience’s eyes a lot of unnecessary detail. Today’s interactive media allows to drill-down into details with a click. Printed presentations can always have backup charts. Simple is beautiful. The Pareto principle is one of those “business commonsense” things that almost everyone has heard about, to the point is almost a cliche, yet people fail to appreciate how powerful it is.
• The use of color is non-gratuitous. Color is very powerful. Most people can differentiate between colors without effort. But they can also get quickly overwhelmed if many colors are used in a chart. Think twice before adding a new color to your chart. Is it communicating something?
  The improved chart has some visual effects, like drop shadows and some color gradient, to make it more appealing. However they don’t work against how easily the message will be understood by the audience, they don’t leave out people with difficulties to tell colors apart, and they will not break when you make a black and white printout of the chart.

Portfolio visualization – PivotGraphs

Without being able to show actual graphics from the data I’m working on, due to confidentiality with my employer, this post is a note on a good technique I’ve found that could be helpful to others in the portfolio management space

I’m finding PivotGraphs a powerful tool to visualize and communicate portfolio interactions. One example would be how different vehicles in a manufacturer’s portfolio interact with each other. Through proper aggregation, it provides insigths that a traditional segmentation will miss.

Here is how a PivotChart looks like, linking to an IBM research article on the topic:

PivotCharts are not related to Excel PivotTables at all

Presenting time series of market participation

One very common need in almost any industry is to show a given market, its size, the key participant and how all has evolved over the last few years. I have used since a few years a technique I like, and yesterday I read on Juice Analytics’ weblog an article describing basically the same approach. By the way, these guys have an interesting blog.

In their sample chart, there’s only one bar and one line, so many people may ask “why bother?” — it’s not that confusing to have the overlay in the default way Excel will leave the combination chart.

The chart below is a good example of why the technique is powerful. Click to see full size. Disclaimer: as this is an excerpt from a slide, the sample is missing a lot of must-have elements of a good chart, like units, meaningful title, etc.

When showing a market and how it is partitioned across players, there are some useful elements you’d like to show:

• Is it a growing, stable or shrinking overall market?
• What is our share? Is it growing?
• How are competitors doing? Are there changes that merit closer understanding?

Most common charts I’ve seen people use for the problem are:

• One pie chart with the last year figures. Ok, but you miss the interesting historical perspective
• Two pie charts side by side. That one hurts my eyes! There are many reasons it’s a bad representation. People are not intuitively good at comparing areas. Requires a lot of effort to follow where in each chart is each player, and how is it doing relatively to competition… enough said!
• Stacked column charts. This is a reasonable approach. Shows the trends nicely. It is hard for people to judge the relative percentage of the bar segments, so sometimes people end up with the percentages annotated in each segment, and in that case the chart is very busy
• Lines showing the share. Basically, the bottom part of the chart shown. It is a nice approach, you can follow what happened to the market players along time. In stable markets, may be all what is needed. The chart may be misleading though in growing markets because it loses the overall market size.

The combination of a bar chart to show the overall picture, plus lines to show the individual player trends has the best mix of good features I’ve found. I’d love to hear suggestions on other ways to approach the problem. It is not easy to spot growth in absolute volume for a given player, but in this is not usually a major drawback. If you are happy with your 3% growth and the market has been growing at 45% you’ll be soon out of business. Talk about charting failure