Solve Linear Programming Problems
Check that Solver is installed Open Excel Click on the ‘tools’ menu If Solver is listed, then go to Formulation. Otherwise, Solver needs to be installed, as follows: Again under ‘tools’ click ‘Add-ins..’. The window that appears lists the available add-ins, Click the box next to Solver so that it contains a tick, click ok. Solver should now appear under the ‘tools’ menu
Formulation
Whenever we formulate a worksheet model of a linear program, we perform the following steps (Par. problem as an example, see appendix):
Step 1: Enter the data in the worksheet
Cells B7:C10 show the production requirements per unit for each product. Cells B5:C5 show the profit contributions per unit for the two products. Cells F7:F10 show the number of hours available in each department. Step 2: Specify cell locations for the decision variables Cells B4:C4.
Step 3: Select a cell and enter a formulation for computing the objective value function. Cell D5: =B4*B5+C4*C5 or SUMPRODUCT($B$4:$C$4,$B5:$C5)
Step 4: Select a cell and enter a formulation for computing the left-hand side of each constraint. Cell D7:=B4*B7+C4*C7 or SUMPRODUCT($B$4:$C$4,$B7:$C7) (copy from Cell D5) Cell D8:=B4*B8+C4*C8 or SUMPRODUCT($B$4:$C$4,$B8:$C8) (copy from Cell D5) Cell D9:=B4*B9+C4*C9 or SUMPRODUCT($B$4:$C$4,$B9:$C9) (copy from Cell D5)
Cell D10:=B4*B10+C4*C10 or SUMPRODUCT($B$4:$C$4,$B10:$C10) (copy from Cell D5)
Tips:
(1) SUMPRODUCT function requires specifying two cell ranges of equal size, separated by a comma, such as SUMPRODUCT($B$4:$C$4,$B5:$C5). The SUMPRODUCT function computes the products of the first entries in each range, second entries in each range, and so on. It then sums these products.
(2) The $ symbol in the cells keeps that cell reference fixed when we copy the formula. This is especially convenient since the formula for calculating the sum of the left-hand-side value for each constrain also follows the same structure as the objective function.
Excel Solution
The following steps show how Solver can be used to obtain the optimal solution to the Par, Inc., problem. Step 1: Select the Tools pull-down menu. Step 2: Select the Solver option.
Step 3: When the Solver Parameters dialog box appears. Enter D5 into the Set Cell box Select the Equal to: Max option
Enter B4:C4 into the By Changing Variable Cells box. Select Add .
Step 4: When the Add Constraint dialog box appears: Enter D7:D10 in the Cell Reference box Select
Enter F7:F10 into the Constraint box Click OK
Step 5: When the Solver Parameters dialog box reappears: Choose Options .
Step 6: When the Solver Options dialog box appears,
Select Assume Linear Models and Assume Non-negative Click OK .
Step 7: When the Solver Parameters dialog box reappears: Choose Solve .
Step 8: When the Solver Results dialog box appears:
Select Keep Solver Solution, and choose Answer and Sensitivity from Reports box.
The following table shows Excel layout for the Par. problem.
The answer report for the Par. problem is:
Answer the following questions: 1.
a. Which constraints are binding? Which are not binding?
b. What is the range of optimality for the objective function coefficient associated with standard
bags?
c. What is the range of optimality for the objective function coefficient associated with deluxe bags?
d. After the production, how many hours remain in finishing, and inspection and packaging
department?
e. What would be the impact on the production plan and profit if the objective function coefficient
associated with standard bags were to change to 12?
f. What would be the impact on the production plan and profit if the number of sewing department
were to decrease to 500?
g. What would be the impact on the production plan and profit if the objective function coefficient
associated with standard bags were to change to 9 while at the same time the objective function coefficient associated with deluxe bag were to change to 8?
2. Solve M&D Problem. (Answer: Obj=800)
3. Solve PM Problem. (Answer: Obj=216,300)
4. Solve MSA Problem. (Answer: Obj=15,166)
5. Solve Whole Wood Problem. (Answer: Obj=0.05)
Solve Linear Programming Problems
Check that Solver is installed Open Excel Click on the ‘tools’ menu If Solver is listed, then go to Formulation. Otherwise, Solver needs to be installed, as follows: Again under ‘tools’ click ‘Add-ins..’. The window that appears lists the available add-ins, Click the box next to Solver so that it contains a tick, click ok. Solver should now appear under the ‘tools’ menu
Formulation
Whenever we formulate a worksheet model of a linear program, we perform the following steps (Par. problem as an example, see appendix):
Step 1: Enter the data in the worksheet
Cells B7:C10 show the production requirements per unit for each product. Cells B5:C5 show the profit contributions per unit for the two products. Cells F7:F10 show the number of hours available in each department. Step 2: Specify cell locations for the decision variables Cells B4:C4.
Step 3: Select a cell and enter a formulation for computing the objective value function. Cell D5: =B4*B5+C4*C5 or SUMPRODUCT($B$4:$C$4,$B5:$C5)
Step 4: Select a cell and enter a formulation for computing the left-hand side of each constraint. Cell D7:=B4*B7+C4*C7 or SUMPRODUCT($B$4:$C$4,$B7:$C7) (copy from Cell D5) Cell D8:=B4*B8+C4*C8 or SUMPRODUCT($B$4:$C$4,$B8:$C8) (copy from Cell D5) Cell D9:=B4*B9+C4*C9 or SUMPRODUCT($B$4:$C$4,$B9:$C9) (copy from Cell D5)
Cell D10:=B4*B10+C4*C10 or SUMPRODUCT($B$4:$C$4,$B10:$C10) (copy from Cell D5)
Tips:
(1) SUMPRODUCT function requires specifying two cell ranges of equal size, separated by a comma, such as SUMPRODUCT($B$4:$C$4,$B5:$C5). The SUMPRODUCT function computes the products of the first entries in each range, second entries in each range, and so on. It then sums these products.
(2) The $ symbol in the cells keeps that cell reference fixed when we copy the formula. This is especially convenient since the formula for calculating the sum of the left-hand-side value for each constrain also follows the same structure as the objective function.
Excel Solution
The following steps show how Solver can be used to obtain the optimal solution to the Par, Inc., problem. Step 1: Select the Tools pull-down menu. Step 2: Select the Solver option.
Step 3: When the Solver Parameters dialog box appears. Enter D5 into the Set Cell box Select the Equal to: Max option
Enter B4:C4 into the By Changing Variable Cells box. Select Add .
Step 4: When the Add Constraint dialog box appears: Enter D7:D10 in the Cell Reference box Select
Enter F7:F10 into the Constraint box Click OK
Step 5: When the Solver Parameters dialog box reappears: Choose Options .
Step 6: When the Solver Options dialog box appears,
Select Assume Linear Models and Assume Non-negative Click OK .
Step 7: When the Solver Parameters dialog box reappears: Choose Solve .
Step 8: When the Solver Results dialog box appears:
Select Keep Solver Solution, and choose Answer and Sensitivity from Reports box.
The following table shows Excel layout for the Par. problem.
The answer report for the Par. problem is:
Answer the following questions: 1.
a. Which constraints are binding? Which are not binding?
b. What is the range of optimality for the objective function coefficient associated with standard
bags?
c. What is the range of optimality for the objective function coefficient associated with deluxe bags?
d. After the production, how many hours remain in finishing, and inspection and packaging
department?
e. What would be the impact on the production plan and profit if the objective function coefficient
associated with standard bags were to change to 12?
f. What would be the impact on the production plan and profit if the number of sewing department
were to decrease to 500?
g. What would be the impact on the production plan and profit if the objective function coefficient
associated with standard bags were to change to 9 while at the same time the objective function coefficient associated with deluxe bag were to change to 8?
2. Solve M&D Problem. (Answer: Obj=800)
3. Solve PM Problem. (Answer: Obj=216,300)
4. Solve MSA Problem. (Answer: Obj=15,166)
5. Solve Whole Wood Problem. (Answer: Obj=0.05)