Goal programming problems can be solved using QM for Windows and Excel spreadsheets. In this section we will demonstrate how to use both computing alternatives to solve the Beaver Creek Pottery Company example we solved graphically in the previous section.
## QM for WindowsWe will demonstrate how to solve a goal programming model by using our Beaver Creek Pottery Company example, which was formulated as follows :
QM for Windows includes a goal programming module that can be accessed by clicking on the "Module" button at the top of the screen. The model parameters are entered onto the data input screen as shown in Exhibit 9.1. Notice that each prioritized deviational variable must be assigned a weight, which for this problem is always one. The solution is obtained by clicking on the "Solve" button at the top of the screen. The solution summary for our model is shown in Exhibit 9.2. ## Exhibit 9.1.## Exhibit 9.2.QM for Windows will also provide a graphical analysis of a goal programming model. We click on the "Windows" button and then select "Graph" from the menu. The graph for our example is shown in Exhibit 9.3. ## Exhibit 9.3.## (This item is displayed on page 400 in the print version)## Excel SpreadsheetsSolving a goal programming problem by using Excel is similar to solving a linear programming model, although not quite as straightforward. Exhibit 9.4 shows the spreadsheet format for our Beaver Creek Pottery Company example. Cells G5, G6, and G7, under the heading "Constraint Total," contain the formulas for our goal constraints, including deviational variables. The formula for the labor constraint is shown on the formula bar at the top of the screen in Exhibit 9.4. The model decision variables are in cells B10 and B11, and the deviational variables are in cells E5:F7 . The goals are established by setting the constraint formulas in G5 to G7 equal to the goal levels in cells I5 to I7. ## Exhibit 9.4.## (This item is displayed on page 400 in the print version)When using a spreadsheet (or any regular linear programming program) to solve a goal programming problem, it must be solved sequentially . In this procedure, a new problem is formulated and solved for each priority goal in the objective function, beginning with the highest priority. In other words, the minimization of the deviational variable at the highest priority is the initial objective. Once a solution for this formulation is achieved, the value of the deviational variable that is the objective is added to the model as a constraint, and the second-priority deviational variable becomes the new objective. A new solution is achieved for each new objective sequentially until all the priorities are exhausted or it is clear that a better solution cannot be reached. For our purposes, this means editing Excel's Solver for each new solution. Exhibit 9.5 shows the Solver Parameters window for our spreadsheet example. Recall that the first-priority goal for our model is the minimization of the negative deviational variable ( d ## Exhibit 9.5. The spreadsheet with the solution to our example problem is shown in Exhibit 9.6. We know this is the most satisfactory solution we can achieve from the QM for Windows solution, so it will not be necessary to perform any additional sequential steps. From the spreadsheet we can see that we have achieved all the goals except the fourth-priority goal, to minimize d ## Exhibit 9.6.## (This item is displayed on page 401 in the print version)
Next we will use Excel to solve a slightly more complicated goal programming modelthat is, the altered Beaver Creek Pottery Company example we developed at the beginning of this chapter, with goals for overtime and maximum storage levels for bowls and mugs:
The spreadsheet for this modified version of our example is shown in Exhibit 9.7. The spreadsheet is set up much the same as the original version of this example, with the exception of the goal constraint for overtime. The formula for this goal constraint is included in cell G8 as = F5 + E8 F8 . In addition, the positive deviational variables for the last two goal constraints are now included in the formulas embedded in cells G9 and G10. For example, in cell G9 the constraint formula is = C9 * B13 + E9 . ## Exhibit 9.7.The Solver Parameters window for this spreadsheet is shown in Exhibit 9.8, and the resulting solution is shown in Exhibit 9.9. ## Exhibit 9.8.## (This item is displayed on page 403 in the print version)
## Exhibit 9.9.## (This item is displayed on page 403 in the print version) You will notice that this solution achieves the first two priority goals for minimizing d ## Exhibit 9.10. The solution is shown in Exhibit 9.11. This is the same solution that we obtained for our original version of the model, without the alterations for overtime and production levels. This solution achieves the first three priority goals. We could continue to attempt to achieve the fourth-priority goal by including F7 = 0 as a constraint in Solver and minimizing cell F5 (i.e., d ## Exhibit 9.11. |

Introduction to Management Science (10th Edition)

ISBN: 0136064361

EAN: 2147483647

EAN: 2147483647

Year: 2006

Pages: 358

Pages: 358

Authors: Bernard W. Taylor

Similar book on Amazon

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net