Ensuring Scalability and Re-Usability of Spreadsheet Analytical and Optimization Models
16 Pages Posted: 2 Jan 2016 Last revised: 24 Sep 2016
Date Written: December 30, 2015
Spreadsheet optimization models are commonly understood to be difficult to scale up and down in size, reducing their utility for models that are large or will be reused. In contrast, an equivalent algebraic optimization model scales up and down very easily. In this paper, we show how to overcome this spreadsheet scalability disadvantage. We provide a technique to program an optimization model in a spreadsheet that can easily be scaled up or down in size and re-optimized using the Excel Solver. Our technique enables a spreadsheet model to be scaled and reused as easily as its equivalent algebraic implementation. We give examples involving transportation models and are working on examples involving manufacturing optimization and other extensions and generalizations, and on techniques for sharing spreadsheet optimization models with other systems.
Suggested Citation: Suggested Citation