Home | Business News | Browse by Publication | I | Interfaces

Implementing large-scale optimization models in Excel using VBA.

Article Excerpt
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.

Key words: computers/computer science: system design, operations; planning: corporate. History: This paper was refereed.

**********

Spreadsheets have significantly increased managers' awareness of optimization modeling, primarily because most nontechnical managers can more easily visualize and comprehend spreadsheet models than classic algebraic models. For example, they understand such concepts as constraints more quickly when explained in spreadsheets terms (Figure 1). Conway and Ragsdale (1997), Powell (1997), Savage (1997, 2003), Willemain et al. (1997), Grossman (1999), and LeBlanc (2000) have discussed the importance of spreadsheets for explaining MS/OR concepts. MS/ OR analysts have used spreadsheets successfully in developing models for many industrial applications (Jacobs and Peck 2000; Appa and Sridharan 2000; LeBlanc et al. 2000, 2004; Brown et al. 2001; Fader and Hardie 2001; Gupta et al. 2002; Srinivasan et al. 2003; Gordon and Erkut 2004; Tyagi et al. 2004).

[FIGURE 1 OMITTED]

Thus, MS/OR analysts should design and present optimization models within spreadsheets for projects that bridge theory and practice. However, spreadsheets have inherent limitations when dealing with large models, and the intuitive procedures recommended in textbooks are often unsuccessful. For the special case of supply chain problems, we have described efficient implementation of spreadsheet linear programs (LeBlanc and Galbreth 2007). In this paper, we generalize that work, discussing the difficulties that can be encountered when developing optimization models in spreadsheets.

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.

In our first attempt at an Excel-based model of Nu-Kote's problem, we used a rectangular range for the changing cells and their target cell coefficients. This model worked well for the initial problem. However, as the project with Nu-Kote evolved, the model size expanded. The frequent changes in model size were time consuming, requiring constant modifications to the basic spreadsheet design. We had to insert new rows to accommodate additional sites in Mexico and later in Europe. On several occasions, we needed new columns to incorporate additional time periods into the model. Although cell formulas adjust when rows and columns are inserted within a range, they do not adjust when ranges are expanded to include additional columns that previously bordered the range. Furthermore, we had to manually update documentation within each worksheet, such as cell comments containing formulas, when we added new rows or columns.

Eventually, the problem reached the point at which we could no longer use rectangular ranges--they wouldn't fit in the 256 columns available in each worksheet. Thus, we modified it to use individual columns containing the changing cells...

View this article FREE - Now for a Limited Time, try Goliath Business News
Free for 3 Days!