Ensuring Scalability and Re-Usability of Spreadsheet Analytical and Optimization Models

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