Estimated completion dates

A

ac7

New Member
Joined
Jul 26, 2023
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I work for a manufacturer and am trying to come up with a way to calculate estimated completion dates for builds. I am struggling with how to get this started.

A new build is starting approximately every 11 hours. I have 5 zones that a build moves through, with 1 person in each zone. As soon as 11 hours is up, the build moves forward to the next zone. The issue comes in with the number of employees and working hours per day. Some days I have 4 people working, sometimes 5. Sometimes I have 8 hours available per person, sometimes 9 or 10. If there are 5 people building, the 1st person starts a new build after the 11 hours is up. If there are only 4 people building, then the person in the 1st zone will then move to the 2nd zone to work on a build.

If anyone has ideas of how to set something like this up, I would love to see them.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
There are some question that pop into my head reading your problem:

1. If you start a new build, do all available persons (4 or 5) work simultaneously on the same build in zone 1, after zone 1 is completed, all work in zone 2? Or does 1 person work in zone 1, (rest idle till first
build in zone 1 is complete), and then person 2 works in zone 2 with the build from zone 1 and so on?

2. (dependent on the response of 1) If more than one person can work on the same build, can for example 2 person finish the build in 5.5 hours (5.5x2=11)?

3. Can you provide a schedule of you working personal availability? for example for a week, how many persons are working each day and how many hours each day.

4. Do all you workers work in any of the zones?

5. How are you planing your builds till now. Do you have some schedules or something that you can show us?
 
Upvote 0
felixstraube said:
There are some question that pop into my head reading your problem:

1. If you start a new build, do all available persons (4 or 5) work simultaneously on the same build in zone 1, after zone 1 is completed, all work in zone 2? Or does 1 person work in zone 1, (rest idle till first
build in zone 1 is complete), and then person 2 works in zone 2 with the build from zone 1 and so on?

2. (dependent on the response of 1) If more than one person can work on the same build, can for example 2 person finish the build in 5.5 hours (5.5x2=11)?

3. Can you provide a schedule of you working personal availability? for example for a week, how many persons are working each day and how many hours each day.

4. Do all you workers work in any of the zones?

5. How are you planing your builds till now. Do you have some schedules or something that you can show us?
Click to expand...

1. 1 person works in a zone, then advances the build forward to the next zone when done, where the next person works on the build until they're done, where it then advances to the next zone where 1 person works on the build, etc. Once the 1st person advances their build, they can start the work on a brand new build.

2. I'd like to keep the plan at 1 person per zone.

3. Here's an example of the schedule with their availability. It's likely not a full 9 hours, as effectivity (90%) needs to be taken into consideration. So if they're planned for 9 hours, they're likely really only working 8.1 hours.
Employee06/25/2406/26/2406/27/2406/28/24
Zone 19999
Zone 20099
Zone 39999
Zone 49900
Zone 59999

4. Yes, all workers can work in any zone, however, we typically have set people working in the same zone. However, if the Zone 2 person is out one day, the Zone 1 person can move forward with the build into Zone 2 and continue the work while the Zone 2 employee is out.

5. We use a static build time that does not take any employee availability into consideration. Every 2 days a build moves forward on this schedule, regardless of what time it's finished or who is available. It's not very accurate when we should be taking availability into account. Unfortunately, I cannot share the schedule.

I hope this helps clarify what I'm looking for. I really appreciate the help.
 
Upvote 0
Ok this in not quite a solution yet, but I would like you to check it and see if this schedule/workplan is correct.

Here the file im working on:

Estimated completion dates.zip

First there is a Employee availability table:
Availability
DatePerson 1Person 2Person 3Person 4Person 5
25/6/20249-999
26/6/20249-999
27/6/2024999-9
28/6/2024999-9
1/7/2024108999
2/7/2024-10999
3/7/20248810-9
4/7/2024999-9
5/7/202499999
8/7/202499999
9/7/20249-999
10/7/20249-999
11/7/2024999-9
12/7/2024999-9


I added some random number for this example.

Now lets suppose there isn't any build in progress. So you start on day 6/25/2024 with the first build (I name it "B-1") and you create this schedule with days, working hours by zones:

Working schedule
Day / Working HourZone 1Zone 2Zone 3Zone 4Zone 5
6/25/2024 - 1
6/25/2024 - 2
6/25/2024 - 3
6/25/2024 - 4
6/25/2024 - 5
6/25/2024 - 6
6/25/2024 - 7
6/25/2024 - 8
6/25/2024 - 9
6/25/2024 - 10
6/26/2024 - 1
6/26/2024 - 2
6/26/2024 - 3
6/26/2024 - 4
6/26/2024 - 5
6/26/2024 - 6
6/26/2024 - 7
6/26/2024 - 8
6/26/2024 - 9
6/26/2024 - 10
6/27/2024 - 1
6/27/2024 - 2
6/27/2024 - 3


