Ensuring scalability and reusability of spreadsheet linear programming models

作     者：LeBlanc, Larry J. Grossman, Thomas A. Bartolacci, Michael R. 

作者机构：Vanderbilt Univ Owen Grad Sch Management Nashville TN 37203 USA Univ San Francisco Sch Management San Francisco CA 94117 USA Penn State Univ Informat Sci & Technol Reading PA 19610 USA 

出 版 物：《OMEGA-INTERNATIONAL JOURNAL OF MANAGEMENT SCIENCE》 (国际管理科学杂志)

年 卷 期：2019年第84卷

页      面：55-69页

核心收录：

学科分类：12[管理学] 120202[管理学-企业管理（含：财务管理、市场营销、人力资源管理）] 0202[经济学-应用经济学] 02[经济学] 1202[管理学-工商管理] 1201[管理学-管理科学与工程(可授管理学、工学学位)] 

主　　题：Linear programming Spreadsheet modeling Model scaling OR practice Excel Code re-use 

摘      要：Linear programming models implemented in spreadsheets are understood to be difficult to reuse, whether with modified data that increases or decreases model scale (such as routine model maintenance), as well as with new data (such as deploying a model to a new business setting). The difficulty arises because spreadsheets commingle cell formulas with data, which requires editing cell formulas when the data changes. We provide a novel technique to implement a linear programming model in a spreadsheet that allows for full re-use of the spreadsheet code. It robustly accommodates modified or new data, and enables a spreadsheet LP easily to be reused or even deployed to a new setting with an entirely new dataset. This technique applies to any linear programming model up to approximately 1 million non-zero constraint coefficients, and operates in native Excel without use of macros or VBA. Spreadsheet LP models can now be re-used, re-deployed, and re-optimized as easily as with algebraic software. (C) 2018 Elsevier Ltd. All rights reserved.