Implementing Large-Scale Optimization Models in Excel Using VBA
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.
…
A Spreadsheet Linear Program (LP) for Supply Chain Optimization
We introduce the importance of efficient spreadsheet design for optimization modeling with an overview of a project in which the initial design was so inefficient that the project almost failed. Nu-kote International asked us to develop Excel-based LP models for managing its supply chain. Nu-kote is the world’s largest independent remanufacturer of cartridges for printers, copiers, and fax machines, with a global network of suppliers, production facilities, and customers. Its facilities are located in various cities in the US, China, Thailand, and Mexico. In addition, empty cartridges can be sourced from brokers in Asia and Europe. Because of the lead times for shipping to and from overseas locations, Nu-kote’s supply chain problem requires a multiperiod model.
The Nu-kote model has changing cells that specify shipments in each period of empty cartridges for remanufacturing and of remanufactured cartridges to customer sites. The model chooses shipments to meet demands in each period while minimizing total procurement, shipping, holding, and remanufacturing costs. Considerations include lead times for shipping and remanufacturing and, for each period, the limited availabilities of empty cartridges at each source, remanufacturing capacities at each site, and customer demands for remanufactured cartridges.
…
Website: ormstomorrow.informs.org | Filesize: 336kb
No of Page(s): 13
Click here to download Implementing Large-Scale Optimization Models in Excel Using VBA.
Related Tutorial
Tags: microsoft excel, office vba, Optimization, VBA
Comments
Leave a Reply