Analyze Competition with Scatter Plots – A business chart example

Posted on October 25th, 2010 in Charts and Graphs - 7 comments

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?
Competition in Mobile Handset Space - Analytical chart
[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:

Competition Analysis - Raw Data

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:

Processed Data - Competition Analysis in Excel

Step 3: Make a scatter plot

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

Competition Analysis using Excel Charts - Step 1 - Make a Scatter Plot

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
    Competition Analysis using Excel Charts - Step 2 - Connect Dots
  • Set arrow symbol as the end-point style for these lines (new feature in Excel 2007 and above)
    Using Arrow symbols in Scatter plots
  • 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:

Competition Analysis using Excel Charts - Final View

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:

Competition Analysis using Excel Charts - a different view

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.

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

7 Responses to “Analyze Competition with Scatter Plots – A business chart example”

  1. Gregory says:

    I like the first chart because the four quadrants are clearly evident, but for this data set I like your second chart even more because you get to see the starting points.

  2. Michael Pierce says:

    For me, the second chart is more useful. The first only tells me movement that each product has made, but there is no context for that movement. In the second chart, I get to see the same movement but also the context (i.e. the relative position of each. For example, in the first chart Product D looks to be the absolute worst. In the second chart, however, I can see that Product D is falling, but is still the dominant product.

    Sorry for the remedial question, it must be a typical Monday for me and everything is going wrong. Scatter charts are always the worst for me. When I select the data ranges and create the chart, nothing even remotely resembles the unformatted scatter chart you have in Step 3. What am I doing wrong?

  3. Chandoo says:

    @Gregory & Michael... I agree. Even I like the 2nd chart better.

  4. DTH says:

    I've downloaded the file and repeat the instruction steps myself. It took me some time to realize that you didn't add trendlines to have the arrows. You wrote "add line" instead.

    I had a problem with the direction of the arrows if I add trendlines to the data series, C & D for instance (I did set arrow for end style of trendline).

    Could you help to explain why? 

  5. arun kumar chaurasia says:

    dear sir,

    first time i show this types of clock in excel . this is fantastic i like it very very nice.

    once again very nice.

  6. Joe says:

    On the relative chart the labels for B & C are reversed. They are correct on the absolute chart. Also they are correct on the preliminary relative charts. Perhaps it's coffee time, but can't seem to fix this in the downloaded file. For example, what I check "Format data labels", not of the boxes are checked. Very strange...

    I already have great appreciation for the site, but help with this would also be appreciated!

    Joe

  7. Joe says:

    Figured it out! (In Excel 2010 anyway). When you set the data label on the end point, it defaults to the value. If you right click the data label, none of the check boxes are on (Series Name, X, Y). If you activate Series Name, then it is on both begin and end points....

    However if you **double click** the data label, then the "Y" value is activated. Change it to Series Name and all set.

    A truly arcane bug in Excel!

Leave a Reply