Erlang C Calculation

[grogmi1]

I'm hoping someone on here can help me. I have read through forums, watched endless videos and downloaded umpteen Excel spreadsheets to try and achieve a calculation that will work out how many staff I need in my call centre. Is there anyone on here that can help me, Please? I am simply looking for an Excel formula or even an explanation on how to work this figure out. I'd prefer to stay away from writing VBA if I can (it's not my strongest skill) but if it will get the job done then I'm ready and willing.
Thanks

 

[KRice]

@Clement, I had a look at your question, but I can't replicate your answer. Did you use the ErlangC file I posted (link in post #9)? Here are my inputs...and the preliminary computations in sections 2 and 3 of the worksheet:

ErlangC_20240116.xlsx
	A	B
1	Tool for Estimating Staffing for Call Center using Erlang C Method
2
3	1. Inputs:
4	Number of calls	4620
5	In a period of minutes (min)	720
6	Average Handling Time (AHT) (s)	308
7	Required Service Level	80%
8	Target Answer Time (s)	20
9	Maximum Occupancy	85%
10	Shrinkage	19%
11
12	2. Determine number of calls per hour
13	n_avg (# calls/h)	385
14
15	3. Determine the Traffic Intensity (A)(hour basis)
16	# of calls in hour basis	385
17	Avg time per call (min)	5.133333333
18	Call-minutes per hour	1976.333333
19	Traffic intensity (A) , call-hours per hour (Erlang unitless)	32.93888889
ErlangC

Cell Formulas
Range	Formula
B13	B13	=B4/(B5/60)
B16	B16	=B13
B17	B17	=B6/60
B18	B18	=B16*B17
B19	B19	=B18/60

This version of the worksheet requires a little bit of user interaction once you follow the computation down to section 4 (row 23 where an initial guess is made for the "raw" number of agents required). This part of the worksheet is not entirely automated, as the initial guess in B23 is a reasonable starting point, but that number needs to be increased until the Target Service Level is Met (row 31). Ideally, you will see some raw number of agents where the criteria is not met, and an increase of one agent makes sufficient difference to cause the Target Service Level to be met. To see this, for this particular case, I overwrote the values on row 23 to explore this transition point (which occurs between 38 and 39). When I run your input numbers, the raw number of agents needed is 39.

ErlangC_20240116.xlsx
	A	B	C	D	E	F	G
21	4. Estimate the raw number of agents required (N)
22	One agent max capability is 1 call-hour per hour actual time
23	Initial guess for N agents required	34	38	39	40	41	42
24	X term	4.34168E+14	6.76016E+13	4.89301E+13	3.54731E+13	2.55874E+13	1.8288E+13
25	Y term	1.11127E+14	1.59484E+14	1.68487E+14	1.76092E+14	1.82354E+14	1.87384E+14
26	Probability a call has to wait, Pw	79.62%	29.77%	22.51%	16.77%	12.31%	8.89%
27
28	5. Calculate the Service Level (list item #13 in ref)
29	Service Level	25.68%	78.57%	84.82%	89.40%	92.71%	95.06%
30	Target Service Level	80%	80%	80%	80%	80%	80%
31	Target Service Level Met?	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE
ErlangC

Cell Formulas
Range	Formula
B23	B23	=ROUNDUP(B19,0)+1
B24:G24	B24	=B$23/FACT(B$23)/(B$23-$B$19) * $B$19^B$23
B25:G25	B25	=IF(B$23<2, 1, 1 + SUMPRODUCT($B$19^ROW($A$1:INDEX($A:$A, B$23-1)) / FACT(ROW($A$1:INDEX($A:$A, B$23-1)))))
B26:G26	B26	=B24/(B24+B25)
B29:G29	B29	=1-B26*EXP(-(B23-$B$19)*($B$8/$B$6))
B30:G30	B30	=$B$7
B31:G31	B31	=B29>=B30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B31:G31	Cell Value	=TRUE	text	NO

This raw number of agents is further adjusted to account for shrinkage in the lower section, leading to 49 for the final estimated number of agents.

ErlangC_20240116.xlsx
	A	B	C	D	E	F	G
33	6. Average Speed of Answer (ASA) (list item #15 in ref)
34	Avg Speed of Answer (ASA) (seconds)	231.1087137	18.11644821	11.43616207	7.313645176	4.70156253	3.022445337
35
36	7. Percentage of Calls Answered Immediately (list item #16 in ref)
37	Immediate Answer	20.38%	70.23%	77.49%	83.23%	87.69%	91.11%
38
39	8. Check Maximum Occupancy (list item #17 in ref)
40	Occupancy	96.88%	86.68%	84.46%	82.35%	80.34%	78.43%
41	Max Occupancy	85%	85%	85%	85%	85%	85%
42	Max Occupancy constraint met?	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE
43
44	9. Factor in Shrinkage (list item #18 in ref)
45	Number agents needed considering shrinkage of 19%	42	47	49	50	51	52
46
47	10. Summary
48	Number agents needed (minimum of #9 where #5 and #8 are satisfied)	49
49	Service Level	84.8%
50	Probability a call has to wait	22.5%
51	Average Speed of Answer (seconds)	11.4
52	% of calls Answered Immediately	77.5%
ErlangC

Cell Formulas
Range	Formula
B34:G34	B34	=B26*$B$6/(B23-$B$19)
B37:G37	B37	=1-B26
B40:G40	B40	=$B$19/B23
B41:G41	B41	=$B$9
B42:G42	B42	=B40<B41
A45	A45	="Number agents needed considering shrinkage of "& TEXT($B$10,"0%")
B45:G45	B45	=ROUNDUP(B23/(1-$B$10),0)
B48	B48	=AGGREGATE(15,6,(B45:G45)/((B31:G31)*(B42:G42)),1)
B49	B49	=INDEX($B$29:$G$29,MATCH(B$48,$B$45:$G$45,0))
B50	B50	=INDEX($B$26:$G$26,MATCH(B$48,$B$45:$G$45,0))
B51	B51	=INDEX($B$34:$G$34,MATCH(B$48,$B$45:$G$45,0))
B52	B52	=INDEX($B$37:$G$37,MATCH(B$48,$B$45:$G$45,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B42:G42	Cell Value	="TRUE"	text	NO
B45:G45	Expression	=AND(B31,B42)	text	NO

This result agrees with what you determined using the online tool. Regarding the online tool, I am not sure what is involved in their determination of of 78.4 FTE per day. Initially, I suspected they calculated the same 39 raw number of agents mentioned above...noted that this was for a 12 hour period, and then doubled that number to arrive at 78 for a 24 hour period, as though you might run two 12-hour shifts to cover an entire 24-hour period. But a more careful reading of their summary suggests they are utilizing other information ("...based on our analysis of multiple contact centres") to develop a "...typical distribution of calls per day", and this leads them to develop an estimate to handle the largest surges in call volume. The ErlangC computations in the worksheet tool rely on several averages, and perhaps the other online tool applies a different model to estimate peak demand for call services.

 

[Clement]

Hi @KRice ,

Thanks a lot for your swift response, appreciate it. I managed to replicate the results based on your input.

Just have a quick thing I need your insight on. The number of agent required is based on the number of call per hour which in this case it's 385 (Cell B16). Could you help me understand how to calculate the total number of agents needed for the entire day? Thanks a bunch!




Cheers,
Clement

 

[KRice]

@Clement, good to hear that you obtained the same results. A quick disclaimer: I am not a workforce management expert, so I may not possess the insights or expertise needed, but I was curious about the history, development, and mathematics of the Erlang equations. You're correct...ErlangC relies on averages, such as average call volume rate. The number of agents determined by the equation would generally be considered a reasonable starting point when the assumed conditions actually apply, but be careful about extending that conclusion further. In other words, if you had a call center that operates for 16 hours over a 24 hour period, and the 16 hours of active staffing is done in two 8-hour shifts, you would probably want to study (if you have data), or estimate (based on data from similar types of operations or expert knowledge) the appropriate input values for each shift. Perhaps those two shifts historically show that one has a heavier call volume, and the calls are more complicated, than the other. So each shift could, and probably should, be assessed separately. In such a case, you might find that the equation estimates 49 agents for 1st shift, 34 for 2nd shift, and 0 for the 8-hour closed period. I think is why the online calculator you examined had a note about creating a schedule for more detail. But I would recommend evaluating your operation in terms of call demands throughout the workday, determine if there are general blocks of time where the demands differ significantly, and then use some method (Erlang is one option) for estimating staffing needed to handle those call volume rates. For another example, suppose the call center operates around the clock and the call volume and complexity of calls is fairly constant, and under these conditions, you would need 10 agents. In terms of scheduling staff for the call center, you would need 10 agents on duty at any one time. Taking this further, if your workday consisted of two 12-hour shifts, then you would need 20 agents for the day (but only 10 per shift); and if your workday consisted of three 8-hour shifts, then you would need 30 agents for the day (but only 10 per shift); and if your workday consisted of four 6-hour shifts, you would need 40 agents for the day (but again, only 10 per shift). Does this help to clarify how the Erlang estimation can be used?

 

[not_a_swim_fan28]

@KRice are you able to give insight as to how use this for an office team that handles work in a similar fashion to a call center team KPI/parameter wise? Despite work items being resolved in days, I'd like to incorporate team members being able to take on multiple requests and what that looks like as opposed to the Erlang calculator telling me to staff 100+ individuals.

 

[KRice]

I haven't tried applying this to an office team environment, although it could probably be done. Recognize that the original development of the Erlang equations were undertaken in the early days of telephony...so their underlying assumptions consider that we have a switchboard into which all calls arrive at some rate, and those handling the calls require a certain amount of time to address the callers while other callers wait (and some drop off if they are tired of waiting). And of course, the workers also require some "downtime", so there is some finite capacity for accomplishing work. If you can "translate" your office workflow and key performance indicators (KPIs) into this type of model, then the equations may very well be adaptable to your situation. Be aware of the units for the input variables. Depending on what your workflow and KPIs are, you may need to redefine a "day" or otherwise normalize the input variables to some timescale that is both consistent with your conditions and the equations.

 

[not_a_swim_fan28]

I haven't tried applying this to an office team environment, although it could probably be done. Recognize that the original development of the Erlang equations were undertaken in the early days of telephony...so their underlying assumptions consider that we have a switchboard into which all calls arrive at some rate, and those handling the calls require a certain amount of time to address the callers while other callers wait (and some drop off if they are tired of waiting). And of course, the workers also require some "downtime", so there is some finite capacity for accomplishing work. If you can "translate" your office workflow and key performance indicators (KPIs) into this type of model, then the equations may very well be adaptable to your situation. Be aware of the units for the input variables. Depending on what your workflow and KPIs are, you may need to redefine a "day" or otherwise normalize the input variables to some timescale that is both consistent with your conditions and the equations.

So from a standard cell center type environment, it outputs what looks to be the correct number of FTEs but I was curious to see if you have any thoughts around how to manipulate the parameter values to be inclusive of distributing a certain number of requests into the model and if so, what part of the model to fit that into.

 

[KRice]

...how to manipulate the parameter values to be inclusive of distributing a certain number of requests into the model...

Could you elaborate on this further? I don't understand what you mean.

 

[not_a_swim_fan28]

See the screenshot below. When I use the Erlang table you put together (which is great btw), it errors out based on the values listed below. Because of the time durations, it looks like I would need to further expand the number of FTEs that would normally be applicable though that will most likely give me an FTE required number of 100+. I wanted to see if it was possible to add a goal included where I use the table to provide me the number of FTEs needed if the total number of inquiries forecasted this month is 804 but wanting each FTE to handle for example 50. That would initially indicate a team of 16 not inclusive of AHT/SLA/SHRINK etc.

 

[KRice]

What is the best way to describe this operation's working period? It appears that 1 month consists of 20 working days. Does one work day (the time during which work is actually accomplished) consist of 8 h or 24 h?

In order to fulfill a single inquiry, does that actually require full time attention from a single person for three full workdays on average...or is the actual "hands on" time something less? Similarly, does it require two full workdays to determine whom shall be tasked with the job to answer an inquiry?

As a notional exercise, I used these inputs:

ErlangC_20240220_20240801.xlsx
	A	B	C	D	E	F
4	Number of inquiries	804
5	In a period of minutes (min)	9600		1	mo, redefine "month" as 20 d	9600
6	Average Handling Time (AHT) (s)	86400		3	days, redefine day as 8 h, convert to s...so	86400
7	Required Service Level	95%
8	Target Answer Time (s)	57600		2	days, convert to s…so	57600
9	Maximum Occupancy	85%
10	Shrinkage	30.0%
ErlangC_not_a_swim_fan2

Cell Formulas
Range	Formula
F5	F5	=20*8*60
F6,F8	F6	=D6*8*60*60

...and obtained the following results (note that I've excluded/hidden several rows to reduce the size of this table):

ErlangC_20240220_20240801.xlsx
	A	B	C	D	E	F	G
21	4. Estimate the raw number of agents required (N)
22	One agent max capability is 1 call-hour per hour actual time
23	Initial guess for N agents required	123	125	127	129	131	142
27
28	5. Calculate the Service Level (list item #13 in ref)
29	Service Level	84.70%	96.85%	99.36%	99.87%	99.97%	100.00%
30	Target Service Level	95%	95%	95%	95%	95%	95%
31	Target Service Level Met?	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
32
39	8. Check Maximum Occupancy (list item #17 in ref)
40	Occupancy	98.05%	96.48%	94.96%	93.49%	92.06%	84.93%
41	Max Occupancy	85%	85%	85%	85%	85%	85%
42	Max Occupancy constraint met?	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE
43
44	9. Factor in Shrinkage (list item #18 in ref)
45	Number agents needed considering shrinkage of 30%	176	179	182	185	188	203
ErlangC_not_a_swim_fan2

Cell Formulas
Range	Formula
B23	B23	=ROUNDUP(B19,0)+2
C23:F23	C23	=B23+2
G23	G23	=F23+11
B29:G29	B29	=1-B26*EXP(-(B23-$B$19)*($B$8/$B$6))
B30:G30	B30	=$B$7
B31:G31	B31	=B29>=B30
B40:G40	B40	=$B$19/B23
B41:G41	B41	=$B$9
B42:G42	B42	=B40<B41
A45	A45	="Number agents needed considering shrinkage of "& TEXT($B$10,"0%")
B45:G45	B45	=ROUNDUP(B23/(1-$B$10),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B31:G31	Cell Value	=TRUE	text	NO
B42:G42	Cell Value	=TRUE	text	NO
B45:G45	Expression	=AND(B31,B42)	text	NO

The results suggest that 125 staff are needed to satisfy the workload requirement; however, in order to also satisfy the occupancy goal (so that staff are not completely overwhelmed), the staff size needs to be 142...and then to further burden the staffing requirement with shrinkage, those same baseline values increase to 179 and 203.

And if the Target Answer Time were reduced to one day (within one day of an inquiry being made, it will have been assigned to someone for response), and the staff member's Average Handling Time were reduced to 1.5 days, then the results become 64 and 71 (staff to handle basic workload, but overwhelmed, and staff needed to achieve occupancy), which staff sizes are further burdened to satisfy shrinkage, yielding 92 and 102...so 102 for all conditions.

 

[not_a_swim_fan28]

What is the best way to describe this operation's working period? It appears that 1 month consists of 20 working days. Does one work day (the time during which work is actually accomplished) consist of 8 h or 24 h?

In order to fulfill a single inquiry, does that actually require full time attention from a single person for three full workdays on average...or is the actual "hands on" time something less? Similarly, does it require two full workdays to determine whom shall be tasked with the job to answer an inquiry?

The results suggest that 125 staff are needed to satisfy the workload requirement; however, in order to also satisfy the occupancy goal (so that staff are not completely overwhelmed), the staff size needs to be 142...and then to further burden the staffing requirement with shrinkage, those same baseline values increase to 179 and 203.

And if the Target Answer Time were reduced to one day (within one day of an inquiry being made, it will have been assigned to someone for response), and the staff member's Average Handling Time were reduced to 1.5 days, then the results become 64 and 71 (staff to handle basic workload, but overwhelmed, and staff needed to achieve occupancy), which staff sizes are further burdened to satisfy shrinkage, yielding 92 and 102...so 102 for all conditions.

Please see my answers to your questions below. Using May's numbers as a baseline, a count of 22 FTEs on average each closed 28 requests with a cycle time of 11 days. The impacting factor was the wait time for routed requests, where from a hands to keys perspective they would still be individually taking 3 days to complete the work after assigning within <=2 days. The ability to close the 28 requests is what I would like to somehow factor in to the WFM table to give me a true number of FTE's to confirm whether or not staffing should be 22 or another number.


What is the best way to describe this operation's working period? It appears that 1 month consists of 20 working days. Does one work day (the time during which work is actually accomplished) consist of 8 h or 24 h?

• The operation can take 3 days to complete after initially being assigned which historically on average has been 2 days.

In order to fulfill a single inquiry, does that actually require full time attention from a single person for three full workdays on average...or is the actual "hands on" time something less? Similarly, does it require two full workdays to determine whom shall be tasked with the job to answer an inquiry?

• Depending on the numerous issues needed to be worked by the FTE it can. Because FTE's are usually overlapping in work items moving from one to the next depending on system results/responses received/running calculations/researching, on average it takes them 2 days to pick up a new inquiry while closing 1 out in 3.