We also have a table that tells us how many hour was worked on each each build in every zone:

Build in queueZone 1 completionZone 2 completionZone 3 completionZone 4 completionZone 5 completion
B-100000
B-200000
B-300000
B-400000
B-500000


So now we start with day 6/25/2024 we have 4 workers for 9 ours each. But as we will have just 1 worker per zone we will have for B-1 the following (you can try this out on sheet2):

Working schedule
Day / Working HourZone 1Zone 2Zone 3Zone 4Zone 5
6/25/2024 - 1B-1
6/25/2024 - 2B-1
6/25/2024 - 3B-1
6/25/2024 - 4B-1
6/25/2024 - 5B-1
6/25/2024 - 6B-1
6/25/2024 - 7B-1
6/25/2024 - 8B-1
6/25/2024 - 9B-1
6/25/2024 - 10


As we complete the Build number in the zone columns the build table is updated:

Build in queueZone 1 completionZone 2 completionZone 3 completionZone 4 completionZone 5 completion
B-190000
B-200000
B-300000
B-400000
B-500000


As you mentioned with an effectivity of 90% the 11 hours to complete a build in one zone becomes 12.2 hours, rounded up to 13 hours. So im considering 13 hours per build per zone.

So to complete build B-1 we will have this schedule:

Working schedule
Day / Working HourZone 1Zone 2Zone 3Zone 4Zone 5
6/25/2024 - 1B-1
6/25/2024 - 2B-1
6/25/2024 - 3B-1
6/25/2024 - 4B-1
6/25/2024 - 5B-1
6/25/2024 - 6B-1
6/25/2024 - 7B-1
6/25/2024 - 8B-1
6/25/2024 - 9B-1
6/25/2024 - 10
6/26/2024 - 1B-1
6/26/2024 - 2B-1
6/26/2024 - 3B-1
6/26/2024 - 4B-1
6/26/2024 - 5B-1
6/26/2024 - 6B-1
6/26/2024 - 7B-1
6/26/2024 - 8B-1
6/26/2024 - 9B-1
6/26/2024 - 10
6/27/2024 - 1B-1
6/27/2024 - 2B-1
6/27/2024 - 3B-1
6/27/2024 - 4B-1
6/27/2024 - 5B-1
6/27/2024 - 6B-1
6/27/2024 - 7B-1
6/27/2024 - 8B-1
6/27/2024 - 9B-1
6/27/2024 - 10
6/28/2024 - 1B-1
6/28/2024 - 2B-1
6/28/2024 - 3B-1
6/28/2024 - 4B-1
6/28/2024 - 5B-1
6/28/2024 - 6B-1
6/28/2024 - 7B-1
6/28/2024 - 8B-1
6/28/2024 - 9B-1
6/28/2024 - 10
7/1/2024 - 1B-1
7/1/2024 - 2B-1
7/1/2024 - 3B-1
7/1/2024 - 4B-1
7/1/2024 - 5B-1
7/1/2024 - 6B-1
7/1/2024 - 7B-1
7/1/2024 - 8B-1
7/1/2024 - 9B-1
7/1/2024 - 10B-1
7/2/2024 - 1B-1
7/2/2024 - 2B-1
7/2/2024 - 3B-1
7/2/2024 - 4B-1
7/2/2024 - 5B-1
7/2/2024 - 6B-1
7/2/2024 - 7B-1
7/2/2024 - 8B-1
7/2/2024 - 9B-1
7/2/2024 - 10B-1
7/3/2024 - 1B-1
7/3/2024 - 2B-1
7/3/2024 - 3B-1
7/3/2024 - 4B-1
7/3/2024 - 5B-1
7/3/2024 - 6B-1
7/3/2024 - 7B-1
7/3/2024 - 8B-1
7/3/2024 - 9B-1
7/3/2024 - 10


Taking into account that on day 7/1/2024 and 7/2/2024 we will have a worker that works 10 hours each day so we can advance with B-1 on working hour 10.

We can see in our progress table that B-1 is complete with 13 hour per zone:

Build in queueZone 1 completionZone 2 completionZone 3 completionZone 4 completionZone 5 completion
B-11313131313
B-200000
B-300000
B-400000
B-500000


Now we do the same for build B-2. As son as zone 1 is idle and a worker available, we start B-2.

