IDEAS home Printed from https://ideas.repec.org/a/inm/orinte/v37y2007i4p370-382.html
   My bibliography  Save this article

Implementing Large-Scale Optimization Models in Excel Using VBA

Author

Listed:
  • Larry J. LeBlanc

    (Owen Graduate School of Management, Vanderbilt University, Nashville, Tennessee 37203)

  • Michael R. Galbreth

    (Department of Management Science, Moore School of Business, University of South Carolina, Columbia, South Carolina 29208)

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.

Suggested Citation

  • Larry J. LeBlanc & Michael R. Galbreth, 2007. "Implementing Large-Scale Optimization Models in Excel Using VBA," Interfaces, INFORMS, vol. 37(4), pages 370-382, August.
    • Handle: RePEc:inm:orinte:v:37:y:2007:i:4:p:370-382
    DOI: 10.1287/inte.1060.0256
    as

    Download full text from publisher

    File URL: http://dx.doi.org/10.1287/inte.1060.0256
    Download Restriction: no
    File URL: https://libkey.io/10.1287/inte.1060.0256?utm_source=ideas
    LibKey link: if access is restricted and if your library uses this service, LibKey will redirect you to where you can use your library subscription to access this item
    ---><---

    References listed on IDEAS

    as
    1. Thomas A. Grossman, 1999. "Teachers' Forum: Spreadsheet Modeling and Simulation Improves Understanding of Queues," Interfaces, INFORMS, vol. 29(3), pages 88-103, June.
    2. Rajesh Tyagi & Peter Kalish & Kunter Akbay & Glenn Munshaw, 2004. "GE Plastics Optimizes the Two-Echelon Global Fulfillment Network at Its High Performance Polymers Division," Interfaces, INFORMS, vol. 34(5), pages 359-366, October.
    3. Gautam Appa & R. Sridharan, 2000. "OR Helps the Poor in a Controversial Irrigation Project," Interfaces, INFORMS, vol. 30(2), pages 13-28, April.
    4. Gerald Brown & Joseph Keegan & Brian Vigus & Kevin Wood, 2001. "The Kellogg Company Optimizes Production, Inventory, and Distribution," Interfaces, INFORMS, vol. 31(6), pages 1-15, December.
    5. Lynn Gordon & Erhan Erkut, 2004. "Improving Volunteer Scheduling for the Edmonton Folk Festival," Interfaces, INFORMS, vol. 34(5), pages 367-376, October.
    6. Conway, D. G. & Ragsdale, C. T., 1997. "Modeling optimization problems in the unstructured world of spreadsheets," Omega, Elsevier, vol. 25(3), pages 313-322, June.
    7. Larry J. LeBlanc & Dale Randels & T. K. Swann, 2000. "Heery International's Spreadsheet Optimization Model for Assigning Managers to Construction Projects," Interfaces, INFORMS, vol. 30(6), pages 95-106, December.
    8. Arnd Huchzermeier, 2005. "The Real Option Value of Operational and Managerial Flexibility in Global Supply Chain Networks," Springer Books, in: Michael Frenkel & Markus Rudolf & Ulrich Hommel (ed.), Risk Management, edition 0, pages 609-629, Springer.
    9. Stephen G. Powell, 1997. "The Teachers' Forum: From Intelligent Consumer to Active Modeler, Two MBA Success Stories," Interfaces, INFORMS, vol. 27(3), pages 88-98, June.
    10. Mandyam M. Srinivasan & Steven J. Ebbing & Alan T. Swearingen, 2003. "Woodward Aircraft Engine Systems Sets Work-in-Process Levels for High-Variety, Low-Volume Products," Interfaces, INFORMS, vol. 33(4), pages 61-69, August.
    11. Larry J. LeBlanc & James A. Hill & Gregory W. Greenwell & Alexandre O. Czesnat, 2004. "Nu-kote’s Spreadsheet Linear-Programming Models for Optimizing Transportation," Interfaces, INFORMS, vol. 34(2), pages 139-146, April.
    12. Peter S. Fader & Bruce G. S. Hardie, 2001. "Forecasting Repeat Sales at CDNOW: A Case Study," Interfaces, INFORMS, vol. 31(3_supplem), pages 94-107, June.
    Full references (including those not matched with items on IDEAS)

    Citations

    Citations are extracted by the CitEc Project, subscribe to its RSS feed for this item.
    as


    Cited by:

    1. Mauro Falasca & Christopher Zobel & Cliff Ragsdale, 2011. "Helping a Small Development Organization Manage Volunteers More Efficiently," Interfaces, INFORMS, vol. 41(3), pages 254-262, June.
    2. Larry J. LeBlanc & Thomas A. Grossman, 2008. "Introduction: The Use of Spreadsheet Software in the Application of Management Science and Operations Research," Interfaces, INFORMS, vol. 38(4), pages 225-227, August.

    Most related items

    These are the items that most often cite the same works as this one and are cited by the same works as this one.
    1. Thomas A. Grossman, 2002. "Student Consulting Projects Benefit Faculty and Industry," Interfaces, INFORMS, vol. 32(2), pages 42-48, April.
    2. Jeffrey W. Herrmann, 2008. "Disseminating Emergency Preparedness Planning Models as Automatically Generated Custom Spreadsheets," Interfaces, INFORMS, vol. 38(4), pages 263-270, August.
    3. Burcu B. Keskin & Halit Üster, 2012. "Production/distribution system design with inventory considerations," Naval Research Logistics (NRL), John Wiley & Sons, vol. 59(2), pages 172-195, March.
    4. Cunha, Claudio Barbieri & Mutarelli, Fernando, 2007. "A spreadsheet-based optimization model for the integrated problem of producing and distributing a major weekly newsmagazine," European Journal of Operational Research, Elsevier, vol. 176(2), pages 925-940, January.
    5. Larry J. LeBlanc & James A. Hill & Gregory W. Greenwell & Alexandre O. Czesnat, 2004. "Nu-kote’s Spreadsheet Linear-Programming Models for Optimizing Transportation," Interfaces, INFORMS, vol. 34(2), pages 139-146, April.
    6. LeBlanc, Larry J. & Grossman, Thomas A. & Bartolacci, Michael R., 2019. "Ensuring scalability and reusability of spreadsheet linear programming models," Omega, Elsevier, vol. 84(C), pages 55-69.
    7. Larry J. LeBlanc & Dale Randels & T. K. Swann, 2000. "Heery International's Spreadsheet Optimization Model for Assigning Managers to Construction Projects," Interfaces, INFORMS, vol. 30(6), pages 95-106, December.
    8. S Eom & E Kim, 2006. "A survey of decision support system applications (1995–2001)," Journal of the Operational Research Society, Palgrave Macmillan;The OR Society, vol. 57(11), pages 1264-1278, November.
    9. Habibi, M.K. Khakim & Battaïa, Olga & Cung, Van-Dat & Dolgui, Alexandre, 2017. "Collection-disassembly problem in reverse supply chain," International Journal of Production Economics, Elsevier, vol. 183(PB), pages 334-344.
    10. Christophe Oggier & Emmanuel Fragnière & Jeremy Stuby, 2005. "Nestlé Improves Its Financial Reporting with Management Science," Interfaces, INFORMS, vol. 35(4), pages 271-280, August.
    11. Amir Hossein JAHANIKIA & Vahidreza YOUSEFI & Ehsan MEHRABANFAR & Negar KAVOOSI KHOSROWSHAHII, 2015. "Decision-Making for Crisis Management of Power Distribution Networks," Informatica Economica, Academy of Economic Studies - Bucharest, Romania, vol. 19(4), pages 5-12.
    12. Suzanne de Treville & Ann van Ackere, 2006. "Equipping Students to Reduce Lead Times: The Role of Queuing-Theory-Based Modeling," Interfaces, INFORMS, vol. 36(2), pages 165-173, April.
    13. Braathen, Christian, 2022. "Interview Scheduling: An Integer Programming Approach," Discussion Papers 2022/10, Norwegian School of Economics, Department of Business and Management Science.
    14. Peter J. Regan, 2006. "Professional Decision Modeling: Practitioner as Professor," Interfaces, INFORMS, vol. 36(2), pages 142-149, April.
    15. Allan W. Gray & Joshua D. Detre & Brian C. Briggeman, 2005. "Valuing Limited Information in Decision Making Under Uncertainty," Working Papers 05-02, Purdue University, College of Agriculture, Department of Agricultural Economics.
    16. Calvin Sonntag & Thomas A. Grossman, 1999. "End-User Modeling Improves R&D Management at AgrEvo Canada, Inc," Interfaces, INFORMS, vol. 29(5), pages 132-142, October.
    17. Briggeman, Brian C. & Detre, Joshua D. & Gray, Allan W., 2004. "Compound Options: A Real Options Application To An Agricultural Business," 2004 Annual meeting, August 1-4, Denver, CO 19996, American Agricultural Economics Association (New Name 2008: Agricultural and Applied Economics Association).
    18. Sperling, Martina & Schryen, Guido, 2022. "Decision support for disaster relief: Coordinating spontaneous volunteers," European Journal of Operational Research, Elsevier, vol. 299(2), pages 690-705.
    19. Stüve, David & van der Meer, Robert & Lütke Entrup, Matthias & Agha, Mouhamad Shaker Ali, 2020. "Supply chain planning in the food industry," Chapters from the Proceedings of the Hamburg International Conference of Logistics (HICL), in: Kersten, Wolfgang & Blecker, Thorsten & Ringle, Christian M. (ed.), Data Science and Innovation in Supply Chain Management: How Data Transforms the Value Chain. Proceedings of the Hamburg International Conference of Lo, volume 29, pages 317-353, Hamburg University of Technology (TUHH), Institute of Business Logistics and General Management.
    20. Hrabec, Dušan & Hvattum, Lars Magnus & Hoff, Arild, 2022. "The value of integrated planning for production, inventory, and routing decisions: A systematic review and meta-analysis," International Journal of Production Economics, Elsevier, vol. 248(C).

    Corrections

    All material on this site has been provided by the respective publishers and authors. You can help correct errors and omissions. When requesting a correction, please mention this item's handle: RePEc:inm:orinte:v:37:y:2007:i:4:p:370-382. See general information about how to correct material in RePEc.

    If you have authored this item and are not yet registered with RePEc, we encourage you to do it here. This allows to link your profile to this item. It also allows you to accept potential citations to this item that we are uncertain about.

    If CitEc recognized a bibliographic reference but did not link an item in RePEc to it, you can help with this form .

    If you know of missing items citing this one, you can help us creating those links by adding the relevant references in the same way as above, for each refering item. If you are a registered author of this item, you may also want to check the "citations" tab in your RePEc Author Service profile, as there may be some citations waiting for confirmation.

    For technical questions regarding this item, or to correct its authors, title, abstract, bibliographic or download information, contact: Chris Asher (email available below). General contact details of provider: https://edirc.repec.org/data/inforea.html .

    Please note that corrections may take a couple of weeks to filter through the various RePEc services.

    IDEAS is a RePEc service. RePEc uses bibliographic data supplied by the respective publishers.