Scheduling

[Dan Reedy]

I am in the process of creating a scheduling spreadsheet for a small manufacture. We are scheduling machines, not people. I am just about complete, having everything but the following working well:

When scheduling 2 jobs that use the same machine and need to be done at the same time, it will double book the machine, instead of completing 1 task, then scheduling the 2nd. Where do I begin to make this work as desired?

Thank you

Dan

 

[Dan Reedy]

Below is a larger sample of the spreadsheet Column a is the furthest to the left, row 9 is where the customer heading is located.

Column A is customer name
Column B is the part number we are making
Column C is the date to ship by
Column D is a formula - Column C minus lead time. Lead time is built into the part routings from another sheet.
Column E is the work order number - internal tracking purposes only
Column F is the Batch quantity - typically the quantity to be shipped.
Column G is the completed quantity.
Column H is a formula - Column F minus Column G
Column I is a formula - Column G divided by column F
Column J is a user entered field y or n to setup the formula in column S
Column K is a user entered field in conjuction with column I
Column L is the operation sequence
Column M is the cell or machine
Column N is the time to setup the machine.
Column O is the hourly rate - parts produced per hour
Column P is the number of hours machine is available per day. This is pulled from another sheet via vlookup
Column Q is transfer hours. This is the number of hours after the proceeding operation starts, that the next operation can start - an overlap if you will.
Column r is a formula - =IF(G10=0,(F10/O10)+(Q10+N10),(F10-G10)/O10)+Q10
Column S is the time of day that the machine or resource is available to start work - pulled from another sheet
Column T is the time of day that a machine ends its availability - pulled from another sheet
Column U is the start time as noted in earlier post
Column V is the operation end time as noted in earlier post
Column W is the # of days operation is scheduled to be early or late. =SUM($V13-C13)

If you need any more information, please let me know. Thank you for the help.

 

[Dan Reedy]

