Solving Classic Discrete Facility Location Problems Using Excel Spreadsheets

Abstract

Listen

Translate article

There are a variety of discrete facility location models that have practical relevance for operations management and management science courses. Integer linear programming (ILP) is the standard technique for solving such problems. An alternative approach that is often conceptually appealing to students is to pose the problem as one of finding the best possible subset of p facilities out of n possible candidates. I developed an Excel workbook that allows students to interactively evaluate the quality of different subsets, to run a VBA macro that finds the optimal subset, or to solve an ILP formulation that finds the optimal subset. Spreadsheets are available for five classic discrete location models: (1) the location set-covering problem, (2) the maximal covering location problem, (3) the p-median problem, (4) the p-centers problem, and (5) the simple plant location problem. The results from an assignment in a master’s-level business analytics course indicate that the workbook facilitates a better conceptual understanding of the precise nature of the discrete facility location problems by showing that they can be solved via enumeration of all possible combinations of p subsets that can be drawn from n candidate locations. More important, students directly observe the superiority of ILP as a solution approach as n increases and as p approaches n/2.