EXCEL
Excel is a spreadsheet package. In this part, we will use Excel Solver to solve linear programming problems.
Helpful Information
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.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
Miller
Welding |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
3 |
Variables |
|
x1 |
x2 |
|
|
|
4 |
|
|
|
|
|
|
|
5 |
Profit/unit |
|
7 |
6 |
|
|
|
6 |
Optimal
solution |
|
|
|
|
|
|
7 |
Optimal
solution value |
|
|
|
|
|
|
8 |
|
|
|
|
|
RHS |
|
9 |
Constraint
1 |
|
3 |
6 |
≤ |
450 |
|
10 |
Constraint
2 |
|
9 |
6 |
≤ |
630 |
|
11 |
Constraint
3 |
|
2 |
|
≤ |
110 |
|
12 |
|
|
|
|
|
|
|
13 |
|
|
LHS |
RHS |
|
|
|
14 |
Constraint
1 |
|
|
450 |
|
|
|
15 |
Constraint
2 |
|
|
630 |
|
|
|
16 |
Constraint
3 |
|
|
110 |
|
|
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.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
Miller
Welding |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
3 |
Variables |
|
x1 |
x2 |
|
|
|
4 |
|
|
|
|
|
|
|
5 |
Profit/unit |
|
7 |
6 |
|
|
|
6 |
Optimal
solution |
|
|
|
|
|
|
7 |
Optimal
solution value |
0 |
|
|
|
|
|
8 |
|
|
|
|
|
RHS |
|
9 |
Constraint
1 |
|
3 |
6 |
≤ |
450 |
|
10 |
Constraint
2 |
|
9 |
6 |
≤ |
630 |
|
11 |
Constraint
3 |
|
2 |
|
≤ |
110 |
|
12 |
|
|
|
|
|
|
|
13 |
|
|
LHS |
RHS |
|
|
|
14 |
Constraint
1 |
|
0 |
450 |
|
|
|
15 |
Constraint
2 |
|
0 |
630 |
|
|
|
16 |
Constraint
3 |
|
0 |
110 |
|
|
Using Excel Solver
To solve the problem,
1. With Excel 2007, select Data, Solver to display the Solver Parameters dialog box. Complete the parts as you see below.
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.
With Excel 2003, select Tools, Solver (if not there, select Add-Ins and then Solver Add-in) to display the Solver Parameters dialog box. Complete the parts as you see below.
- 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.
To enter the nonnegativity constraints, select Add. Click the Cell Reference box, and outline cells C6 and D6. Click the >= option from the down arrow button. Click the Constraint box and enter 0.
Since no other constraint is left, select OK.
3. Solver Parameters dialog box comes back. Select Options, Solver Options dialog box appears. Select Assume Linear Model, (Assume Non-Negative can be selected to enter the nonnegativity constraints), OK.
4. Solver Parameters dialog box comes back. Choose Solve. The worksheet is revised as given below. The optimal solution is x1 = 30, x2 = 60, and the value of the objective function is 570.
|
|
A |
B |
C |
D |
E |
F |
|
1 |
Miller
Welding |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
3 |
Variables |
|
x1 |
x2 |
|
|
|
4 |
|
|
|
|
|
|
|
5 |
Profit/unit |
|
7 |
6 |
|
|
|
6 |
Optimal
solution |
|
30 |
60 |
|
|
|
7 |
Optimal
solution value |
570 |
|
|
|
|
|
8 |
|
|
|
|
|
RHS |
|
9 |
Constraint
1 |
|
3 |
6 |
≤ |
450 |
|
10 |
Constraint
2 |
|
9 |
6 |
≤ |
630 |
|
11 |
Constraint
3 |
|
2 |
|
≤ |
110 |
|
12 |
|
|
|
|
|
|
|
13 |
|
|
LHS |
RHS |
|
|
|
14 |
Constraint
1 |
|
450 |
450 |
|
|
|
15 |
Constraint
2 |
|
630 |
630 |
|
|
|
16 |
Constraint
3 |
|
60 |
110 |
|
|
5. Solver Results dialog box appears. Highlight Answer and Sensitivity in the Reports box, and select OK. The sheet tabs will have Answer Report, Sensitivity Report, Sheet 1.
Excel Solver for
Transportation Problem
Enter the problem into a worksheet as you see below. The values in cells B5:E7 are the transportation costs. The supply left-hand sides in Part II are the number of units shipped from each factory and the demand left-hand sides are the number of units shipped into each warehouse. The right-hand sides are the factory supplies and the warehouse demands. The cells B16:E18 will contain the solution values for the decision variables.
Enter the formula =SUMPRODUCT(B5:E7, B16:E18) to compute the cost of the solution.
In cell F16, enter the formula =SUM(B16:E16) to represent the left-hand side of the first supply constraint. Copy cell F16 into cell F17 and F18 for the second and the third supply constraints, respectively. Enter the formula =SUM(B16:B18) in cell B19 to represent the left-hand side of the first demand constraint. Copy cell B19 into cells C19, D19, and E19 for the second, third and fourth demand constraints, respectively.
Use the Excel Solver as done before and refer to the Solver Parameters dialog box below as needed. There is no need to enter the nonnegativity constraints separately, specify the Assume Non-Negative option (in addition to Assume Linear Model), instead.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
Transportation
Problem |
|
|
|
|
|
|
|
|
2 |
Part I |
|
|
|
|
|
|
|
|
3 |
|
|
Warehouse |
| ||||