Designing large-scale supply chain linear programs in spreadsheets

Galbreth

2007

Interfaces

Self Cite

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.

Section: S Preadsheets Have Significantly Increased Managers'mentioning

confidence: 95%

Implementing Large-Scale Optimization Models in Excel Using VBA

Galbreth

2007

Interfaces

Self Cite

“…(2002) are solved by Lagrangian relaxation techniques and genetic algorithms. LeBlanc and Galbreth (2007b) provide additional examples.…”

Section: Review Of Logistics Optimization: Strategic Tactical Operamentioning

confidence: 99%

Optimization Modeling for Logistics: Options and Implementations

Bartolacci

J of Business Logistics

Kayıkçı

et al. 2012

Self Cite

L ogistics optimization has significantly grown in popularity over the last few decades. Improvements in computing power, modeling software, and the willingness of companies to invest time in the modeling effort have allowed models that were once too unwieldy to solve to optimality to be solved quickly. This has led to a more wide-spread recognition by logistics managers of the potential advantages of using optimization. The scope of logistics optimization in companies and organizations has expanded to address strategic, tactical, operational, and collaborative decision making. Spreadsheets, an analytical tool familiar to managers, have played a crucial role in the expanded modeling efforts of companies. Although optimization's role in logistics has grown tremendously, there still are areas that remain to be explored that will allow it to achieve an even larger and more successful role in the management of companies. Additionally, there are some models that are still too large or too complex to currently solve to optimality, despite the advances in computing power and modeling ⁄ solving software.

“…More recently, Lipman and Delucchi (2006) describe how a spreadsheet model was used to analyze the lifecycle costs of hybrid versus conventional vehicles. Other spreadsheet optimization models have addressed tactical problems such as the loading schedule of an automated storage and retrieval system (Jacobs et al 2000), as well as strategic decisions regarding large-scale networks for global sourcing, production, and distribution (LeBlanc et al 2004;LeBlanc and Galbreth 2007). Schuster et al (2000) optimized recipes and supply chain flows in a process industry context using a spreadsheet model.…”

Section: Introductionmentioning

confidence: 99%

Overcoming Spreadsheet Risk in Supply Chain Modeling

Galbreth

J of Business Logistics

2010

Self Cite

Spreadsheet optimization modeling plays an important role in the management of today's sophisticated supply chains. However, despite the advantages of spreadsheets (or in some cases because of these advantages) there are significant risks associated with spreadsheet models. Spreadsheet risk is defined as the chance of adverse operational or financial consequences due to erroneous creation, maintenance, and/or use of spreadsheet models. Such errors arise because the intrinsic complexity of optimization models is beyond the expertise of many non‐technical managers. In this article we discuss procedures that we developed for managing the substantial risks associated with the use of spreadsheets for supply chain optimization. The issues we address go far beyond simple data entry mistakes, which are possible in virtually every aspect of business and can never be completely eliminated.