Linear Programming Problem (LPP) using Solver in Microsoft Excel

Linear programming problem is a method of finding the optimum value (maximum or minimum) of any variable. It uses mathematical modelling to represent and solve the problem at hand. The problems will be linear function. There will be different set of constraints applied to such linear problems. We can solve different kind of problems. Please see the examples given below.

• Product Mix Problem
• Flight Scheduling Problem
• Investment Problem
• Capital Budgeting Problem
• Production Mix Problem

This article we will discuss one Linear Programming Problem, we will formulate it in mathematical model, and step by step solve it using Solver add-in in Microsoft Excel. I have provided the Spreadsheet used in example at end of article.

Question:

A company has two bottling plants, one located at Bhopal and other at Indore. Each plant produces three drinks, energy drinks, flavored drinks and fruit juices named A, B, C respectively. Per day bottle production capacity is as follows:

Demand of the drinks A, B and C will be 20000 bottles, 40000 bottles and 44000 bottles, during the month of April, according to market research. Per day operating cost for plants at Bhopal and Indore are 600 and 400 monetary units.

Now, the question is, for how many days each plant should be run in April so as to minimize production cost and to meet customer demand?

Formulation of Linear Programming Model

The first step will be developing the mathematical model for the problem given,

Let x₁ and x₂ are decision variables which denotes the number of days in April for which the bottling plant in Bhopal and Indore must be run respectively.

After this next step will be making the model in Excel Spreadsheet. As shown in image “Excel Spreadsheet Model” we have converted mathematical model into the tabular format for further calculation.

Making the Excel Spreadsheet Model

Cell Values

Here , Cell E5 represents the objective function (which is to minimize the cost).Cell C4 and D4 are values of decision variables x₁ and x₂ which denotes the number of days in April for which the bottling plant in Bhopal and Indore must run respectively.

Here Cell G8:G10 represents the minimum demand to be met for product A, B and C respectively. Cell E8:E10 represents the Total quantity of the product A, B and C to be produced. Also cell C8:C10 represents the quantity of product A, B and C to be produced at Bhopal Plant. Cell D8:D10 represents the quantity of product A, B and C to be produced at Indore Plant.

Now we can use the solver to find the optimal solution for the problem. Solver is a free Excel add in need to be installed (Link).After installing this add in, Go to Data > Analysis > Solver.

Input solver parameters

Use of solver has made the calculation easy for us while solving Linear Programming Problem if we compare it with manual calculation methods(Graphical, Simplex). Therefore we need to make sure that we are defining the following things in correct manner otherwise it will lead to an error.

• Defining the objective function (Maximize or Minimize)
• Define the variable cells (Decision Variables)
• Define the constraints.

We want to minimize the total cost. For that, Select cell E5 in set objective value. Choose “Min” in radio button. Now in “By changing cell variables” select the decision variables C4 and D4.

To add the constraints , Click on Add in as shown in Image above. It will open the dialogue box as shown in “Cell reference” select the cell E8. Choose the “>=” (greater than or equal to) sign from drop down menu. Select cell G8 in “Constraints”. Do the same for remaining two constraints.

Now select the check box “Make Unconstrained Variables Non- Negative” which states that decision variables x₁ and x₂ are greater than or equal to zero.

Select solving method as “Simplex LP”. Once all the inputs parameters are defined click on the “Solve”.

Excel spreadsheet model with Solver Solution

Above image shows the solver solution for our Linear Programming Problem. Solver has come up with solution that plant at Bhopal should run for 12 days and Indore should run for 4 days to meet the objective of minimizing the cost which is 8800 monetary units.(Cell E5)

In above example, the manager has to decide for how many days he has to run the plant at Bhopal and Indore. To minimize the cost it is clearly seen(intuitively) that Indore has low production cost compared to Bhopal plant as a result we should only run the plant at Indore. But if you compare it with the solution we can see that the plant at Bhopal has more production cost than Indore, still it should be run more no. of days considering the demand constraints.

Template

Excel File

Thank you…!

If you know any subject that can be related to manufacturing industry or industrial engineering, you can earn some income by becoming article contributor of this website. For knowing more about it, please visit Join us page.

You don’t need to have any experience in article writing, just knowledge on the subject is needed.

Also you can know more about our team of article contributors by visiting the about us page.

About the Author

Nikunj Patel is a researcher and post graduate in Industrial engineering, Graduate in Mechanical engineering. His area of interests are Statistics, Operations Research, Supply Chain Management, Quality Management.