Working schedule
Day / Working HourZone 1Zone 2Zone 3Zone 4Zone 5
6/25/2024 - 1B-1
6/25/2024 - 2B-1
6/25/2024 - 3B-1
6/25/2024 - 4B-1
6/25/2024 - 5B-1
6/25/2024 - 6B-1
6/25/2024 - 7B-1
6/25/2024 - 8B-1
6/25/2024 - 9B-1
6/25/2024 - 10
6/26/2024 - 1B-1
6/26/2024 - 2B-1
6/26/2024 - 3B-1
6/26/2024 - 4B-1
6/26/2024 - 5B-2B-1
6/26/2024 - 6B-2B-1
6/26/2024 - 7B-2B-1
6/26/2024 - 8B-2B-1
6/26/2024 - 9B-2B-1
6/26/2024 - 10
6/27/2024 - 1B-2B-1
6/27/2024 - 2B-2B-1
6/27/2024 - 3B-2B-1
6/27/2024 - 4B-2B-1
6/27/2024 - 5B-2B-1
6/27/2024 - 6B-2B-1
6/27/2024 - 7B-2B-1
6/27/2024 - 8B-2B-1
6/27/2024 - 9B-2B-1
6/27/2024 - 10
6/28/2024 - 1B-2B-1
6/28/2024 - 2B-2B-1
6/28/2024 - 3B-2B-1
6/28/2024 - 4B-2B-1
6/28/2024 - 5B-2B-1
6/28/2024 - 6B-2B-1
6/28/2024 - 7B-2B-1
6/28/2024 - 8B-2B-1
6/28/2024 - 9B-2B-1
6/28/2024 - 10
7/1/2024 - 1B-2B-1
7/1/2024 - 2B-2B-1
7/1/2024 - 3B-2B-1
7/1/2024 - 4B-2B-1
7/1/2024 - 5B-2B-1
7/1/2024 - 6B-2B-1
7/1/2024 - 7B-2B-1
7/1/2024 - 8B-2B-1
7/1/2024 - 9B-2B-1
7/1/2024 - 10B-1
7/2/2024 - 1B-2B-1
7/2/2024 - 2B-2B-1
7/2/2024 - 3B-2B-1
7/2/2024 - 4B-2B-1
7/2/2024 - 5B-2B-1
7/2/2024 - 6B-2B-1
7/2/2024 - 7B-2B-1
7/2/2024 - 8B-2B-1
7/2/2024 - 9B-2B-1
7/2/2024 - 10B-1
7/3/2024 - 1B-2B-1
7/3/2024 - 2B-2B-1
7/3/2024 - 3B-2B-1
7/3/2024 - 4B-2B-1
7/3/2024 - 5B-2B-1
7/3/2024 - 6B-2B-1
7/3/2024 - 7B-2B-1
7/3/2024 - 8B-2B-1
7/3/2024 - 9B-2B-1
7/3/2024 - 10B-2
7/4/2024 - 1B-2
7/4/2024 - 2B-2
7/4/2024 - 3B-2
7/4/2024 - 4B-2
7/4/2024 - 5B-2
7/4/2024 - 6B-2
7/4/2024 - 7B-2
7/4/2024 - 8B-2
7/4/2024 - 9B-2
7/4/2024 - 10
7/5/2024 - 1B-2
7/5/2024 - 2B-2
7/5/2024 - 3B-2
7/5/2024 - 4B-2
7/5/2024 - 5B-2
7/5/2024 - 6
7/5/2024 - 7
7/5/2024 - 8
7/5/2024 - 9
7/5/2024 - 10


considering that on day 7/2/2024 we only have a worker that works 9 hours left and, on day 7/3/2024 we have a worker that works 10 hours.

On Sheet1 you will have the complete schedule for the 5 considered builds.

Sorry for the long post. But to exemplify the process I think it is needed.
Would you consider that the process as described is correct? If not, let me know what should be done differently. Once we hace this worked out we can automate this schedule building process.
Let me know what you think.
 
Upvote 0
This is a great start! Thank you for the detailed explanation. It may take me a few days to review, but I will get back to you as soon I am able to review in full.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Formula or VBA to track pro-rated attendance hours
Replies
8
Views
387
bh24524
B
B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
239
bh24524
B
C
  • Question Question
How can I allocate task across multiple departments and periods?
Replies
0
Views
301
charltondav
C
P
  • Question Question
Calculating Percentage Completed using Dates
Replies
1
Views
327
Joe4
Joe4
G
  • Question Question
Call number of hours in Project Plan Gantt Chart Template
Replies
4
Views
44
GreenLanternX
G

Forum statistics

Threads
1,224,812
Messages
6,181,105
Members
453,021
Latest member
Justyna P

Share this page

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