Variance / Difference / Spread of Performance problems

[dwcjmilo]

Hey Everyone,

Perhaps I'm suffering from some tunnel vision and forgive me for the wordy explanation... but I could use some assistance. I'm needing to calculate the Percentage Point difference between the top and the bottom performers across a series of different stats. This is normally very simple if the stat is already represented in a percentage format. The challenge that I'm having is using the same formula (for consistency) on data that isn't inherently in a % format (like Gross Sales Revenue example below).

I am trying to measure the performance of a group of people to get them within a 5% Point Difference of each other, when the difference of the Top Sales performer and the bottom Sales performer is reflecting $645k difference in a currency format instead of a Percentage format. (example below)

I tried switching this to a Percent Variance calculation and the outliers in performance are causing a large % of variance and isn't the same message as Percentage Point Difference.

So I guess I need insight/suggestions on out-of-the-box ways that you've seen/done to measure a gap in performance and account for outliers that would work on large revenue numbers as well as numbers already in a percentage format. And it would need to be something that would appropriately scale when communicated in a percentage Point Difference.

The two formulas that are most favored so far are below the sample data, but it still feels like there is an option I'm overlooking.


[TABLE="width: 1176"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Revenue
[/TD]
[TD]% to Rev Goal
[/TD]
[TD]Revenue Goal
[/TD]
[/TR]
[TR]
[TD]Salesperson 1
[/TD]
[TD]275590.64
[/TD]
[TD]=SUM(B2/D2)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 2
[/TD]
[TD]223687.99
[/TD]
[TD]=SUM(B3/D3)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 3
[/TD]
[TD]355685.14
[/TD]
[TD]=SUM(B4/D4)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 4
[/TD]
[TD]38658.22
[/TD]
[TD]=SUM(B5/D5)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 5
[/TD]
[TD]272800.49
[/TD]
[TD]=SUM(B6/D6)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 6
[/TD]
[TD]500215.99
[/TD]
[TD]=SUM(B7/D7)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 7
[/TD]
[TD]2297.56
[/TD]
[TD]=SUM(B8/D8)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 8
[/TD]
[TD]191519.39
[/TD]
[TD]=SUM(B9/D9)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 9
[/TD]
[TD]647587.01
[/TD]
[TD]=SUM(B10/D10)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 10
[/TD]
[TD]210512.61
[/TD]
[TD]=SUM(B11/D11)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD]Salesperson 11
[/TD]
[TD]297947.96
[/TD]
[TD]=SUM(B12/D12)
[/TD]
[TD]500000
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]CURRENTLY BUILT
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference
[/TD]
[TD]=SUM(MAX(B2:B12)-MIN(B2:B12))
[/TD]
[TD]645289.45
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]OTHER OPTIONS
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Percent Variance (Spread)
[/TD]
[TD]=SUM(MAX(B2:B12)-MIN(B2:B12))/(MIN(B2:B12))
[/TD]
[TD]280.858584759484
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 724"]
<colgroup><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Employee[/TD]
[TD]Revenue[/TD]
[TD]dist from ave[/TD]
[TD][/TD]
[TD][/TD]
[TD]ranked[/TD]
[TD]name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 1[/TD]
[TD="align: right"]275591[/TD]
[TD="align: right"]1363[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-271930[/TD]
[TD]Salesperson 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 2[/TD]
[TD="align: right"]223688[/TD]
[TD="align: right"]-50540[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]-235569[/TD]
[TD]Salesperson 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 3[/TD]
[TD="align: right"]355685[/TD]
[TD="align: right"]81458[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-82708[/TD]
[TD]Salesperson 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 4[/TD]
[TD="align: right"]38658[/TD]
[TD="align: right"]-235569[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-63715[/TD]
[TD]Salesperson 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 5[/TD]
[TD="align: right"]272800[/TD]
[TD="align: right"]-1427[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-50540[/TD]
[TD]Salesperson 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 6[/TD]
[TD="align: right"]500216[/TD]
[TD="align: right"]225988[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]-1427[/TD]
[TD]Salesperson 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 7[/TD]
[TD="align: right"]2298[/TD]
[TD="align: right"]-271930[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1363[/TD]
[TD]Salesperson 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 8[/TD]
[TD="align: right"]191519[/TD]
[TD="align: right"]-82708[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]23720[/TD]
[TD]Salesperson 11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 9[/TD]
[TD="align: right"]647587[/TD]
[TD="align: right"]373359[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]81458[/TD]
[TD]Salesperson 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 10[/TD]
[TD="align: right"]210513[/TD]
[TD="align: right"]-63715[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]225988[/TD]
[TD]Salesperson 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salesperson 11[/TD]
[TD="align: right"]297948[/TD]
[TD="align: right"]23720[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]373359[/TD]
[TD]Salesperson 9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]average[/TD]
[TD="align: right"]274228[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]from this you can see your top performers and poorest performers[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]it could be that salesperson 7's area is in a poverty stricken area etc etc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]you will know about that[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]not sure why you want percentages if the info is as above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]I would call salesperson 9 in for a chat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]to try and find out why he/she is so successful[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]