Case study · a large supermarket chain · home delivery

A rota for 294 shifts a week & not one rule broken.

One store’s home-delivery drivers, about 66 of them, and a week of shifts built around every rule they work to.

Real case · store and chain not named

One store, about 66 drivers & one spreadsheet.

Every driver’s contract, availability and notes lived in one sheet, and the week’s rota was built by hand on top of it.

Illustration · example names

driver tracker.xlsx

Driver	Contract	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Notes
Sam	30 h	6–14	6–14	off	7–13	any	off	off
Robin	16 h	off	14–23	14–23	off	18–23	18–23	off	no movement
Priya	24 h	8–14	off	8–14	8–14	off	8–14	8–14	earliest start 8am
Jo	36 h	any	any	12–20	any	off	off	6–12
Marek	20 h	18–23	off	18–23	18–23	off	off	19–23	evenings only
Shaz	30 h	6–12	6–12	6–12	off	off	6–18	off	days off together
Leah	24 h	12–18	12–18	off	12–18	12–18	off	off	no back-to-back long days
Sue	24 h	off	8–14	8–14	8–14	off	off	13–19	no movement

movedquerytrainingother deptholiday

no movement
earliest start 8am
days off together
no back-to-back long days

Eight rules & every driver’s own notes.

The store’s rules, as the tool keeps them. Every one of them sits on top of each driver’s availability.

The real rules

Never more than 12 hours’ work in any 24.
At least 10 hours between shifts, including Sunday into Monday.
Evening shifts stand alone.
A morning and afternoon can join into one long day, with an hour’s break.
Never more than 3 hours over contract.
No availability window moves more than an hour.
“No movement” means no movement.
At most two drivers on one van.

After the eight come every driver’s own notes: earliest start, days off together, no back-to-back long days.

73 rules broken in the rota built by hand.

Every shift was covered, so on paper the week worked. Checked rule by rule, it didn’t:

27 long days joined the wrong way
24 times more than 12 hours’ work in 24
18 evening shifts joined to another
3 drivers over contract by more than 3 hours
1 driver’s own note missed

Real count Illustration · where the dots sit

Thousands of decisions & one best answer.

Every driver, shift and day became a yes or a no, over 2,000 of them. The rules became sums the answer had to keep, and the cost was paid hours.

It found its best rota in 41 seconds, then spent 39 more minutes proving nothing could beat it by more than 1%.

The real search log

The search, from its first answer to the end of the run. Score: lower is better.
Measure	First answer, 3 seconds in	After 40 minutes
Best rota found	134.25	118.5, found at 41 seconds
Best possible	117.08	117.64
Gap	12.8%	0.7%

From 73 rules broken to none.

Checked twice: by the sums, then by separate code.

Real result

54.75 fewer paid hours a week & about £3,000 a month.

Two part-time roles instead of a 70-hour hiring drive. That’s against the rule-clean proposal it replaced: 1,627.5 paid hours a week down to 1,572.75, every shift still covered.

Real figures

Hours a week: the rule-clean proposal against the tool’s rota
Measure	Proposal	Tool’s rota
Paid hours	1,627.5	1,572.75
New-hire hours	70	27.75
Double cover: two drivers on one van	71.75	22.25
Hours over contract	50	37.5

Nobody loses contracted pay.

The new rota went back into the store’s own spreadsheet, in the same layout, with a colour legend and a column saying what changed. The manager got a six-page pack to go with it.

27 small asks of drivers, none over an hour
None for anyone whose note says “no movement”
Everyone stays on their full contracted pay

Real outputs · redrawn with example names

driver tracker.xlsx · rebuilt

Driver	Shift	Changes
Sam	7:00–12:30	Thu: starts an hour earlier
Robin	19:15–23:00
Jo	13:30–19:00	Wed: an hour added
Hire A	19:15–23:00	New part-time role
Marek	18:15–22:00	Shares a van on Fri

Within availabilityExtended by an hourMoved, an hour at mostTwo on one vanNew hire

−54.75 ha week

small asks, none over an hour

Duet

Your turn. Which job runs on a spreadsheet only one person understands?

Got a spreadsheet like this? Tell us the job & get a fixed quote.

Or email [email protected] · We’ll come to you in Bath or Bristol.