Route Planning by Hand: Distance Matrices, Nearest-Neighbour Tours, 2-Opt Improvements, Time Windows, Vehicle Capacity & Sweep Routes

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn the grid coordinates of a fictional depot and seven stops into road kilometres: one distance by Pythagoras, a full 8 × 8 matrix with INDEX/MATCH, the farthest stop and the kilometres of the worst possible plan – one trip per stop.
• Continue a fictional nearest-neighbour tour from the depot: at each step MINIFS finds the shortest leg to a stop not yet visited, INDEX/MATCH names that stop, and the cumulative kilometres grow until the van is back at the depot.
• Measure the fictional nearest-neighbour tour, test five 2-opt moves – each reverses a stretch of the tour and swaps two legs for two others – keep the move with the biggest gain and work out the improved tour length.
• Schedule the fictional improved tour against each customer's time window: drive minutes, arrival times, service starts with MAX, waiting minutes, late stops and the time the van is back at the depot.
• Load a fictional 10-stop route into vans of 40 crates: cumulative crates, the van each stop goes on when a new van starts as soon as the next stop no longer fits, the load per van and the lower bound on the number of vans.
• Plan a fictional Friday with the sweep method: the angle of every stop seen from the depot with ATAN and a quadrant correction, the sweep order with RANK.EQ, a van per sector and the crates in each sector against the van capacity.
• Cost three fictional sweep routes: drive and service hours, a route cost from fictional rates per km, per driver hour and per van day, the cost per stop on each route and for the whole day, and the route that serves its stops most cheaply.
• Compare three fictional plans for the same seven stops – nearest neighbour, the 2-opt improvement and a two-van sweep – on road km, driver hours, cost, savings and late stops, pick the cheapest plan that keeps every time window and chart the costs.