Solve linear programming problem using Excel

Today we’ll be learning how to solve Linear Programming problem using MS Excel? Linear programming (LP) is useful for resource optimization. There are so many real life examples and use of linear programming. We’ll see one of the real life examples in the following tutorial.

Modelling Linear Programming

As the first step we have to do the modelling i.e. make the required equation. We can find the constraints in the right side (column N). Cell F4 is our equation P which has to be minimized and F6,F7,F8 are the constraints.

Solving the linear model using Excel Solver

Now go to Data and open solver. If you don’t find the Solver option please check How to enable Solver in MS Excel.

Solver window will appear. Now we have to follow the steps mentioned below-

1. Set objective is our equation which has to minimized here cell F4, 
2. Set To = Min,
3. Changing variables are x and y i.e. D3 and E3,
4. add constraints using Add option.
Please check image below for reference.

Add constraint window will appear once Add option clicked. Be careful while adding constraints and the sign (greater or less than). Once the constraints are added click OK.

Once everything is done Solver parameters will look like below and click Solve. That’s done.

We’ll see that now we have values for x and y and P. This was optimize solution for the problem.

 

Problem Source

So this was about how to solve linear programming in MS Excel using Solver add-in. Keep visiting Analytics Tuts for more tutorials. Comment your suggestions and doubts.