An application of business intelligence and optimization techniques to minimize expired medical inventory

This project aimed to develop an intelligent decision-support system for managing short-expired medical inventory through integrated product selection and transport distance optimization. By combining Visual Basic for Applications (VBA) for data handling and Python-based Genetic Algorithm (GA) for optimization, the system offers a cost-effective and scalable solution suitable for warehouse settings, particularly within the healthcare sector. The objective was to address both what products to pick by prioritizing those closest to expiration—and how to pick them efficiently by optimizing route paths with path smoothness awareness. The system is composed of three key modules: a VBA-based input interface for product and demand data collection, a Solver-based optimization benchmark using Excel, and a Python GA module for full-scale metaheuristic route optimization. The initial phase utilized linear programming to allocate expiring stock based on demand constraints, followed by a routing model grounded in the Travelling Salesman Problem (TSP) structure to minimize total picking distance. Hyperparameter tuning was conducted on key GA parameters including population size, mutation rate, and elite ratio to enhance model performance and ensure convergence. Experimental results showed that the GA model achieved up to 19.78% distance reduction over traditional worker-generated picking paths, with consistently smoother and more structured routes. The model also demonstrated the ability to generate multiple optimal solutions, enhancing operational flexibility. Despite Excel Solver’s computational limitations, its use in the early phase validated the problem structure before transitioning to Python. The final model balances solution quality with computational efficiency and highlights practical deployment potential in real warehouse operations. In summary, this study bridges a critical gap in inventory-routing research by combining expiry-aware prioritization and dynamic route optimization using accessible, non-intrusive technologies. It contributes both methodological and practical advancements to logistics planning in time-sensitive healthcare environments.