Imagine one day you’re running a business or sitting as a transport planner with one task in mind, how do I delivery my product in the right time and the most efficient way? This enigma is often related to one of the famous Vehicle Routing Problem, which is a combinatorial optimization and integer programming problem to achieve the optimal set of routes for a fleet of vehicles to traverse in order to deliver to a given set of customers. Of course nowadays you can easily solved this by utilizing a thousand dollar worth of software. But in this series of post, I’d like to take a basic view on how to solve this by using a simple spreadsheet tool.

##### Basic component

First of all, to we have to be familiar with common objectives of VRP such as;

- Minimize the global transportation cost based on the global distance traveled as well as the fixed costs associated with the used vehicles and drivers
- Minimize the number of vehicles needed to serve all customers
- Least variation in travel time and vehicle load
- Minimize penalties for low quality service

With these objectives, of course any delivery problem can effortlessly rises into much complicated problem. In a way that VRP can have several variation, e.g. VRP with Time Windows (VRPTW), Pickup and Delivery (VRPPD), and many others. We will talk about that more later.

One of the basic idea of VRP is how to derived the shortest path, and assign the node to deliver. Subsequently, we can check whether the assign node will contradict with defined constraint. For example, if we assign X node, with certain demand, does it comply with vehicle capacity? Or if the service time adhere with defined operating time windows?

##### Sample of Problem

Let’s establish a sample problem where as you need to deliver a certain amount of water jugs from a distribution center in Surabaya, Indonesia. We will have the demand value or daily order from the sales team for each outlets. The outlets will spread out across the city. In the most sophisticated way, you can easily mapped out this nodes using a Geographical Information Systems (GIS) database, which however requires investment. So instead, we try an old school way of manually mapping with spreadsheet.

##### Connecting the dots

Once we collect the nodes location data on X, Y basis, we can create a logic to build distance matrix, which in this case is an euclidean one . It is a simple and useful tools to give us the insight of distance data within every nodes. This data will required later as a basis to assign the nodes (the shortest nodes will be selected).

We set up the constraints to the problem such as truck speed (you don’t want to be speeding right?) and truck capacity. After that the logic would follow like this.

Of course if we write in visual basic language it would be a different, so I will leave spreadsheet including it’s code free in this post. Pardon my silly codes, it’s far from perfect, I know.

##### Result

The result is a set of routes with sequence of delivery. In this case with dummy demand data, we can see that the grouping/clustering will be like these.

The result is there will be three different route or clustering. What if we want to downsized it into two only route with larger truck capacity? It turns out worked well.

##### Epilog

Is that the optimum result? No. Is that applicable to real life? Probably not. What we can develop from this, is that we add more real-life constrain such as time windows (or outlet operating time), road constraint (traffic, blockade, etc), and others. We will talk this one later. Just keep in touch and share your thought guys.

**Image courtesy**: Freepik

**Reference**:

[1] Dr. Güneş Erdoğan spreadsheet solver

**Spreadsheet**: get it | gist