Handle complex data models and solve for the optimal solution.

Excel Solver example and step-by-step explanation

Excel’s Solver tool can do complex statistical modeling.

There are many ways to benefit from it when you want to manipulate multiple cells to get a certain result.

It can be used when solving more complex problems which are beyond the scope of the Goal Seek and What-if tool.

What else can it do?

It can also easily solve for the simpler problems that we might face at work.

For example:

How can we maximize our profit by slightly altering price and costs AND take into account specific constraints for costs and price; such that price cannot go above a certain level and costs cannot go below a certain point. Or:
How can we distribute the remaining budget dollars among our different projects based on certain constraints?

Solver for Advanced Goal Seek

Goal Seek is restricted to changing only one input variable.

If you have multiple input variables along with some situational constraints as shown below, the Solver function will come in handy.

Units sold: Input variable.
Price per unit: Input variable.
Revenue: Calculated field.

Cell B5 = B3*B4

Cost per unit: A fixed number indicating how much each unit costs.
Costs: Total cost. This is derived by multiplying the number of units by the cost per unit.

Cell B9 = B3*B8

Income: Calculated field. This net income is obtained by subtracting the Cost from the Revenue.

Cell B11 = B5-B9

For this scenario, there is a target income of 2,000 and you need to find out how many units need to be sold and the corresponding unit price. It also has the following restrictions:

Units sold needs to be a whole number
Price per unit should be between 3 and 4.

Activating the Solver tool

To activate the Solver tool,

Go to File.
Click on Options.
In the Excel Options window, select Add-ins.
At the bottom, go to Manage add-ins and click Go.

5. Tick on Solver Add-in.

6. Click on OK.

Defining parameters and restraints

The Solver tool is now enabled.

You can find it at the Data tab, under Analyze Section.

To use the Solver tool to find the values we need:

Set Objective: Cell $B$11, (which is the income ) to a value of 2,000.
This keeps the income cell fixed to 2,000. Other options allow you to indicate if you are looking for the minimum or maximum value based on the constraints.
To select variable cells that you want to manipulate, highlight the cells and – when necessary – use a comma as a separator for multiple cells. In this case, use =$B$3:$B$4,$B$8
To add constraints, click on Add at the right side of the Solver Parameters window. The Add Constraint window will pop up. Select the cell reference you want to add a constraint to. For Units sold parameter, set it to Integer since we want a whole number. Click Add.

Solving method: GRG Nonlinear.
Click on Solve. The Solver Results window will pop up, saying that it has found a solution. You will notice that the values of the input cells have changed but the formulas in your calculated cells have been retained. You are given the option to accept the new values by clicking on OK, or reject them by clicking on Cancel.

Types of Solving methods

There are three options for the Solver method:

GRG Nonlinear – This is the most commonly used method to solve nonlinear problems. This looks for a local optimal solution.
Simplex LP – This is used to solve linear problems.
Evolutionary – This is used to solve more complex and non-smooth non-linear problems. It looks for a global optimal solution, which makes it take longer to run compared to GRG Nonlinear.

You can select any of the three options, but by default, the GRG Nonlinear method is used.

Solver for Complex What-if problems with constraints

Consider a scenario where you have to allocate a budget among multiple parameters in the table below:

Notice that there are constraints that indicate the following:

Extra costs should not exceed 1,000
Fixed costs need to remain at 2,000
Total project cost for each project should not exceed 9,600

To set it up in the Solver tool, do the following:

Set objective: $B$14 to value of: 65000
Change variable cells:
- Extra costs column: $D$5:$D$13
- Fixed cost is already at 2,000 so you don’t need to change it.
- Production cost column: $B$5:$B$13. We will choose the Production Cost column instead of Total Costs column because the latter is a calculated field with the production cost as the input.
Constraints
- Extra costs: $D$5:$D$13 <= $E$18
- Total cost: $E$5:$E$13 <= $E$20

After clicking on Solve, the table will have the new values as follows: