How to Find Optimal Solution with Linear Programming in Excel

Linear Programming is very useful in terms of resource optimization.

In this article, we will demonstrate how to find an optimal solution in linear programming in Excel. We’ll use the Solver tool to find this solution. Solver is a Microsoft Excel tool that uses techniques from operations research in order to find optimal solutions for various kinds of decision problems.

Step 1 – Activating the Solver Add-in

Generally, you will find the Solver option in the Data tab on the Excel Ribbon, which is at the top of an Excel sheet.

If the option is not available, follow these steps to add Solver to your Data tab:

Click on the File menu.

The Options tab is at the bottom of the list.

Click the Options button and a tab will open like the one below.
Select Add-ins.

Click on Go.

The Add-ins window will appear.

Tick the Solver Add-in option and click OK.

We have successfully activated the Solver option on the Data tab.

Step 2 – Presenting the Problem Data in a Table

Now we’ll place a practical problem into a data table, in order to find the optimized result.

Suppose a manufacturer is producing two products (Product A and Product B) that need the same three types of raw materials, named Raw Material 1, Raw Material 2, and Raw Material 3.
To produce Product A, they need 10 units of Raw Material 1, 30 units of Raw Material 2, and 20 units of Raw Material 3.
For Product B, they use these materials in quantities of 15, 20, and 25 units.
It is mandatory to use a minimum amount of each material. The minimum quantities of Raw materials 1, 2, and 3 are 120, 90, and 70.
The market price of Product A is $400 and Product B is $500.

Let’s determine the optimized Quantity of Products A and B so that the company will gain maximum profit.

Step 3 – Applying Formulas for Linear Programming

After preparing our table, we want to determine the optimal quantity to determine the maximum price. Therefore, we will apply some formulas to the cells to get the total price.

Enter the following formula in cell E6;

=($C$5*C6)+($D$5*D6)

This formula multiplies the unit price by the quantity for both products, then adds both prices to get the total price in cell E6.

Copy the formula to cells E9 to E11 by using the AutoFill tool.

Step 4 – Using the Solver Add-in to Determine Optimized Value

This is the most important step, where the Solver is used.

Go to the Data tab and select Solver from the Analyze menu.

The Solver Parameters window will appear.

Fill the values for these 3 options accordingly.

The set objective is cell $E$6.

As we want the minimum amount of materials to gain maximum profit, select the Min option.
Enter $C$5:$D$5.for the Variable cells, in which the optimized quantity of products will be determined.

Step 5 – Adding ‘Subject to the Constraints’ Conditions

The Solver needs some Subject to the constraints conditions in order to make a decision.

Below the Subject to the Constraints box, Add the conditions.

Now we enter values for Cell Reference, Inequality sign, and Constraint.

Cell Reference is $C$5:$D$5, inequality is >= and Constraint will be 0. X and Y cannot both be zero.

Click on Add again.

Add the following: Cell Reference as $C$5:$D$5, inequality as >= and Constraint as $G$9:$G$11.

Click OK.

Step 6 – Finding the Optimal Solution to Maximize Profit

Now we can solve according to the given Solver Parameters.

Click Solve.

The Optimized Quantity of Product A and B are returned, which are 12 and 54 respectively. Moreover, the Total required units of each raw material and the optimized Price are also provided.