Spreadsheet Inventory Simulation and Optimization Models and Their Application in a National Pharmacy Chain

This paper presents a spreadsheet model for the solution of inventory management problems that arise in pharmacy chain stores. Though there have been abundant spreadsheet models for inventory management, most of these models focus on the use of embedded functions to compute complicated formulas. The spreadsheet model presented in this paper, however, incorporates a simulation of the ordering process and an iterative procedure to search for near-optimal solutions, and is easy to understand by students and practitioners. The methodology has been successfully implemented in a national chain of pharmacies; and the spreadsheet model integrated into the curriculum of several engineering courses including inventory management, simulation, and optimization and it has provided an interactive environment for students to experience real-life inventory decision making. This paper is dedicated to the memory of Dr. Paul A. Jensen, whose widely adopted Excel add-ins have inspired our education and research in spreadsheet models and provided the foundation for our work with pharmacy inventory systems.