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.