7.2 Solver

What is the solver? The latter is an optional tool that you can add to Excel via the Add-ins bar. You will find it by clicking on Options at the bottom of the Home screen:

In the window that appears, click on Add-ins, then Go:

Then you select the Solver Add-in and click OK.

The solver will appear under the Data tab on the right:

This tool makes it possible to find results according to different conditionalities/constraints. In practice, this allows you, for example, when creating a budget (for a project proposal), to find the optimal number of units for an activity while remaining within the budgetary limits.

After installing the module, download this file here.

Once this has been downloaded, we will try to define - in our budget - the number and unit cost for both the latrine and agriculture activities (A4 and A3). We know that we are going to pay a minimum of €350 for latrines and €5,000 for agriculture, but that we cannot spend more than €100,000 in total for these two activities.

You could probably deduce the optimal amounts quite easily with some quick maths, but the solver can make it easier for you by making an initial forecast.

So, you go to the file and click on the newly installed solver:

The following window will appear:

What you want is for the sum of D3 and D4 (the total costs of activities) not to exceed €100,000, it will therefore be your goal to define, that cell G3 is equal to the value 100,000.

The cells that will vary for this will be the number of both latrines and agriculture activities, i.e., the B3:B4 range, which will be your variable cells.

You will add constraints, given that you must build at least 20 latrines (so have B3 >20) and carry out between 2 and 10 agricultural activities (so B4 <10 and B4 >2)

You should get the following window when you are done:

If you then click Solve, the solver will offer you an optimal solution for these parameters, which will be as follows:

You can thus multiply conditionalities and goals, which allows you to quickly get numbers you can be sure of, and thus work from a solid foundation to check files with many interrelated values.

Feel free to vary the results in the sample file, then in your own file if necessary!