The Rota Tool

Generate optimised dealer assignments for any week. Download as CSV ready to paste into the rota sheet. The optimiser respects every dealer constraint (capacity, holidays, roadtrip caps) while minimising total travel.

Future Weeks Compare Current vs Optimised Map View

Forward Planning

Future Weeks

Upcoming events from the Roadshow Tracker that don't yet have dealer assignments. The optimizer pre-populates an efficient rota. Download as CSV to paste into the Google Sheet.

Production draft. Built using monthly quotas, roadtrip caps, holiday data, and dealer-specific constraints from Liam's spreadsheet. Expect minor manual adjustment for edge cases (e.g. last-minute dealer availability changes), but the structure should be operational.

Week

Hours per Dealer (this week)

Date	Day	Area	Region	Dealer 1	D1 Travel	Dealer 2	D2 Travel	Dealer 3	D3 Travel	Total Travel

What If

Optimised Rota

The CP-SAT constraint solver reassigns dealers to minimise total travel across the fleet, while respecting all hard constraints (capacity, holidays, one event per day) and rewarding geographic clustering.

What CP-SAT Does

CP-SAT (Constraint Programming with Satisfiability) is Google's open-source constraint solver. It takes a set of decisions (which dealer goes to which event), a set of rules (max 1 event per day, respect holidays, fill all seats), and an objective (minimise total travel). It then explores the solution space intelligently, proving it has found the best possible assignment or the best it can find within the time limit. Unlike a greedy algorithm which makes local decisions one event at a time, CP-SAT considers all events and all dealers simultaneously.

How It Optimises

For each week (~60 events, ~40 dealers = ~2,400 possible assignments), the solver creates a binary decision variable for every dealer-event pair, plus overnight-stay variables that let the same dealer chain across two days. It enforces hard constraints (holidays, capacity, one-per-day, Jersey exclusion) and minimises a weighted objective: total travel (weight 10) + worst individual travel (weight 1) - clustering bonus (weight 3) + chain penalty (10h per chain). On chain days the travel cost is prev-venue-to-current-venue (they stayed nearby), not home-to-venue — so the solver sees the real cost. Solves in under 2.5 seconds per week.

2.6h

Current avg drive per dealer per week

2.2h

Optimised avg drive per dealer per week

232.8h

Total saved across 14 weeks

16.9%

Reduction in total fleet travel

Projected Financial Impact

16.6h

Hours saved per week

831h

Hours saved per year

£349

Saved per week

£17,460

Saved per year

Assumptions: 50 working weeks per year. Fuel cost at 15p/mile at 40mph average = £100/week in fuel. Dealer time valued at £15/hour (opportunity cost of driving vs being at an event) = £249/week. Does not include reduced vehicle wear, fewer hotel nights, or dealer satisfaction improvements. Actual savings will vary with Liam's per-dealer travel constraints (some road trips are operationally necessary regardless of optimisation).

How to read the table below: Each event shows the current dealer assignment and the optimised assignment side by side. Drive times are colour-coded (green <1.5h, amber 1.5-3h, red >3h). Changed assignments are highlighted. The solver considers all events and all dealers simultaneously, finding the globally optimal assignment.

Week Show only reassigned events

			Current						Optimised
Date	Area	County	D1	Hrs	D2	Hrs	D3	Hrs	D1	Hrs	D2	Hrs	D3	Hrs

Geographic View

Travel Map

Dealer-to-event travel lines. Red = current assignment, green = optimised. Toggle to compare.

Week Current Optimised Both

Rota Analysis

How your current rota performs against six metrics: fill rate, travel efficiency, fairness, long trips, geographic clustering, and workload balance. The data behind the optimiser.

Scorecard Criteria Travel Dealers Rota Detail Worst Trips Trends Method Roadtrip Budget

Most Recent Week

Rota Scorecard

Week 2026-W18 · w/c 27 Apr: 34 events, 77 dealer assignments, 59.9h total travel.

Fill rate: 100/100. Are all dealer seats filled? 0 unfilled seats across 34 events. 100 = every event fully staffed.

