How Long Does Methocarbamol Stay In Your Urine?

PL Solving Problems with Excel

Excel provides a quick and efficient solution for solving linear programming problems (LP), which is why, it is essential to understand the mechanics of using the Excel Solver add.

the Solver in Excel is an optimization tool that uses programming routines that calculate values \u200b\u200bthat meet several conditions known as constraints.

SOLUTION OF A CLASSIC PROBLEM PL

Of course, to solve a problem PL, you need a statement, so that in the first instance shall the exercise proposed by CHASE (2000) on page 292, related to decisions subject to some constraints, whose content is paraphrased:

A company manufactures Hockey sticks and chess sets, bats take 6 hours on machine A, and 2 hours on machine B, while Chess games, take 6 hours on machine A, 6 B and 1 in the machine C. Hockey sticks produce a marginal profit of U.S. $ 2 while the chess game of $ 4.

The availability of machine time is 120, 72 and 10 hours respectively, how many Hockey sticks and chess games should PRODIC to maximize profit?

To solve the above problem, we proceed to design a mathematical model is then written in Excel, the PL model to be worked in Excel is shown in the chart below:

From the graph above we can say the following:

• B5 and C5 cells receive the value calculated by SOLVER and represent the values \u200b\u200bthat should have the variables for the utility is maximized.
• The cell D4 has a formula that calculates the value, this formula linked possible values \u200b\u200bof the variables with their coefficients expressed in cells B4 and C4. The formula that is handled in this cell can be = SUMPRODUCT (B4: C4, B5: C5) o = B4 * B5 + C4 * C5
• cells D7, D8 and D9 are the formulas that represent the constraints and why they are smaller than cells E7, E8 and E9, which represent the value restrictive.
• restrictions formulas are like this in cell D7, like this: = SUMPRODUCT (B $ 4: C $ 4, B7: C7).

To solve the exercise, you must activate the checkbox "Assume Linear Model" of the "Solver options", click the button "Options ..." and take you to the next window:

When I returned to the Solver dialog box, click the "Resolve" and immediately the exercise is solved, the results are shown as follows:

The interpretation of these results is as follows: For Maximum benefits of $ 56 (cell D4) must occur 12 hockey sticks (cell B5) and 8 games Chess (cell C5 .)

SOLUTION TO THE PROBLEM OF TRANSPORT

Transport Method is a special case of the Simplex Method, which was the method previously used to solve the problem illustrates the solution PL.Se Transport Method using the following example of CHASE (2000), p. 317.

To solve the transport table, write an elaborate scheme of tables and formulas in Excel, the picture is as follows:

In the figure above, should be taken into account the following considerations:

• The first table is the same model written statement in excel rows and columns
• The second table represents the space where SOLVER calculated values \u200b\u200bto be assigned to all variables to be settled the LP problem, the range B12: E14, represents the space for those values.
• The third table presents the cost estimates, which are obtained by multiplying the costs in the first table referenced by the values \u200b\u200bcalculated in the second table SOLVER. An example of the formulas in this second table is cell B19, the formula is = B12 * B5.
• The cell F22 represents the sum total of the costs, formula = SUM (B22: E22).

After these considerations, we proceed to fill in the Solver dialog box, as follows:

Note that the target cell is F22, as it represents the sum of the costs and the range B12: E14 is the place where SOLVER calculated values \u200b\u200bto be assigned to variables. Also the box of the target cell value is minimal.

Additional considerations, such as B8, C8, D8 and E8 equal to B15, C15, D15, E15 means that they have complied with the requirements of the problem. Also, cell F12, F13, F14 should be less than F5, F6 and F7 because it can not be assigned more than what we have available in stock.

After making sure to take a linear model in the "Solver Options", click on the "Resolve" and the result is as follows:

The interpretation is that when allocating INICA values \u200b\u200bas in the range B12: E14 minimum transport cost is U $ 720 (cell F22).

Guide Book: CHASE, Richard (2000). Production and Operations Management. Bogotá: Mc Graw Hill.
To download the complete document, click here