Put the Constraints in the Solver, Not the Spreadsheet

The first version of an allocation tool is almost always a spreadsheet with a scoring column. You have something scarce — new fund commitments, machine hours, a limited pool of capital — and a list of claimants, each with rules about what it can take. So you build a sheet: a row per option, a few weighted columns, a score, and a sort. Allocate top down until you run out. It is a reasonable first move, and for a while it works.

It stops working the moment the rules start interacting.

A score is not a constraint. A weighted score says “this option is better than that one.” It does not say “this fund cannot hold more than eight percent in a single obligor,” or “this line cannot run these two jobs back to back.” Those are not preferences to be traded off. They are hard limits that either hold or do not. Encode a limit as a heavy weight and the sheet will happily hand you an allocation that scores well and breaks it, because a large penalty is still a number the sort can climb over.

Constraints interact, and that is where spreadsheets quietly fail. With one or two rules, you can eyeball it. With a dozen rules across a dozen claimants, satisfying one pushes another over the edge, and fixing that breaks a third. The honest name for what happens next is tuning: someone nudges weights until the output stops looking obviously wrong. The result is not an allocation that satisfies every rule. It is an allocation nobody has found a violation in yet. In credit, that difference has a dollar value and an audit finding attached to it.

The fix is not a better spreadsheet. It is a different shape of tool.

Separate the model from the search. State each rule as a constraint: a statement that must hold. State what you are trying to do as an objective: deploy the most capital, leave the least capacity idle, whatever the real goal is. Then hand the whole thing to a solver whose only job is to find an assignment that satisfies every constraint at once while doing best on the objective. This is mixed-integer programming, and it is a mature, boring, well-understood technology. The point is not that it is clever. The point is what it can tell you that a sheet cannot.

A solver can prove what a score can only assert. Ask a scoring spreadsheet whether an allocation is feasible and it shrugs. It produced a number, not a guarantee. Ask a solver and it answers one of two ways. Either it returns an allocation that provably satisfies every constraint and is optimal against your stated objective, or it tells you the constraints cannot all be satisfied at once. The problem is infeasible, and, set up well, it tells you which rules are in conflict. Both answers are useful. “There is no valid allocation, and here is why” is a far better thing to learn in a model than on a closing call.

This changes the conversation around a number. When a portfolio manager asks why a commitment was split the way it was, the answer is not “the model preferred it.” The answer is the constraint set and the objective: these are the limits every fund’s agreement imposes, this is what we were maximizing, and this is the split that satisfies all of them best. The allocation becomes explainable by construction, because the reasons are the inputs.

The hard part was never the solver. The solver is the easy, solved piece. The work is stating the constraints correctly, and the constraints do not live in a requirements document. They live in the credit agreements: defined terms amended over years, concentration limits written in paragraphs, eligibility criteria buried in schedules. Getting those into the model accurately is the actual engagement. It is reading the documents, encoding what they say, and checking the encoding against how the operation already reasons about them. That is forward-deployed work, done next to the people who know why a limit exists.

This is the architecture behind GrayBeck Avail’s allocation engine. It states each fund’s credit-agreement constraints as a mathematical program and hands them to an optimization solver, so the allocation that comes back fits every constraint at once: not a score that sorted well, an assignment that holds. The document intelligence that reads the agreements and the solver that respects them are two halves of the same idea: put the rules where a machine can check them, not where a person has to remember them.

If your allocation runs on a spreadsheet with a scoring column and a lot of manual tuning, the tuning is the tell. It means the rules are interacting faster than a person can track, and the sheet is giving you plausibility instead of proof. That is exactly the point where the work moves into the solver.