Linear Programming in Excel using Solver
Linear Programming is one of the important concepts in statistics. Based on available data of variables we can do predictive analysis. In our earlier article “Linear Regression in Excel” we have discussed in detail about the “Linear Regression”. However, in excel we have an option called “Solver in excel” which can be used to solve a linear programming problem, with this solver we can use linear programming to enable resources optimization.
In this article, we will show you how to solve the linear programming problem in excel in detail. Follow the entire article to learn about this.
How to Solve Linear Programming through Excel Solver?
To apply solver to solve linear programming, we should have a proper problem in detail. For this example, I have created the below scenario.
Problem: A manufacturer wants to alter this production model of the current product. He has two kinds of products, “Product 1” & “Product 2”. For Product 1 requires three raw materials, Raw Material 1 20 Kg, Raw Material 2 30 Kg, and Raw Material 3 5 Kg. Similarly, for Product 2, it requires three raw materials, Raw Material 1 10 Kg, Raw Material 2 25 Kg, and Raw Material 3 10 Kg.
Manufactures require a minimum of Raw Material 1 550 Kg, Raw Material 2 800 Kg, and Raw Material 3 250 Kg. If Product 1 costs Rs. 30 per unit and Product 2 costs 35 per unit, how many units of each product should the manufacturer blend meet the minimum raw material requirements at a low cost as possible, and what is the cost?
Now enter all this information into an excel spreadsheet in the below format.
In cell D3 and D5 to D7, we need to apply the excel formulaExcel FormulaThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions., i.e., Cost * Cost Per Unit. Cost Price we need to arrive from the solver in cell B2 & C2. To apply the formula as below.
After setting up this, we need to go to the solver tool in excel. The solver tool is available under the Data tab in excel.
Enable Solver Add-in
If your spreadsheet is not showing this option, then you need to enable it. To enable this solver option to follow the below steps.
- Step 1: Go to the File tab; then, under the File tab, click on “Options.”
- Step 2: Go to Add-ins under Excel Options.
- Step 3: Under this, select “Excel Add-insExcel Add-insAdd-ins are different Excel extensions that can be found in the options section of the file tab. The first box displays the system's enabled add-ins, and if the user wishes to enable more, they must click on manage add-ins.” and click on Go.
- Step 4: Under below pop up choose “Solver Add-in” and click on “Ok” to enable it.
Now we can see “Solver Add-in” under the DATA tab.
Solve Linear Programming through Excel Solver
- To apply solver, go to the DATA tab and click on “Solver” we will see below the window.
In the above window, our first option is “Set Objective.”
- Our objective is to identify the “Total Cost,” so our total cost cell is D3, so select the cell D3 for this “Set Objective” and set it to “Min.”
- The next option is “By changing variables.” In this example, our variables are “Product 1” and “Product 2”. To select a range of cell B2:C2 and click on “Add.”
- Once you click on “Add,” we will see below the add constraint window. In this window, select B2:C2 range of cells and put the constraint as “>=0”.
- Click on “Add” to stay back in the same window. Now in the second constraint, select the range of values as D5:D7 and select “>=” and under constraint, select G5:G7 cells.
- Click on “Ok” to come out of the Add Constraint window.
- Now all our parameters are ready. Click on the “Solve” option to get the result.
- So, the cost to produce Product 1 per unit is 20, and Product 2 per unit is 15.
Like this, by using SOLVER, we can solve linear programming in excel.
Things to Remember
- Solver, by default, is not available to use.
- A solver is not only limited to a linear programming language, but we can solve many other problems as well. Refer to our article “Solver Option in Excel.”
- Setting the objective cell is important.
- Adding constraints should be ready well in advance.
This has been a guide to Linear Programming in Excel. Here we discuss how to solve the linear programming problem in excel using the solver option with example and downloadable excel template. You may learn more about excel from the following articles –