Calculate average time to complete tasks based on sales data.

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,611
Hey guys and gals. I don't have a spreadsheet yet. I was hoping you could help me figure out a formula and I would create the spreadsheet afterwards. It seems simple enough, but the solution eludes me. Here's the senerio:

There are 3 work shifts in a day. Lets take Friday for example. I would look at last week's sales report and determine that we made $2000 last Friday. Now I have job assignment for employees such as mop the floor, clean bathrooms, etc. I am going to determine how long it takes to do each task and sum up the minutes. So on Friday for shift 1, we'll just say that there is 2 hours worth of tasks and the rest of the time is spend helping customers. What I'm trying to calculate is the amount of time that is not spent ringing up customers at the cash register using sales data. So what here's what I'm thinking. I look at the job assignments list for the last week and determine what was done. Then I look at the sales data and see how much money we made that day. According to the job assignments, they completed 2 hours of tasks and we made $2000 that day. On Saturday they completed 1 hour of tasks and we made $3000 that day. It's obvious that depending on how much money we make, we have less time for other work tasks. Using the sales data and previous job assignments, I want to figure out the average amount of time in 1 hour is used on the customer and how much is used on tasks. So for example, 1 hour of work might mean 45 minutes with customers and 15 minutes on tasks. I hope this makes sense.

Background: I'm the new assistant manager and have been tasked out to create a more efficient work schedule. Currently we have days where nothing gets done because we are so busy with customers. I need to schedule enough employees to get these jobs done but I can't go over budget. So I need to know how much time is needed to complete tasks based on sales data. Using the formula you help me with, I'll be able to determine if we need 1, 2, or 3 employees to work and for how many hours does the 3rd employee need to be there. You are helping me and my company become more efficient while minimizing payroll costs.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Friday
Sales Data: $2000 (We made $2000 in the entire day)
Shifts Data Split: $666.66 (2000 / 3 shifts = 666.66)
Job Assignments Shift 1: 2 hours (They completed 2 hours worth of work according to last week's job assignments sheet)

Saturday
Sales Data: $3000 (We made $3000 in the entire day)
Shifts Dta Split: $1000 (3000 / 3 shifts = 1000)
Job Assignments Shift 1: 1 hour (They completed 1 hour worth of work according to last weeks' job assignment sheet)

So if on Friday, shift 1 completed 2 hours of work tasks and made an average of $666.66 during their shift, how much time was spent on customers and how and how much time was spent on work tasks in a span of 1 hour. In other words, in an average 1 hour work period, 45 minutes might be spent on custormers and 15 minutes on work tasks for Friday during Shift 1. I need that information and the equation that helped figure that out before I can make a spreadsheet for the purpose I need it for.
 
Upvote 0
I figured it out. Thanks anyway.
Saturday Sales = $3000
Shift 1 Sales (=3000/3 shifts) = $1000
Shift Sales per hour (=$1000/8 hours in a work shift) = $125

Saturday Job Assignments Completed in an 8 hour work shift = 2 hours of tasks completed
2/8 = 25% or 15 minutes of tasks were completed every hour on average.

So for every $125 made, we complete 15 minutes of tasks.
Then I just need to use the same equation on other days of the week and find a good average for all the days. Thanks for your help anyways.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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