{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Excel Solver ex 10-10-08

# Excel Solver ex 10-10-08 - Solving LP’s in Excel 2000 Max...

This preview shows pages 1–5. Sign up to view the full content.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Solving LP’s in Excel 2000 Max 2 = 50xl + 20x2 + 25x3 st. 9x1 + 3x2 + 5x3 S 500 5x1 + 4x2 S 350 3xI + 2x3 S 150 x S 20 Problem 3.1-3 from Hillier and Liberman =SUMPRODUCT(B3:D3,B4:D4) SUMPRODUCT(B\$4:D\$4,B5:D5) SUMPRODUCT(B\$4:D\$4,B6:D6) -SUMPRODUCT(B\$4:D\$4,B7:D7) SUMPRODUCT(B\$4:D\$4,B8:D8) 1. Under Tools menu, select Solver option. (If it is not there, you may have to go the original disks and install using the customize Option. If you choose only the equation solver, it should not affect the other portions that are already installed.) 2. Click on Options button, a. Check box by “Assume Linear Model” b. Check box by "Assume Non-Negative" Click OK button. In box labeled “Set Target Cell”, enter F3 (it is the objective function value). Select proper objective (max or min). In box labeled “By changing cells”, enter B4:D4 (these are values of decision variables). Under the box labeled “Subject to the constraints”, click the Add button. For cell reference, put E5:E8; select <=; for constraint, put F5:F 8 and click OK button. 7. Click on Solve button, optimum decision variable values will be in boxes B4 through D4. 9995” Always name your variables and constraints (as in row number 2 and column A). This will permit easy reading of the solution. Notice the \$ in the formula used in cell E5. The dollar sign designates an absolute address so ' that the “ﬁll down” command (use the control-d keys) may be used to automatically put the proper formulas in the remaining cells. For integer problems, variables can be restricted to be integer or binary. For example, if the above problem had the restriction that x1 and x2 were integers, then under step six after the Add button is clicked, enter B42C4 on the left side and for the inequality select the option int" for integer. Notice that "bin" can be selected for integer variables that are restricted to be either zero or one. LP Solver Example 10-10-08 x1 x2 X3 0 0 50 20 9 3 5 4 3 0 0 0 2 O 5 AMOU’I x0 500 350 150 20 LP Solver Example 10-10-08 x1 26.19048 50 OWU‘ItD x2 54.7619 20 3 4 0 0 x3 x0 20 2904.762 5 500 0 350 2 118.5714 1 20 500 350 150 20 Microsoft Excel 11.0 Sensitivity Report Worksheet: [LP Solver 10-10.xls]Sheet1 Report Created: 1011 012008 8:13:45 AM Aﬂﬂﬁﬂﬂﬁﬁfﬂimm” ”ﬁéaﬁbéa”” u “"139 Va \$A\$3 x1 26.19 \$1333 x2 54.76190476 \$ ,, Eooo‘; Cell Nam Value M ultiplier .\$D..\$:6:..: ::..,:-,; . . .‘ _...,-..‘-..1- ‘-..1._. - -. . .. .. \$D\$7 X0 350 1 428571429 \$D\$8 x0 1185714286 0 w w M2 WWSBM W Z: Xp—ZX-L-I-Xg #36 ’Kl—l—ZXZ +{X3 2X! 'I'IX'Z— +3X'5 LG ’(Xl +3X'z. +6 X5 5 9 X232 ...
View Full Document

{[ snackBarMessage ]}

### Page1 / 5

Excel Solver ex 10-10-08 - Solving LP’s in Excel 2000 Max...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online