Spreadsheet: 854483d1704342949-how-to-calculate-number-of-headcount-needed-using-erlang-formula-in-call-center-erlang-formula-spreadsheet
Sheet: Erlang C Formula explained

 | Erlang [NAME] Example | © Call Centre Helper
1 | Inputs
 | Number of calls | 100
 | In a period of minutes | 30
 | Average Handling Time (seconds) | 180
 | Required Service Level | 0.8
 | Target Answer Time  (Seconds) | 20
 | Maximum Occupancy | 0.85
 | Shrinkage | 0.3
2 | Calls per hour
3 | Traffic Intensity (Erlangs) A
4 | Estimate the Raw Number of Agents N |  | Start with the number of Erlangs + 1
5 | Calculate the Erlang Formula for Probability a Call Waits
6 | Lets start by working out N!
 | So let's break down the formula into parts
 | N! is mathematical notation for N factorial
 | A factorial is the  product of an integer (whole number) and all the integers (whole numbers) below it
 | For example 3! = 3 Factorial = 3 x 2 X 1 = 6
 | 11! = 11 Factorial = 11 x 10 x 9 x 8 x 7 x 6 x 5 x 4 x 3 x 2 x 1 =  39916800
 | Luckily Excel has a formula that can do this for you
 | So for 11 Factorial  it is =FACT(11)
 | N! = 11 Factorial = FACT (11)
7 | Be careful with large factorials
 | Note that factorials get very large as you increase the number of agents and very soon can cause Excel to overload.
 | 10 Factorial
 | 100 Factorial
 | 170 Factorial
 | 171 Factorial |  | This will cause errors in your calculations
 | So it won't easy work in Excel above 170 Agents
 | Luckily we have devised a Fast Erlang formula  for high number so agents
 | https://www.callcentrehelper.com/tools/erlang-calculator/
8 | Work out the Powers - AN
 | So AN means A multiplied by A, N times
 | e.g. 2 to the Power of 2 = 22 = 2 x 2 = 4
 | So if A = 10 and N = 11 then AN = 1011 = 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10
 | Luckily Excel has a formula for this = POWER (10,11)
 | AN = 1011 = POWER (10,11)
9 | Let's simplify the Erlang Formula
 | We can use a substitution
 | X =
 | and
 | Y =
 | So Pw = X / ( Y + X)
10 | Work out the top row of the Erlang C Formula (X)
 | Top Row = AN / N! * (N / (N-A))
 | Top Row = 1011 / 11! * (11 / (11-10))
11 | Work out the Sum of a series (Y)
 | Now this looks scary but is simply adding together a sum of numbers looping  through a number of times
 | In this case you start with i=0 and keep going through until i= N-1
 | so if N = 11 , N - 1 = 10 and we loop through from i= 0 until i = 10
 | and Σ is the mathematical symbol for the sum of a series so you add together all of the results of the individual loops
 |  | i | i! | Ai | Ai/i! | Σ Ai/i!
 |  | 0
 |  | 1
 |  | 2
 |  | 3
 |  | 4
 |  | 5
 |  | 6
 |  | 7
 |  | 8
 |  | 9
 |  | 10
 | Which gives the sum of the series Y =
 | Now note that the maths becomes simplified
12 | Put X and Y into the Erlang C Formula (The probability a call has to wait)
 | So Pw = X / ( Y + X)
 | Pw = 27557 / (12842 + 27557)
 | Or in percentage terms
13 | Calculate Service Level
 | Service Level Follows the Formula
 | SL= 1 - (Pw *e -(N - A) * (TargetTime / AHT))
 | Where e is the mathematical constant ([NAME]'s number) = 2.71828
 | Luckily Excel has an Equation for this =EXP()
 | Lets work out -(N - A) * (TargetTime / AHT)
 | Lets work out -(11 - 10) * (20 /180)
 | Then work out Service Level
 | SL =  1- (0.6821 * EXP(-0.11111))
 | Service Level as a Percentage
 | This is below the target of 80% so we need to increase the number of agents
14 | Increase the Number of Agents by 1 to see If Service Level is achieved
14.1 | Number of Agents N | 12
 | Traffic Intensity A | 10
 | Work out X
 | i | i! | Ai | Ai/i! | Σ Ai/i!
 | 0
 | 1
 | 2
 | 3
 | 4
 | 5
 | 6
 | 7
 | 8
 | 9
 | 10
 | 11
 | Work out Y
 | Probability Call Waits
 | Service Level
 | Service Level as a Percentage
 | This is below the target of 80% so we need to increase the number of agents
14.2 | Increase the Number of Agents by 1 to see If Service Level is achieved
 | Number of Agents N | 13
 | Traffic Intensity A | 10
 | Work out X
 | i | i! | Ai | Ai/i! | Σ Ai/i!
 | 0
 | 1
 | 2
 | 3
 | 4
 | 5
 | 6
 | 7
 | 8
 | 9
 | 10
 | 11
 | 12
 | So value of Y
 | Probability Call Waits
 | Service Level
 | Service Level as a Percentage
 | This is below the target of 80% so we need to increase the number of agents
14.3 | Increase the Number of Agents by 1 to see If Service Level is achieved
 | Number of Agents N | 14
 | Traffic Intensity A | 10
 | Work out X
 | i | i! | Ai | Ai/i! | Σ Ai/i!
 | 0
 | 1
 | 2
 | 3
 | 4
 | 5
 | 6
 | 7
 | 8
 | 9
 | 10
 | 11
 | 12
 | 13
 | So value of Y
 | Probability Call Waits
 | Service Level
 | Service Level as a Percentage
 | This is above the target of 80%
 | So the Raw Number of Agents | 14
15 | Average Speed of Answer
 | ASA = Pw x AHT / (N-A)
 | ASA = 0.1741 x 180 / (14 - 10) |  | seconds
16 | Percentage of Calls Answered Immediately
 | Immediate Answer = (1- PW)
 | Immediate Answer = (1- 0.1741)
17 | Check Maximum Occupancy
 | Raw Agents | 14
 | Traffic Intensity A | 10
 | Occupancy = Traffic Intensity (Erlangs) / Raw Agents
 | This is less than 85% so we can keep it at this.
 | If this is more than 85% the number of Raw Agents is Traffic Intensity/ (Occupancy %/100)
 | If you have an occupancy of more than 85% your agents will likely get burnt out and you will find that Average Handling Time may rise to cover it.
18 | Factor in shrinkage
 | Shrinkage is a factor that is widely used in the industry to include holidays, sickness, training and meetings etc.  The industry average is around 30 - 35%
 | Shrinkage
 | Number of Agents Required = Raw Agents/ (1- (Shrinkage% /100))
 | Number of Agents Required = 14/ 0.7
19 | Summary
 | So the total number of Agents Required | 20
 | Service Level
 | Probability a call has to wait
 | Average Speed of Answer |  | Seconds
 | % of calls [NAME]
20 | There is an Easier (and Faster) Way to do an Erlang C Calculation
 | There is a much easier (and faster) way to use the Erlang C Formula.
 | We have developed two Erlang Calculators
 | Erlang calculator (Online Version)
 | Excel Erlang Calculator (Handy spreadsheet format)
© Call Centre Helper 2017
https://www.callcentrehelper.com