How to Use Excel Solver for Linear Programming (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

We can use Excel to carry out many different mathematical operations. Linear Programming is an aspect of Statistics and Applied Mathematics. This has huge practical applications. Solving Linear Programming problems manually can seem a hassle. On the other hand, we have Excel Solver which can find out the solutions to those problems very readily. In this article, we’ll show you the step-by-step procedures to Use Excel Solver for Linear Programming.


Introduction to Linear Programming

Linear Programming is an important aspect of Statistics and Applied Mathematics. You can perform predictive analysis with prevalent data variables. It helps us in the optimization of the resources. We must have some constraints and an objective function for that purpose. The Excel Solver can quickly figure out the solutions to Linear Programming problems by solving equations in Excel.


To illustrate, we’ll use the following business problem as an example.

Suppose, a manufacturer has two kinds of products, ‘A’ & ‘B’. A single unit of product A requires three raw materials, P 25 kg, Q 35 kg, and R 10 kg. Similarly, B requires P 15 kg, Q 20 kg, and R 15 kg. The manufacturer needs a minimum of P 500 kg, Q 850 kg, and R 300 kg. If A costs $35 per unit and B costs $30 per unit, how many units of each product should the manufacturer blend to meet the minimum raw material requirements at a low cost as possible, and what is the price?

Now, to solve this problem, go through the below steps carefully and also learn how to use Excel Solver for Linear Programming.


STEP 1: Enabling Solver Tool in Excel

The Solver is an MS Excel add-in program. It stays deactivated by default. So, you need to enable it to use the program. Therefore, follow the process to perform the task.

  • First, go to File ➤ Options.
  • Then, select the Add-ins tab.
  • After that, choose Excel Add-ins from the Manage drop-down.
  • Subsequently, press Go.

Enable Solver in Excel

  • As a result, the Add-ins dialog box will pop out.
  • Now, check the box for Solver Add-in.
  • Next, press OK.

Enable Solver in Excel

  • Thus, you’ll see the Solver program in the Analyze section under the Data tab.

Enable Solver in Excel


STEP 2: Inserting Constraints

In this step, we’ll input the Constraints and the Objective Function in the Excel worksheet. According to the problem, suppose, we’ll blend x units of product A and y units of B. So the total cost will be $35x + $30y. This is our objective function, and we want to minimize this cost. At the same time, we have to meet the requirements. 25x + 15y >= 500, 35x + 20y >= 850, 10x+15y >= 300, x >= 0 and y >= 0 are our constraints. Now, we’ll input these.

  • Firstly, type the per-unit costs of A and B.
  • See the following picture to understand better.
  • Next, input the materials under the respective products.
  • Insert the minimum required amounts.


STEP 3: Creating Excel Formula

  • We’ll insert the value of x in cell C5 and y in cell D5.
  • Firstly, select cell E6.
  • Then, type the formula:
=($C$5*C6)+($D$5*D6)
  • Press Enter.
  • It’ll return 0 or blank as the C5 and D5 cell values are empty for the moment.

  • Afterward, select the cell E8 to type the formula:
=($C$5*C8)+($D$5*D8)
  • Consequently, press Enter to return the values.
  • Use the AutoFill tool to complete the rest.
  • For now, the results are 0 as C5 and D5 are empty.

Read More: How to Do Portfolio Optimization Using Excel Solver


STEP 4: Using Excel Solver to Solve Linear Programming

  • Now, select the Solver program under the Data tab.
  • Accordingly, the Solver Parameters dialog box will emerge.
  • Next, choose cell E6 in the Set Objective box.
  • After that, check the circle for Min.
  • Select the range C5:D5 as variable cells.
  • The following image demonstrates the process clearly.
  • Then, press Add to add the constraints.

Solve Linear Programming with Excel Solver

  • The Add Constraint dialog box will appear.
  • Choose the range C5:D5 and click >= (greater than or equal to) symbol from the drop-down.
  • Type 0.
  • Press Add afterward.

Solve Linear Programming with Excel Solver

  • Moreover, choose the range E8:E10 for minimum requirement constraints.
  • Click the >= symbol from the drop-down.
  • Select the range G8:G10 in the Constraint field.
  • Press OK.

Solve Linear Programming with Excel Solver

  • Hence, you’ll see the desired constraints.
  • Press Solve.

Solve Linear Programming with Excel Solver

  • You’ll get a dialog box about the solved results.
  • Check the circle for Keep Solver Solution.
  • Press OK.

Solve Linear Programming with Excel Solver

  • Lastly, it’ll return the precise results in the appointed cells.

Solve Linear Programming with Excel Solver

Read More: Example with Excel Solver to Minimize Cost


Final Output

  • The value of x is 77 units and y is 6.15 units.
  • The minimum cost is $912.
  • The optimized amounts of P, Q, and R is 54 kg, 850 kg, and 300 kg respectively.
  • Therefore, the manufacturer should blend 77 units of A and 6.15 units of B.


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to use Excel Solver for Linear Programming following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Excel Solver Examples | Solver in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo