Cellwise Start free

Free Excel template · Excel for call centers

Free call center shrinkage calculator template

For workforce planners and team leads who need to know how much scheduled time never reaches the phones, and how many agents to schedule because of it. Enter the scheduled paid hours for a week or month and the hours lost to each category (breaks, meetings, coaching, training, time off, sickness, absence), each marked Planned or Unplanned. The template works out planned, unplanned and total shrinkage, checks it against your target, finds the biggest category, and turns your forecast of agents needed on the phones into the number to schedule.

cellwise-call-center-shrinkage-calculator.xlsxExcel workbook · 4.3 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup
Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

RowABCD
1Call center shrinkage calculator
2Enter scheduled hours and the hours lost to each category for the same period.
3Agents scheduled24
4Scheduled paid hours per agent40.0
5Total scheduled hours960.0
6Shrinkage target30%
7
8CategoryTypeHours lost% of scheduled
9BreaksPlanned60.06.25%
10Team meetingsPlanned18.01.88%
11Coaching 1:1sPlanned12.01.25%
12TrainingPlanned36.03.75%
13Paid time offPlanned32.03.33%
14Sick leaveUnplanned28.02.92%
15Unplanned absenceUnplanned16.01.67%
16Late arrival / early leaveUnplanned5.50.57%
17System downtimeUnplanned6.00.63%
18Unscheduled aux / otherUnplanned9.00.94%
19
20
21
22
23
24Planned shrinkage16.46%
25Unplanned shrinkage6.72%
26Total shrinkage23.18%
27StatusWithin target
28Productive hours left737.5
29Biggest single categoryBreaks
30
31Agents needed on the phones (your forecast)18
32Agents to schedule after shrinkage24

How the formulas work

Total shrinkage

B26=IF($B$5>0,SUM(C9:C22)/$B$5,0)

All hours lost divided by all scheduled paid hours for the same period. The sample team of 24 agents was scheduled for 960 hours and lost 222.5 of them: 23.18% shrinkage, within the 30% target in B6. The IF keeps the cell at 0 instead of a #DIV/0! error until scheduled hours are filled in.

Planned versus unplanned

B25=IF($B$5>0,SUMIF(B9:B22,"Unplanned",C9:C22)/$B$5,0)

SUMIF adds only the rows marked Unplanned. Planned shrinkage (breaks, meetings, training, booked time off) is known when the schedule is built; unplanned shrinkage (sickness, absence, lateness, outages) is the part you have to forecast from history. Here planned is 16.46% and unplanned 6.72%.

Biggest single category

B29=INDEX(A9:A22,MATCH(MAX(C9:C22),C9:C22,0))

MAX finds the most hours lost, MATCH finds its row and INDEX returns the category name. In the sample it is Breaks, 60 hours. That is normal: the biggest category is often planned and fixed. The useful question is which category grew since last period.

Agents to schedule after shrinkage

B32=IF(B26<1,ROUNDUP(B31/(1-B26),0),0)

If 23.18% of scheduled time is lost, only 76.82% of each scheduled agent is on the phones. Dividing the agents your forecast needs (B31) by that share, and rounding up, gives the number to schedule: 18 / 0.7682 = 23.4, so 24. Get B31 from your own forecast or staffing model. To track shrinkage by week and team and see why 18 x 1.2318 would understaff, read how to calculate contact center shrinkage in Excel.

Make it yours

FAQ

What is the formula for shrinkage in a call center?

Shrinkage % = hours lost (paid time not spent available for or handling contacts) divided by total scheduled paid hours, for the same period. In Excel that is =SUM(hours_lost)/scheduled_hours, formatted as a percentage.

What is the difference between planned and unplanned shrinkage?

Planned shrinkage is known in advance and built into the schedule: breaks, meetings, coaching, training and booked time off. Unplanned shrinkage happens on the day: sickness, unplanned absence, lateness and system outages.

How do I use shrinkage to work out how many agents to schedule?

Divide the number of agents you need on the phones by 1 minus the shrinkage rate and round up: =ROUNDUP(18/(1-0.2318),0) gives 24. The agents needed on the phones comes from your forecast or staffing model, not from this sheet.

Functions used

Related guides

More free templates

Updated