A single-page reference of every rule the optimiser follows. Read top to bottom. If something doesn't match how you actually run the rota, flag it before signing. Once signed, this is the locked spec.
Each week there are roughly 60 events, around 40 active dealers, and millions of possible ways to staff them. The optimiser solves the whole week as one problem. It respects every hard rule in this document, tries to satisfy every soft preference, and picks the assignment that minimises total fleet travel.
The engine is Google's OR-Tools CP-SAT constraint solver. Each week solves in under a second. The output is a complete dealer assignment for every event, ready to paste into the rota sheet.
What follows is the complete specification. Hard rules the optimiser will never break. Soft preferences it tries to satisfy in priority order. Data defaults it reads from your spreadsheet. Special rules for specific dealers.
When rules conflict, higher-priority goals win. Items 1-3 are non-negotiable. Items 4-6 flex when they have to.
Absolute constraints. The optimiser will never violate these, even if it means a seat stays unfilled.
Standard events staff 2. Twin events and 3-dealer events staff 3 (see rule T1). The solver cannot assign fewer than 2.
A dealer works a maximum of one event per day. Twin events are the exception: if a dealer does both halves of a twin pair, that counts as one event for daily-limit purposes (see T1).
Each dealer's yearly quota divided by 47 working weeks gives their weekly target. Their hard weekly ceiling is target + 1. Twin pairs count as one event (0.75 weight) against this cap.
Examples:
To raise a dealer's cap, raise their yearly quota. 47 = 52 minus 2 shutdown weeks minus 3 typical holiday weeks.
A dealer on holiday cannot be assigned to an event on that date. Holidays come from the Holidays tab in the sheet and from the dedicated Holiday Days sheet in the enriched file. Both sources are merged.
Phrases like "does not work Tuesdays" in a dealer's comment field create hard weekday blocks. The solver parses the comment and refuses to schedule that dealer on the named day.
A dealer needs a yearly quota greater than zero (either Jan-Apr or May-onwards) to be eligible for any assignment. Inactive dealers are excluded from the candidate pool.
A twin event is two roadshows on the same day in nearby venues within the same county, staffed by the same team.
Twin pairs are grouped by date and county. The optimiser forces the same dealers onto both halves of a twin pair. A dealer assigned to Gosforth Tuesday morning is also assigned to Seascale Tuesday afternoon.
Per your feedback: the operational need is a 3rd dealer to set up the afternoon venue while the other two finish the morning event. The solver defaults to staffing every twin with 3 dealers. It will drop to 2 only when no other dealer has capacity — the soft penalty for an unfilled 3rd seat is high enough that the solver will accept significant extra travel before it lets a twin go understaffed.
Historical context: 11 of the 15 completed twin pairs in Jan-Apr ran with 2 dealers. You've indicated those were capacity compromises. The optimiser now closes that gap wherever it can.
Even though a dealer attends two venues on a twin day, it counts as less than two events for their quota. Twin = 0.75 units, standard event = 1 unit. The solver uses integer arithmetic (4×-scaled: twin = 3, standard = 4, cap × 4) for CP-SAT compatibility.
Roadtrips are the expensive edge case. These rules govern when and how they're allowed.
Any drive over 3h 30min from a dealer's home postcode counts as a roadtrip and burns one of their monthly roadtrip allowances. Flat threshold across all counties. The iterative roadtrip Pareto sweep (see Open Questions) will let you visualise the trade-off at different thresholds post sign-off rather than calibrating per-county.
A 3-day chain (stepping-stone Monday + far event Tuesday + stepping-stone Wednesday) counts as one roadtrip against the dealer's monthly cap, not three. This reflects how you actually manage long trips: one trip, one exhaustion cost, not three separate penalties.
Each dealer has a monthly roadtrip cap from the spreadsheet. Values of 0, 1 or 2. The cap also encodes the dealer's travel preference — no separate happy/reluctant tag is needed because the cap already expresses it.
Some dealers sit in tiers with bespoke rules. These override the general logic.
Gary picks his own events. His existing rota assignments are locked — the optimiser treats them as fixed and works around them. Gary is exempt from the weekly cap (H3), holiday blocks (H4), weekday blocks (H5), and roadtrip caps (R1-R3). If you've assigned Gary to an event, that assignment stands.
Why: Gary's assignments are manually approved by you. They can legitimately exceed formula-derived constraints. For example he can work 5 days in a week where the formula would cap him at 4, or take Jersey trips during a marked holiday week. Locking bypasses those false conflicts.
Used only when needed. Heavy preference penalty in the objective — the solver will prefer any non-reserve dealer with capacity. They are not inactive; they just sit at the back of the queue.
Moderate preference penalty. Preferred for high-volume days or when core dealers are exhausted, not for general coverage.
Friday assignments are soft-avoided (she can still work if it's the only option). The optimiser gets a small bonus for scheduling her at a Sheffield-area event on a Thursday (her preferred Thursday run).
These shape the objective function. The solver trades them off against each other according to their weights (higher weight = stronger pull).
Each unfilled seat adds 10000 to the cost. This is by far the heaviest penalty in the objective. It's why the solver will accept long drives to fill a seat, and why twins default to 3 dealers.
Each event a dealer is below their weekly target adds 5000 to the cost. This is what stops the solver from benching local dealers just because they're close to fewer events.
Each roadtrip chain start adds 1500 to the cost, on top of the drive-time cost. Equivalent to about 25 hours of "virtual" driving. This is what makes the solver treat roadtrips as a last resort.
Each time a reserve dealer (AW, JM, OR) is used adds 800 to the cost. Keeps them in reserve.
Each time a substitute dealer (ET, LR) is used adds 200. Lower than reserves — they get called in before reserves but after core dealers.
The primary objective once constraints are met. Every minute of drive time across the whole fleet adds 10 to the cost.
Tiebreaker for fairness. Caps the worst-travelled dealer's total. Only kicks in when the primary total-travel objective is tied.
When the same dealer works the same county on consecutive days, the objective gets a 30-point bonus (reduces total cost). This pulls the solver towards consecutive-day clustering.
What the optimiser assumes when reading your spreadsheet.
Current matrix gaps: Gwynedd and Merthyr Tydfil — filled by Haversine stage 3. Each run prints a summary of fallback usage.
The optimiser produces suggestions up to 6 weeks ahead. Beyond that, too many new events get added to the calendar and the rota becomes unstable. 6 weeks balances forward visibility with stability.
A dealer is active if their Jan-Apr or May-onwards yearly quota is greater than zero. The May-onwards check matters for dealers who start mid-year.
An event is a twin if its Type field contains the word "Twin". Twin pairs are grouped by (date, county). Venues with different area names but the same county on the same date form a pair.
Events come from three sources: the main Rota tab, Liam's extra_events (Jan-Apr historical), and the May Events tab. Duplicates are removed by (date, area) comparison using case-insensitive matching ("Barrow Upon" vs "Barrow upon" are treated as the same event).
Jersey events are handled outside the optimiser. Liam selects the Jersey team manually (it's a political decision, not a travel-cost decision — ferry + accommodation changes the shape of the problem). The dealers picked for a Jersey week are then marked unavailable for normal events that same week, so the optimiser doesn't double-book them.
Items parked waiting on your input. None of these block sign-off — they're opportunities to sharpen the tool after the first review cycle.
These are known open items that we'll bolt onto the tool after the first review cycle. None of them block sign-off. Listing them here so you sign knowing what's still coming.
Once you sign, this is the spec the optimiser runs against. Any change after sign-off goes through a versioned update. The open questions above can still be answered and bolted on, but the hard rules in this document become the contract.
If any rule in here doesn't match how you actually run the rota, flag it before signing. It's easier to fix now than unpick later.
I've reviewed the rules in this document. They reflect how I want the optimiser to staff the rota. Any deviations from my current practice are either bugs to fix in the data, or cases I'll confirm after running the tool against the live May rota.