1 / 19

Solving Linear Programming Problems Using Excel

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

fedella
Download Presentation

Solving Linear Programming Problems Using Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Solving Linear Programming Problems Using Excel Ken S. Li Southeastern Louisiana University

  2. 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.

  3. Who and When Major contributors: Kantorovich Stigler 1945 Dantzig 1947 Karmarkar 1984 Gonzaga 1992 Wright 1996 Terlaky 1995 Todd 1991

  4. Standard Formulation • Mathematical Formulation minimize cx subject to Ax = b x >= 0

  5. 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.

  6. 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.

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

  8. 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

  9. 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

  10. 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.

  11. 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.

  12. Interior Point Method Step 1: Choose any feasible interior point solution, and set solution index t=0. Step 2: If any component of 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

  13. Interior Point Method -contd Where ,

  14. Interior Point Method - contd Step 4: If there is no limit on feasible moves in the direction (all components are nonnegative), stop ; the given model is unbounded. Otherwise, construct the step size

  15. Interior Point Method - contd Step 4: compute the new solution Then let and return to Step 2.

  16. 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.

  17. The Formulation • Maximize Subject to Where x and y are the numbers of model A and model B bathtubs that the company will make, respectively.

  18. Solving by Interior Point Method

  19. Solving by Excel • http://www.selu.edu/Academics/Faculty/kli/linearprog.xls

More Related