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

16 Pages Posted: 2 Jan 2016 Last revised: 24 Sep 2016

See all articles by Larry J. LeBlanc

Larry J. LeBlanc

Vanderbilt University - Operations Management

Thomas Grossman

University of San Francisco - School of Management

Date Written: December 30, 2015

Abstract

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

LeBlanc, Larry and Grossman, Thomas, Ensuring Scalability and Re-Usability of Spreadsheet Analytical and Optimization Models (December 30, 2015). Vanderbilt Owen Graduate School of Management Research Paper No. 2709788, Available at SSRN: https://ssrn.com/abstract=2709788 or http://dx.doi.org/10.2139/ssrn.2709788

Larry LeBlanc

Vanderbilt University - Operations Management ( email )

Nashville, TN 37203
United States

Thomas Grossman (Contact Author)

University of San Francisco - School of Management ( email )

San Francisco, CA 94117
United States

Do you have negative results from your research you’d like to share?

Paper statistics

Downloads
52
Abstract Views
534
Rank
687,410
PlumX Metrics