Define the Feasible Region. Use the constraints (inequalities) to bound the feasible region. For constraints with the form “ax1+bx2≤C”, convert them into equations by dropping the “≤” or “≥” symbol and adding the equal sign “=”, such as “ax1+bx2=C”. Then solve the equation for x1=0 to obtain the point where the linear equation crosses the x1 axis. Similarly, solve for x2=0 to obtain the point where the linear equation cross the x2 axis. Obtain the vertices of the feasible region. Do this by identifying the points in the feasible region and, for the constraints with the form “ax1+bx2≤C”, convert them into equations “ax1+bx2=C” and obtain the value of the unknown variable, either x1 or x2, by solving the equation. Substitute vertices into the Objective Function. Substitute the values of the variables (x1 and x2) at the vertices into the objective function equation. Select the values of the variables that are the maximum or minimum for the objective function, depending on the definition of the objective function (Step 1). Installing Open Solver - Model Installing the Open Solver in excel. 1.- Copy the OpenSolver21.zip into your computer. 2.- Unzip this file 3.- Double click on the file: “OpenSolver” which is the add-In 4.- If a dialogue-window pops up, please select “enable macros” 5.- On the data menu of Excel you should see the Open Solver add-in in the right corner of the menu.
Figure 19 Now, we will add the constraints to the model. Let’s start with the first constraint (XA≤400), go to the constraints section and click on the icon to browse for the left hand side of the constraint (Figure 20). Select the cell O10 (Figure 21), then go back to the dialogue menu. Select the symbol of equal or less than “≤” (Figure 22). Now click on the icon to select the right side of the inequality (Figure 23), cell Q10 (Figure 24) and go back the dialogue window. Click on “Add Constraint” (Figure 25). The inequality Cell O10 ≤ Cell Q10 should have appeared in the left side of the Constraints section of the dialogue window (Figure 26). We just have finished declaring the first constraint. Figure 20 Figure 21 Figure 22 Figure 23 Figure 24 Figure 25 Figure 26 To declare the second constraint (XA ≥ 0), click on the icon to browse for the left hand side of the constraint (Figure 20). Select the cell O13 (Figure 27), then go back to the dialogue menu. Select the symbol of equal or greater than “≥” (Figure 22). Now click on the icon to select part of the inequality (Figure 23), select cell Q13 (Figure 29) and go back the dialogue window. Click on “Add Constraint”. The inequality Cell O13 ≥ Cell Q13 should have appeared in the left side of the Constraints section of the dialogue window (Figure 30). Figure 27
To declare the fifth constraint (3XA+2XB ≤ 1800), click on the icon to browse for the left hand side of the constraint (Figure 20). Select the cell O22 (Figure 39), then go back to the dialogue menu. Select the symbol of equal or greater than “≤” (Figure 40). Now click on the icon to select right-side part of the inequality (Figure 23), select cell Q22 (Figure 41) and go back the dialogue window. Click on “Add Constraint”. The inequality of cell O22 ≥ Cell Q22 should have appeared in the left side of the Constraints section of the dialogue window (Figure 42). Figure 39 Figure 40 Figure 41 Figure 42 Check the box that says “List constraints and shadow prices in a table with top left cell:” and select the cell “AC8” (Figure 43) Figure 43 Click on “Save Model” (Figure 44). A Series of colorful rectangles should have appeared in your Spreadsheet (Figure 45). Notice that the Objective Function (cell Q5) has a “Max” label on top of it. Also, notice that the variables have a pink rectangle on top. In addition, for each constraint the right and left side of the equations/inequality are related with a line and a symbol (“≥”, “≤” or “=”), Double check that these symbols are correct. Figure 44 Figure 45 Running the Linear Programming Model It is time to run the model!!! Click on the Solve icon (Figure 46) of the Open Solver. After the screen blinks you will notice that numbers have changed. The maximum profit that can be obtained are $360,000 (Cell Q5)!!! The values for the variable have changed, XA=200 and XB=600, as we calculated in the presentation at class!!!