Tanjim Reza
At times, we need to solve multiple variables through linear programming. There are several ways to solve linear programming. The handiest method to solve linear programming is through graphing. In this article, I will show you detailed steps to graph linear programming in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
What Is Linear Programming?
Linear programming is a mathematical tool to analyze a situation through several mathematical functions and constraints and find the optimum point of your objective. This technique is vastly used in optimizing business investment, production cycle, in terms of buying our required products, etc.
Basic Components of Linear Programming
- Decision Variables: These are the variables that are needed to calculate the optimum point of our objective through linear programming. The situation of our decisions, constraints and objective function are set with these variables.
- Constraints: Constraints are the conditions that limit the objective function and determine the feasible region. They can be both equalities or inequalities.
- Objective Function: This is the function of your objective. You have to satisfy this equation with proper constraints to find the optimal solution.
- Feasible Region: This region is the optimal region of the objective function after applying the proper constraints. The optimal solution lies somewhere in this region.
- Feasible Solution: Feasible solutions are the solutions of the objective function for the corner points of the feasible region.
- Optimal Solution: Optimal solution is the optimal point of your objective function. You can find this from the calculated feasible solutions.
Steps to Graph Linear Programming in Excel
Let’s say, you are given an objective function as F = 6X+8Y. You have to maximize this function with the constraints:
2X+4Y <= 60
4X+2Y <= 48
Now, you can find the optimum point by graphing the linear programming in Excel by using the following steps below.
📌 Step 1: Record Objective Function & Constraints Line Points
To graph linear programming in Excel, first and foremost, you need to record your objective function and the points of the constraints.
- To do this, first record the coefficients and symbols of the objective function and constraints properly.
- Now, for the first constraint, C1, find two points of the equation to draw the constraint. You can do this by taking X=0 which would give you Y as 15. Similarly, taking Y=0 would give X as 30.
- Subsequently, find two points for the second constraint, C2, similarly. Here, if you take X=0, you would find Y as 24. And, similarly, for taking Y=0, you would find X=12.
As a result, you will have a worksheet with your objective function, constraints, and two points for drawing the constraints. The worksheet would look like this finally.
📌 Step 2: Find Feasible Region
Following the first step, you need to find the feasible region now.
- To do this, initially, select the B6:C8 cells. Subsequently, go to the Insert tab >> Charts group >> Insert Scatter or Bubble Chart tool >> Scatter with Smooth Lines option.
- As a result, you will have a scatter plot with smooth lines according to the values of the B6:C8 cell.
- But, the plot is not in the format that you need. So, right-click on the chart. Subsequently, choose the Select Data… option from the context menu.
- Consequently, the Select Data Source window will appear. Following, choose the series1 option here and click on the Edit button.
- At this time, the Edit Series window will appear. At the Series name: text box, write C1. At the Series X values: text box, select the range as B6:B7 cells. Similarly, at the Series Y values: text box, select the range as C6:C7 values. Last but not least, click on the OK button.
- Now, you will be back in the Select Data Source window. Here, click on the Add button.
- As a result, another Edit Series window will appear. At the Series name: text box, write C2. Following, at the Series X values: text box, select the data range as B11:B12 cells. Similarly, at the Series Y values: text box, select the data range as C11:C12 cells. Subsequently, click on the Ok button.
- Now, you will be back again in the Select Data Source window. Next, click on the OK button here.
- Consequently, you will see you will have a scatter graph with all constraints of your linear programming. And, the graph should look like this.
- Now, as both the constraints are in less than or equal inequality, both constraint lines will be directed to the origin. As a result, the feasible area would be like the following figure.
Thus, ABCD is the feasible region, and A, B, C, and D are the corner points of the region.
📌 Step 3: Find Optimum Solution
After determining the feasible region, you need to find the feasible solutions now.
- In doing this, at the very beginning, you have to find the X and Y coordinates of the corner points. From the graph and constraint value table, we can find the A, B and C points very easily that are (0,15), (0,0), and (12,0) respectively.
- Now, for finding the coordinates of point D, select the cells D5:D6 and insert the formula below which involves the MMULT and MINVERSE functions. Subsequently, press Ctrl+Shift+Enter.
=MMULT(MINVERSE('Finding Points of Constraints'!C6:D7), 'Finding Points of Constraints'!F6:F7)
🔎 Formula Breakdown:
- MINVERSE(‘Finding Points of Constraints’!C6:D7)
This returns the inverse matrix of the Finding Points of Constraints worksheet’s C6:D7 cells’ values.
Result: (-0.166666667, 0.333333333) & (0.333333333, -0.166666667)
- =MMULT(MINVERSE(‘Finding Points of Constraints’!C6:D7), ‘Finding Points of Constraints’!F6:F7)
This returns the matrix product of the previous result’s array and the Finding Points of Constraints worksheet’s F6:F7 array.
Result: {6,12}
- As a result, you will get the coordinates of the intersecting point D of the two constraint lines.
- Now, you have all the corner points. At this time, you need to find feasible solutions from these points. For this, write the formula below in the C7 cell. Subsequently, press the Enter button.
=(C5*'Finding Points of Constraints'!$C$5)+('Finding Points of Constraints'!$D$5*C6)
- =(C5*’Finding Points of Constraints’!$C$5)
This would calculate the multiplication of the C5 cell value and the Finding Points of Constraints worksheet’s C5 cell value.
Result: 0
- (‘Finding Points of Constraints’!$D$5*C6)
This would multiply the Finding Points of Constraints worksheet’s D5 cell value with the C6 cell’s value of the current worksheet.
Result: 120
- =(C5*’Finding Points of Constraints’!$C$5)+(‘Finding Points of Constraints’!$D$5*C6)
This would sum up the previous two results.
Result: 120
- As a result, you will get your objective function’s value for the corner point, A. Following, place your cursor in the bottom right position of your cell. Consequently, the fill handle will appear. Drag it rightward to copy the same formula for all other points.
- As a result, you will get all the feasible solutions.
- Last but not least, you will need to find the maximum value of F to solve your linear programming through graph as you are required to maximize the F. Now, as you can see the maximum value of F is 132 at the D (6,12) point. So, your optimum point is the D (6,12) point.
Thus, your linear programming through graph ends and the final result comes.
Read More: How to Solve Blending Linear Programming Problem with Excel Solver
Conclusion
To conclude, in this article, I have shown you all steps in detail to graph linear programming in Excel. I would suggest you go through the full article carefully and practice thoroughly with our practice workbook. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit Excel Demy for many more articles like this. Thank you!
