Using Optimizer for production line allocation



I am having issues using the optimizer for production allocation. The model is for capacity planning.


Problem statement

Need to allocate the SKU demand to compatible factory lines based on the following logic. Each SKU has a production priority and a factory line priority.

  1. If the production priority for a SKU is 1 total demand for the SKU must be allocated before moving to the SKU with production priority 2.
  2. For each SKU, each compatible factory line is given a priority value. For e.g., in the case of SKU-106 the maximum demand possible should be allotted to Line priority 1(Factory Line 3) and then the remaining demand can be allotted to Line priority 2(Factory Line 2)

SKU Code

Production Priority

Factory Line 1

Factory Line 2

Factory Line 3

Factory Line 4

Factory Line 5

Factory Line 6


























I have shared the demand values for each SKU in the attached file. The demand is in tons and needs to be divided by the throughput for that SKU-Line combination to give the loading time. E.g., for SKU-101 the demand 1393 will require (1393/4.8) = 291 hrs. on Factory line 1. The loading time available on Factory line 1 is 648 hrs.


These are the optimizer parameters I have used but I am not getting the right allocation. Need some help with where I'm going wrong. Please advise on what my constraints and objectives should be to get the result based on the logic in the problem statement.


Constraint 1: 'OPT03 Variable'.'Production Hrs.' * 'OPT01 Input'.'Throughput (with T1 Months)' <= 'OPT01 Input'.'Demand (with T1 Months)'

Constraint 2: 'OPT03 Variable'.'Production Hrs.' <= 'OPT01 Input'.'Factory Line Available Loading Time (with T1 Months)'

Variable: OPT03 Variable'.'Production Hrs.

Objective (Maximize): 'OPT03 Variable'.'Production Hrs.' * 'OPT01 Input'.Allocation weights

Where allocation weight is highest for Production priority 1-Line priority 1. Then reduces for Production priority 1-Line priority 2 and so on using the same logic above.