We will build an agent scorecard from nothing, using three agents and one month of data. The names and numbers are invented. The aim is to see every decision a scorecard hides, because each one changes who looks good.
The raw month
The client has asked for a weighted score: Quality 40%, AHT 30%, CSAT 30%. The target AHT is 360 seconds. Here is what the three agents did.
| Agent | Quality % | AHT (seconds) | CSAT % |
|---|---|---|---|
| Asha | 92 | 400 | 88 |
| Binu | 88 | 340 | 91 |
| Chitra | 80 | 330 | 95 |
Quality and CSAT are already percentages where higher is better. AHT is different: it is in seconds, and lower is better. You cannot multiply 400 by 30% and add it to 92 times 40%. The units do not match, and the direction is reversed. First job: convert AHT into a score on the same 0 to 100 scale.
Turning AHT into a score
There are many ways. One simple rule: AHT score equals target divided by actual, capped at 100%. In Excel that is the MIN of 1 and target over actual, times 100. Asha: 360 divided by 400 is 90. Binu: 360 divided by 340 is 105.9, capped at 100. Chitra: 330 gives 109.1, capped at 100.
The cap is a choice with consequences. Without it, a very fast agent earns a score above 100 and pulls up the weighted total, which rewards rushing. With it, everyone at or under target is equal. Another approach is slabs: within 5% of target scores 100, within 10% scores 90, and so on. Slabs are easy to explain and produce cliffs. Whichever you choose, write it on the scorecard itself.
The weighted sum
Now each agent has three comparable scores. Put the weights (0.4, 0.3, 0.3) in a row at the top of the sheet and the three scores in the agent’s row. The overall score is the sum of each score times its weight. In Excel, SUMPRODUCT does this in one step, multiplying matching cells across two ranges and adding the results. Lock the weight row with absolute references so the formula can be copied down for every agent, and check that the weights add up to 100% with a plain SUM in a corner cell. A scorecard with weights adding to 110% is a more common error than you would think.
Results:
- Asha: 0.4 times 92 plus 0.3 times 90 plus 0.3 times 88 gives 36.8 + 27.0 + 26.4 = 90.2
- Binu: 35.2 + 30.0 + 27.3 = 92.5
- Chitra: 32.0 + 30.0 + 28.5 = 90.5
Look at the ranking. Binu first, as expected. But Chitra, whose quality is 80%, ahead of Asha, whose quality is 92%? On the face of it the scorecard says Chitra is the better agent this month. A team leader reading only the totals would conclude exactly that, and the incentive would follow.
What a weighted average hides
A weighted average lets a strength in one area cover a weakness in another. Chitra’s very fast calls and high CSAT lifted a weak quality score. Maybe she is a genuinely warm, fast agent who skips mandatory steps; maybe the quality form is harsh. The scorecard cannot tell you which. It only tells you the arithmetic. This is why many scorecards add a gate: if quality falls below a threshold, say 85%, the overall score is capped or the incentive is not paid, whatever the total. In Excel an IF wrapped around the weighted sum does it. With that gate, Chitra’s 90.5 is overridden and her sheet flags quality for coaching. Asha, whose actual issue is speed, sees a clear message too: AHT is the only component pulling her down.
This is the difference between a score and a diagnosis. Use the weighted total for ranking and pay. Use the three components for coaching.
Spreadsheet hygiene
A few habits save embarrassing corrections:
- Keep inputs, weights, targets and results in separate, labelled areas. Hard-coding 0.4 inside a formula means the day the client changes the weights, you have to find every copy.
- Handle blanks. An agent with no CSAT surveys this month should not get zero. Decide a rule: exclude the component and reweight the rest, or use the team average, and apply it the same way for everyone.
- Watch sample size. A CSAT score from three surveys is not a CSAT score. Show the count beside the percentage, and set a minimum before it counts.
- Pull agent-level data with
AVERAGEIFSorSUMIFS, and for AHT, calculate total handle time divided by total calls, not an average of daily averages. Days with few calls would otherwise count the same as days with many. - Round at the end, not at each step, or totals can drift by a point.
- Protect formula cells so a stray typing does not overwrite them, and keep a dated copy of each month’s sheet.
One last check: after the scorecard is built, test it with an extreme case, like an agent with 100% quality and absurd AHT, and see if the result makes sense. Formulas that behave well at the average often behave strangely at the edges.
Figures and names in the examples are illustrative.
Leave a Reply