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!
