calculate ratio


i'm pretty smart in figuring out how to use excel but i'm having difficulties in trying to figure this out..

I complied a data set to determine how many employees i need per day based on the number of customer serviced per hour for each day.  (see attachment).

my next issue is that i'm trying to determine how many employees can service a customer. For example if on 10/1 i serviced 4 customers between 7-8a and 5 between 8-9a for two hours i had 9 customers.  I can make assumptions that i need 1 employee between those hours. But can't assume if i had 18 customers within that same period that i only need one employee.

so i guess i'm looking to figure a ratio in excel.. maybe?




Ratios should be easily calcul;ated but I'm not sure a simple calculation would sort your proble. Evn if a simple approach could work, won't your "required employees" calculation need to take account of:

1) the duration of the service? (Does it vary a lot; easier if not!)
2) how long customers are prepared to wait at busy times? (So if it's a 10 minute task, will they really wait 50 minutes before being dealt with?)
3) if there's a leadtime before the second employee can start (or minimum time employed before they depart when not needed)?
4) cost of adding another employee versus revenue you'd lose if you don't?

John_R (rep: 9) Oct 13, '16 at 4:25 am
John_R has it right! Excel is going to be the easiest part of this process and won't require much work. The important information to figure out has to do with the business logic. I will post what I think might help you with this as an answer.
don (rep: 1576) Oct 13, '16 at 4:33 pm
Add to Discussion



One thing that might help you better understand employee utilization is to use the Erlang-C formula. This is for call center staffing, but it looks like it just might apply to your situation.

This web page has a great explanation and a sample file for using the Erlang-C formula in Excel and I have used this particular page to help out in projects that I have worked-on in the past. (I know you are supposed to summarize links here but that page can't really be summarized here without copy/pasting it. If the link ever breaks, just search for "Erlang C Formula" and you will find other similar resources.)

In addition, looking at your spreadsheet, I would be most interested in finding peak customer times in order to figure out how many people I need to service them given an acceptable, definition depends on your business, wait time.

As I mentioned in my above comment, John_R's comment has very good points that you should consider when making these calculations.


Answer the Question

You must create an account to use the forum. Create an Account or Login