Email and Chat FTE Calculation

julzjulz

New Member
Joined
Mar 13, 2021
Messages
1
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,
Hope everyone is fine. Just need some help here. I need to calculate required FTE for our Email and Chat support group. Can anyone share calculation and computation here are the requirements:


1. Email
Volume per week = 49k
Handling Time = 3 minutes
SLA #1= 90% volume ans in 12 hours
SLA #2 = 80% volume ans in 24 hours
Shrinkage = 21%, Occ 80%

2. Chat
Volume per week = 49k
Handling Time = 3 minutes
SLA #1= 80% volume answ in 5 minutes; concurrency:1
SLA #2= 80% volume answ in 5 minutes; concurrency:2.5
Shrinkage = 21%, Occ 80%

Thanks in advance!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Welcome to the Board! This type of problem is often examined using operations research methods. A common approach is described by Erlang. See this thread for some idea about its application to estimate staffing needs for call centers... Erlang C Calculation

In that thread, I include a reference to an online resource that describes many of the "terms of art" involved...which you've used in your post. The thread also includes a link to my Excel file that encodes the Erlang methodology via a series of formulas, and some improvements to that approach based on suggestions by @joeu2004. I've attempted to adapt that spreadsheet to your first scenario (email with SLA #1 parameters). You should be able to adapt it to examine all four scenarios you've described. Please note that the only inputs are blue-shaded cells, except for the helper cells I've added in D4:F15...those need to be tailored to ensure that your inputs are in the same units expected by the formulas. The units expected are shown in the input field descriptions in A4:A10. For example, 49,000 emails per week would be equivalent to 583 emails/hr IF you define a week as 7 days and a day as 12 hours. Your particular business schedule/work hours may dictate that different conversion factors are needed, so adjust the D4:F15 helper area as necessary to convert your requirements into the correct input units.

The revised version of the worksheet with the helper conversion section and a notional solution for your first scenario is available here:

Also, note that the objective is to use the initial guess for the number of staff needed (in B23) to create a series of guesses in C23:G23 (change these to ever increasing values)...so that conditional formatting in B31:G31, B42:G42, and B45:G45 turns cells green in each of those ranges. If a green cell is not shown in each of these ranges, a solution has not been found. The summary block at the bottom of the worksheet uses these ranges to find the smallest number of staff necessary to meet the requirements.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top