Hi – I am looking for some assistance in optimizing a production schedule based on a set of constraints / business rules. My current dataset is ~100k rows of data, each describing a process that a product goes through, what department it occurs in, and how long we estimate it will take.
- I am currently backwards scheduling utilizing a multi-row formula tool from the ‘Coating’ process and assuming 1 day for the product to move btwn departments
- The Process Seq column denotes the order of processes for a given Job-SubJob#. The prior must finish before the other can start
Main data set is structured as follows (+sample attached)
Job# | Sub-Job# | Department | Process Name | Total Units | Process Seq | Production Days | Planned Start Date |
60000 | 01 | Casting | Cast | 1000 | 0 | 1 | 1/2/2023 |
60000 | 01 | Molding | Mold | 1000 | 1 | 2 | 1/4/2023 |
60000 | 01 | Forming | Form | 1000 | 2 | 3 | 1/7/2023 |
60000 | 01 | Cutting | Cutting | 1000 | 3 | 3 | 1/11/2023 |
60000 | 01 | Coating | Coating | 1000 | 4 | 3 | 1/15/2023 |
Each process occurs within a specific department and each department has a weekly capacity that they can handle:
Department | Weekly Capacity (Units) |
Casting | 1000 |
Molding | 500 |
Forming | 600 |
Cutting | 700 |
Coating | 1000 |
Right now, my workflow creates a schedule that does not account for capacity and simply schedules back from the ‘Coating’ start date. I’d like to build a tool/workflow to find the optimal start dates that take the weekly capacity constraints into account and dynamically adjusts them so that weekly capacity is around ~80%.
I believe the right tool to utilize here is either an iterative macro or the Optimization tool within the Predictive Analytics package. The solution will need to scale to be able to accommodate a dataset of 100k+ rows. Any input here on how to structure this problem would be greatly appreciated.
