Excel Solver and Linear Programming

• Download examples - 47.92 KB

Introduction

This article explains the use of Excel Solver for solving problems, specially the ones related to Linear Programming. If you do not know much about Linear programming it's okay, this article will give you a little insight about that as well. However, the focus here is to make you aware of Excel Solver and let you know how to make use of it for solving interesting problems.

What is Solver and What Does It Do

In simple terms, Excel solver is an Add-in that solves problems for you. For a given problem, Excel solver can run various permutations and combinations and find out the best possible solution for you.

Enabling Solver in Excel

Go to options:

Select Add-In:

Click on 'Go' button, check Solver Add-in, click OK:

You should be able to see the Solver button in your Excel Data Ribbon:

What is Linear Programming

LP is a mathematical method for determining a way to achieve the best outcome (such as maximum profit or lowest cost) in a given mathematical model for some list of requirements represented as linear relationships.

Some Sample Problems that Solver can Solve

Problem 1

Let's say we have a function e.g. f=x²-x+2 and we want to find out the minimum value of x where -1<= x <= 5.

Problem 2

A company manufactures desks and chairs. Each desk uses 4 units of wood, and each chair 3 units of wood. A desk contributes $40 to profit and a chair contributes $25. Marketing restrictions require that the number of chairs produced to be at least twice the number of desk produced. There are 20 units of wood available. What set of production will bring maximum profit.

Problem 3

Here is a more complex problem which is represented in terms of equations and solved through the matrix approach.

Maximize 50x₁ + 30x₂ + 25x₃ + 30x₄

Where

2x1 + 2.5x2 + 3x3 + 1.8x4 = 800

1.2x1 + 1x2 + 2x3 + 0.8x4 = 400

1.5x1 + 1.2x2 + 1.5x3 + 0.8x4 = 380

x2 = 50

x3 = 30

x₁, x₂, x₃, x₄ = 0

Solving the Problems using Excel Solver

Problem 1

The following image shows the solved example. Cell in green (C15) is the cell which gets the value through solver and is named as x.

Formula for cell C12 is x^2-x+2. By default, put 1 in cell C15 which is the initial value for variable x.

Objective field is f which is the name of cell C12 which we want to solve through solver. "By changing the variable cells" field has x which is cell C15, our variable. We have also set the constraints using add button x <= 5 and x>=-1. Just click the 'solve' button and it will give the value of x.

Problem 2

This problem is solved in a traditional way. Unitstoproduce is the range C18:C19. totoalwoodused is the name of cell F20.

Here in the following figure, you can see the solver setting. Give 1 in green cells as initial values. Solver should be able to give you the correct values for them.

Problem 3

In real life, LP problems asre not as simple as in Problem 2. Therefore, we need to solve the problem using the matrix. In this problem, you can see many equations which you can relate to some equations in problem 2. To maximize the result of the equation, all we need to do is to Maximize (Transposed C) * x.

For the sake of uniqueness of the variable name, I named the matrix variable as xn, bn, etc. Values of x is what you want to get through solver. Give 1 as initial values for x1, x2, x3 and x4. When you run the solver, it will update those green cells with the solved values.

History

• 20^th September, 2011: Initial post