PROPOSED IMPLEMENTATION OF ROUTE DETERMINATION AND SCHEDULING SYSTEM USING VBA EXCEL DASHBOARD WITH VRP SPREADSHEET SOLVER APPROACH ON GARBAGE PICKUP TO BANK SAMPAH INDUK CIMAHI CUSTOMER (SAMICI BANK)

Case Study: Bank SAMICI

Abstract

Bank Samici has received some complaints from its customers regarding the delays and postponement in picking up waste to customer units which, if presented, may reach 60% of the total customers weekly, or equal to 6-7 customers submitting their complaints per week. One of the delays and postponement in picking up garbage is caused by the waste pick-up scheduling
factor that is carried out at the end of the month, the weather factor is also a supporting factor, the delay in picking up garbage caused by weather factors reaches 1 day and the minimum time to reschedule is 2 days, while the error factor of officers in planning may take 1-2 days and the minimum time to reschedule takes 2 days. This causes inconvenience and the waiting list, which is caused by scheduling, route choices, and selection of operational vehicles by Bank Samci which is still done manually and has not been equipped with a support system to make the arrangement automatically. The purpose of this research is to determine out what kind of route and scheduling is required on garbage pickup to Samici Bank customers and to know what kind of system can support optimization of route determination and scheduling on garbage pickup to Samici Bank customers. The method used in processing the data is Vehicle Routing Problem with Time Window (VRPTW) with Large Neighborhood algorithm. Based on the research there obtained the results of print out solutions and print out visualization routes. In the first week there are as many as 17 customers who are scheduled to have their garbage picked up. The total optimal distance traveled on the first week route is 48.99 km. The optimal time taken on the first week's route is 125 minutes. The second week as many as 15 customers, with the total optimal distance taken on the second week route of 40.30 km. The optimal time taken on the second week route is 90 minutes. In the third week as many as 12 customers, with the optimal distance taken on the third week route of 37.92 km. The optimal time taken on the third week route is 99 minutes. In the fourth week as many as 12 customers. With the optimal distance traveled on the fourth week route 33.24 km. The optimal time taken on the second week route is 91 minutes. All routes that are taken begin and end at the Cimahi Main Waste Bank. Optimal route determination and scheduling can be done by implementing a support system that is VBA Excel Dashboard with VRP Spreadsheet Solver Approach.