Optimization between 2 dependent variables

Question

I couldn't find a solution nowhere, so I'm asking here. Hope someone knows how to guide me thru.

So, I'm not quite sure if this is a optimization problem (if anyone know what kind of problem is this, let me know), but I need to find the quantity of clients that each attendant has to have so that each has the same amount of orders. I don't know if there is a function or a regression that could be made of this.

Column A has the clients name. Column B has the "difficulty" that each client is to assist - that is, "1" is normal difficulty, "2" is double the normal difficulty and so on - meaning that the orders of this clients will be multiplied by his difficulty. Column C has a spec that only attendant Y can assist. Column D has the quantity of orders that each client requested. And finally column E is the account attendant.

CLIENT	ATTENTION	SPEC	ORDERS	ATTENDANT
a1	3	0	6	y
a2	3	0	7	x
a3	1	0	1	y
a4	1	0	9	y
a5	2	0	6	y
a6	1	0	7	y
a7	3	0	2	y
a8	3	0	9	x
a9	3	0	9	y
a10	2	1	8	y
a11	2	0	8	x
a12	2	0	9	y
a13	1	1	2	y
a14	2	0	4	x
a15	3	0	10	y
a16	2	0	9	x
a17	2	0	8	y
a18	1	1	5	y
a19	3	0	8	x
a20	1	1	3	y
a21	2	0	10	x
a22	2	0	6	x

Summary tables:

ATTENDANT	TOTAL ORDERS
x	61
y	84

ATTENDANT	TOTAL CLIENTS
x	8
y	14

ATTENDANT	TOTAL ORDERS
x	61
y	84
y (spec 0)	66
y (spec 1)	18

Answer 1

Here is a linear program that solves this. Fun problem! This uses the pyomo environment and the separately installed glpk solver. Result: It's a tie! X and Y don't need to haggle! ;)

Code:

# client assignment

import csv
from collections import namedtuple
import pyomo.environ as pyo

data_file = 'data.csv'

Record = namedtuple('Record', ['attention', 'spec', 'orders'])

records = {}
with open(data_file, 'r') as src:
    src.readline()  # burn header row
    reader = csv.reader(src, skipinitialspace=True)
    for line in reader:
        record = Record(int(line[1]), int(line[2]), int(line[3]))
        records[line[0]] = record

#print(records)

# set up the ILP

m = pyo.ConcreteModel()

# SETS

m.C = pyo.Set(initialize=records.keys())  # the set of clients
m.A = pyo.Set(initialize=['X', 'Y'])      # the set of attendants

# PARAMETERS

m.attention  = pyo.Param(m.C, initialize={c: r.attention for (c, r) in records.items()})
m.y_required = pyo.Param(m.C, initialize={c: r.spec for (c, r) in records.items()})
m.orders     = pyo.Param(m.C, initialize={c: r.orders for (c, r) in records.items()})

# VARIABLES

m.assign = pyo.Var(m.A, m.C, domain=pyo.Binary)   # 1: assign attendant a to client c
m.work_delta = pyo.Var(domain=pyo.NonNegativeReals)   # the abs(work difference)

# OBJECTIVE

# minimize the work delta...
m.obj = pyo.Objective(expr=m.work_delta)

# CONSTRAINTS

# each client must be serviced once and only once
def service(m, c):
    return sum(m.assign[a, c] for a in m.A) == 1
m.C1 = pyo.Constraint(m.C, rule=service)

# y-specific customers must be serviced by attendant y, and optionally if not reqd.
def y_serves(m, c):
    return m.assign['Y', c] >= m.y_required[c]
m.C2 = pyo.Constraint(m.C, rule=y_serves)

# some convenience expressions to capture work...
m.y_work = sum(m.attention[c] * m.orders[c] * m.assign['Y', c] for c in m.C)
m.x_work = sum(m.attention[c] * m.orders[c] * m.assign['X', c] for c in m.C)

# capture the ABS(y_work - x_work) with 2 constraints
m.C3a = pyo.Constraint(expr=m.work_delta >= m.y_work - m.x_work)
m.C3b = pyo.Constraint(expr=m.work_delta >= m.x_work - m.y_work)

# check the model
#m.pprint()

# SOLVE
solver = pyo.SolverFactory('glpk')
res = solver.solve(m)
# ensure the result is optimal
status = res.Solver()['Termination condition'].value
assert(status == 'optimal', f'error occurred, status: {status}.  Check model!')

print(res)

print(f'x work: {pyo.value(m.x_work)} units')
print(f'y work: {pyo.value(m.y_work)} units')

# list assignments
for c in m.C:
    for a in m.A:
        if pyo.value(m.assign[a, c]):
            print(f'Assign {a} to customer {c}')

Output:

Problem: 
- Name: unknown
  Lower bound: 0.0
  Upper bound: 0.0
  Number of objectives: 1
  Number of constraints: 47
  Number of variables: 46
  Number of nonzeros: 157
  Sense: minimize
Solver: 
- Status: ok
  Termination condition: optimal
  Statistics: 
    Branch and bound: 
      Number of bounded subproblems: 53
      Number of created subproblems: 53
  Error rc: 0
  Time: 0.008551836013793945
Solution: 
- number of solutions: 0
  number of solutions displayed: 0

x work: 158.0 units
y work: 158.0 units
Assign Y to customer a1
Assign Y to customer a2
Assign X to customer a3
Assign Y to customer a4
Assign Y to customer a5
Assign Y to customer a6
Assign Y to customer a7
Assign Y to customer a8
Assign X to customer a9
Assign Y to customer a10
Assign X to customer a11
Assign X to customer a12
Assign Y to customer a13
Assign X to customer a14
Assign X to customer a15
Assign X to customer a16
Assign X to customer a17
Assign Y to customer a18
Assign X to customer a19
Assign Y to customer a20
Assign Y to customer a21
Assign Y to customer a22
[Finished in 588ms]

In Excel Solver

Note: in the solver options, be sure to de-select "ignore integer constraints"

This seems to work OK. The green shaded areas are "locked" to Y and not in the solver's control.

I'm always suspicious of the solver in Excel, so check everything!