Question

1. Solve the following integer optimization model by using Excel Solver:

Maximize Z = 5x₁ + 6x₂

Subject to

3x₁ + 4x₂ < 10

4x₁ + 2x₂ < 15

     x₁, x₂ > 0 and integer

Please show how to use the excel solver as well as steps. :)

Answer 1

Spreadsheet Model:

Yellow Highlighted Cells are the decision variables for solver

Green Highlighted Cell is the objective function formula using the Yellow Highlighted Cells

Orange Highlighted Cells are the constraint formulae using Yellow Highlighted Cells and the Blue Highlighted Cells are the right-hand side values of the constraints

Excel Formulae Used:

The formulae used in the highlighted cells are identical to the equations given in the question but in place of x1 and x2, corresponding cell references are used.

Adding Parameters to solver:

The solver is an excel plug in which can be installed form excel options. After installation, it is available in the data segment of the excel sheet (encircled in red in the below screenshot). Once installed and launched, the parameters can be added

1st: Set Objective: Enter the cell reference of the Green Highlighted Cell.

2nd: To: Select Max (as this is a maximization problem)

3rd: By Changing Variable Cells: Provide the reference of Yellow Highlighted Cell (should be empty).

4th: Subject to the constraints: Click on add to enter the constraint cells (Orange Highlighted Cells and Blue Highlighted Cells)

Clicking on add button gives this dialogue box where you can enter the orange highlighted cells in the cell reference part and select the relationship as greater or equal to/smaller of equal to/equal to/intger/binary etc. Enter the blue highlighted cells in the constraint part. After one constraint value is added click add to enter another constraint in similar manner or else click ok to return to solver parameters.

After adding the two constraints, add the integer constraint.

As shown in the above screenshot, provide the changing variable cells reference and select int from the relationship option and then click ok.

After returning to the solver parameters box, select make unconstrained variables non-negative. This satisfies the x1 and x2 >0 constraint. Select simplex lp in the select solving method option then click solve to get the solution:

x1 = 2, x2 = 1, Z = 16