Fishing For Chart Ideas

[smartin]

Up front, here's a sample of what I came up with:

http://vfdrake.home.comcast.net/~vfdrake/files/excel/crwp.pdf

I am looking for suggestions to improve this chart, or other ways to
present it. A bit of verbose description is required though, so if you
are so inclined please read on...

We have data for 50-odd companies and the industry as a whole, and want
to show where we stand relative to the fray. There are two key metrics
to consider:

"Combined Ratio" (CR). Values less than 100% are considered best (less
is better). Our target is to be under the industry average CR by at
least 4%.

"Written Premium Growth" (WP). Values above 0% are good (more is
better). Our target is to beat the industry average.

So I tried an x-y chart, with CR (reversed*) on the x-axis and WP on the
y-axis. This puts the ideal range in the upper-right, which most will
recognize as "the quadrant where everything is 'positive'".

I removed the legend for posting, but here's a description of what
things mean:
- big fancy icons are for my group
- red icons are companies that consistently perform well
- blue icons are everyone else
- bold axis lines are industry averages
- dashed line is our CR target==4% less than average
- light green shading is our target area per definitions of CR and WP above

* The first pass had CR plotted low-to-high and was well received
internally but the real audience (board level) was confused because the
target area was in the upper-left quadrant.

I can appreciate this and so changed the sense of the x-axis, but the
more I stare at this chart I feel it isn't telling the story so well,
but I have run out of thoughts on how to improve it. I am loathe to add
more clutter than there already is. Given the ultimate audience I do
want to make choices carefully.

So, any ideas?

 

[Ed Ferrero]

Hi smartin,

Perhaps the BCG matrix here
http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx
will give you some ideas?

Ed Ferrero
www.edferrero.com

 

[Jon Peltier]

In general the story can be well told by your chart. I have one correction
and a handful of uncluttering steps you can take.

1. Looks like you put the CR target at 104% (to the left of the average)
when it should be at 96% (to the right of the average).

2. I would use labels on the bold lines to indicate "Average" or "Target".

3. I would use light gray solid lines for the gridlines and medium gray
solid lines for the outer borders. Dashed lines can be distracting, despite
using "less ink".

4. I would use average and target lines which are less bold, perhaps dark
gray instead of black, and thinner than you've used.

5. I would use simpler markers. Lighter blue diamonds for typical companies,
the red circles are good. For the ones I want to highlight, if there are
only a handful I'd use a single style (large dull-green square), and put a
label on each.

- Jon

 

[smartin]

Thank you for the good suggestions.

In general the story can be well told by your chart. I have one correction
and a handful of uncluttering steps you can take.

1. Looks like you put the CR target at 104% (to the left of the average)
when it should be at 96% (to the right of the average).

2. I would use labels on the bold lines to indicate "Average" or "Target".

3. I would use light gray solid lines for the gridlines and medium gray
solid lines for the outer borders. Dashed lines can be distracting, despite
using "less ink".

4. I would use average and target lines which are less bold, perhaps dark
gray instead of black, and thinner than you've used.

5. I would use simpler markers. Lighter blue diamonds for typical companies,
the red circles are good. For the ones I want to highlight, if there are
only a handful I'd use a single style (large dull-green square), and put a
label on each.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

 

[smartin]

Thanks Ed, that does give me some ideas. I tend to shy away from big
areas of color though I actually tried that here thinking the audience
would respond well, but I was frustrated by the built-in shading Excel
offers. I had not thought about using a well-crafted image as a backdrop.