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 most important as well as a 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 Data tab which can be found on the Excel Ribbon placed at the upper most part as shown below:
If You can’t see this utility tool there, you need to enable it through Excel Options. Follow the steps below to enable Solver under Excel.
Step 1: Navigate towards the File menu and click on Options, which is 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 in 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 Go… button placed beside.
Step 4: As soon as you click on Go… button, you will be able to 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 product:: A. These two products need raw materials as shown below: Product A needs three types of raw materials – Material_1 20KG, Material_2 30KG, Material_3 as 5 KG. On similar lines, Product B requires 10KG of Material_1, 30 KG of Material_2 and 10 KG of Material_3. The manufacturer requires a minimum of 460KG or Material_1, 960KG of Material_2 and 220KG of Material_3. If the per-unit cost for Product A is $30 and the cost of Product B is $35, how many products the manufacturer should blend to meet the minimum material requirements at the lowest cost possible? Let’s use the information provided under this example for modelling the equations.
Step 1: We can see all the equation constraints we can form using the information provided 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 as below:
Step 3: Now, we need to use the formula Quantity * Per Unit Cost and sum it up for both the products to get the actual material requirements. You can see this formulated under column D for all cells containing constraints B3, B4, C3). Please see the attached screenshot below:
If you will have a closer look at this formula, we have used B3 and C3 as fixed members for each formula across the different cells in column D. This is because B3 and C3 are the cells denoting quantities for Product A and Product B, respectively. These quantities will appear once the system of equations is solved using Excel Solver.
Step 4: Click on 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 the equation 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 so that it can be minimized, 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 be the one’s containing 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 on Solve button at the bottom of the window to solve this linear equation and come up to 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, and we get the values for B3, 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 the 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 variable to be set ready with us.
- If Solver is not enabled, you can enable it under Excel Add-in options.
Recommended Articles
This is a guide to Linear Programming in Excel. Here we discuss How to use Linear Programming in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
