How to Calculate Call Centre Shrinkage From Your Own Timesheets

To calculate call centre shrinkage, take the paid hours your agents were not available to answer calls, divide by their total paid hours, and use four to eight weeks of your own timesheet and phone login data to do it. Don’t borrow a percentage from a conference slide. A team that plans at 25% and really runs at 30% is two or three people short on a rota built from a 25-agent requirement, and nobody finds out why until the queue shows it.

I covered why the gap matters in why 25 agents on the sheet means 36 on the rota. This is the other half: where the percentage actually comes from, how to build it in Excel so a stranger could rebuild it, and how it goes wrong between the spreadsheet and the roster.

Where does the shrinkage number come from?

Two data sets, both of which you already have. The first is paid hours per agent per week, from payroll or the timesheet. The second is the hours each agent was logged in and available for calls, from the login and logout report your phone system produces. Shrinkage is the gap between them. Call Centre Helper makes the same point about using historical login and logout data rather than spot checks: a Tuesday walk round the floor tells you what Tuesday looked like, and nothing about the week with two people off sick and a training day.

Build one workbook with two sheets. The first sheet, which I call Weekly, has one row per agent for each week you are measuring. Row 1 holds the headings and rows 2 to 31 hold 30 agents:

• Column A: agent name.
• Column B: paid hours for the week, straight from payroll or the timesheet.
• Column C: available hours, meaning the time that agent was logged in and ready for calls, from the phone system report.
• Column D: that agent’s own shrinkage, =1-C2/B2, filled down to row 31. This column is for spotting outliers, not for the team figure.
• Row 33: the team totals. Cell B33 is =SUM(B2:B31), cell C33 is =SUM(C2:C31), and cell D33 holds the team shrinkage.

The team figure in D33 is:

=1-C33/B33

Read it from the inside out. C33 is all the hours your people were actually available. B33 is all the hours you paid for. Dividing one by the other gives the share of paid time that reached the phones, and subtracting that from 1 gives the share that did not. With 30 agents on a mix of full-time and part-time contracts, say B33 comes to 1,100 paid hours and C33 to 774.4 available hours. Then 774.4 / 1,100 = 0.704, and 1 – 0.704 = 0.296, so shrinkage is 29.6%.

Use the sum of the columns, not the average of each agent’s own percentage. Suppose one full-timer was paid 40 hours and was available for 28, which is 30% shrinkage, and one part-timer was paid 20 hours and was available for 12, which is 40%. The average of the two percentages is 35%. The honest figure is 1 – (28 + 12) / (40 + 20) = 1 – 40/60 = 33.3%. The part-timer counts for half as much because they were paid for half as many hours, and the sum method respects that. Run it for each of the last four to eight weeks, then look at the spread as well as the average. If it swings from 24% to 36%, the average is hiding the problem you need to plan for.

What does a measured shrinkage figure look like?

Here is an illustrative example for one full-time agent on a 40-hour week. The numbers are made up to show the method, so replace them with yours. Each share in the last column is the hours divided by 40 paid hours: 2.50 / 40 = 0.0625, which is 6.25%.

Where the time goes	Hours per week	Share of paid time
Paid breaks	2.50	6.25%
Training and coaching	2.00	5.00%
Team meetings	0.75	1.88%
Holiday	4.00	10.00%
Email and admin away from the phones	1.00	2.50%
Sickness	1.20	3.00%
Lateness and early leave	0.40	1.00%
Total	11.85	29.6%

The first five rows are planned: 2.50 + 2.00 + 0.75 + 4.00 + 1.00 = 10.25 hours, and 10.25 / 40 = 25.6% of paid time. The last two are unplanned: 1.20 + 0.40 = 1.6 hours, and 1.6 / 40 = 4.0%. Add the two and you get 11.85 hours, or 29.6%, which matches the total row. That split is the useful part. Planned shrinkage is yours to schedule, so you can put breaks and coaching into quiet half hours. Unplanned shrinkage is a bet on who walks in on Monday, and it clusters on Mondays and the day after a long weekend.

