Ranking Sales Reps based on multiple weighted criteria

[fkrueger1]

Good day,

I am hoping someone can help me simplify a project to rank our sales force. We need to rank them by 4 criteria and each has a different weight. Here are the columns, criteria and weights:

[TABLE="width: 509"]
<tbody>[TR]
[TD][TABLE="width: 509"]
<tbody>[TR]
[TD]Weight[/TD]
[TD]0.4[/TD]
[TD]0.2[/TD]
[TD]0.25[/TD]
[TD]0.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rep[/TD]
[TD]% of Budget[/TD]
[TD]% of PY[/TD]
[TD]PY $ Change[/TD]
[TD]Subjective[/TD]
[TD] Rank[/TD]
[/TR]
[TR]
[TD]Rep 1[/TD]
[TD]88%[/TD]
[TD]75%[/TD]
[TD] (71,899.26)[/TD]
[TD] 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rep 2[/TD]
[TD]68%[/TD]
[TD]33%[/TD]
[TD] (141,314.02)[/TD]
[TD] 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rep 3[/TD]
[TD]81%[/TD]
[TD]78%[/TD]
[TD] (80,251.84)[/TD]
[TD] 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rep 4[/TD]
[TD]81%[/TD]
[TD]82%[/TD]
[TD] (45,960.40)[/TD]
[TD] 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rep 5[/TD]
[TD]145%[/TD]
[TD]149%[/TD]
[TD] 27,948.30[/TD]
[TD] 10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I currently rank each column separately and assign points where the first column is worth 40, then 20, 25, and finally 15. A rep would earn a share of those points based on how high they rank in each column. The total one rep may earn would be 100 if they were the top in each category. I was wondering if there is an easier way to complete this using a single formula. I got stuck because of the switch in criteria between percentages and whole numbers.

Thanks in advance for any help you wonderful people may be able to provide.

 

[BlueHornet]

Maybe I can help you to consider how this can be visualized, but there's a lot more work for you to make the thing operate.

We use something very similar to evaluate Percent Complete of large-scale and high-complexity construction projects of long duration. Our problems are not entirely dissimilar, though. The construction project involves cubic yards of concrete, tons of steel erected, quantities of welds completed, miles of electrical cable run, etc. How to compare those as apples-to-apples? For our uses, it all comes down to man-hours. (That is, for "erection only". For supply of the materials it would probably come down to "$$$".) So we go through the line items of all components to be erected and come up with an engineer's estimate (which doesn't have to agree too closely with the contractor's closely held estimate of manhours per line item, which they are not required to release to us). This enables us to arrive at the "project total weighting" for each line item. (As line items are added and subtracted, then those weights may vary over the life of the project, so it is a 'living document'.)

But wait, that's not all!

Since these projects take a long time, there's a time element, too. When the contractor receives materials and checks them for damage, quantity, count, etc. and puts them into temporary storage against the day that they'll be erected, he expects that to be part of his completed work — and it is. Then there's often a pre-assembly stage, where he ground-assembles some of the components into larger assemblies, then further when he lifts the components into place, then fastens or welds them into a permanent location, and finally when whole subsystems and larger systems are finish-painted, tested, cleaned, commissioned, etc. prior to turnover. The contractor needs to be paid over the entire life of the project for completed work, and we need to arrive at an equitable way to determine and agree on "how much?" per month.

So we've developed a method to break down each line item into "Receive and Store", "Pre-Assemble and Stage", "Erect / Weld in Place", "Commissioning", "Turnover". We've broken those out (in general, and we can change the mix as required) to 5%, 15%, 25%, 35%, 10%, respectively for each of those stages. That gives us a line-item weighted value for each element of the project further broken out into those five categories of completion.

The beauty of the system is that we don't have to guess at things. If one line item of the project is, for example, "Steel Erection". We can know by watching the shipping manifests and the receipt of materials, how much of the structural steel has been received, month by month, and each month award "x% complete" of the "Receive and Store" box for structural steel. Then as the building erection starts, we can estimate the % complete for that — this is all "stuff that can be counted" and laid into the various categories. (Since structural steel doesn't take a lot of finesse, then the Pre-Assemble and Erect / Fasten is pretty much all done at the same time. (The contractor won't argue against that, since it accelerates his payments.)

Anyway, you see how that can go: once the methodology is arrived at, the rest is just counting and agreeing that "this line item is x% complete" vs. "this line item is y% complete". There's room for slight differences of opinion line-by-line, but it's all viewable, out in the open, and negotiable between reasonable observers.

Your problem is somewhat different. You have a comparison of percentages, which is going to be tough to start with, because the base numbers are not on view (except for the values for PY $ Change and Subjective), but in addition to that "Subjective" is just that — completely subjective, not objective at all. You have an additional problem, because you don't have objective measures of excellence / completion. It might be easy enough, once you work out how to standardize the measures somehow, where each Rep ranks against the other Reps, but you'll have no measure of "objective excellence", which will make this a somewhat iffy measure to start with. See, in our projects we can't compare "Steel" to "Pressure Parts" to "Electric Cable" in any meaningful way, because "the things" don't compare. All we can do is assign that line-item's weight to each item and go from there.