Scheduling our handymen efficiently

We've put quite a bit of effort over the last few weeks into tweaking our scheduling practices to try and reduce the time spent travelling between jobs (even our London handymen, on motorbikes, still can't actually teleport between appointments, so there is some downtime).

One of our longest serving handymen, Don, said to me at our Christmas party that he didn't think that his average travel distance between jobs had reduced at all in the four years or so he had worked for us. This surprised me, as we now have about three times as many handymen in London as we did when Don first joined, so we should be able to keep each handyman in a tighter area.

At the time, I briefly considered actually measuring the average actual distance between every job we had ever done, but quickly dismissed this as computationally too complex.

Then I had a flash of inspiration late yesterday afternoon and figured out a way we could get the right data out of our system to do a full analysis of inter-job distance. So I built this monster spreadsheet, incorporating (for the first time since I did GCSE maths) Pythagoras' theorem.

Spreadsheet turned out to be such a monster that, for first time since working as a financial analyst at OC&C, I had to switch Excel to manual recalc (only really hardcore spreadsheet nerds like me ever need to do that). And then when I hit F9 to calculate everything, my PC sat whirring away for 2 full hours before telling me:

Our average inter-job distance has dropped by 9% since 2004 (first year we have good quality data available), from 4.6km to 4.2km.

I think it could be much lower: there are some handymen, in some months, for whom we have managed to keep inter-job distance below 3.0km, a further 30% below our recent average.

Anyway, now we have the tool in place to monitor this we can work on keeping that distance down further (and it only needed 2hours to churn through every job since mid-2004, some tens of thousands of data points; manages to figure out the distances for a few days at a time in just a few seconds).

Holy Grail is to keep every handyman on the same street all day (happens sometimes!). I have heard that there is a chap in Chelsea who washes cars on Tregunter Road, and makes his entire living out of that one street. Everyone uses him, and by the time he has worked his way to the end of the street, it is time to start again at the beginning. Not sure if this is true, perhaps any readers who are fortunate enough to live on Tregunter Road could confirm / refute?