Ensuring scalability and reusability of spreadsheet linear programming models

Larry J. LeBlanc, Thomas A. Grossman, Michael R. Bartolacci

Research output: Contribution to journalArticle

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.

Original languageEnglish (US)
Pages (from-to)55-69
Number of pages15
JournalOmega (United Kingdom)
Volume84
DOIs
StatePublished - Apr 1 2019

Fingerprint

Linear programming
Spreadsheet
Scalability
Reuse
Editing
New business
Software
Coefficients
Excel

All Science Journal Classification (ASJC) codes

  • Strategy and Management
  • Management Science and Operations Research
  • Information Systems and Management

Cite this

@article{f9ad0b5ecc97487c8da7af6e0e4897ea,
title = "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.",
author = "LeBlanc, {Larry J.} and Grossman, {Thomas A.} and Bartolacci, {Michael R.}",
year = "2019",
month = "4",
day = "1",
doi = "10.1016/j.omega.2018.04.005",
language = "English (US)",
volume = "84",
pages = "55--69",
journal = "Omega",
issn = "0305-0483",
publisher = "Elsevier BV",

}

Ensuring scalability and reusability of spreadsheet linear programming models. / LeBlanc, Larry J.; Grossman, Thomas A.; Bartolacci, Michael R.

In: Omega (United Kingdom), Vol. 84, 01.04.2019, p. 55-69.

Research output: Contribution to journalArticle

TY - JOUR

T1 - Ensuring scalability and reusability of spreadsheet linear programming models

AU - LeBlanc, Larry J.

AU - Grossman, Thomas A.

AU - Bartolacci, Michael R.

PY - 2019/4/1

Y1 - 2019/4/1

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

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

UR - http://www.scopus.com/inward/record.url?scp=85046662044&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=85046662044&partnerID=8YFLogxK

U2 - 10.1016/j.omega.2018.04.005

DO - 10.1016/j.omega.2018.04.005

M3 - Article

AN - SCOPUS:85046662044

VL - 84

SP - 55

EP - 69

JO - Omega

JF - Omega

SN - 0305-0483

ER -