Travel efficiency: 100/100. How far are dealers driving on average? Mean 0.8h per assignment. 100 = everyone under 1 hour. 0 = average 3h+ drives.

Fairness: 90/100. How evenly is travel spread across dealers? CoV = 0.76. Lower CoV = more even. Some variance is inevitable since dealers have different capacities and home locations. 100 = very even, 0 = a few dealers doing all the heavy driving.

Long trips: 92/100. What proportion of assignments are 3+ hour drives? 2 out of 77 this week. Road trips are part of the business, but fewer is better. 100 = none, 0 = a third of all assignments are 3h+.

Clustering: 54/100. When a dealer works consecutive days, are they in the same area? 21% of consecutive-day assignments stay in the same county. Higher = less wasted travel between days. 100 = 40%+ same-county.

Workload balance: 96/100. Are events spread evenly across active dealers relative to their capacity? CoV = 0.38. Some variance is expected (dealers have different weekly targets). 100 = proportional to capacity, 0 = very uneven.

How We Score

Scoring Criteria

Each metric is scored 0-100 based on thresholds calibrated to WBV's operations. The overall score is the average of all six.

Fill Rate

Percentage of dealer seats filled across all events. 2 seats for standard events, 3 for twins (one buyer sets up the second venue while the other two finish the first). 100 = every seat filled. 0 = no seats filled. Straightforward: unfilled seats = lost revenue.

Travel Efficiency

Mean drive time per dealer-event assignment. 100 = average under 1 hour (close to home). 50 = average 2 hours. 0 = average 3+ hours. Based on the pre-computed drivetime matrix from dealer home to event county.

Fairness

Coefficient of variation (std dev / mean) of total weekly travel hours per dealer. Measures how evenly travel burden is spread. 100 = CoV under 0.5 (very even). 0 = CoV over 3.0 (a few dealers doing all the driving). Some variance is inevitable because dealers have different capacities and home locations.

Long Trips

Percentage of all assignments where the drive exceeds 3 hours one-way. Road trips are a normal part of the business (Cornwall, Wales, etc.) so this isn't penalised harshly. 100 = no 3h+ trips. 50 = about 17% are 3h+. 0 = a third of all assignments are 3h+.

Clustering

When a dealer works consecutive days, what percentage stay in the same county? If Monday is West Yorkshire, does Tuesday stay in West Yorkshire too? 100 = 40%+ of consecutive-day pairs are same-county. 0 = dealers change county every day. Higher clustering = less wasted travel between days.

Workload Balance

Coefficient of variation of events-per-dealer across the week. 100 = CoV under 0.3 (everyone working close to their target). 0 = CoV over 2.3 (some dealers overloaded, others idle). Dealers have different weekly targets (1-4), so perfect equality isn't the goal; proportional loading is.

Travel Analysis

Dealer Travel

Total travel hours per dealer across all analysed weeks. Sorted by total travel (highest first).

Dealer Profiles

Dealer Summary

Every active dealer with their home location, capacity, total events, and travel across the analysed period.

