The following tutorial will show how to solve a Linear Programming (LP) model with What’sBest! 11.1. Of course, you previously need to download and install What’sBest! as a add-in for Excel the way we explained it step by step in a previous tutorial.
In order to show how to use this Excel add-in we will use the Transportation Problem to find a distribution policy that minimizes logistics costs, while satisfying customer demands and respects the capacity of suppliers. The information is summarized in the following diagram for the particular case of 2 industrial plants (suppliers) and 3 customers, where the numbers on the arrows represent the unit costs of transportation between an industrial plant and a customer.
How to Solve a Linear Programming problem with What’sBest!
The steps to implement this Linear Programming problem in What’sBest! are:
Step A: Define the Decision Variables: Previously set the cells that you will use as variables in an Excel worksheet. In the example the Xij: transported units from industrial plant i to customer j. If i=1,2 and j=1,2,3 there are 6 decision variables.
Important: Complete the cells that will be decision variables with zero as shown in the image above. Then select the range of cells that correspond to the variables in the model and press “Make Adjustable”.
Step B: Define the Objective Function: As the name suggests, this cell corresponds to the goal of the optimization problem which in this case is to minimize the total costs of transportation. The cell contains a formula: SUMPRODUCT(C3:E4;C12:E13) previously uploaded that ponders the unit costs of transportation for the different combinations (data or parameters) and the decision variable previously defined (“SUMAPRODUCTO” is the name of the formula in the spanish version of Excel). Finally go to the cell of the objective function and select “Minimize” in this case.
Step C: Define the Constrains: The constrains of the optimization model, i.e., the conditions to be fulfilled the decision variables at the time of the resolution are incorporated. For this purpose, select the “Constraints” option in the menu. The image below shows how, the restriction that guarantees that the number of units shipped by each plant (LHS) does not exceed (<=) the capacity of it, (RHS) is attached. As you can see the constrains of the capacity of Plant 1 and 2 are incorporated simultaneously.
Finally, to go ahead with the resolution of the model we select the option “Solve” from the menu:
After which we get the following results:
Optimal Solution: X11=80.000; X12=0; X13=40.000; X21=0; X22=70.000; X23=50.000. The Optimal Value (minimum cost) is $940.000.
Do you want to have the Excel file with the solution of this problem with What’sBest!? Recommend us on Facebook, Google or Twitter using the social network tool at the bottom of this article and then download the file.