PERANCANGAN SISTEM PENGENDALIAN SUKU CADANG BERBASIS METODE SAFETY STOCK DAN REORDER POINT MENGGUNAKAN MICROSOFT EXCEL VISUAL BASIC FOR APPLICATIONS DAN MACRO DI BENGKEL OTOXPERT CABANG ROXY

Abstract

Effective spare parts inventory control is a crucial factor in maintaining smooth workshop operations and ensuring service quality. At OTOXPERT Workshop Roxy Branch, stock management is still carried out manually, which creates risks of both stockouts and overstocking that can disrupt vehicle repairs and reduce customer satisfaction. This study aims to design an inventory control system based on Microsoft Excel Visual Basic for Applications (VBA) and Macros, applying the Safety Stock method to maintain minimum availability and the Reorder Point (ROP) method to determine the optimal reorder level. The research methodology involves analyzing historical demand data, lead time, and variability of spare parts requirements. The results show that the calculated Safety Stock for the sample spare part “TMOLITE 10W30 SN 3.5L†is 11.23 units, rounded to 11 units, whereas previously there was no standardized calculation, often leading to stock shortages. The Reorder Point (ROP) was determined at 16.76 units, rounded to 17 units, compared to the old system which only placed orders after stock depletion or based on technicians’ visual checks. The implementation of the new system with an interactive dashboard, automated calculations, and reorder indicators improves operational efficiency, reduces the risk of both stockouts and overstocking, and provides a practical solution without requiring complex ERP software. Therefore, this system is feasible to be implemented in medium-scale workshops such as OTOXPERT