How to Graph Linear Programming in Excel (Detailed Steps)

What Is Linear Programming?

Linear programming is a powerful technique used to optimize various scenarios, such as business investments, production cycles, and resource allocation.

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: The optimal solution is the optimal point of your objective function. You can find this from the calculated feasible solutions.

Objective Function and Constraints

Suppose we have the following objective function:

F = 6X+8Y

And two constraints:
- Constraint 1:

2X+4Y <= 60

- Constraint 2:

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

Begin by noting down the coefficients and symbols for your objective function and constraints.

For the first constraint (C1), find two points on the equation. Set X=0 to get Y=15, and set Y=0 to get X=30.

Similarly, find two points for the second constraint (C2). Setting X=0 gives Y=24, and setting Y=0 gives X=12.

Create a worksheet with your objective function, constraints, and these two points. The worksheet would Finally look like this.

Step 2 – Determine the Feasible Region

Select cells B6:C8.
Go to the Insert tab, choose Scatter or Bubble Chart, and select Scatter with Smooth Lines.

The resulting scatter plot will not be in the desired format.

Right-click on the chart, select Select Data.

Choose the series1 option.

Edit the series by naming it “C1” and specifying the X and Y values from B6:B7 and C6:C7, respectively.
Click on the OK button.

In the Select Data Source window, click on the Add button.

Add another series named “C2” with X values from B11:B12 and Y values from C11:C12.

Click on the Ok button.

Confirm your selections and create the scatter graph.

Since both constraints are less than or equal inequalities, the feasible area will be bounded by the lines and directed toward the origin.

The resulting graph should resemble the figure provided, with ABCD representing the feasible region, and A, B, C, and D as the corner points.

Step 3 – Determine the Optimum Solution

After identifying the feasible region, the next step is to find feasible solutions.

Begin by locating the X and Y coordinates of the corner points. From the graph and constraint value table, we can easily identify points A (0,15), B (0,0), and C (12,0).

To find the coordinates of point D, select cells D5:D6 and enter the following formula involving the MMULT and MINVERSE functions:

=MMULT(MINVERSE('Finding Points of Constraints'!C6:D7), 'Finding Points of Constraints'!F6:F7)

Formula Breakdown

MINVERSE(‘Finding Points of Constraints’!C6:D7)

The MINVERSE function calculates the inverse matrix of the values in the Finding Points of Constraints worksheet (C6:D7)

Result: The result provides the coordinates of point D: (-0.166666667, 0.333333333) and (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.

With all corner points identified, proceed to find feasible solutions. In cell C7, enter the formula:

=(C5*'Finding Points of Constraints'!$C$5)+('Finding Points of Constraints'!$D$5*C6)

Formula Breakdown

=(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 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.