Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Determining how many employees I need per shift per hour

0

Problem:

I own a small business which is based on the number of customer i service on a daily basis. My issue is that my payroll is out of control and i need to bring it back inline.  What I want to do is determine how many employees i need per shift based on the number of customers i service between a certain time.

I don't know if I should create mutliply worksheets for each week or should i create worksheets for each day that shows how many customers i service and avg the services

based on the how many customers i service i want to be able to know how many employees i need for that particular day and  have excel (if possible) show me this is how many employees you need for this shift and a shift could be 6:30am - 9am... maybe if i can determine how many employees i need per hour i can figure out the shifts later.

any suggestions  on how to solve this would be great

excel
MS Office Version: 2013
posted by: davane (rep: 2) on Mon Oct 10, 2016 at 11:00 pm
Answer
Discuss

Answers

0
Selected Answer

There are many ways to do this and probably there are some templates floating around the internet in regards to this.

But, what I would do first is to input when a customer is serviced or comes in or purchases something. The important thing to input is the time that the were served and how much the purchased or how much of your service they used.

The format for this would be like a flat database table, which is what Excel is, basically. In the first column put "Customer 1" or whatever you use to ID them and in the columns to the right, put all relevant information, how much they purchased, when they came in, how long they stayed, etc.

This list of data should be on its own separate worksheet.

Once you have that data, then you can start to run diagnostics against it. Filtering the data would be my first step probably; then, I might use a PivotTable; or I would just input some formulas to display the specific data that I need.

The first step is to get the customer data into Excel!

posted by: don (rep: 1989) on Tue Oct 11, 2016 at 5:54 pm
Discuss

Discussion

Don

thanks that is what i thought about doing which is pretty simple but the hard part is how to figure out how many employees i need for a specific shift based on the number of customers i serviced that is formula i'm trying to figure out
davane (rep: 2) Oct 11, '16 at 9:32 pm
So, do this in a step-by-step manner. Compile the data first, then make a sample spreadsheet that shows similar data, then post that sample and ask a new question about it. Once the first step is completed, it will be easier to see which way you need to go to get the desired results.
don (rep: 1989) Oct 12, '16 at 1:00 pm
You can add a ratio formula that will show you the number of customers to employees, thereby, allowing you to make the adjustments for your employees to customer service according to the amount of time that is spent with the customer. Piggybacking off of Don, this will help with the flat database and adjusting the cost for employees. You can look at an array of time between 2 - 4 weeks to get an overall analysis. 

=B4/GCD(B4,C4)&":"&C4/GCD(B4,C4)

This should help. I hope it does
Chevy Oct 13, '16 at 2:48 pm
Add to Discussion


Answer the Question

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