Spring 2014

EXCEL

Excel is a spreadsheet package. In this part, we will use Excel Solver to solve linear programming problems.

To correct a mistake as you are typing information in a cell, use the Backspace key to backup and delete characters. To correct a mistake after you enter the data, click the cell you want to edit. Press F2 to put Excel into edit mode. Use Backspace, Delete, Left Arrow, or Right Arrow keys or the mouse to edit the cell contents.

Double-clicking the column heading dividing line makes the column as wide as the longest text or number. ### will appear if your column is not wide enough for your data.

Formulas always begin with an equal sign and can contain numbers or cell references such as =5+10 or = C6*E6. The order of operations are: 1) parentheses, 2) exponentiation (^), 3) multiplication (*) or division (/), 4) addition or subtraction, 5) comparison.

A function is a special prewritten formula that provides a shortcut for some calculations. An example would be using = SUM(A3:A5) instead of writing =A3+A4+A5. Another example would be using =SUMPRODUCT(C5:D5,C6:D6) instead of writing =C5*C6+D5*D6.

In writing formulas or functions we may need to use **absolute
references** if the formula or the function refers to a cell in a fixed
location. Absolute references contain
dollar sign ($) before the column letter, the row number, or both. For example, in entering the left-hand sides
of three linear programming problem constraints, suppose we use the formula
=SUMPRODUCT(C9:D9,$C$6:$D$6) in, say, C14, and then copy it to cells C15 and
C16. Because of $, values in cells C6
and D6 are always used.

To copy the contents of a cell to other cells, click the cell you want to copy. Position the pointer over the lower-right corner of the cell. When the pointer changes to +, hold down the left mouse button and drag over range where you want the contents copied. Click any cell to remove the highlighting.

**Entering Less Than or
Equal to/Greater Than or Equal to Sign**

The easiest way of entering it would be to use the key board for the strict inequality, and then underline.

**Solving Linear
Programs with Excel Spreadsheet**

**Problem:**

Max Z = 7x_{1} + 6x_{2}

Subject to:

3x_{1 }+ 6x_{2
}≤ 450

9x_{1} + 6x_{2} ≤ 630

2x_{1} ≤ 110

x_{1}, x_{2 }≥ 0

First, we enter the problem into a worksheet. The following format is one possible way of doing it. Since Excel names each cell based on the first text label in the cell on the left (in the same row) and the first text label in the cell above (in the same column) some care is needed in setting up the information table.

To compute the optimal solution value in cell B7, enter the formula =SUMPRODUCT(C5:D5,C6:D6). For the values of variables in cells C6 and D6, Cell B7 gives the value of the objective function.

Cells C6 and D6 will contain the solution values for the variables. In cell C14, enter =SUMPRODUCT(C9:D9,$C$6:$D$6) to represent the left-hand side of the first constraint. Copy cell C14 into cell C15 and C16 for constraints two and three, respectively. After entering the formulas notice the changes in cell B7 and column LHS on the worksheet, as given below.

**Using Excel Solver**

To solve the problem,

1. With Excel 2010, select **Data, Solver** to display the **Solver
Parameters** dialog box. Complete the
parts as you see below.

If Solver is not there, click **File** on the upper left corner of the screen, select **Options**, **Add-Ins**, **Solver Add-in**, **Go**, again **Solver Add-in**, and **OK**.

2. To incorporate the constraints, select **Add**.
The **Add Constraint** dialog box
appears. Complete it as you see
below. Notice that all three constraints
are entered together, because they are all “≤ “ constraints. Alternatively, we could have entered the
three constraints one at a time. Also
notice that we selected the Make Unconstrained Variables Non-Negative
option. Otherwise, we had to enter the
nonnegativity constraints for the
decision variable separately. Since we
are not adding any more constraints, select **OK**.

3. **Solver Parameters**
dialog box comes back. Choose **Solve**.
The worksheet is revised as given below and the **Solver Results** dialog box appears.
The optimal solution is x_{1} = 30, x_{2} = 60, and the
value of the objective function is 570.

4. In **Solver Results**
dialog box, highlight **Answer** and **Sensitivity** in the **Reports** box, and select **OK**. The sheet tabs will have Answer Report,
Sensitivity Report, Sheet 1.

To solve the problem with Excel 2007

1. Select Data, Solver to display the Solver Parameters dialog box.

If Solver is not there, click the office button on the top left corner of the screen, select Excel Options on the lower right side of the menu section, select Add-Ins, Solver Add-in, Go, again Solver Add-in, and OK.

Complete the parts in a similar way as described above. Add the constraints similarly.

2. Solver Parameters dialog box comes back. Select Options, Solver Options dialog box appears. Select Assume Linear Model, Assume Non-Negative to enter the nonnegativity constraints, OK.

3. Solver Parameters dialog box comes back. Choose Solve. The worksheet is revised. The optimal solution is x1 = 30, x2 = 60, and the value of the objective function is 570. The answer and sensitivity report is obtained similarly as in Excel 2010.