Nu-kote’s Spreadsheet Linear-Programming Models for Optimizing Transportation

Abstract

Nu-kote International manufactures ink-jet, laser, and toner cartridges; ribbons; and thermal fax supplies. We developed spreadsheet linear-programming models for planning shipments of finished goods between vendors, manufacturing plants, warehouses, and customers to minimize overall cost subject to maximum-shippingdistance policies. Nu-kote used versions of these linear programs (LPs) to model supply chains with different warehouse configurations. The LPs have between 5 000 and 9 700 variables and 2 452 constraints. Nu-kote has used these LPs for an inherently nonlinear problem to identify improved shipments that will reduce annual costs by approximately $1 million and customer transit time by two days. It has already saved $425 000 from insight gained from the model.

Designing large-scale supply chain linear programs in spreadsheets

“…Obviously, problems with more than 256 options cannot be modeled using a single rectangular range. 4 Note also that only the unshaded changing cells in D7:FC162 are needed because of shipping The feasible (non-shaded) changing cells in Figure 1 consist of approximately 300 separate ranges 5 -K7:CC7, CJ7:FC7, L8:CC8, CK8:FC8, M9:CC9, and so forth. Passing ranges of changing cells to Solver manually requires selecting each individual range in turn and then clicking an "Add" button.…”

“…As a result, supply chain LP models have become one of the most common LPs being solved. Table 1 shows some of the organizations that have used spreadsheet-based optimization models [3]; see [4][5][6] for descriptions of other applications of LP for supply chain management. Because of this increasing use, the ability to design an optimization model within a spreadsheet is important.…”

Implementing Large-Scale Optimization Models in Excel Using VBA

“…Conway and Ragsdale (1997), Powell (1997), Savage (1997Savage ( , 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 2000LeBlanc et al , 2004Brown et al 2001;Fader and Hardie 2001;Gupta et al 2002;Srinivasan et al 2003;Gordon and Erkut 2004;Tyagi et al 2004).…”

Enhancing mathematical programming models to account for demand priorities increasing as a function of delivery date

“…Linear programming (LP) and mixed integer programming (MIP) have been used often in solving resource allocation problems in a wide range of manufacturing and service industries [11,13,17,18,31]. These include distribution [21], finance [5,29], government [4,7], health care [22], manufacturing [3], purchasing [6,25], and others industries. The resources to allocate vary across organizations but often involve the allocation of some combination of materials, people, money, and machine capacity to fulfill needs such as satisfying customer demand.…”