How to Solve a Linear Programming (LP) problem with What’sBest!

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.

transportation-problem-diag

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.

decision-variables-whatsbes

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.

linear-programming-in-whats

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.

constraints-whatsbest

Finally, to go ahead with the resolution of the model we select the option “Solve” from the menu:

solve-whatsbest

After which we get the following results:

optimal-solution-transporta

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.

Diet Problem in Linear Programming solved with Excel Solver

One of the classic applications of Linear Programming is the diet problem. The Diet Problem in Linear Programming was one of the first optimization problems studied in the 1930s and 1940s. The main goal is to select a set of foods that meets certain daily nutritional requirements and preferences and additionally at minimum cost. Let´s consider the following list of foods with their nutritional profile to illustrate this application:

foods-table-diet-problem

We wish to propose a diet containing at least 2,000 (Kcal), at least 55 grams of protein and 800 (mg) of calcium. In addition, to provide some variety in the diet, some limits are set for the daily portions of food. This information is required to find the diet that has the lowest cost associated with meeting the above requirements. To do this we define the following linear programming model:

Diet Problem in Linear Programming

1. Decision Variables: Select a set of foods to include in the daily diet.

decision-variables-diet-pro

2. Objective Function: Minimize the cost of the daily diet.

objective-function-diet-pro

3. Constrains: Meets daily nutritional requirements and preferences.

constraints-diet-problem-2

The implementation of this model in Excel Solver to get optimal solution and optimal value is shown in the following image:

optimal-solution-diet-probl

The optimal solution is X1=4, X2=0, X3=0, X4=2,08, X5=1,68, X6=2 and the optimal value (cost of the daily diet) is $764,07. As the model is linear programming, fractional values ​​are allowed for the decision variables. So if we only look for integer values ​​for the decision variables, in this case we define an integer programming model which we will review in a future article.

Do you want to have the Excel file with the solution of this problem with Solver? Recommend us on Facebook, Google or Twitter using the social network tool at the bottom of this article and then download the file.

How to Download and Install the Trial version of What’sBest! 11.1 in Excel 2010

What’sBest! is an excellent add-in to Excel that allows us to solve linear, non linear, integer and probabilistic (stochastic) optimization models through an easy and intuitive interface. This program is highly recommended both for students as for professors in the field of Operations Research and is available in a free trial version. The following tutorial shows, step by step, how to download and install the trial version of What’sBest! 11.1 if you are user of Excel 2010 (if you have another operating system or version of Excel this tutorial will also help you for sure).

How to Download and Install the Trial version of What’sBest!

Step 1: Verify the operating system you use and the number of bits associated. What’sBest! It is compatible with Windows 2000, XP, Vista, Windows 7 and Windows 8. In this case we will show how to activate the add-in on a computer that uses Windows 7 Home Premium with a 64-bit operating system. To verify this setting go to My PC (or Computer) and then Properties of the System.

Properties of the System

In the system information, you can find the number of bits associated with your operating system as shown in the following image:

operating system bits

Step 2: Log in the download section of What’sBest! in the website of Lindo, a company located in Chicago, United States, with more than 22 years of experience in the development of software and applications for optimization and decision support. After getting to the download link, you must select the version of the program that is compatible with your operating system and your version of Excel.

version-whatsbest

Step 3: Fill out the form to get the file with the program. The fields with an asterisk (*) are mandatory.

form whatsbest

After completing the above correctly and pressing Submit, you will get a message indicating that a download link for the version of What’sBest! has been sent to the email address that you have provided.

download-whatsbest

Step 4: Log in your email address (the one provided when filling in the form). You should have received an email from LINDO Systems Inc with the link to download the program as shown below. (Confidential information has been hidden with black stripes and download link with red). Select the download link and will begin to download the program that comes on a file compressed in ZIP format to your computer.

link-to-download-whatsbest

Step 5: Once the download is complete (by default the file will be saved in the Downloads section of your computer) open the ZIP file and then run the setup.exe file inside as shown in the following image:

winrar-whatsbest

This will make the installation application that will guide you in the process of software activation.

install-whatsbest

licence agreement whatsbest

Step 6: The installation is complete. What’sBest! will be available at the right of the Add-Ins menu. The program is ready to be used and solve your optimization models.

wb-installed

Now that What’sBest! is installed on your computer you are ready to solve an optimization model.

Problem of Cutting, Assembling and Production of Chairs solved with Excel Solver

“The Old Chairs”, a rustic company manufactures, among many other products, three types of chairs A, B and C, which are sold at price of 11, 13 and 12 dollars each respectively. The chairs go through three processes, Cutting, Assembly and Painting, for which up to 17, 13 and 15 hours a week respectively are dedicated to these operations for these products. The type A chair takes 3 hours for cutting, 1 hour for assembling and 3 hours for painting. The type B chair takes 1 hour for cutting, 4 hours for assembling and 3 hours for painting. And finally the type C chair takes 5 hours for cutting, 2 hours for assembling and 2 hours for painting. According to the above information:

a) Solve the problem with continuous variables and point out the results for each variable.

Decision Variables: the level of weekly production was set for each of the varieties of chair as detailed below:

decision-variables-chairs

Objective Function: Maximize weekly earnings associated with the production and sale of the chairs.

objective-function-chairs

Constraints: In the process of cutting, assembling and painting the availability of weekly hours must be respected. Additionally the constraints of non-negativity must be met.

constraints-chairs

The computational implementation of the above problem with Excel Solver can achieve the following results:

optimal-solution-chairs

Where the optimal solution is A=1,914286, B=1,828571 y C=1,885714 with optimal value V(P)=67,45714.

b) Modify the conditions of the variables and select them whole (integer) and observe the difference between the response from point a) and this new one found.

When defining the decision variables as integers we are facing an Integer Programming model (being the first stage a problem in Linear Programming). The results are:

integer-optimal-solution-ch

The optimal solution is A=1B=2 y C=2 with optimal value V(PE)=61.

c) Reach to a conclusion about what happened between continuous variables and integer variables.

It is important to note that the domain of feasible solutions of the problem (part b) is a subset of the domain of feasible solutions of the linear problem (part a). Therefore it is natural that, without obtaining a solution with integer values for the decision variables in the first problem, the optimal value necessarily will decrease in whole variant of this maximization (V(PE)<V(P)). It can also be noted that the integer solution, not necessarily is achieved by approximating the fractional results of a solution of a linear problem to the nearest lower or higher integer. Consequently, to address the resolution of a model which takes integer values for the decision variables efficiently, it requires a specific algorithmic alternative such as the Branch and Bound Method.

Below is a link to download the Excel file used for solving the problem of cutting, assembling and production of chairs. Two sheets corresponding to part a) and b) the proposed problem are included in the file: Production of Chairs.