Name	Initials	Home	Max/Wk	Events	Total Travel	Avg Travel
Herman Hazu	HH	Leeds	4	64	126.3h	2.0h
Matt Case	MC	Ellesmere Port	3	66	92.2h	1.4h
Chris Kodic	CK	Darlington	4	36	86.9h	2.4h
Fred Bickle	FBI	Torquay	4	51	79.4h	1.6h
Dan Stone	DS	Leeds	3	47	72.9h	1.6h
Colleen Phelan	CP	Bromsgrove	4	66	67.0h	1.0h
Neil Church	NC	Bristol	3	52	67.0h	1.3h
James Reynolds	JR	Kingswinford	4	63	66.7h	1.1h
Sally Brogden	SB	Boston	3	52	65.9h	1.3h
Rebecca Sandberg	RS	Lyneham	3	54	62.4h	1.2h
Ella Vasey	EV	Newmarket	3	41	60.7h	1.5h
Ishy Khan	IK	Aldershot	4	57	59.6h	1.0h
Chloe Cropper	CC	Stanningfield	4	52	57.5h	1.1h
Christophe Hearn	CH	Reigate	4	51	55.9h	1.1h
Laura Wray	LW	Warrington	4	40	51.5h	1.3h
Kate Talbot	KT	Waterlooville	3	42	48.7h	1.2h
Christopher Christoforou	CCC	Enfield	4	53	48.5h	0.9h
Adam Chapman	AH	Hartlepool	5	55	46.9h	0.9h
Gary Trainor	GC	Smithy Bridge	3	48	42.1h	0.9h
Danielle Lizdek	DL	Stevenage	4	49	42.0h	0.9h
Lee Rushworth	LR	Houghton le Spring	1	14	39.5h	2.8h
Lauren Gardner	LG	Methwold	2	40	36.1h	0.9h
Patricia Negus	PN	Ushaw Moor	3	55	35.4h	0.6h
Josh Mousavi	JMO	Wakefield	4	21	35.1h	1.7h
Charlotte Cropper	CSC	Oldham	3	35	34.0h	1.0h
Harriet Dakin	HD	London	3	26	29.7h	1.1h
Edd Thomas	ET	Wootton Bassett	1	23	28.2h	1.2h
Amanda Roberts	AR	Bewdley	3	23	26.9h	1.2h
Asha Nayak	AN	Bracknell	3	23	21.3h	0.9h
Paul Waterhouse	PW	Dereham	2	22	17.1h	0.8h
Andrew Wood	AW	Cambridge	1	13	16.6h	1.3h
Kieran Moule	KM	Leeds	1	11	16.6h	1.5h
Gemma Stanley	GS	Leeds	2	22	14.0h	0.6h
Nicola Whittaker	NW	Brixton	3	8	11.9h	1.5h
Charlotte Swindell	CS	Blyth	4	23	11.5h	0.5h
Francis Bellwood	FB	York	1	8	7.8h	1.0h
James Moulds	JM	Stafford	0	4	3.2h	0.8h
Oliver Reay	OR	Cheltenham	1	1	0.7h	0.7h

Event Detail

The Rota

Every event with dealer assignments and drive times. Colour-coded: green under 1.5h, amber 1.5-3h, red over 3h. Select a week to view.

Week

Date	Day	Area	County	Region	Dealer 1	D1 Travel	Dealer 2	D2 Travel	Dealer 3	D3 Travel

Flagged

Longest Trips

Top 20 longest individual dealer trips across all weeks. These are the assignments with the most room for improvement.

Dealer	Area	County	Date	Drive Time
CK	Launceston	Cornwall	2026-03-23	6.9h
CK	Dawlish	Devon	2026-03-24	6.4h
CK	Sherborne	Dorset	2026-03-27	6.1h
LR	Denmead	Hampshire	2026-02-11	5.8h
EV	Bude	Cornwall	2026-04-07	5.8h
CK	Ferndown	Dorset	2026-03-25	5.7h
CK	Northmoor Green	Somerset	2026-03-26	5.5h
HH	Pembroke	Pembrokeshire	2026-03-11	5.5h
CK	North Petherton	Somerset	2026-03-26	5.4h
HH	Haverfordwest	Pembrokeshire	2026-04-21	5.4h
DS	Pembroke	Pembrokeshire	2026-03-11	5.4h
EV	Tavistock	Devon	2026-04-08	5.3h
CH	Hayle	Cornwall	2026-04-14	5.3h
JMO	Haverfordwest	Pembrokeshire	2026-04-21	5.2h
LR	Writtle	Essex	2026-02-03	4.9h
HH	Carmarthen	Carmarthenshire	2026-03-10	4.8h
DS	Ystradgynlais	Powys	2026-02-03	4.7h
DS	Carmarthen	Carmarthenshire	2026-03-10	4.7h
LR	Ware	Hertfordshire	2026-02-02	4.7h
JMO	Chichester	West Sussex	2026-04-30	4.6h

Week by Week

Rota Trends

How the rota score and total travel have changed over time.

