Updated June 9, 2023
Excel Linear Programming (Table of Contents)
- Introduction to Linear Programming in Excel
- Methods to Solve Linear Programming through Excel Solver
Introduction to Linear Programming in Excel
Linear Programming is the most important and fascinating aspect of applied mathematics, which helps in resource optimization (either minimizing the losses or maximizing the profit with given resources). If we have constraints and the objective function well defined, we can use the system to predict an optimal solution for a given problem. In Excel, we have Excel Solver, which helps us solving the Linear Programming Problems a.k.a. LPP. We will see in this article how to use Excel Solver to optimize the resources associated with business problems with the help of Linear Programming.
First thing first. Let’s see how we can enable Excel Solver (a key component of LPP under Excel).
Methods to Solve Linear Programming through Excel Solver
Let’s understand how to use Linear Programming through Excel Solver with some methods.
Method #1 – Enabling Solver under Microsoft Excel
In Microsoft Excel, we can find Solver under the Data tab, which can be found on the Excel Ribbon placed at the uppermost part, as shown below:
If You can’t see this utility tool there, you must enable it through Excel Options. Follow the steps below to enable Solver under Excel.
Step 1: Navigate to the File menu and click on Options, the last thing on that list.
Step 2: A new window will pop up named Excel Options. Click on Add-ins from the list of options present on the left-hand side of the window.
Step 3: Under the Manage section at the bottom of the window, select Excel Add-ins from the dropdown list and click on the Go… button beside.
Step 4: As soon as you click the Go… button, you will see the list of all Add-ins available under Excel in a new window. Tick to select the Solver Add-in so that you can use it under the Data tab for solving the equations. Click the OK button after tick selecting the Solver Add-in.
This way, you can enable the Excel Solver under Microsoft Excel.
Method #2 – Solving Linear Programming Problem using Excel Solver
Now, we will try to solve the linear programming problem using the Excel Solver tool.
Example: A chemical plant produces two products:: A. These two products need raw materials as shown below: Product A needs three types of raw materials – Material_1 20KG, Material_2 30KG, and Material_3 as 5 KG. Product B requires 10 kg of Material_1, 30 KG of Material_2, and 10 KG of Material_3 on similar lines. The manufacturer requires a minimum of 460 kg of Material_1, 960 kg of Material_2, and 220 kg of Material_3. If the per-unit cost for Product A is $30 and the cost of Product B is $35, how many products should the manufacturer blend to meet the minimum material requirements at the lowest cost possible? Let’s use the information provided in this example for modeling the equations.
Step 1: We can see all the equation constraints we can form using the information in the example above.
Step 2: Use these equations to add the constraints cell-wise under Excel across A2:C8 of the given sheet. See the screenshot below:
Step 3: We need to use the formula Quantity * Per Unit Cost and sum it up for both products to get the actual material requirements. This is formulated under column D for all cells containing constraints B3, B4, C3). Please see the attached screenshot below:
If you look closely at this formula, we have used B3 and C3 as fixed members for each formula across the different cells in column D. B3 and C3 are the cells denoting quantities for Product A and Product B, respectively. The quantities will appear once we solve these equations using an Excel solver.
Step 4: Click on the Data tab and then on Solver, which is present under Analyze section in the tab.
Step 5: Once you click on Solver, a new tab named “Solver Parameter” will open up, under which you need to set the parameters for this set of equations to be solved.
Step 6: The first thing we need to identify is Set Objective: Since our objective is to figure out the total cost involved to minimize it, set this to D4.
Step 7: Since we need to minimize the cost with the highest production possible, set the next parameter as Min. You can get this done by clicking on the Min radio button.
Step 8: under By Changing Variable Cells: we need to mention B3 and C3 since these cells will contain Quantities for Product A and Product B, respectively, after the problem gets solved.
Step 9: Now add the constraints. Click on Add button under the Subject to the Constraints: section, and it will open up a new window to add constraints. Under that window – B3:C3 as Cell Reference, >= and 0 as Constraints. This we are doing since the basic constraint in any LPP is that X and Y should be greater than zero.
Step 10: Click again on the Add button, and this time use B3:C3 as Cell Reference and F6:F8 as Constraints with inequality as >=. Click the OK button to add this constraint as well under the solver.
Solver now has all the parameters required to solve this set of linear equations, and it looks as below:
Step 11: Now, click the Solve button at the bottom of the window to solve this linear equation and find the optimal solution.
As soon as we click on the solve button, the system starts searching for an optimal solution for the problem we have provided. We get the values for B3, and C3, using which we also get the values under column F for F4, F6:F8, which are the optimal costs and material values that can be used for Product A and Product B.
This solution informs us that if we need to minimize the cost of Production for Product A and Product B with optimal usage of Material_1, Material_2, and Material_3, we should produce 14 quantities of Product A and 18 Quantities of Product B.
This is it from this article. Let’s wrap things up with some points to be remembered:
Things to Remember About Linear Programming in Excel
- It is mandatory to solve Linear Programming Problems using Excel Solver. There is no other method we can do this using.
- We should always have constraints and object variables to set ready with us.
- You can enable Solver under the Excel Add-in options if Solver is not enabled.
Recommended Articles
This is a guide to Linear Programming in Excel. Here we discuss How to use Linear Programming in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
