We will elucidate on Solvers in Excel and discover to use solvers to solve linear programming problems that are used to achieve minimum costs or maximum profit objectives in an organization.
Solvers are highly useful to solve business-related mathematical and accounting problems. Let us discuss using the Solver Add-in in Microsoft Excel to solve linear programming problems to achieve organizational objectives.
Also read: Basic Mathematical Operations on Complex Numbers in Excel
Steps to use the Solver add-in in Excel
You cannot find the Solver in the Excel ribbon. You must manually add the Solver add-in from the add-in options in Excel.
1. Add Solver to Excel
First, we will learn to insert the Solver add-in the Excel ribbon, follow the steps below.
- Open an Excel file.
- Go to the File tab.
- Click on Options. Or, click More and click Options.
- Select the Add-in tab.
- Choose Excel Add-ins in the Manage menu at the bottom of this window.
- Press Go.
- Check Solver Add-in and click OK.
The Solver add-in gets added to the Data tab in the Analyze Section.
Now, let us take an example of a linear problem with maximum profit as the objective.
In this example, a company has three products named A, B, and C. The table above displays the number of units of each product sold, profit per unit of every product, and the total profit earned per product.
The objective of this LP problem is to maximize profit. These are the constraints of this problem.
- A minimum of 100 units of product A to be produced.
- A minimum of 20 units of product B to be produced.
- A minimum of 50 units of product C to be produced.
- The total quantity produced should be equal to 350 units.
The variables here are the quantities of products A, B, and C that will change to maximize the total profit.
2. How to Use Solver in Excel?
Let’s begin using the Solver tool to solve this linear problem.
- Now that we have added the tool to the Excel ribbon, go to the Data tab.
- Click on Solver in the Analyze section.
- The Solver Parameters window opens with some options.
- In the Set objective box, select the cell with the gross profit. Here, it is cell D5 with 6100 as the gross profit.
- Choose Max because we want to maximize total profits. You can choose Min if the objective is to reduce costs or expenses. Or set a custom value to be achieved in the Value of box.
- In the By Changing Variable Cells box, select the quantities of all products.
- Click Add.
- The Add Constraints window opens to set constraints for the variables.
- In the Cell reference field, select the quantity of product A. Here, 200 in cell B2.
- Now, choose “>=” (greater than equal to), and put 100 in the Constraints box. It fulfills the first constraint of producing a minimum of 100 units of product A.
- Click Add.
- Repeat the same steps by putting appropriate constraint values for other variables.
- Once you’ve added all constraints, click OK.
- You will see all the constraints together in the Subject to the Constraints field.
- Uncheck the Make Unconstrained Variables Non-negative as we do not want the profits to be negative. In the end, it depends upon the objective of your LP problem.
- Select a solving method. We select Simplex LP because it is suitable for the problem here.
- Check the figures and click Solve.
If there is an error occurring, make sure to follow the steps below.
- Choose Restore Original Values.
- Check Solver Parameters Dialogue box.
- Press OK.
If everything works correctly, the Solver Results page opens, showing the message Solver found a solution. All constraints and optimality conditions are satisfied.
- Choose Keep Solver Solution on the left.
- Choose the reports you want to render for this solver solution on the right. Every report you select will be shown in a separate new sheet with its name.
- We want to render the Answer report, so we selected it.
Take a look at the new values that the Solver created.
You can see that a new sheet creates named Answer Report within the workbook.
You can view and compare the original and final values of the gross profit and the variables for a detailed analysis.
Conclusion
This article was all about using solvers to solve linear programming problems in Microsoft Excel. We also learned how to use solvers to achieve minimum costs or maximum profit objectives in an organization.
Reference- TrumpExcel