How to build a staffing model in a spreadsheet, and where it breaks

Every calculator on this site started life as a spreadsheet, and there are good reasons to have one of your own: it forces you to understand the chain, it is easy to show to finance, and it is free. This guide gives you the formulas for a working model, in the order you would build it, and is honest about the places where the spreadsheet stops being the right tool.

The layout

One row per interval. Columns, left to right: interval start, forecast contacts, handle time, erlangs, agents from Erlang C, agents after the occupancy ceiling, agents after shrinkage. Parameters (service level target, target time, occupancy ceiling, shrinkage, contracted hours) live in named cells at the top, never typed into formulas.

Step 1: erlangs

With contacts in column B, handle time in seconds in C, and the interval length in minutes in a named cell IntervalMin:

= B2 / IntervalMin * C2 / 60

That is the workload in erlangs: arrival rate per minute times handle time in minutes.

Step 2: Erlang C without the factorials

The textbook formula uses A^N ÷ N!, and a spreadsheet will overflow somewhere around 170 agents. Use the recurrence for Erlang B instead, which walks up from one agent and never forms a factorial. On a helper tab, put agent counts 1 to 2,000 down column A and, for a single erlang value in a named cell A:

B1: = A / (1 + A)
B2: = A * B1 / (A2 + A * B1)

and fill down. Column B is the Erlang B blocking probability for each agent count. Erlang C, the probability of waiting, is then:

C2: = B2 / (1 - (A / A2) * (1 - B2))

and service level for a target of T seconds against handle time AHT:

D2: = 1 - C2 * EXP(-(A2 - A) * T / AHT)

The required agents is the first row where column D reaches the target. MATCH(TRUE, D:D >= Target, 0) as an array formula does it, or INDEX/MATCH on a helper column of D >= Target.

The catch is that this helper tab is for one erlang value. For a full day of intervals you need one of these per interval, or a macro, or a LAMBDA in recent Excel. This is the first place the spreadsheet fights you: the calculation is easy, running it 48 times a day is not.

Step 3: occupancy ceiling

= MAX(E2, CEILING(D2 / MaxOcc, 1))

where E2 is the Erlang C result, D2 the erlangs and MaxOcc the ceiling as a fraction. This raises the agent count where Erlang C’s answer would leave agents too busy.

Step 4: shrinkage

= CEILING(F2 / (1 - Shrinkage), 1)

Round up, always. This column is the number to schedule for the interval.

Step 5: agent-hours and FTE

Sum the scheduled column, multiply by the interval length in hours, multiply by open days per week, divide by contracted hours per FTE:

= SUM(G:G) * IntervalMin / 60 * DaysPerWeek / HoursPerFTE

Because shrinkage is already in column G, divide by contracted hours, not productive hours. Dividing by productive hours counts shrinkage twice and is the single most common error in a spreadsheet staffing model.

Check it against the calculator

Before you trust the sheet, put one interval through the calculator below and compare. The reference case is 200 contacts in 30 minutes at a 300-second handle time and 80/20: the sheet should say 39 agents, 82.6 per cent service level, 85.5 per cent occupancy.

Inputs

Reset

Demand

Contacts in the interval

Calls, chats or tickets arriving during one interval.

Interval length15 minutes30 minutes60 minutes

Average handle time

s

Talk plus hold plus wrap-up.

Targets

Service level target

%

Share of contacts to answer within the target time.

Target answer time

s

Results

Copy results

Agents required

39

On the phones for the whole interval, before shrinkage.

Service level achieved

82.6%

Occupancy

85.5%

Average speed of answer

13.4 s

Contacts that will wait

25.4%

Workload

33.3 erlangs

Show the working

1. Workload = 200 contacts ÷ 30 min × 300 s ÷ 60 = 33.3 erlangs.
2. The queue is only stable with more agents than erlangs, so the search starts at 34.
3. With 39 agents, Erlang C gives a 25.4% chance that a new contact finds every agent busy.
4. Service level = 1 − 0.254 × e^(−(39 − 33.3) × 20 ÷ 300) = 82.6%, which clears the 80% target.
5. With 38 agents it would be 75.6%, which does not.
6. Occupancy = 33.3 ÷ 39 = 85.5%.

Doing this for every interval of the week? Pebble WFM computes the requirement from your forecast and builds the roster. Free month, no card needed.

Where the spreadsheet breaks

• Running Erlang for every interval. The helper-tab approach needs one tab per interval or code. Most planners end up with a fixed lookup table of erlangs to agents, which is only right for one handle time and one target.
• Shrinkage as one number. The model above applies the same shrinkage to every interval. In reality breaks and coaching cluster, and leave has seasons, so the sheet is systematically wrong in the same intervals every day.
• Multi-skill. Two queues sharing agents cannot be modelled with two independent Erlang columns; the requirement for each depends on the other. The spreadsheet answer is a fixed split, which is wrong whenever volumes move.
• From requirement to roster. The sheet produces a curve. Fitting shifts to it is an optimisation problem, and doing it by hand is where planners lose their week. The schedule efficiency calculator will tell you how well the result fits.
• Versions. A spreadsheet overwrites its history. When the roster and the forecast disagree, there is no record of which changed.

None of these mean the sheet is wrong. They mean it is doing a planner’s job at a planner’s speed. The week planner on this site does steps 1 to 5 for a day without the helper tabs; a workforce management tool does them for every day from the forecast, then builds the roster and keeps the versions.

Where next

• How to calculate Erlang C staffing: the maths behind step 2.
• When a spreadsheet stops being enough: how to tell, and what to move first.