How We Did This

Methodology

Data source: WBV Rota Google Sheet, pulled live via service account. Tabs used: Rota (events + assignments), Dealers (capacity), Drivetime (travel matrix), Holidays (blackout dates).

Drive times: Pre-computed matrix of dealer home location to county (in hours), maintained by WBV ops team. Missing entries estimated via Haversine distance at 50mph with 1.3x road factor.

Scoring: Six metrics scored 0-100 and averaged. Fill rate (seats filled), travel efficiency (mean drive time), fairness (coefficient of variation of per-dealer travel), road trips (count of 3h+ drives), clustering (consecutive-day same-county %), workload balance (CoV of events per dealer).

Optimiser: The Tool tab above runs a Google OR-Tools CP-SAT constraint solver over this data. Produces optimised dealer assignments for every week, before/after comparison against the manually-built rota, and CSV export ready to paste into the rota sheet.

Trade-off explorer

Roadtrip Budget

Liam's original question: how does the optimised rota change if every willing dealer is allowed 0, 1, 2, … up to 6 roadtrips per month? More trips should cut driving but cost more nights away. The chart below sweeps that cap and shows the average outcome across willing travellers.

How to read: x-axis is the monthly roadtrip cap applied to every willing dealer (cap-0 dealers still stay at 0 regardless). Gold line = average drive hours per willing dealer across the period. Red dashed line = average nights away. Faint horizontal lines = Liam's manual rota for reference.

For this sweep the virtual roadtrip penalty is dropped to 0h so the cap is the binding constraint — otherwise the default penalty keeps roadtrips near zero at every cap and the sweep reads flat. With the penalty off, roadtrips get used whenever they genuinely save driving (under the new chain-aware cost model: drive cost on chain days is prev-venue-to-current, not home-to-venue).

