# Linear Programming Using Excel Application

Only available on StudyMode
• Published : September 1, 2011

Text Preview
Linear Programming Using Excel
Subject: Linear Programming using Excel Application: Microsoft Excel 2007 Task: Solving a Linear Program Using Excel Tutorial Date: 25th February, 2010 by Nathan Smith

1. In the Microsoft Office button, go to excel options to click Add-ins 2. In the Add-Ins box, select Solver Add-In and click Go...

Linear Programming Using Excel - 1

3. In the Add-Ins available box, check the Analysis ToolPak and then OK

Linear Programming Using Excel - 2

Setting Up the Problem on the Spreadsheet

Example Min Z = 6X + 7Y s.t 2X + 6Y â‰¥ 10 5X + 3Y â‰¥ 10 X,Y â‰¥ 0

Linear Programming Using Excel - 3

(continued)

1. Enter the coefficients of the objective function Z i.e., (6, 7) in cells E5 and F5. 2. Enter the coefficients of the Constraint-1 i.e., (2,6) and RHS value 10 in cells E9, F9 and H9 respectively 3. Enter the coefficients of the Constraint-2 i.e., (5,3) and RHS value 10 in cells E10, F10 and H10 respectively

Linear Programming Using Excel - 4

(continued)

1. For the Objective function value, enter the formula for computing Z = SUMPRODUCT(E5:F5,E6:F6). This formula uses the coefficient values and also the solution values for variables X and Y, which are supposed to be solved. 2. Similarly enter the formula for LHS of the Constraints 1 & 2 i.e., SUMPRODUCT(E9:F9,\$E\$6:\$F\$6) & SUMPRODUCT(E10:F10,\$E\$6:\$F\$6) respectively

Linear Programming Using Excel - 5

Now Excel Solver will be used, in the Data tab click Solver. The solver box appears as follows.

(continued)

1. Set the Target Cell for the Objective Function Z value i.e., \$E\$7 2. Check the Equal to Min i.e., Minimum Option. 3. For Changing Cell, select the solution values of the variables X & 7 i.e., \$E\$6:\$F\$6 Linear Programming Using Excel - 6

4. For subject to the constraints, LHS >=RHS i.e., click on the Add option and select \$E\$13:\$E\$14 >= \$E\$13:\$E\$14 5. Also all the...