How can I best visualize performance data by tenure?

March 7, 2009 12:31 PM

Excel charting help: Help me analyze sales stats based on salesperson tenure.

I have an Excel file with the names, start dates, sales volumes, margins, and average unit selling prices for a sales team of 1,300 people dating as far back as 11 years.

I want to understand the performance differences between subgroups with different tenures (for example, how much better or worse on average do those with 1 -1.5 years experience perform than those with 2-2.5 years experience?).

I would really like to represent this data graphically in a chart. It seems like a histogram or a scatter plot with a trend line makes the most sense, but I am not a statistician and would describe my charting capability as “just above basic”. What is the best way to represent this stuff graphically and how do I build that chart?

Should I create a “sales performance index” of the sales metrics so that I can simplify the data into three variables: name, start date, and sales performance index? If so, how would you recommend creating such an index?

posted by samuelad to computers & internet (6 answers total)

First idea would be to create a new field, called "experience", with a formula something along the lines of: (assuming B2 has the start date of the first person)
=ROUND(2*(NOW() - C2)/365,0)/2
This will group everyone into categories of 0, 0.5, 1, 1.5, etc. years of experience.

Second thing would be to put the data into a pivot table to figure out what trends you can identify. I know that you want to create an index, but you don't know what the index should be until you've explored the data. I'm guessing that you haven't used pivot tables before, but they are perfect for this type of data exploration.

I didn't really answer your question, but that's where I would start.
posted by nyc_consultant at 1:24 PM on March 7, 2009

This will be a lot simpler if you put it into a database, and then run aggregate ("group by") queries on it.

As far as charting: Excel presents a shallow learning curve, but doing anything "unexpected" (such giving a date axis reasonable labels) becomes hard. Gnuplot has a much steeper learning curve, but once you've figured it out, actually getting what you want is much easier.

What I'm telling you is, you have to pick the right tools for the job. If your analysis is just going to be ad hoc, if you'll only spend a few days on it, if you're not planning to repeat the analysis periodically as you get more data, if the analysis is not critical to your business, then by all means use Excel as your sole tool. Expect to end up with something brittle, hard to understand, and not re-usable.

If this analysis is something you're planning to devoting significant time to, if the output is business critical, if you'll be coming back periodically to repeat the analysis on new data and to modify analysis in light of knowledge gained by studying the analysis's outputs, then take the time to use the right tools for the job.

Basically, it's the difference between using a typewriter and a word processor: if you just plan to bang out something once and never edit or revise it, if there's little chance you'll make typos, if what you're banging out isn't that important, then there's little reason to prefer using a word processor to a typewriter. But if you're document is business critical, take the time to learn to use the word processor.

Excel's a great typewriter.
posted by orthogonality at 1:28 PM on March 7, 2009

Not a direct answer, but if you want to use data that's 11 years old alongside data that's current, have you considered adjusting all the numbers for inflation? I'm obviously assuming the sales volumes are measured in dollars.

Aside from that issue, you might want to make separate charts for each sort of statistic (volumes, margins, average prices), with various tenure subgroups across the horizontal axis and the statistics illustrated vertically. I'd probably choose a bar graph, but then I'm not a statistician either.

The sales performance index idea sounds like it would force you to oversimplify the data. My guess is that it won't be so helpful.
posted by jon1270 at 1:29 PM on March 7, 2009

Technically orthogonality is correct, a database program would do the job better, but if this is all you need to do it may not make sense to go through the process of getting new software and having to go through the learning curve. Especially since Excel can do what you need it to do.

Basically I'd put some formulas on a separate sheet which will summarize the data points (i.e. average volume, average margin, average selling price) for each subgroup (1-1.5 years, etc). Then I'd create a bar graph which shows each. I don't think it needs to be any more complicated. Also, I agree with Jon1270 that an index runs the risk of oversimplifying the data. Unless you can categorically state precisely how much weight each of the three factors should have, your best bet is to look at the data on the three metrics and use your judgment

One important question, though - what version of Excel do you have? Excel 2007 introduced new functions that would enable you to cull the necessary data and then create graphs from it. There are workarounds in older versions that would also let you do the same thing, just a little bit messier.


If you're interested, I could probably set up a template for you in about 15 minutes. Just let me know what version of Excel you have and what the tenure breaks would be. (I wouldn't do it in 11 breaks - perhaps 0-3, 3.1 to 5, 5.1 to 7 and 7.1 to 11?) Send me an e-mail if you're interested.

As far as jon1270's point about inflation: if you are using historical data this is key as failure to adjust for inflation would make your comparisons meaningless. Similarly, if the margin for whatever it is you sell has changed, then comparing historical margins to current would not be accurate. Obviously if you're just looking at people with different levels of seniority did during a recent finite period, then you needn't worry about that.
posted by robverb at 6:11 PM on March 7, 2009

I would personally use a scatter plot for each quantity. As others pointed out, you'd want to make sure all the numbers are adjusted for inflation.

To give a specific example. the plot of sales volume. I'm going to assume you have numbers for each month, but you can adjust accordingly. Then you'll have one point for each sales person, each month. Each data point has as its X-value the total work experience that particular employee had that month. Each Y-value is the sales volume that month. You can make a the column of X-values by just subtracting (Entry Date - Start Date). By avoiding binning, you keep all the variation explicit. Not only do you see how well people do on average, you see the spread. If you plot each salesperson in separate columns but on the same plot, you can make them different colors (which may reveal differences due to some people just being plain better than others, or more variable, etc).
posted by Humanzee at 7:02 PM on March 7, 2009

As I think about this more, it seems to me that you can get a rough idea by simply sorting the data by tenure and then plotting the three variables on line graphs. If there is any correlation between tenure and your variables it should be evident (the lines will slope in one direction or another or they'll be a peak somewhere in the middle). If there is no trend, then there's no point in breaking data down further. If there is you can then parse the data into sub groups to get a finer measure.

Be careful how you use the results, though; correlation is not causation and past performance may not predict future results.
posted by robverb at 10:09 AM on March 8, 2009