Finding. Even with the penalty off, the optimiser barely uses roadtrips (0.07–0.11 chains per willing dealer, vs 1.43 nights per dealer in Liam's manual rota). Drive hours land around 30h per dealer per week regardless of the cap — a ~17h reduction against Liam's 47h/week. Conclusion: at current fleet size and geography, the travel savings from chains are marginal. The tool naturally schedules for local work.

About the Tool

Background on why this tool exists, what goals it optimises for, the data it consumes, and how the algorithm works. The "why" behind the three tabs above.

Problem 10 Goals Data Approach Build Phases

The Challenge

The Problem

Every week someone assigns ~40 dealers to ~60 events across England and Wales. Each event needs 2-3 dealers. Each dealer has a home, a weekly cap, holidays, and personal constraints. The assignment needs to minimise travel while keeping it fair.

Why it's hard: with 40 dealers and 60 events there are roughly 40¹²⁰ possible rotas. Most are bad. The current manual process works but takes hours of Liam's time per week, relies on institutional memory, and can't easily prove a different assignment would save 20 hours of driving. A constraint solver explores the space in seconds and guarantees every rule is respected.

Requirements

Liam's 10 Goals

The priorities in the order Liam ranked them. Goals 1-3 are non-negotiable. 4-10 are optimised as far as possible without breaking the top three.

01 — Fill every seat. 2 dealers minimum, 3 for twin events and high-traffic shows. Empty seats = lost revenue.

02 — Minimise total travel. Primary objective. Then as a tiebreaker, minimise the spread (standard deviation) of per-dealer travel. Utilitarian: respect hard constraints, total first, fairness second.

03 — Minimise roadtrips. Only use long-distance assignments when they're necessary to fill seats. Roadtrip as last resort, not default.

04 — Keep trips under 90 minutes where possible. 1.5h is the comfort threshold. Beyond that the dealer is losing their day to driving.

05 — Hit each dealer's ideal events per month. PAYE have contracted targets; contractors have preferred volumes. Hit these, not just the weekly max.

06 — Hit each dealer's ideal events per week. Related to monthly targets at a different cadence. Some prefer 4-on-1-off, some prefer 3-day weeks.

07 — Fill the 3rd seat at twin events and high-traffic shows. Where predicted attendance justifies it, add a third dealer. Feeds into the attendance prediction model (Model 6).

08 — Train new dealers in 3rd seats. New hires learn by working alongside experienced dealers at busy events.

09 — Limit roadtrips per dealer. Willing dealers: max 2/month. Reluctant: 1/month. Exemptions: zero. The tool needs to know who's who.

10 — Respect individual constraints. Family commitments, specific day rules, geographic limits, pair preferences. Hard constraints, not soft penalties.

Liam's answer: utilitarian rota. Minimise total travel first. Respect all per-dealer constraints. Then as a tiebreaker, spread the remaining travel as evenly as possible. In the optimiser this is a lexicographic objective: total travel (weight 10) dominates, standard deviation (weight 1) only matters when two rotas are otherwise similar.

Per-Dealer Travel Constraints

Each dealer's monthly roadtrip cap (0, 1 or 2) in the dealer sheet already encodes their travel preference:

Cap 2 — willing traveller (happy) — up to two roadtrip chains per month. Preferred for unavoidable long trips.
Cap 1 — one per month (neutral) — available for long trips when necessary, capped at one chain per month.
Cap 0 — never (reluctant / exempt) — local only. The solver will not assign any drive over 3.5h, regardless of seat-filling pressure.

Chain rules: a multi-day chain (stepping-stone + far event + stepping-stone) counts as one roadtrip against the dealer's monthly cap, not three.

Inputs

Data

Everything feeds live from the WBV Google Sheets via a service account. No manual exports.

Rota Sheet — scheduled events (Feb-Apr 2026) with area, date, venue, postcode, county, region, event type, and current dealer assignments. Each row tracks weekly and monthly workload counters.

Dealer Profiles — 43 dealers (39 active) with initials, names, weekly caps, employment type. PAYE contracted targets and contractor preferences both modelled.

Drive Time Matrix — Liam's maintained matrix of dealer home to county centroid. Overlaid with postcode-precision Haversine for dealer-to-event drives where we have postcodes for both ends.

Holidays & Blacklists — 317 holiday entries across 33 dealers at date-level granularity. Plus per-dealer daily blocked weekdays pulled from comments ("Fridays only", "never Mondays" etc).

How It Works

The Approach

Google OR-Tools CP-SAT — the constraint solver Google uses internally for scheduling problems at scale.

Why CP-SAT. Roughly 2,400 binary decisions per week (60 events × 40 dealers). CP-SAT explores this space intelligently, handles hard and soft constraints natively, solves in under 10 seconds, and produces deterministic output (same inputs, same rota).

Hard vs soft. Hard constraints never violated: seat counts, one-per-day, weekly capacity, holidays, blocked weekdays, per-dealer roadtrip caps. Soft objectives: minimise total travel, minimise max individual travel, reward geographic clustering, respect region preferences.

Two-pass solver. First pass tries to fill every seat under all constraints. If the dealer pool is too thin for a particular day, the second pass relaxes to "fill as many as possible" and flags the unfilled events in red for manual attention. The tool should handle 95%+ automatically and surface the edge cases.

Delivery

Build Phases

Built incrementally. Each phase delivers something usable.

Phase 1: Rota Analyzer. Shipped. Scores the current manually-built rota against the 10 goals. Total travel hours, per-dealer breakdown, fairness metrics, clustering score, worst trips, weekly trends. Lives in the Analysis tab above.

Phase 2: Rota Optimizer. Shipped. Produces an optimised dealer assignment for any week using CP-SAT. Before/after comparison, per-assignment explanations, CSV export ready to paste into the rota sheet. Lives in The Tool tab above.

Phase 3: Continuous & Integration. In progress. Nightly automated refresh via Mac Mini cron, Google Sheets write-back so results populate directly, in-UI constraint editing so Liam can experiment with thresholds. Tracked in the Project Tracker →

What-if scenarios (dealer on holiday, new hire in Birmingham, volume changes) sit with the separate Area Capacity & Scheduling deliverable — that's a capacity question, not a rota question.