Analyze Competition with Scatter Plots – A business chart example

Analyzing competition is one of the key aspects of running a business. In this article, learn how to use Excel’s scatter plots to understand competition.

How Mobile Handset Market Changed from 2007 to 2010?

[view large]

Recently, Kaiser at Junk Charts pointed to a very effective business chart that shows the dynamics of competitive land scape with ease.

The chart shown aside originally appeared in Asymco, shows how mobile handset market has changed between 2007 & 2010.

What is so special about this chart?

I like this type of chart because it clearly tells the story of what happened in mobile handset market between 2007 and 2010. It shows how then leader, Nokia, kept loosing profit share despite a tiny loss in market share. It shows how new entrants like Apple have eroded the profit share for others. [related: good charts tell stories]

The chart instantly lets me ask important questions like, “So what did Nokia do wrong?”, “How come RIM is also in the same league as Apple (ie both market share and profit share went up) ?” etc. and explore for answers.

And that is what a chart should do. It should present a story and poke our curiosity to ask questions (or address problems).

How to construct similar chart in Excel?

Here is how you can construct similar business chart in excel.

Step 1: Get your data

In case of analytical charts like this, getting correct data is all important. For the sake of example, we will use the same variables – Market Share & Profit Share for 4 fictitious products – A,B,C & D. The data is shown below:

Step 2: Re-arrange the data, so we get first and last values

This is very simple. Use cell references to extract the data for just first and last periods. Now make starting values as zero and calculate ending values. Something like this:

Step 3: Make a scatter plot

Select the data and make a scatter plot. When you are done, it should look something like this:

Step 4: Format the chart

Do the following to format the chart:

• Add lines to scatter plot so that starting and end point are connected
  
• Set arrow symbol as the end-point style for these lines (new feature in Excel 2007 and above)
  
• Remove grid lines and legend
• Add data labels to either starting or end points alone.
• Add axis labels, position them accordingly
• Make axis and labels subtle.
• Add a descriptive chart title

And you are done. The chart should look something like this:

Bonus Step: Making a scatter plot of absolute values

As you can see, the above chart only shows changes in market share and profit share of products between Q1-2008 and Q2-2010. But a more descriptive option would be to show absolute position of each product at both times.

Like this:

To make this chart, all the steps are same as above, just change your data to starting and end points, not the calculated ones.

Download Competition Analysis Chart Templates:

I have prepared a simple excel chart template to help you create similar charts on your own. Click below links to download.

Excel 2007+ | Excel 2003

You can see the chart construction steps in the downloaded workbook.

What do you think about this chart?

As I mentioned I really liked how this chart lays out the dynamics of market place without complicating or animating anything. I think it is both simple and elegant [related: keep your charts simple]

What do you think? Please share your opinion and ideas thru comments. Also, tell us how you would have plotted same data?

More Excel Charts for Analysis:

Excel charts are powerful visual tools for analysis and exploration. We have posted several useful chart templates & ideas on chandoo.org. Please visit these pages for more resources on charting & analytics.

• Excel Charts – Templates & Tutorials
• Charting Principles
• Dynamic Charts in Excel