Solving Linear Programming Problems Using Excel Ken S. Li Southeastern Louisiana University Linear Programming The Problem An optimization model is a linear program if it has continuous variables, a single objective function, and all constraints are linear equalities or inequalities History Linear programming was conceptually developed before World War II by the outstanding Russian mathematician A.N. Kolmogorov. Who and When Major contributors: Kantorovich Stigler Dantzig Karmarkar Gonzaga Wright Terlaky Todd 1945 1947 1984 1992 1996 1995 1991 Standard Formulation Mathematical Formulation minimize cx subject to Ax = b x >= 0 Standard Formulation where x is the vector of variables to be solved for, A is a matrix of known coefficients, and c and b are vectors of known coefficients. The expression "cx" is called the objective function, and the equations "Ax=b" are called the constraints. Standard Formulation Usually A has more columns than rows, and Ax=b is therefore quite likely to be under-determined, leaving great latitude in the choice of x with which to minimize cx. The word "Programming" is used here in the sense of "planning"; the necessary relationship to computer programming was incidental to the choice of name. Hence the phrase "LP program" to refer to a piece of software is not a redundancy, although I tend to use the term "code" instead of "program" to avoid the possible ambiguity. Applications Applications Linear and integer programming have proved valuable for modeling many and diverse types of problems in planning, routing, scheduling, assignment, and design. Industries that make use of LP and its extensions include transportation, energy, telecommunications, and manufacturing of many kinds. Applications Specific Applications 1. Development of a production schedule that will satisfy future demands for a firm’s production and at the same time minimize total production and inventory costs 2. Selection of the product mix in a factory to make best use of machine-hours and labor-hours available while maximizing the firm’s products Application - Contd 3. Determination of grades of petroleum products to yield the maximum profit 4. Selection of different blends of raw materials to feed mills to produce finished feed combinations at minimum cost 5. Determination of a distribution system that will minimize total shipping cost from several warehouses to various market locations Computational Method Simplex Methods Simplex methods, introduced by Dantzig about 50 years ago, visit "basic" solutions computed by fixing enough of the variables at their bounds to reduce the constraints Ax = b to a square system, which can be solved for unique values of the remaining variables. Basic solutions represent extreme boundary points of the feasible region defined by Ax = b, x >= 0, and the simplex method can be viewed as moving from one such point to another along the edges of the boundary. Computational Method Interior Point Methods Barrier or interior-point methods, by contrast, visit points within the interior of the feasible region. These methods derive from techniques for nonlinear programming that were developed and popularized in the 1960s by Fiacco and McCormick, but their application to linear programming dates back only to Karmarkar's innovative analysis in 1984. Interior Point Method Step 1: Choose any feasible interior point solution, x(0) 0 and set solution index t=0. Step 2: If any component of x ( t ) is 0, or if recent steps have made no significant change in the solution value, stop. Current point is either optimal or very nearly so. Step 3: Construct the next move direction x (t 1) Xt Pt c (t ) Interior Point Method -contd Where x1( t ) 0 Xt 0 0 x2( t ) 0 0 0 (t ) xn , T 1 t Pt I A (At A ) At , ct Xt c. T t Interior Point Method - contd Step 4: If there is no limit on feasible moves in the direction x (t 1) (all components are nonnegative), stop ; the given model is unbounded. Otherwise, construct the step size 1 x ( t 1) 1 t X . Interior Point Method - contd Step 4: compute the new solution x (t 1) x (t ) x (t 1) Then let t t 1, and return to Step 2. A Simple Example The Marriott Tub Company manufactures two lines of bathtubs, called Model A and model B. Every tub requires a certain amount of steel and zinc; the company has available a total of 25,000 pounds of steel and 6,000 pounds of zinc. Each model A bathtub requires a total of 125 pounds of steel and 20 pounds of zinc, and each yields a profit of $90. Each model B bathtub can be sold for a profit of $70; it in turn requires 100 pounds of steel and 30 pounds of zinc. Find the best production mix of the bathtubs. The Formulation Maximize Subject to P 90 x 70 y 125 x 100 y 25000 20 x 30 y 6000 x, y 0 Where x and y are the numbers of model A and model B bathtubs that the company will make, respectively. Solving by Interior Point Method x1 x2 x3 x4 Obj Initial 1st 2nd 3 rd 100 100 2500 1000 16000 129.4 86.4 178.8 818.8 17698 152.8 58.7 3.3 1184 17861 157 53.6 5.7 1250 17882 4 th 5 th 6 th 7 th 8 th 189 13.60 8.3 1810 17962 199.6 0.4 6.5 1995 17992 199.7 0.3 0.1 1994 17994 199.9 0.1 0.1 1999 17998 200 0 2000 18000 0 Solving by Excel http://www.selu.edu/Academics/Faculty/kli /linearprog.xls

Descargar
# Solving Linear Programming Problems Using Excel