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]

Welcome to the MrExcel board!

Using something similar to the approach outlined here:

Erlang C Formula – Made Simple With an Easy Worked Example

We’ve simplified the Erlang C Formula into easy steps with a clear, worked example to help you understand and apply it effortlessly.

www.callcentrehelper.com

...do you have some estimates/statistics that would allow you to use the inputs described in that description, namely something like this?

• Number of calls – 100
• In a period of minutes – 30
• Average Handling Time (seconds) – 180 (3 minutes)
• Required Service Level 80%
• Target Answer Time (Seconds) 20
• Maximum Occupancy 85%
• Shrinkage 30%

If so, then I think a formula-based spreadsheet could work. What is the approximate size of the call center (i.e., number of agents)?

 

[KRice]

Here is spreadsheet version based on the method outlined in the reference above. You should be able to click on the clipboard icon in the upper left at the intersection of rows/columns to copy this and then paste it into cell A1 in your workbook. The terms of art are described at the URL above. Your inputs are shaded blue.

ErlangC.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Tool for Estimating Staffing for Call Center using Erlang C Method
2
3	1. Inputs:								Helper area
4	Number of calls	100							N =	11	12	13	14	15	16
5	In a period of minutes (min)	30							i	Σ(Ai/i!)	Σ(Ai/i!)	Σ(Ai/i!)	Σ(Ai/i!)	Σ(Ai/i!)	Σ(Ai/i!)
6	Average Handling Time (AHT) (s)	180							0	1	1	1	1	1	1
7	Required Service Level	80%							1	11	11	11	11	11	11
8	Target Answer Time (s)	20							2	61	61	61	61	61	61
9	Maximum Occupancy	85%							3	227.6667	227.6667	227.6667	227.6667	227.6667	227.6667
10	Shrinkage	30%							4	644.3333	644.3333	644.3333	644.3333	644.3333	644.3333
11									5	1477.667	1477.667	1477.667	1477.667	1477.667	1477.667
12	2. Determine number of calls per hour								6	2866.556	2866.556	2866.556	2866.556	2866.556	2866.556
13	n_avg (# calls/h)	200							7	4850.683	4850.683	4850.683	4850.683	4850.683	4850.683
14									8	7330.841	7330.841	7330.841	7330.841	7330.841	7330.841
15	3. Determine the Traffic Intensity (A)(hour basis)								9	10086.57	10086.57	10086.57	10086.57	10086.57	10086.57
16	# of calls in hour basis	200							10	12842.31	12842.31	12842.31	12842.31	12842.31	12842.31
17	Avg time per call (min)	3							11		15347.52	15347.52	15347.52	15347.52	15347.52
18	Call-minutes per hour	600							12			17435.19	17435.19	17435.19	17435.19
19	Traffic intensity (A) , call-hours per hour (Erlang unitless)	10							13				19041.1	19041.1	19041.1
20									14					20188.17	20188.17
21	4. Estimate the raw number of agents required (N)								15						20952.89
22	One agent max capability is 1 call-hour per hour actual time								16
23	Initial guess for N agents required	11	12	13	14	15	16		17
24	X term	27557.32	12526.05	6958.919	4014.761	2294.149	1274.527		18
25	Y term	12842.31	15347.52	17435.19	19041.1	20188.17	20952.89		19
26	Probability a call has to wait, Pw	68.21%	44.94%	28.53%	17.41%	10.20%	5.73%		20
27									21
28	5. Calculate the Service Level (list item #13 in ref)								22
29	Service Level	38.96%	64.02%	79.56%	88.84%	94.15%	97.06%		23
30	Target Service Level	80%	80%	80%	80%	80%	80%		24
31	Target Service Level Met?	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE		25
32									26
33	6. Average Speed of Answer (ASA) (list item #15 in ref)								27
34	Avg Speed of Answer (ASA) (seconds)	122.7813	40.44494	17.11623	7.835937	3.673525	1.72021		28
35									29
36	7. Percentage of Calls Answered Immediately (list item #16 in ref)
37	Immediate Answer	31.79%	55.06%	71.47%	82.59%	89.80%	94.27%
38
39	8. Check Maximum Occupancy (list item #17 in ref)
40	Occupancy	90.91%	83.33%	76.92%	71.43%	66.67%	62.50%
41	Max Occupancy	85%	85%	85%	85%	85%	85%
42	Max Occupancy constraint met?	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
43
44	9. Factor in Shrinkage (list item #18 in ref)
45	Number agents needed considering shrinkage of 30%	16	18	19	20	22	23
46
47	10. Summary
48	Number agents needed	20
49	Service Level	88.8%
50	Probability a call has to wait	17.4%
51	Average Speed of Answer (seconds)	7.8
52	% of calls Answered Immediately	82.6%
ErlangC

Cell Formulas
Range	Formula
J4:O4	J4	=B23
I6:I35	I6	=ROWS(I$6:I6)-1
J7:O35	J7	=IF($I7<=J$4-1,J6+($B$19^$I7)/FACT($I7),"")
B13	B13	=B4/(B5/60)
B16	B16	=B13
B17	B17	=B6/60
B18	B18	=B16*B17
B19	B19	=B18/60
B23	B23	=B19+1
B24:G24	B24	=B$23/FACT(B$23)/(B$23-$B$19) * $B$19^B$23
B25:G25	B25	=MAX(INDEX($J$6:$S$35,,MATCH(B23,$J$4:$S$4,0)))
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
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))

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

 

[joeu2004]

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?

Presumably the "umpteen spreadsheets" work. So I don't see any explanation of the problem that you are having.

Are you simply running into computational limits, for example (using the notation in the article that KRice cites): A^N exceeds about 1.8E+308; or N exceeds 171, exceeding the limit of FACT(170)?

Either might cause #NUM errors.

The root cause might be that you are simply trying to use the Excel implementation for a "raw number of agents required" (N) that exceeds the limits of computation.

If KRice's spreadsheet (#umpteen+1 ) does not help you, please explain your problem.

Here is spreadsheet version based on the method outlined in the reference above.

For the calculation of the "Y term" in row 25, you can eliminate the helper cells in columns I:O and replace the formulas in B25:G25 with the following formula in B25, then copy across:

=1 + SUMPRODUCT($B$19^ROW($A$1:INDEX($A:$A, B$23-1)) / FACT(ROW($A$1:INDEX($A:$A, B$23-1))))

This follows from the mathematical Sigma expression directly. The value 1 replaces the term A^0/FACT(0), which is 1/1 = 1.

Note: B23 ("N") must be 2 or more. The formula could be tweaked to allow for B23=1, if necessary, to wit:

=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)))))

 

