Excel Solver is a tool for running scenarios where the target cell is optimized by changing values of other cells used to calculate the target cell. Solver is very useful for optimizing Excel models.
By default the Excel Solver tool is not loaded in the Excel ribbon. Learn how to add Excel Solver to the ribbon. Once enabled, Solver can be found on the Data tab.
Table of Contents
Excel Solver overview
Excel Solver is an Excel data analysis command that allows you to optimize (maximize, minimize, or set to a specific value) a variable, by changing other variables that are used in the calculation. Solver allows you to set various types of constraints on variable ranges. Solver uses algorithms to solve the Excel model and find the optimal value and associated variable cells values.
Excel Solver interface
Excel Solver interface allows you to set the objective cell to optimize, cells to change, and customize a few other options for the optimization.
Set Objective – this is the target cell to be optimized
To: – the target cell can be optimized to three options: max, min, or a specific value
By Changing Variable Cells: – these are the cell or cells that the tool which change or simulate to find an optimal target value. The target value must use these cells in the calculation.
Subject to the Constraints: – this optional input allows you to define additional constraints for cells in the calculation
Make Unconstrained Variables Non-Negative – if checked all unconstrained variables will remain positive
Select a Solving Method: – this option defines the type of algorithm excel will use to solve your optimization and there are three options to choose from:
- GRG nonlinear (Generalized Reduced Gradient)
- Simplex LP
- Evolutionary
The best algorithm will depend on the problem being solved (specifically if there are discontinuities in your formulas and the randomization of starting points that the solver uses). A good option for most optimization problems is selecting GRG nonlinear, and using the multistart option with the steps below:
- Select GRG nonlinear
- Click Options
- Click the GEG nonlinear tab
- Click the Use Multistart checkbox
- Click OK
If you want to read more about the details of each Solver Method, Excel Engineer has a great summary located here.
Example
In a business example, let’s say we run a business that sells three products at different prices and profit margins. Let’s assume that the amount of each product we can sell is limited by our supply chain (our vendors’ production capabilities), and our total units sold each month are limited by our sales team’s capacity. Given these limitations, we want to optimize the total profit margin by selling the right mix of products that will achieve the highest profit margin for the business.
In the example graphic, you can see product A, B, and C, and their associated price and profit margin in rows 6 and 7. The monthly sales report is calculated in rows 9 through 13, where each product’s units sold is multiplied by price to calculate total sales, and by profit margin percentage to calculate profit margin. SUMming all three products sales and margin results in the total sales and total profit margin.
Setting Solver Parameters
Set Objective – we are optimizing the total profit margin in cell F13
To: – set to “Max” in order to maximize the total profit margin
By Changing Variable Cells: – the units sold for product A, B, and C are the variable to be changed in cells C11:E11
Subject to the Constraints: – each product is set to not exceed their respective maximum production rate (e.g., product A: C11 <= C16), and the total units sold is limited to 55 (F11 <= F17)
Make Unconstrained Variables Non-Negative – left checked (default)
Select a Solving Method: – set to GRG nonlinear with multistart option (see above)
Solver answer
The solver runs an algorithm and returns the answer with the maximum total profit margin.
In this example, the maximum total profit margin is $1,312, which can be achieved by selling the optimal mix of products: 25 units of product A, 20 units of product B, and 10 units of product C.
