Unformatted text preview: Microsoft Excel’s Solver Reference Guide Written for the Cal Paly Industrial and Manufacturing Enqlnnring Department by Rob Connolly. October 2004 Microsoﬁ Excel has an Add-in called “Solver" that can be used to solve linear programming (LP) problems. This reference
demonstrates how to install and use the Solver for LP problems. Installation of the Solver The Solver Add-In is not included with default installations of Microsoft Excel. To check to see if Solver is installed, in the
Tools menu, check to see if you see Solver in the menu. [fit is listed in your menu, the Solver has already been installed, and
you may proceed to Solving LP Problems using Microsoft Excel’s Solver. See Figure l I Step "if Diasczjiption ' ; 1'. . .
If the Solver is not in your Tools menu, select Add-Ins
.. _ from the Tools Menu. See a
.MMﬂm Select Solver Add-In, and click OK. ”the Solver will mm 31% then lac-installed and you can proceed to solve your LP .Rﬁm problem. See (b) Arm-um.
madam»: Figure 1 Solving LP Problems using Microsoft Exeel’s Solver
This Solver reference guide will use the Maximization problem below: Objective Function:
Maximize Z = 50x1 + 80):; Subject to the Constraints:
X] + X2 S 1000
x; 2 250
x; 2 250
x. - 2x2 2: 0
in, x: 2 0 I. First type in the Objective Function as in Figure 2. Row 6 Type in your variables x1, x2, (these will not be used
in an calculation and are for our reference on] ) ' T J‘ue our Ob'ective Function coefﬁcients - . Row 8, Cells Type in zeros underneath each variable. The Solver will Wows Ev." "on i . -; ' ' ' C8 & D8 change the values of these zeros to the values that will
' ' " " ivc ou an otimal solution to our LP formulation. Leave one blank cell to the riht of our variables Type in an “=” Sign for reference (this will serve ﬂ . ur nose for Excel; it is there for our reference on] .) Using the “Borders“ tool 3 put a box around your
“Target Cell"; in this case it is cell GS. Later, this will be where we tell Solver to calculate the optimal solution
(Mafoin Z) for your problem _I ...
View Full Document
- Winter '08