Images of Microsoft® Excel dialog boxes © Microsoft. All rights reserved. This content is excluded from our Creative Commons license. For more information, see http://ocw.mit.edu/help/faq-fair-use/.

 

 

2

Tool for Solving a Linear Program:

Excel has the capability to solve linear (and often nonlinear) programming

problems. The SOLVER tool in Excel:

 May be used to solve linear and nonlinear optimization problems

 Allows integer or binary restrictions to be placed on decision variables

 Can be used to solve problems with up to 200 decision variables

 

 

3

How to Install SOLVER:

The SOLVER Add-in is a Microsoft Office Excel add-in program that is

available when you install Microsoft Office or Excel. To use the Solver Add-in,

however, you first need to load it in Excel. The process is slightly different for

Mac or PC users.

MAC: 1. Open Excel for Mac 2011 and begin by clicking on the Tools menu. 2. Click Add-Ins, and then in the Add-Ins box, check Solver.xlam and then click OK. 3. After restarting Excel for Mac 2011 (fully Quit Excel 2011), select the Data tab, then select Solver to launch

Microsoft: 1. Click the Microsoft Office Button , and then click Excel Options. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins and click Go. 3. In the Add-Ins available box, select the Solver Add-in check box, and then click OK. If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in. If you get prompted that Solver is not currently installed, click Yes to install it. 4. After you load Solver, the Solver command is available in the Analysis group on the Data tab.

 

 

4

How to Use SOLVER:

The key to solving an LP on a spreadsheet is:

 Set up a spreadsheet that tracks everything of interest

(e.g., costs, profits, resource usage)

 Identify the decision variables that can be varied. These

are called Changing Cells

 Identify the cell that contains your objective function as

the Target Cell

 Identify the constraints and tell SOLVER to solve the

problem

At this point, the optimal solution to our problem will be placed

on the spreadsheet

 

 

5

LP Solutions with SOLVER, an Example:

Consider the problem of diet optimization. There are four different types of food: Brownies, Ice Cream, Cola, and Cheese Cake. The nutrition values and cost per unit are as follows: The objective is to find a minimum-cost diet that contains at least 500 calories, at least 6 grams of chocolate, at least 10 grams of sugar, and at least 8 grams of fat.

Brownies Ice Cream Cola Cheese Cake

Calories 400 200 150 500

Chocolate 3 2 0 0

Sugar 2 2 4 4

Fat 2 4 1 5

Cost $0.50 $0.20 $0.30 $0.80

 

 

6

LP Solutions with SOLVER, an Example:

STEP 1: Decision Variables

To begin we enter heading for each type of food in B2:E2.

In the range B3:E3, we input trial values for the amount of each food eaten. (Any values will work, but at least one should be positive.)

For example, here we indicate that we are considering eating three brownies, no scoops of chocolate ice cream, one bottle of cola, and seven pieces of pineapple cheesecake.

 

 

 

 

 

A B C D E

1 DECISION VARIABLES

2 Brownies Ice Cream Cola Cheese Cake

3 Eaten 3 0 1 7

 

 

7

LP Solutions with SOLVER, an Example:

STEP 2: Objective Function

To see if the diet is optimal, we must determine its cost as well as the calories, chocolate, sugar, and fat it provides.

In the range B7:E7,we reference the number of units and in B8:E8 we input the per unit cost for each available food. Then we compute the cost of the diet in cell B10 with the formula

= B7*B8 + C7*C8 +D7*D8+ E7*E8

But it is usually easier to enter the formula

= SUMPRODUCT (B7:E7, B8:E8)

And this is much easier to understand for anyone reading the spreadsheet. The =SUMPRODUCT function requires two ranges as inputs. The first cell in the first range is multiplied by the first cell in the second range, then the second cell in the first range is multiplied by the second cell in the second range, and so on. All of these products are then added. Thus, in cell B10 the =SUMPRODUCT function computes total cost as (3)(50)+(0)(20)+(1)(30)+(7)(80) = 740 cents.

 

 

8

LP Solutions with SOLVER, an Example:

STEP 2: Objective Function (cont.)

 

 

 

 

 

 

 

 

 

 

A B C D E

1 DECISION VARIABLES

2 Brownies Ice Cream Cola Cheese Cake

3 Eaten 3 0 1 7

4

5 OBJECTIVE FUNCTION

6 Brownies Ice Cream Cola Cheese Cake

7 Eaten =B3 =C3 =D3 =E3

8 Cost 50 20 30 80

9

10 Total 740 = SUMPRODUCT ( B7:E7, B8:E8)

 

 

9

LP Solutions with SOLVER, an Example:

STEP 3: Constraints

The final step required to set up our LP in Excel is to set

Order your essay today and save 10% with the discount code ESSAYHELP