[TABLE="width: 1879"]
<colgroup><col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5339;"> <col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5339;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="126" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4608;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" span="2"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108; mso-outline-level: 1;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510; mso-outline-level: 1;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889; mso-outline-level: 1;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560; mso-outline-level: 1;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560; mso-outline-level: 1;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194; mso-outline-level: 1;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925; mso-outline-level: 1;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;" span="2"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <tbody>[TR]
[TD="class: xl80, width: 144, bgcolor: yellow"]Customer[/TD]
[TD="class: xl80, width: 146, bgcolor: yellow"]Part Number[/TD]
[TD="class: xl80, width: 146, bgcolor: yellow"]Priority[/TD]
[TD="class: xl81, width: 114, bgcolor: yellow"]Job Ship Date[/TD]
[TD="class: xl81, width: 126, bgcolor: yellow"]Job Start Date[/TD]
[TD="class: xl81, width: 79, bgcolor: yellow"]Work Order[/TD]
[TD="class: xl86, width: 98, bgcolor: yellow"]Batch Quantity[/TD]
[TD="class: xl86, width: 98, bgcolor: yellow"]Completed Quantity[/TD]
[TD="class: xl86, width: 98, bgcolor: yellow"]Quantity Remaining[/TD]
[TD="class: xl86, width: 98, bgcolor: yellow"]% Complete[/TD]
[TD="class: xl86, width: 78, bgcolor: yellow"]Lock Schedule? Y or N[/TD]
[TD="class: xl82, width: 142, bgcolor: yellow"]Locked Schedule Start Date[/TD]
[TD="class: xl83, width: 85, bgcolor: #FFE699"]Sequence[/TD]
[TD="class: xl84, width: 96, bgcolor: #FFE699"]Cell / Machine[/TD]
[TD="class: xl83, width: 79, bgcolor: #FFE699"]Setup Time[/TD]
[TD="class: xl84, width: 70, bgcolor: #FFE699"]Hourly Rate[/TD]
[TD="class: xl84, width: 70, bgcolor: #FFE699"]Hours Available Per Day[/TD]
[TD="class: xl84, width: 60, bgcolor: #FFE699"]Transfer Hours[/TD]
[TD="class: xl84, width: 80, bgcolor: #FFE699"] Remaining Hours[/TD]
[TD="class: xl85, width: 113, bgcolor: #FCE4D6"]Start of Working Hours[/TD]
[TD="class: xl85, width: 113, bgcolor: #FCE4D6"]End of WorkingHours[/TD]
[TD="class: xl85, width: 127, bgcolor: #FCE4D6"]Operation Start Date and Time[/TD]
[TD="class: xl85, width: 124, bgcolor: #FCE4D6"]Operation End Date and Time[/TD]
[TD="class: xl85, width: 113, bgcolor: #FCE4D6"]Days Operation is Early or Late (+ is late / - is early)[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #D9E1F2"]Mitec[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]815[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/30/18 7:00 AM[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/16/18 7:00 AM[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]68[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]17280[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]5270[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]12010[/TD]
[TD="class: xl74, bgcolor: #D9E1F2"]30%[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]n[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]1[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]1002[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]2[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]600[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]15.5[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]0[/TD]
[TD="class: xl71, bgcolor: #D9E1F2"]20.0[/TD]
[TD="class: xl72, bgcolor: #D9E1F2"]12:00 AM[/TD]
[TD="class: xl72, bgcolor: #D9E1F2"]11:59 PM[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/16/18 7:00 AM[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/17/18 11:31 AM[/TD]
[TD="class: xl71, bgcolor: #D9E1F2"]-12.8[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Mitec[/TD]
[TD="class: xl77, bgcolor: transparent"]815[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 7:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 7:00 AM[/TD]
[TD="class: xl77, bgcolor: transparent"]68[/TD]
[TD="class: xl77, bgcolor: transparent"]17280[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl77, bgcolor: transparent"]17280[/TD]
[TD="class: xl67, bgcolor: transparent"]0%[/TD]
[TD="class: xl77, bgcolor: transparent"]n[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"]2[/TD]
[TD="class: xl77, bgcolor: transparent"]M11[/TD]
[TD="class: xl77, bgcolor: transparent"]0.5[/TD]
[TD="class: xl77, bgcolor: transparent"]150[/TD]
[TD="class: xl77, bgcolor: transparent"]21.6[/TD]
[TD="class: xl77, bgcolor: transparent"]8[/TD]
[TD="class: xl68, bgcolor: transparent"]131.7[/TD]
[TD="class: xl75, bgcolor: transparent"]12:00 AM[/TD]
[TD="class: xl75, bgcolor: transparent"]11:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 3:53 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/24/18 5:59 PM[/TD]
[TD="class: xl68, bgcolor: transparent"]-5.5[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Mitec[/TD]
[TD="class: xl77, bgcolor: transparent"]815[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 7:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 7:00 AM[/TD]
[TD="class: xl77, bgcolor: transparent"]68[/TD]
[TD="class: xl77, bgcolor: transparent"]17280[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl77, bgcolor: transparent"]17280[/TD]
[TD="class: xl67, bgcolor: transparent"]0%[/TD]
[TD="class: xl77, bgcolor: transparent"]n[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"]3[/TD]
[TD="class: xl77, bgcolor: transparent"]5003 - Furnace[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl77, bgcolor: transparent"]600[/TD]
[TD="class: xl77, bgcolor: transparent"]22.5[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl68, bgcolor: transparent"]28.8[/TD]
[TD="class: xl75, bgcolor: transparent"]12:00 AM[/TD]
[TD="class: xl75, bgcolor: transparent"]11:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/25/18 5:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/27/18 12:18 AM[/TD]
[TD="class: xl68, bgcolor: transparent"]-3.3[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Mitec[/TD]
[TD="class: xl77, bgcolor: transparent"]815[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 7:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 7:00 AM[/TD]
[TD="class: xl77, bgcolor: transparent"]68[/TD]
[TD="class: xl77, bgcolor: transparent"]17280[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl77, bgcolor: transparent"]17280[/TD]
[TD="class: xl67, bgcolor: transparent"]0%[/TD]
[TD="class: xl77, bgcolor: transparent"]n[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"]4[/TD]
[TD="class: xl77, bgcolor: transparent"]6069[/TD]
[TD="class: xl77, bgcolor: transparent"]0.5[/TD]
[TD="class: xl77, bgcolor: transparent"]150[/TD]
[TD="class: xl77, bgcolor: transparent"]18[/TD]
[TD="class: xl77, bgcolor: transparent"]4[/TD]
[TD="class: xl68, bgcolor: transparent"]123.7[/TD]
[TD="class: xl75, bgcolor: transparent"]12:00 AM[/TD]
[TD="class: xl75, bgcolor: transparent"]11:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/25/18 11:19 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]5/4/18 3:02 PM[/TD]
[TD="class: xl68"]4.3[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #D9E1F2"]Stackpole[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]829[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/30/18 7:00 AM[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/16/18 7:00 AM[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]57[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]12600[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]0[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]12600[/TD]
[TD="class: xl74, bgcolor: #D9E1F2"]0%[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]n[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"] [/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]1[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]1002[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]2[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]600[/TD]
[TD="class: xl76, bgcolor: #D9E1F2"]15.5[/TD]
[TD="class: xl70, bgcolor: #D9E1F2"]0[/TD]
[TD="class: xl76, bgcolor: #D9E1F2"]23.0[/TD]
[TD="class: xl72, bgcolor: #D9E1F2"]11:00 PM[/TD]
[TD="class: xl72, bgcolor: #D9E1F2"]3:30 PM[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/16/18 7:00 AM[/TD]
[TD="class: xl73, bgcolor: #D9E1F2"]4/17/18 2:30 PM[/TD]
[TD="class: xl71, bgcolor: #D9E1F2"]-12.7[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Stackpole[/TD]
[TD="class: xl77, bgcolor: transparent"]829[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 7:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 7:00 AM[/TD]
[TD="class: xl78, bgcolor: transparent"]57[/TD]
[TD="class: xl78, bgcolor: transparent"]12600[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl78, bgcolor: transparent"]12600[/TD]
[TD="class: xl67, bgcolor: transparent"]0%[/TD]
[TD="class: xl77, bgcolor: transparent"]n[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"]2[/TD]
[TD="class: xl77, bgcolor: transparent"]M12[/TD]
[TD="class: xl77, bgcolor: transparent"]0.5[/TD]
[TD="class: xl77, bgcolor: transparent"]150[/TD]
[TD="class: xl79, bgcolor: transparent"]18.0[/TD]
[TD="class: xl77, bgcolor: transparent"]4[/TD]
[TD="class: xl79, bgcolor: transparent"]84.5[/TD]
[TD="class: xl75, bgcolor: transparent"]12:00 AM[/TD]
[TD="class: xl75, bgcolor: transparent"]11:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 12:20 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/23/18 12:51 AM[/TD]
[TD="class: xl68, bgcolor: transparent"]-7.3[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Stackpole[/TD]
[TD="class: xl77, bgcolor: transparent"]829[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 7:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 7:00 AM[/TD]
[TD="class: xl78, bgcolor: transparent"]57[/TD]
[TD="class: xl78, bgcolor: transparent"]12600[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl78, bgcolor: transparent"]12600[/TD]
[TD="class: xl67, bgcolor: transparent"]0%[/TD]
[TD="class: xl77, bgcolor: transparent"]n[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"]3[/TD]
[TD="class: xl77, bgcolor: transparent"]5003 - Furnace[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl77, bgcolor: transparent"]600[/TD]
[TD="class: xl79, bgcolor: transparent"]22.5[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl79, bgcolor: transparent"]21.0[/TD]
[TD="class: xl75, bgcolor: transparent"]12:00 AM[/TD]
[TD="class: xl75, bgcolor: transparent"]11:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/24/18 12:51 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/24/18 9:51 PM[/TD]
[TD="class: xl68, bgcolor: transparent"]-5.4[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]Stackpole[/TD]
[TD="class: xl77, bgcolor: transparent"]829[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 7:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/16/18 7:00 AM[/TD]
[TD="class: xl78, bgcolor: transparent"]57[/TD]
[TD="class: xl78, bgcolor: transparent"]12600[/TD]
[TD="class: xl77, bgcolor: transparent"]0[/TD]
[TD="class: xl78, bgcolor: transparent"]12600[/TD]
[TD="class: xl67, bgcolor: transparent"]0%[/TD]
[TD="class: xl77, bgcolor: transparent"]n[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"]4[/TD]
[TD="class: xl77, bgcolor: transparent"]6072[/TD]
[TD="class: xl77, bgcolor: transparent"]0.5[/TD]
[TD="class: xl77, bgcolor: transparent"]150[/TD]
[TD="class: xl79, bgcolor: transparent"]18.0[/TD]
[TD="class: xl77, bgcolor: transparent"]4[/TD]
[TD="class: xl79, bgcolor: transparent"]84.5[/TD]
[TD="class: xl75, bgcolor: transparent"]12:00 AM[/TD]
[TD="class: xl75, bgcolor: transparent"]11:59 PM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/24/18 6:11 AM[/TD]
[TD="class: xl66, bgcolor: transparent"]4/30/18 6:41 PM[/TD]
[TD="class: xl68"]0.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

So, if you add the priority column, you are going to manually determine that? And then, once we have the priority established, would that then affect the Start Time (Col U)?

 

[dreid1011]

And another question, with the transfer hours at 0, couldn't the next piece go at the same time? According to your description anyways:

"Column Q is transfer hours. This is the number of hours after the proceeding operation starts, that the next operation can start - an overlap if you will."

 

[Dan Reedy]

Actually No. The formula in the subsequent operation is =if(R11>0,V10+(R11/Q11),W10+1) - so it refers to the end date of the previous operation. We do not use transfer hours in the first operation.

 

[dreid1011]

Hmm, ok. Where is that formula then? Also, is there any indicator of how long a part will be on the machine before the next one can start? (I was thinking that was the Transfer Hours, but it seems not)

 

[Dan Reedy]

I will manually determine the priority. It would only effect the start time if 2 parts were scheduled at the same time, then i would like the item with the higher priority to run first.

 

[Dan Reedy]

Column r is the hours that the part will be on the machine.

 

[dreid1011]

Column r is the hours that the part will be on the machine.

Ok, so for the sample given, let's say the first occurrence of machine 1002 has priority. Then, ideally, the start time for the 2nd occurrence is (pick one): exactly 20 hours later, more than 20 hours later with a buffer, or less than 20 hours?

 

[Dan Reedy]

Exactly 20 hours later.