Production Schedule

[northw]

Hello,

I'm trying to design a production schedule and make it visual at the same time.

There are 3 machines, and I would like to allocate jobs for each machine, based on 5 days production and 10hr shifts (jobs are not allocated per person, rather, per machine). Ideally I would like to define how many hours each job will take (rounded to nearest half-hour).

I have done an example on Excel by colouring some cells - hope this gives you an idea.

How could I achieve something like this? At this stage I'm not too worried about the stats / efficiency side of the things, first and foremost I would like to "visualise" the production schedule, and the rest should be relatively easier.

Any help/advice/direction would be great!

Thanks

 

[Saba Sabaratnam]

Hi

I suggest the following process

1. Create a table as shown below with job code and description

2. Create a schedule as shown below with for Monday ( and other days)

3. Drop down list for the job codes as shown below.

4. Use conditional formatting as shown below to color different job codes with different colors. for example, Job 1 is highlighted in green.

B4 cell is linked to Job 1 in W11 of the job codes table shown above (the table is in range W11 to X28). This will allow you to change job code and apply conditional formatting even you change jobs codes.

You will have to create a conditional formatting for each of job codes. I just created conditional formatting for Job 1, Job 2 and Job 3 as shown below.

My entire worksheet layout is shown below for clarity,

Kind regards

Saba

 

[northw]

Hi @Saba Sabaratnam ,

Thanks for the recommendation, this is a good idea.

Can you think of a way to make this a little more fool-proof? For example, if I enter Job1 4hrs, and Job 2 9hrs, could this fill in the machine time automatically? In that case first 6hrs of the Job2 would be done on Monday, and the remaining 3hrs would flow into Tuesday (based on 10hr shifts per day).

I am trying to make this in a simple way so that production supervisor can generate the plan easily. I have tried to go in a slightly different way, but it still has some limitations. Basically, I created a graph for each machine, each day. And then put them side by side. So graphs are dynamically changed based on values entered, but the biggest issue is of course continuation of colour (if a job over spills to the following day, it doesn't have the same colour).

Here is a screenshot..

 

[Saba Sabaratnam]

Hi

To automate this, I I suggest the following process

1. create a table as shown below for scheduling as shown below.

Enter the following formula in AB 11 and copy it down.


=SUMIFS($AA$11:AA11,$Z$11:Z11,Z11,$Y$11:Y11,Y11)

2. create your visual output as shown below.

Please note Date in B1 and Machine Name are critical (that is why they are highlighted in red) and much match the date and machines names in the above table as shown above.


And enter the following formula in B4 and copy across to U4

=XLOOKUP(B3,($AB$11:$AB$28)*($Z$11:$Z$28=$A4)*($Y$11:$Y$28=$B$1),$W$11:$W$28,"",1)

And enter the following formula in B11 and copy across to U11

=XLOOKUP(B10,($AB$11:$AB$28)*($Z$11:$Z$28=$A11)*($Y$11:$Y$28=$B$8),$W$11:$W$28,"",1)

You can repeat the process for Wednesday, Thursday and Friday

3. Set up the color using conditional formatting for consistency

The most important part of this solution is to create standard JOBCODE to use again and again.

Please let me know how you go.

Kind regards

Saba