VEHICLE ROUTING OPTIMIZATION FOR PARCEL PICKUP AND DELIVERY USING VRP SPREADSHEET SOLVER: A CASE STUDY OF BTF COMPANY

Abstract

     This study aims to determine optimal parcel pickup and delivery routes for a case study company, BTF, by applying the Vehicle Routing Problem Spreadsheet Solver (VRP-SS). In previous operations, delays in parcel pickup, handling, and delivery were frequently observed due to uncertainties and the absence of systematic route optimization, as drivers tended to rely on habitual routes rather than optimal daily planning. Data on pickup and delivery operations were collected for one working day with a relatively high customer volume. The dataset consisted of 136 customers in total, including 41 pickup customers and 95 delivery customers. Six box-type pickup trucks were utilized for the operations, with a working duration of 10 hours per day. The collected data were subsequently processed to determine latitude and longitude coordinates, along with relevant operational constraints, for route optimization analysis. The analysis was conducted under three scenarios: (1) 10 pickup customers and 10 delivery customers, (2) 30 pickup customers and 80 delivery customers, and (3) 41 pickup customers and 95 delivery customers. The results obtained from the VRP-SS for all three scenarios indicate that the optimized routes satisfy all specified constraints and operational conditions. Furthermore, in Scenario 3, a comparison between the pickup and delivery times generated by the VRP-SS solution and the original time windows revealed significant improvements. Vehicle 1 achieved a time reduction of 18.67%, Vehicle 2 of 19.67%, Vehicle 3 of 12.50%, and Vehicle 4 of 19.50%. In addition, the VRP-SS results suggested that only four vehicles were required instead of the original six, representing a vehicle reduction of 33.33%.