To build those categories in Excel, add the second sheet, called Log, with one line per absence or activity. Column A holds the agent, column B the date, column C the category (Holiday, Sickness, Coaching and so on) and column D the hours. Then =SUMIF(Log!C:C,"Holiday",Log!D:D)/Weekly!B33 gives the Holiday row’s share of paid time. SUMIF has three parts: look down column C of the Log sheet, pick the lines whose category says Holiday, and add up the matching hours in column D. If the team logged 110 holiday hours against 1,100 paid hours, 110 / 1,100 = 0.10, which is 10%. Do the same for each category, then check that the category shares add up to the total from the login report. If they don’t, you have found time the login report counts as unavailable that your categories do not name, which is the first thing to fix. Shrinkage is also a different animal from agent occupancy: shrinkage is time away from the queue, occupancy is how busy people are once they are on it, and you need both numbers before the rota makes sense.

How do you apply it without leaving the rota short?

Divide, never multiply. If the Erlang C sheet says you need 25 agents on the phones, the rostered number is 25 divided by (1 minus 0.296). Step by step: 1 – 0.296 = 0.704, and 25 / 0.704 = 35.5, so 36 people. The tempting shortcut is 25 times 1.296, which gives 32.4 and rounds to 33. That is three people short on every interval, and the error grows as shrinkage rises: at 35% the divide gives 25 / 0.65 = 38.5 and the multiply gives 25 x 1.35 = 33.8.

The reason is simple. Shrinkage is a share of the rostered hours, not a share of the hours you need. If 29.6% of a 36-person rota is gone, you have 36 x 0.704 = 25.3 left, which is what you wanted. Take 29.6% off a 33-person rota and you have 33 x 0.704 = 23.2.

Then plan the planned part by interval, not by day. A flat 6% for breaks assumes everyone is on break evenly, and they never are. If lunch breaks stack up at 12:30, that half hour is running at far more than 6% while 10:00 runs at none. Feed the planned portion into each half hour from the actual rota, and keep the 4% of unplanned absence as a day-level buffer. My piece on the inputs that move the Erlang C answer shows how much a small error upstream costs once it’s multiplied by thirty agents.

Should the figure change with the season?

Yes, and the annual average is the reason. Holiday doesn’t spread evenly: August and the last two weeks of December carry far more of it than February. Say your February weeks run at 24% and your August weeks at 36%, around the same 29.6% yearly figure. The same 25-agent requirement then needs 25 / 0.76 = 32.9, so 33 people on the February rota, and 25 / 0.64 = 39.1, so 40 people in August. Plan the whole year on 36 and you carry three people too many in winter and four too few in summer.

The fix is cheap. Keep one figure per month, or at least per quarter, from the same Weekly sheet, and use the one that matches the rota you are building. Where the data is thin, as it will be for a new team, use the same quarter’s figure from last year, not the all-year average.

Does workforce software make the spreadsheet redundant?

If you run a workforce management package, it will report a shrinkage figure for you, usually from schedule adherence data. Keep the spreadsheet anyway, at least for the first quarter, as an audit. The tools only count what they have been told to count, so if your off-phone project work or system downtime sits in a category the package ignores, its number will come out lower than yours. When the two disagree, the gap is a finding, and it nearly always points at a category nobody owns.

How does a shrinkage figure get misread?

The usual way is leaving things out. Email and admin time away from the phones, off-phone project work and system downtime often fall outside the “official” categories, so the figure comes out at 24% when the team really loses 29%. A manager who reports the lower number then wonders why every rota looks tight. Count anything that takes an agent away from the queue, whatever you call it.

The second trap is treating a measured number as a target to be lowered by cutting training or coaching. Squeezing the planned part gets you a better-looking figure and agents who can’t handle the next product change. Measure first, split it into planned and unplanned, and only then decide which of those lines is worth a conversation. The number you already have might be the most honest thing on your dashboard.