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

Where is the source file of that data?

On my job I usually have to present data-rich slides that are the result of analysis on sets of data. One of my pet peeves on this types of slides is the omission of the data source. As I always point out to analysts in training, *please* do yourself a favor: put the footnote with the source. You’ll be happy when 5 months later you are asked to support some results of your analysis.

That answers the question “What is the source of your data?”. More often than not, when you have to support your analysis, its also handy to know “Where is the source of my data?”. My recommendation decks are usually in Powerpoint, and I only sparringly use Office’s embedded file feature, so what is on the slide is just a picture of the spreadsheet, chart or table.

My source notes usually look as shown below:

The way I recommend analysts in training to do it is through the CELL function in Excel.

=CELL("filename")

The formula above will print the whole path and filename of the file. If you want only the filename, you can use

MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1))- 1), FIND("[",CELL("filename", A1))+1, 255)