APPENDIX E

MICROSOFT EXCEL
AND SOLVER
In this appendix, we demonstrate the procedure used to solve a linear programming
problem with Microsoft Excel and Solver. We assume that the reader is familiar with
the standard spreadsheet techniques and formulas.
Implementing Microsoft Excel and Solver to solve a linear programming problem
is accomplished in four basic steps:
1. The data for the problem are entered on the spreadsheet.
2. A representation of the mathematical model for the problem is constructed on
the spreadsheet, usually below the data section.
3. The representation of the problem is transferred to Solver.
4. Using Solver, the problem is solved.
Note that the problem is defined on the spreadsheet in the first two steps and that
Solver is brought into the solution process only in the last two steps. We illustrate
these steps in detail with the following example.
Example E.l. Division P is responsible for the manufacture of two components of
the parent company's final product. The division manager has available four different
processes to produce the two parts. Each process uses varying amounts of labor and
two raw materials, with inputs, outputs, and cost of 1 hr operation of each process
given in the following table.
Process 1

Process 2

Process 3

Process 4

Input

Labor (worker-hrs)
Material A (lb)
Material B (lb)

8
160
30

10
100
35

6
200
60

12
75
80

Output

Units of Part 1
Units of Part 2

35
55

45
42

70
0

0
90

Cost

($/hr)

400

575

620

590

Each week the division is responsible for producing at least 1300 units of Part 1
and 2600 units of Part 2. The division manager has at her disposal weekly up to 2.1
tons of Raw Material A, 1 ton of Raw Material B, and 450 hr of labor. The manager
An Introduction to Linear Programming and Game Theory, Third Edition. By P. R. Thie and G. E. Keough.
Copyright 2008 John Wiley & Sons, Inc.

432

APPENDIX E. MICROSOFT EXCEL AND SOLVER

can also purchase any number of units of Part 2 from an independent supplier at
$18/unit. To determine the minimum cost of the weekly operation, the manager
defines variables x, = number of hours that Process i is used, i 1,2,3,4, and x$
= number of units of Part 2 purchased from the outside vendor, and formulates the
following linear programming problem:
Minimize 400xi + 575x2 + 620x3 + 590x4 + 18x5
subject to
< 450 Labor (hr)
8*i + 10x2 + 6x3 + 12X4
< 4200 Material A (lb)
160xi + 100x2 + 200x3 + V5x4
< 2000 Material B (lb)
30xi + 35x2 + 6OX3 + 80x4
> 1300 Units of Part 1
35xi + 45x2 + 70X3 + 0X4
55xi + 42x2 + 0x3 + 90x4 x5 > 2600 Units of Part 2
X\ ,A^2,-^31' *4,X5 > 0

(E.l)

Now, with the data and the linear programming problem at hand, we turn to
Microsoft Excel. The initial spreadsheet representation for the problem, with steps 1
and 2 already completed, is in Figure E. 1. The data are entered in the upper half of
the spreadsheet, as the reader can see. The values of all the coefficients and constant
terms of (E. 1 ) are contained in the tables, and the rows, columns, and cells are labeled
for easy identification.
B
|
C
A|
1 Division P
2
3
Input
1
4
Labor (hr)
8
Material A (lb)
160
5
30
Material B (lb)
6
7
Output
8
# units Part 1
35
9
# units Part 2
55
Cost ($/hr)
10
$400
Part 2 vendor cost/unit -->
11
12
13
1
14
Process #
Hours used
15
# Units Part 2 purchased -->
16
17
18
Minimize cost
19
Constraints
LHS
20
Labor
0
21
Material A
0
22
Material B
0
23
Parti
0
24
Part 2
0
25

Process
2
3
10
6
100
200
35
60
45
42
$575
$18

70
0
$620

Figure E.l

4
12
75
80

Limit
450
4200
2000
# Required
1300
2600

RHS
450
4200
2000
1300
2600

0
90
$590

Variables
2
3

s
s
s
a

APPENDIX E. MICROSOFT EXCEL AND SOLVER

433

D I E | F
A|
B
1
C
1 Division P
2
Process
1
3
2
3
4
Input
4
Labor (hr) 8
10 6
12
5
100 200 75
Material A (Ib) 160
35 60 80
6
Material B (Ib) 30
7
Output
8
45 70 0
# units Part 1 35
9
42 0
90
# units Part 2 55
10
575 620 590
Cost ($/hr) 400
11
Part 2 vendor cost/unit --> 18
12
13
Variables
14
2
3
4
Process # 1
15
Hours used
16
# Units Part 2 purchased -->
17
18 Minimize cost =SUMPRDUCt(C10:F0,C15:F15)+t)11*D16
19
20
Constraints
LHS
RHS
21
Labor
=SUMPRODUCT(C4:F4,C$15:F$15)
=G4
22
=G5
Material A =SUMPRODUCT(C5:F5,C$15:F$15)
23
=G6
Material B =SUMPRODUCT(C6:F6,C$15:F$15)
24
=SUMPRODUCT(C8:F8,C$15:F$15)
> =G8
Parti
25
=SUMPRODUCT(C9:F9,C$15:F$15)+D16
> =G9
Part 2

Limit
450
4200
2000
# Required
1300
2600

