Choose the “>=” (greater than or equal to) sign from drop down menu. It will open the dialogue box as shown in “Cell reference” select the cell E8. To add the constraints, Click on Add in as shown in Image above. Now in “By changing cell variables” select the decision variables C4 and D4. For that, Select cell E5 in set objective value. Define the variable cells (Decision Variables).Defining the objective function (Maximize or Minimize).Therefore we need to make sure that we are defining the following things in correct manner otherwise it will lead to an error.
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). Input solver parameters solver dialogue box
Solver is a free Excel add in need to be installed ( Link).After installing this add in, Go to Data > Analysis > Solver. Now we can use the solver to find the optimal solution for the problem. Cell D8:D10 represents the quantity of product A, B and C to be produced at Indore Plant. Also cell C8:C10 represents the quantity of product A, B and C to be produced at Bhopal Plant. Cell E8:E10 represents the Total quantity of the product A, B and C to be produced.
Here Cell G8:G10 represents the minimum demand to be met for product A, B and C respectively. Here, Cell E5 represents the objective function (which is to minimize the cost).Cell C4 and D4 are values of decision variables x 1 and x 2 which denotes the number of days in April for which the bottling plant in Bhopal and Indore must run respectively. 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?Ĭell references used in spreadsheet table Per day operating cost for plants at Bhopal and Indore are 600 and 400 monetary units. Per day bottle production capacity is as follows:ĭemand 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. Each plant produces three drinks, energy drinks, flavored drinks and fruit juices named A, B, C respectively. Question:Ī company has two bottling plants, one located at Bhopal and other at Indore. I have provided the Spreadsheet used in example at end of article. 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. There will be different set of constraints applied to such linear problems. It uses mathematical modelling to represent and solve the problem at hand. Linear programming problem is a method of finding the optimum value (maximum or minimum) of any variable.