Implementing large-scale optimization models in Excel using VBA

Implementing large-scale optimization models in Excel using VBA

0.00 Avg rating0 Votes
Article ID: iaor20082074
Country: United States
Volume: 37
Issue: 4
Start Page Number: 370
End Page Number: 382
Publication Date: Jul 2007
Journal: Interfaces
Authors: ,
Keywords: systems, design, planning, spreadsheets
Abstract:

We discuss the importance of spreadsheets for optimization modeling, including a description of their limitations for large-scale problems. We then describe efficient ways to overcome these limits. Our approach makes use of Excel’s standard functionality but augments Excel with its programming language, Visual Basic for Applications (VBA), where necessary. We show how using VBA within Excel to generate and solve large linear programs (LPs) overcomes many of the problems inherent in purely spreadsheet-based models and greatly increases model usability. The techniques described were instrumental in our successful development of a large-scale procurement/distribution LP that resulted in savings of approximately $1,000,000 in the first year, with even greater annual savings expected in the future.

Reviews

Required fields are marked *. Your email address will not be published.