[joeu2004]

Too late to edit....

If KRice's spreadsheet (#umpteen+1 ) does not help you, please explain your problem.

Include data similar to the input parameters described in the article that KRice cites.

 

[KRice]

@joeu2004 , thanks for the improvement to eliminate the helper cell area. I too was wondering whether computation limits were the issue, which is why I asked about the approximate size (number of agents). Some large number approximations may need to be incorporated, but I didn't want to go to that trouble unless it is necessary.

To recap the use of the spreadsheet, @grogmi1, read through the link I referenced and then adjust the blue cells in B4:B10. You may want to leave B9:B10 as is...according to the guidance, those are fairly typical numbers. But you'll need to make some estimates about the other cells based on your particular business and the nature of the support. Once those upper blue cells have been reviewed, jump down to the lower blue cells (B23:G23). The first is fixed based on an initial optimistic guess from the upper blue inputs. You'll want to change the numbers in C23:G23 until you see that other targets/constraints are met (conditional formatting turns those cells green in rows 31 and 42). Once you have green highlighting for a common number of agents (in this example, that occurs at N=14), the smallest value (in green in both rows) is further burdened by the shrinkage factor to account for staffing outages due to any number of reasons...and that is why N=14 (on row 23) becomes N=20 as the final estimate.

If you are dealing with significantly larger numbers, some some other adjustments will be necessary...so more insight about the issues you've encountered will be necessary to offer further guidance.

 

[kiki burgh]

hello kirk, me again. can this be downloaded (or do we just copy the cells over to a local sheet? thank you.

 

[KRice]

The easiest way is to click the clipboard icon in the upper left of the spreadsheet snippet (at the intersection of row and column headings) and then paste that into a fresh worksheet in the same cell as the upper left corner (in this case, A1). That should give complete functionality. Let me know if you run into any issues.

 

[KRice]

For convenience, here is the file with the improvement in the factorial formula offered by @joeu2004, so the helper columns aren't needed.

Dropbox

www.dropbox.com

 

[Clement]

Hello, seeking someone to help me on this.

I'm trying to understand the number of agents required per day with the volume of 4620 (in 12 hours) SL 80% within 20 secs, AHT at 308 secs, max occupancy set at 85%, and shrinkage at 18.9%.

Based on the online Erlang calculator from the contact center helper i'm getting 78.4 FTE per day (i've attached the image below for reference),

I've also used the Excel calculator from contact center helper, where i'm getting 49 agents.
Erlang - CCHelper.xlsm

i've also use the excel file that was provided by Kirk, where i'm getting 61 agents.
Erlang - Kirk.xlsx


I'm actually trying to understand the number of agent required per day with the above assumptions, and i've have to propose multiple simulation. Seeking assistant to all excel + wfm expert over here.


Thanks a lot in advance.