Figure E.2
The representation of the actual programming problem of (E.l) is contained in
the lower half of the spreadsheet. The construction of this representation consists of
three parts.
(pi) The designation of the cells to be used as placeholders for the variables (here
cells C15:F15 and D16), the objective function (cell C18), the left-hand sides
of the constraints (cells C21:C25), and the right-hand sides of the constraints
(cells E21:E25).
(p2) The entering of the appropriate formulas in the objective function and constraints cells, usually through the use of Microsoft Excel's Formula Bar. The
region of cells containing formulas for this example (columns C through F,
rows 18 through 25) are shown in Figure E.2. Microsoft Excel's SUMPRODUCT function (read "dot product of row vectors" if you wish) is especially
helpful in expressing the linear forms of mathematical programming problems,
and frequently the formulas can be effectively drag-copied.
(p3) The completion of the listing of the constraints, designating for each constraint
the relationship between the left-hand and right-hand sides (cells D21:D25).
The last two steps in solving the problem involve Solver. Clicking on Solver
in the Tools pull-down menu superimposes the Solver Parameters window (shown
in Figure E.3) on the initial spreadsheet. In this window we enter the spreadsheet
locations of the components of the problem to be solved. To be designated in the
window are the locations of the cells in the spreadsheet containing the following:

434

APPENDIX E. MICROSOFT EXCEL AND SOLVER

Figure E.4
(si) The Target Cell, that is, the cell containing the objective function formula (with
auxiliary buttons for designating the goal: to maximize or to minimize).
(s2) The Changing Cells, that is, the cells designated for the decision variables.
(s3) The Constraints Cells, both left- and right-hand sides and the type of the constraint. These are added, adjusted, or deleted in the "Subject to the Constraints"
area in the lower, left of the Solver Parameters window, utilizing the corresponding pop-up subwindow (the Add Constraint subwindow is shown in Figure E.4). As the reader will see, all the appropriate assignments are in place in
the Solver Parameters window of Figure E.3.)
After these steps are completed, a click on the Options button in the Solver Parameters window brings the Solver Options window to the screen, as displayed in
Figure E.5. Here, for a linear programming problem we check the "Assume Linear
Model" box; and checking the "Assume Non-Negative" box eliminates the need to
enter in the constraints set window the nonnegativity restrictions on the variables (if
called for in the problem).
That completes the entering of the specifics of the problem into Solver. Clicking
the Solve button in the Solver Parameters window will now generate the "Solver Results" window displayed in Figure E.6. Since a solution exists for this problem, the
Solver Results window shows the message "Solver found a solution. All constraints
and optimality conditions are satisfied." The solution values for the variables, objec-

APPENDIX E. MICROSOFT EXCEL AND SOLVER

435

Figure E.6
tive function, and constraints will be displayed on the original spreadsheet, as seen in
Figure E.7. The user here also has the option of generating the associated Sensitivity
Report by clicking the corresponding word in the Reports window. The nature of
this report is discussed at some length in Sections 5.1 and 5.3.
Two other messages can be displayed when the Solver Results window appears,
indicating either that the objective function is unbounded ("The Set Cell values do
not converge") or that the problem has no feasible solution ("Solver could not find
a feasible solution"). One must carefully read the message in the Solver Results
Window before clicking OK to dismiss it, since each of these outcomes may modify
the data on the original spreadsheet; the hurried user might then unwittingly believe
that a solution has been found upon returning to the spreadsheet.
We close with some helpful comments on using Solver and Microsoft Excel:

436

APPENDIX E. MICROSOFT EXCEL AND SOLVER

A|
B
|
C
D
I
E
1 Division P
2
Process
3
Input
1
2
3
Labor (hr)
4
8
10
6
5
Material A (lb)
160
100
200
30
6
Material B (Ib)
35
60
7
Output
8
# units Part 1
35
45
70
9
# units Part 2
55
42
0
Cost ($/hr)
10
$400
$575
$620
11
Part 2 vendor cost/unit -->
$18
12
13
Variables
14
1
Process #
2
3
15
Hours used
4.82338 25.13737
0
16
# Units Part 2 purch ased -->
181.51766
17
18
Minimize cost
$26,845
19
20
Constraints
LHS
RHS
21
Labor
436
s
450
Material A
4200
22
s
4200
Material B
2000
23
<
2000
24
a
1300
Parti
1300
a
25
Part 2
2600
2600

4
12
75
80

Limit
450
4200
2000
# Required
1300
2600

0
90
$590

4
| 12.19363

Figure E.7
A factor to be considered when laying out the data tables is that the use of the
SUMPRODUCT function requires that the arrays being combined flow in the
same direction. For example, in the spreadsheet of Figure E.l, the variable
cells and their associated coefficients in the constraints both read horizontally,
allowing for the easy use of SUMPRODUCT. On the other hand, you may
want to make layout adjustments to facilitate the use of the SUMPRODUCT
(see, for example, Figure 8.10 of Section 8.4 on page 335, where the variable
cells are placed vertically to accommodate the data table structure).
Placing the characters (<) and (>) in Column D of the initial spreadsheet to
indicate the direction of the inequality in each of the five constraints provides
only a (very helpful) visual aid. (The entry of these characters is systemdependent; you may instead prefer to write simply the two-character sequences
<= or > = .) Solver makes no use of these entries, however; the appropriate inequality relations must still be entered directly in the Add Constraints window
in step (s3) above.
The solution to (E.l) on the spreadsheet in Figure E.7 calls for nonintegral
values for the variables. If integral values are required, one could, on the
spreadsheet, round off the value of each of the variables to the nearest integer
and then note the feasibility or nonfeasibility of this set of integers using the
spreadsheet's adjusted values for the left-hand sides of the constraints. Here,
in fact, the results would show that feasibility is maintained for the first four

APPENDIX E. MICROSOFT EXCEL AND SOLVER

437

constraints and that the output of Part 2 is only 13 units short of the required
2600. These can be purchased from the outside vendor, yielding an integral
solution that costs only $120 more than the original minimum cost, as can be
easily determined with the spreadsheet. Of course, this procedure in no way
guarantees that the optimal integral solution has been found here or that, for a
general problem, the procedures even leads to a feasible integral solution. Integer programming techniques may be required. Integer programming, along
with applications using Solver, is discussed in Chapter 6.