Ensuring scalability and reusability of spreadsheet linear programming models

Abstract 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.