{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["README","Registration","Erlang C","Erlang X","Daily staffing","Including shifts"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"README","index":0,"n_rows":28,"n_cols":2,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["README"]
[]
["This Excel file illustrates the use of the CCmath Erlang module"]
["It shows how the most commonly used functions are used"]
["For a complete overview, please see the manual"]
["http://software.ccmath.com/erlang/manual/"]
["The latest version of this file can be obtained here"]
["http://software.ccmath.com/erlang/ErlangDemo.xlsm"]
["The corresponding add-in can be obtained here"]
["http://software.ccmath.com/erlang/Erlang.xla"]
[]
["This workbook requires macros to be enabled"]
["Registration is needed to use the functions, see the Registration tab"]
["The module will be fully functional for 1 week after registration"]
[]
["For pricing information of a license: contact CCmath at info@ccmath.com"]
[]
["A short description of what can be found in the different tabs is as follows:"]
[null,"Erlang C: the use of the different functions that are related to the Erlang C method"]
[null,"Erlang X: the use of the different functions that are related to the Erlang X method, which includes abandonments"]
[null,"Daily staffing: shows in a single sheet how to use daily profiles and a forecast to calculate staffing levels for a whole day"]
[null,"Including shifts: extends the previous sheet to the use of agent shifts"]
[]
["Color coding"]
[null,"Input"]
[null,"Output"]
[]
["Version 1.12, February 20, 2015, © CCmath"]

{"sheet":"Registration","index":1,"n_rows":10,"n_cols":1,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Registration"]
[]
["Please click the button below to register"]
[]
[]
[]
[]
["Registration is required only once"]
["If the message reads \"Registration successful!\" or \"Product is already registered!\" then the functions can be used"]
["Please contact CCmath if you get a different message"]

{"sheet":"Erlang C","index":2,"n_rows":19,"n_cols":6,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Erlang C"]
[]
["FC = forecast",4,"per minute",4,4]
["AHT = average handling time",3,"minutes",3,3]
["AWT = acceptable waiting time",{"$f":"20/60","v":0.33333333333333331},"minutes",{"$f":"20/60","v":0.33333333333333331},{"$f":"20/60","v":0.33333333333333331}]
["Number of agents",14,null,{"$f":"agentsServiceLevelErlangC(D10,D3,D4,D5)","v":15.2943},{"$f":"agentsWaitingTimeErlangC(E9,E3,E4)","v":14.9649},"(before rounding)"]
[null,null,null,{"$f":"ROUNDUP(D6,0)","v":16},{"$f":"ROUNDUP(E6,0)","v":15},"(rounded)"]
[]
["ASA = average speed of answer",{"$f":"waitingtimeErlangC(B3,B4,B6)","v":0.72255800000000003},"minutes",{"$f":"waitingtimeErlangC(D3,D4,D7)","v":0.15343000000000001},{"$f":"20/60","v":0.33333333333333331}]
["SL = service level = % answered within AWT",{"$f":"serviceLevelErlangC(B3,B4,B6,B5)","v":0.61428000000000005},null,0.8,{"$f":"serviceLevelErlangC(E3,E4,E7,E5)","v":0.77129000000000003}]
["Productivity",{"$f":"B3*B4/B6","v":0.8571428571428571},null,{"$f":"D3*D4/D7","v":0.75},{"$f":"E3*E4/E7","v":0.8}]
[]
["All durations have to be in the same unit (e.g., minutes), thus forecasts for 15 or 30-minute intervals have to be divided by 15 or 30"]
["Column B: calculation of ASA and SL"]
["Column D: calculation of number of agents on basis of SL"]
["Column E: calculation of number of agents on basis of ASA"]
["When calculating the number of agents the resulting number is not rounded to the next highest integer, because sometimes shrinkage has to be added before rounding"]
["Rounding is done in row 7"]
["The results in row 9-11 are based on the rounded number"]

{"sheet":"Erlang X","index":3,"n_rows":18,"n_cols":5,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Erlang X"]
[]
["FC",4,"per minute",4,4]
["AHT",3,"minutes",3,3]
["Average patience of callers",1,"minute",1,1]
["AWT",{"$f":"20/60","v":0.33333333333333331},"minutes",{"$f":"20/60","v":0.33333333333333331},{"$f":"20/60","v":0.33333333333333331}]
["Number of lines",100,null,100,100]
["Number of agents",14,null,{"$f":"ROUNDUP(agentsServiceLevelErlangX(D11,D3,D4,D6,D7,D5,0), 0)","v":13},{"$f":"ROUNDUP(agentsAbandonmentErlangX(E12,E3,E4,E7,E5,0), 0)","v":16}]
[]
["ASA",{"$f":"waitingtimeErlangX(B3,B4,B8,B7,B5,0)","v":0.100441},"minutes",{"$f":"waitingtimeErlangX(D3,D4,D8,D7,D5,0)","v":0.145871},{"$f":"waitingtimeErlangX(E3,E4,E8,E7,E5,0)","v":4.24876E-2}]
["SL",{"$f":"serviceLevelErlangX(B3,B4,B8,B6,B7,B5,0)","v":0.87976299999999996},null,0.8,{"$f":"serviceLevelErlangX(E3,E4,E8,E6,E7,E5,0)","v":0.94938900000000004}]
["Abandonments (patience is over)",{"$f":"abandonmentsErlangX(B3,B4,B8,B7,B5,0)","v":7.4325199999999994E-2},null,{"$f":"abandonmentsErlangX(D3,D4,D8,D7,D5,0)","v":0.105465},0.05]
["Blocked calls (all lines occupied)",{"$f":"blockingprobErlangX(B3,B4,B8,B7,B5,0)","v":1.36215E-87},null,{"$f":"blockingprobErlangX(D3,D4,D8,D7,D5,0)","v":1.93498E-88},{"$f":"blockingprobErlangX(E3,E4,E8,E7,E5,0)","v":6.0861399999999998E-86}]
["Productivity",{"$f":"occupancyErlangX(B3,B4,B8,B7,B5,0)","v":0.79343600000000003},null,{"$f":"occupancyErlangX(D3,D4,D8,D7,D5,0)","v":0.82572400000000001},{"$f":"occupancyErlangX(E3,E4,E8,E7,E5,0)","v":0.72548500000000005}]
[]
["Column B: calculation of ASA and SL"]
["Column D: calculation of number of agents on basis of SL"]
["Column E: calculation of number of agents on basis of abandonment percentage"]

{"sheet":"Daily staffing","index":4,"n_rows":66,"n_cols":20,"rows_kept":66,"cut":false,"hidden":false,"merged":[]}
["Daily staffing"]
["Example of staffing calculations for a 12-hour period using Erlang X with SL restriction"]
[]
["Forecast of total daily call volume",600]
["AHT",4]
["Patience",2]
["AWT",{"$f":"20/60","v":0.33333333333333331}]
["Lines",100]
["Shrinkage",0.2]
["SL",0.8]
[]
["Time Interval","Forecast","Staffing"]
["09:00 - 09:30",{"$f":"$B$4 * T41 / 30","v":0.78503780713214844},{"$f":"ROUNDUP(agentsServiceLevelErlangX($B$10,B13,$B$5,$B$7,$B$8,$B$6,0)/(1-$B$9),0)","v":6}]
["09:30 - 10:00",{"$f":"$B$4 * T42 / 30","v":0.90223889820728154},{"$f":"ROUNDUP(agentsServiceLevelErlangX($B$10,B14,$B$5,$B$7,$B$8,$B$6,0)/(1-$B$9),0)","v":7}]
["10:00 - 10:30",0.95149711185139807,7]
["10:30 - 11:00",0.93538926975765802,7]
["11:00 - 11:30",1.0452023937744492,8]
["11:30 - 12:00",1.0350688432124733,8]
["12:00 - 12:30",0.82313699484655656,6]
["12:30 - 13:00",0.79993425339439062,6]
["13:00 - 13:30",0.92683492945059398,7]
["13:30 - 14:00",1.0424431514253922,8]
["14:00 - 14:30",1.1539531626539821,8]
["14:30 - 15:00",0.98153373395875398,7]
["15:00 - 15:30",1.0549544571635388,8]
["15:30 - 16:00",0.98951919792492182,7]
["16:00 - 16:30",1.2662993083858241,9]
["16:30 - 17:00",1.0782476301896262,8]
["17:00 - 17:30",0.78719847809095223,6]
["17:30 - 18:00",0.6788807158233483,6]
["18:00 - 18:30",0.48673521867331115,5]
["18:30 - 19:00",0.52449568944436287,5]
["19:00 - 19:30",0.51716627735387755,5]
["19:30 - 20:00",0.53524749762054091,5]
["20:00 - 20:30",0.41228493361518476,4]
["20:30 - 21:00",0.28670004604943317,3]
[]
["Profile calculation on the basis of historic call volume"]
[]
[null,"Call volumes of eight weeks (same day)",null,null,null,null,null,null,null,null,"Fractions for each day",null,null,null,null,null,null,null,null,"Profile"]
["09:00 - 09:30",25,18,23,21,23,26,32,18,null,{"$f":"B41/B$66","v":5.030181086519115E-2},{"$f":"C41/C$66","v":3.5785288270377733E-2},{"$f":"D41/D$66","v":3.5825545171339561E-2},3.7102473498233215E-2,3.463855421686747E-2,4.4982698961937718E-2,5.0955414012738856E-2,2.4423337856173677E-2,null,{"$f":"AVERAGE(K41:R41)","v":3.9251890356607422E-2}]
["09:30 - 10:00",25,18,26,34,32,33,22,25,null,{"$f":"B42/B$66","v":5.030181086519115E-2},{"$f":"C42/C$66","v":3.5785288270377733E-2},4.0498442367601244E-2,6.0070671378091869E-2,4.8192771084337352E-2,5.7093425605536333E-2,3.5031847133757961E-2,3.3921302578018994E-2,null,{"$f":"AVERAGE(K42:R42)","v":4.5111944910364077E-2}]
["10:00 - 10:30",27,16,21,24,23,35,49,34,null,5.4325955734406441E-2,3.1809145129224649E-2,3.2710280373831772E-2,4.2402826855123678E-2,3.463855421686747E-2,6.0553633217993078E-2,7.8025477707006366E-2,4.6132971506105833E-2,null,4.7574855592569909E-2]
["10:30 - 11:00",30,29,32,17,25,27,33,29,null,6.0362173038229376E-2,5.7654075546719682E-2,4.9844236760124609E-2,3.0035335689045935E-2,3.7650602409638557E-2,4.6712802768166091E-2,5.2547770700636945E-2,3.9348710990502037E-2,null,4.6769463487882898E-2]
["11:00 - 11:30",40,33,25,32,38,19,27,32,null,8.0482897384305835E-2,6.560636182902585E-2,3.8940809968847349E-2,5.6537102473498232E-2,5.7228915662650599E-2,3.2871972318339097E-2,4.2993630573248405E-2,4.3419267299864311E-2,null,5.2260119688722462E-2]
["11:30 - 12:00",20,34,43,22,45,26,32,27,null,4.0241448692152917E-2,6.7594433399602388E-2,6.6978193146417439E-2,3.8869257950530034E-2,6.7771084337349394E-2,4.4982698961937718E-2,5.0955414012738856E-2,3.6635006784260515E-2,null,5.1753442160623657E-2]
["12:00 - 12:30",18,27,25,19,24,24,33,27,null,3.6217303822937627E-2,5.3677932405566599E-2,3.8940809968847349E-2,3.3568904593639579E-2,3.614457831325301E-2,4.1522491349480967E-2,5.2547770700636945E-2,3.6635006784260515E-2,null,4.1156849742327827E-2]
["12:30 - 13:00",19,14,28,21,25,33,22,32,null,3.8229376257545272E-2,2.7833001988071572E-2,4.3613707165109032E-2,3.7102473498233215E-2,3.7650602409638557E-2,5.7093425605536333E-2,3.5031847133757961E-2,4.3419267299864311E-2,null,3.9996712669719534E-2]
["13:00 - 13:30",15,28,35,33,34,21,19,40,null,3.0181086519114688E-2,5.5666003976143144E-2,5.4517133956386292E-2,5.8303886925795051E-2,5.1204819277108432E-2,3.6332179930795849E-2,3.0254777070063694E-2,5.4274084124830396E-2,null,4.6341746472529699E-2]
["13:30 - 14:00",37,27,21,28,31,28,30,47,null,7.4446680080482899E-2,5.3677932405566599E-2,3.2710280373831772E-2,4.9469964664310952E-2,4.6686746987951805E-2,4.8442906574394463E-2,4.7770700636942678E-2,6.3772048846675713E-2,null,5.212215757126961E-2]
["14:00 - 14:30",22,32,33,38,44,22,37,53,null,4.4265593561368208E-2,6.3618290258449298E-2,5.1401869158878503E-2,6.7137809187279157E-2,6.6265060240963861E-2,3.8062283737024222E-2,5.89171974522293E-2,7.1913161465400277E-2,null,5.7697658132699108E-2]
["14:30 - 15:00",23,21,34,35,30,20,35,40,null,4.6277665995975853E-2,4.1749502982107355E-2,5.2959501557632398E-2,6.1837455830388695E-2,4.5180722891566265E-2,3.4602076124567477E-2,5.5732484076433123E-2,5.4274084124830396E-2,null,4.9076686697937702E-2]
["15:00 - 15:30",22,28,30,41,35,24,35,39,null,4.4265593561368208E-2,5.5666003976143144E-2,4.6728971962616821E-2,7.2438162544169613E-2,5.2710843373493979E-2,4.1522491349480967E-2,5.5732484076433123E-2,5.2917232021709636E-2,null,5.2747722858176936E-2]
["15:30 - 16:00",21,26,36,29,27,33,25,42,null,4.2253521126760563E-2,5.168986083499006E-2,5.6074766355140186E-2,5.1236749116607777E-2,4.0662650602409638E-2,5.7093425605536333E-2,3.9808917197452227E-2,5.698778833107191E-2,null,4.9475959896246091E-2]
["16:00 - 16:30",36,24,41,39,43,44,35,42,null,7.2434607645875254E-2,4.7713717693836977E-2,6.3862928348909651E-2,6.8904593639575976E-2,6.4759036144578314E-2,7.6124567474048443E-2,5.5732484076433123E-2,5.698778833107191E-2,null,6.33149654192912E-2]
["16:30 - 17:00",29,22,42,33,39,24,32,40,null,5.8350100603621731E-2,4.37375745526839E-2,6.5420560747663545E-2,5.8303886925795051E-2,5.8734939759036146E-2,4.1522491349480967E-2,5.0955414012738856E-2,5.4274084124830396E-2,null,5.3912381509481314E-2]
["17:00 - 17:30",19,21,27,24,28,26,21,22,null,3.8229376257545272E-2,4.1749502982107355E-2,{"$f":"D57/D$66","v":4.2056074766355138E-2},{"$f":"E57/E$66","v":4.2402826855123678E-2},{"$f":"F57/F$66","v":4.2168674698795178E-2},{"$f":"G57/G$66","v":4.4982698961937718E-2},{"$f":"H57/H$66","v":3.3439490445859872E-2},{"$f":"I57/I$66","v":2.9850746268656716E-2},null,3.9359923904547615E-2]
["17:30 - 18:00",12,19,24,21,23,18,18,30,null,2.4144869215291749E-2,3.7773359840954271E-2,3.7383177570093455E-2,3.7102473498233215E-2,3.463855421686747E-2,3.1141868512110725E-2,2.8662420382165606E-2,4.0705563093622797E-2,null,3.3944035791167414E-2]
["18:00 - 18:30",8,15,16,7,19,15,17,22,null,1.6096579476861168E-2,2.982107355864811E-2,2.4922118380062305E-2,1.2367491166077738E-2,2.86144578313253E-2,2.5951557093425604E-2,2.7070063694267517E-2,2.9850746268656716E-2,null,2.4336760933665557E-2]
["18:30 - 19:00",10,14,21,6,18,21,15,23,null,2.0120724346076459E-2,2.7833001988071572E-2,3.2710280373831772E-2,1.0600706713780919E-2,2.710843373493976E-2,3.6332179930795849E-2,2.3885350318471339E-2,3.1207598371777476E-2,null,2.6224784472218144E-2]
["19:00 - 19:30",13,16,21,10,20,17,10,17,null,2.6156941649899398E-2,3.1809145129224649E-2,3.2710280373831772E-2,1.7667844522968199E-2,3.0120481927710843E-2,2.9411764705882353E-2,1.5923566878980892E-2,2.3066485753052916E-2,null,2.5858313867693879E-2]
["19:30 - 20:00",12,10,12,11,20,16,21,30,null,2.4144869215291749E-2,1.9880715705765408E-2,1.8691588785046728E-2,1.9434628975265017E-2,3.0120481927710843E-2,2.768166089965398E-2,3.3439490445859872E-2,4.0705563093622797E-2,null,2.6762374881027048E-2]
["20:00 - 20:30",10,8,19,11,8,11,17,16,null,2.0120724346076459E-2,1.5904572564612324E-2,2.9595015576323987E-2,1.9434628975265017E-2,1.2048192771084338E-2,1.9031141868512111E-2,2.7070063694267517E-2,2.1709633649932156E-2,null,2.0614246680759236E-2]
["20:30 - 21:00",4,3,7,10,10,15,11,10,null,8.0482897384305842E-3,5.9642147117296221E-3,1.0903426791277258E-2,1.7667844522968199E-2,1.5060240963855422E-2,2.5951557093425604E-2,1.751592356687898E-2,1.3568521031207599E-2,null,1.4335002302471658E-2]
[]
["Total volume",{"$f":"SUM(B41:B64)","v":497},{"$f":"SUM(C41:C64)","v":503},642,566,664,578,628,737]

{"sheet":"Including shifts","index":5,"n_rows":40,"n_cols":16,"rows_kept":40,"cut":false,"hidden":false,"merged":[]}
["Including shifts"]
["Example of staffing calculations for a 12-hour period using Erlang X with SL restriction"]
[]
["Forecast of total daily call volume",3000]
["AHT",3]
["Patience",2]
["AWT",{"$f":"20/60","v":0.33333333333333331}]
["Lines",200]
["Shrinkage",0.2]
["SL",0.8]
[null,null,null,null,"Shifts and numbers of agents per shift"]
["Time interval","Profile","Forecast",null,11,3,0,2,1,3,2,5,null,"Staffing","SL","Abandonments"]
["09:00 - 09:30",3.9251890356607422E-2,{"$f":"$B$4 * B13 / 30","v":3.9251890356607424},null,1,null,null,null,null,null,null,null,null,{"$f":"SUMPRODUCT($E$12:$L$12, E13:L13)","v":11},{"$f":"serviceLevelErlangX(C13,$B$5,N13*(1-$B$9),$B$7,$B$8,$B$6,0)","v":0.345696},{"$f":"abandonmentsErlangX(C13,$B$5,N13 * (1 - $B$9),$B$8,$B$6,0)","v":0.28992099999999998}]
["09:30 - 10:00",4.5111944910364077E-2,{"$f":"$B$4 * B14 / 30","v":4.5111944910364077},null,1,1,null,null,null,null,null,null,null,{"$f":"SUMPRODUCT($E$12:$L$12, E14:L14)","v":14},{"$f":"serviceLevelErlangX(C14,$B$5,N14*(1-$B$9),$B$7,$B$8,$B$6,0)","v":0.44884200000000002},{"$f":"abandonmentsErlangX(C14,$B$5,N14 * (1 - $B$9),$B$8,$B$6,0)","v":0.223493}]
["10:00 - 10:30",4.7574855592569909E-2,4.7574855592569909,null,1,1,1,null,null,null,null,null,null,{"$f":"SUMPRODUCT($E$12:$L$12, E15:L15)","v":14},0.38806299999999999,0.25279299999999999]
["10:30 - 11:00",4.6769463487882898E-2,4.6769463487882899,null,1,1,1,1,null,null,null,null,null,16,0.56859700000000002,0.165434]
["11:00 - 11:30",5.2260119688722462E-2,5.2260119688722462,null,1,1,1,1,1,null,null,null,null,17,0.50849,0.18884200000000001]
["11:30 - 12:00",5.1753442160623657E-2,5.1753442160623662,null,1,1,1,1,1,1,null,null,null,20,0.733039,9.6295800000000001E-2]
["12:00 - 12:30",4.1156849742327827E-2,4.1156849742327823,null,1,1,1,1,1,1,1,null,null,22,0.96236500000000003,1.3788699999999999E-2]
["12:30 - 13:00",3.9996712669719534E-2,3.9996712669719532,null,1,1,1,1,1,1,1,1,null,27,0.99779099999999998,9.0426000000000005E-4]
["13:00 - 13:30",4.6341746472529699E-2,4.6341746472529701,null,null,1,1,1,1,1,1,1,null,16,0.57964400000000005,0.16084399999999999]
["13:30 - 14:00",5.212215757126961E-2,5.2122157571269607,null,1,null,1,1,1,1,1,1,null,24,0.90812999999999999,3.2972300000000003E-2]
["14:00 - 14:30",5.7697658132699108E-2,5.7697658132699106,null,1,1,null,1,1,1,1,1,null,27,0.92973700000000004,2.5453699999999999E-2]
["14:30 - 15:00",4.9076686697937702E-2,4.9076686697937699,null,1,1,1,null,1,1,1,1,null,25,0.95900799999999997,1.5075E-2]
["15:00 - 15:30",5.2747722858176936E-2,5.2747722858176935,null,1,1,1,1,null,1,1,1,null,26,0.94989000000000001,1.8339399999999999E-2]
["15:30 - 16:00",4.9475959896246091E-2,4.9475959896246087,null,1,1,1,1,1,null,1,1,null,24,0.935581,2.3333199999999998E-2]
["16:00 - 16:30",6.33149654192912E-2,6.3314965419291207,null,1,1,1,1,1,1,null,1,null,25,0.78376100000000004,7.6152600000000001E-2]
["16:30 - 17:00",5.3912381509481314E-2,5.3912381509481309,null,1,1,1,1,1,1,1,null,null,22,0.80063099999999998,7.1103399999999997E-2]
["17:00 - 17:30",3.9359923904547615E-2,3.9359923904547616,null,1,1,1,1,1,1,1,1,null,27,0.99816099999999996,7.5725200000000003E-4]
["17:30 - 18:00",3.3944035791167414E-2,3.3944035791167413,null,null,1,1,1,1,1,1,1,null,16,0.87207299999999999,4.7066900000000002E-2]
["18:00 - 18:30",2.4336760933665557E-2,2.433676093366556,null,null,null,1,1,1,1,1,1,null,13,0.90869599999999995,3.4380399999999998E-2]
["18:30 - 19:00",2.6224784472218144E-2,2.6224784472218143,null,null,null,null,1,1,1,1,1,null,13,0.87268000000000001,4.8182000000000003E-2]
["19:00 - 19:30",2.5858313867693879E-2,2.5858313867693878,null,null,null,null,null,1,1,1,1,null,11,0.74774700000000005,0.100469]
["19:30 - 20:00",2.6762374881027048E-2,2.6762374881027049,null,null,null,null,null,null,1,1,1,null,10,0.62723399999999996,0.15476599999999999]
["20:00 - 20:30",2.0614246680759236E-2,2.0614246680759236,null,null,null,null,null,null,null,1,1,null,7,0.51723399999999997,0.226102]
["20:30 - 21:00",1.4335002302471658E-2,1.4335002302471658,null,null,null,null,null,null,null,null,1,null,5,0.51908500000000002,0.24177100000000001]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Total number of agent hours",{"$f":"SUM(N13:N36) / 2","v":216}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Overall SL",null,{"$f":"SUMPRODUCT(C13:C36,O13:O36) / SUM(C13:C36)","v":0.75509970864162734}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Overall abandonments",null,null,{"$f":"SUMPRODUCT(C13:C36,P13:P36) / SUM(C13:C36)","v":9.7434079709065868E-2}]