Spring 2015

EXCEL

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

# Correcting Mistakes

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.

# Changing Column Width

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.

# Entering Formulas

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.

# Using Functions

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.

# Copying Cell Contents

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 = 7x1 + 6x2

Subject to:

3x1  + 6x2 ≤ 450

9x1 + 6x2 ≤ 630

2x1           ≤ 110

x1, x2  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 2013 and 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 x1 = 30, x2 = 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 2013 and Excel 2010.