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.
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.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Call center shrinkage calculator | |||
| 2 | Enter scheduled hours and the hours lost to each category for the same period. | |||
| 3 | Agents scheduled | 24 | ||
| 4 | Scheduled paid hours per agent | 40.0 | ||
| 5 | Total scheduled hours | 960.0 | ||
| 6 | Shrinkage target | 30% | ||
| 7 | ||||
| 8 | Category | Type | Hours lost | % of scheduled |
| 9 | Breaks | Planned | 60.0 | 6.25% |
| 10 | Team meetings | Planned | 18.0 | 1.88% |
| 11 | Coaching 1:1s | Planned | 12.0 | 1.25% |
| 12 | Training | Planned | 36.0 | 3.75% |
| 13 | Paid time off | Planned | 32.0 | 3.33% |
| 14 | Sick leave | Unplanned | 28.0 | 2.92% |
| 15 | Unplanned absence | Unplanned | 16.0 | 1.67% |
| 16 | Late arrival / early leave | Unplanned | 5.5 | 0.57% |
| 17 | System downtime | Unplanned | 6.0 | 0.63% |
| 18 | Unscheduled aux / other | Unplanned | 9.0 | 0.94% |
| 19 | ||||
| 20 | ||||
| 21 | ||||
| 22 | ||||
| 23 | ||||
| 24 | Planned shrinkage | 16.46% | ||
| 25 | Unplanned shrinkage | 6.72% | ||
| 26 | Total shrinkage | 23.18% | ||
| 27 | Status | Within target | ||
| 28 | Productive hours left | 737.5 | ||
| 29 | Biggest single category | Breaks | ||
| 30 | ||||
| 31 | Agents needed on the phones (your forecast) | 18 | ||
| 32 | Agents to schedule after shrinkage | 24 |
How the formulas work
Total shrinkage
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
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
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
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
- Centers define shrinkage differently: some leave paid time off out because it is planned months ahead, some count only time away from the phones during scheduled shifts. Choose one definition, write it in A2, and keep it.
- Use the same period for scheduled hours and hours lost, for example the same week. Mixing a monthly schedule with a week of absence data gives a meaningless percentage.
- Hours lost can come from your workforce management or phone system as aux codes. Map each code to one category so nothing is counted twice.
- Track unplanned shrinkage by week. A rising trend shows up here before it shows up as missed service level.
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
IFPicks a result from a testSUMAdds numbersSUMIFAdds cells that matchINDEXValue at a positionMATCHPosition of a valueMAXLargest numberROUNDUPRounds away from zero
Related guides
- How to calculate absenteeism rate in Excel
- How to calculate schedule adherence in Excel
- How to calculate agent occupancy in Excel
- SUMIF vs SUMIFS: what is the difference?
- How to calculate percentage in Excel
More free templates
- Call center KPI dashboard templateFor team leads and analysts who want the six call center metrics everyone asks about on one sheet, built from a plain call log rather than a reporting tool.Free .xlsx
- Employee expense report template for ExcelFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order template for Excel (PO form with sample)A simple purchase order form for small businesses, office managers and buyers who send POs to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
Updated