Solver target value search

What is Solver? Solver is a tool of Excel that helps us find an optimal solution to a decision problem. It shows us which combination of input factors maximizes income or minimizes expenses. Without Solver, the problem can only be solved with mathematical knowledge or trial and error – how convenient!

Solver itself is not quite so easy to provide, as it allows for smaller and infinitely complex calculations. For our tutorial, we are talking about a farm, or rather a farmer who has to decide whether to raise ducks, chickens or cattle. They all require different amounts of space on his farm and generate different amounts of costs. The farmer has limited financial and space capacities, which is why he has to carefully consider acquiring expensive cattle or small chickens to achieve his goal: the highest possible profit.

Note: The example calculation is not realistic and is intended to show how Solver works.

Installing a solver

Solver is not integrated in the standard Excel version and must be installed later, which is relatively easy and free of charge.

We click on the File tab to get to the following view. Here we navigate to the options at the bottom.

The Excel Opions dialog window appears and we select the Add-Ins setting in the menu.

Now we can manage all add-ins. We mark names of solvers in the list with one click and then press the “Go… button” further down (green arrow).

Next, we check the Solver box in the list and press OK.

With a little patience, we wait for the installation.

In the Data tab you will find the Analysis group with the Solver button. By clicking on the button we get to the Solver dialog box. But this will be shown with the next chapter.

Find solution with solver

Note: Before you go through this example, you should make sure that Solver is installed correctly on your version of Excel.

Back to our farmer and his problem. The questions were:

“How many and which animals should the farmer procure to maximize his profit?”

So that Excel can answer this question later, we create a table with a listing for each animal. Very important: in the fields Number (in our example C5, C6, C7) only numbers are allowed. Animals are the input factor in our example. For the beginning it is enough to insert any numbers as placeholders, Excel will later overwrite these results. In our example we write 4,3 and 2 as placeholders in the cell.

Next, we create a table with all the parameters that are important for our decision. We list all animals again and write for each animal species how much area [square meters] is needed, costs [euros] are incurred and money is earned. These numbers are important so that we can calculate which combination of different input factors (animals) will give the maximum profit. We see in our example that a cow needs much more space (10 m²) than a duck (2 m²), is much more expensive to maintain (cost), but brings in much more money.

Note: Usually the input factors are divided in such a way that each choice has its advantages and disadvantages. In our example, cattle are large, expensive but valuable, chickens are cheap and small, whereas ducks are a middle ground. If there is an obvious favorite that is best in all respects, we do not need a Solver calculation because the decision is trivial: produce the favorite – as often as possible. Solver is applied when the obvious solution is not apparent.

In the third table, we calculate the actual consumption of our resources by multiplying the per animal data from the second table by the number of animals from table 1. Example: In cell C15 we calculate C5xC10, or number of ducks multiplied by the area per duck. With 4 ducks and 2 m² each, we need 8m². In cell D16 we calculate the cost per day for one chicken by multiplying C6 (number of chickens) by D11 (cost per chicken).

At the bottom we sum up all areas, costs and the revenues as in line 18. Under each column we enter our capacities in line 19. After all, the farmer does not have infinite space and money. In our example, the farmer has 500 m² of space and only 50 € per day for the animals.

Cell E18 is particularly highlighted because this value (total revenues) is to be optimized (objective function).

With this structure, given the conditions in Table 2 and the frequencies from Table 1, we can immediately see the impact on our result. If we change the placeholders from table one, we automatically change our result. With Solver, Excel will give us a combination of chickens, ducks, and cattle that will have the most sales revenue.

We click on the Solver button in the Data tab.

The Solver Parameters dialog box will open. In this tutorial we will go through each value individually and explain step by step how to set up the Solver parameters.

At Set target we click once on the input field and select our target function (total revenue) in cell E18. Since we want to maximize our sales, we select Max.

In the case of a cost function, it is advisable to determine a value that is as free as possible and to select Min. Solver can also calculate a specific value, whereby several combinations of the input factors can be possible as a result.

In the next step we show Excel the variable cells with the input factors. With a click on the symbol (red arrow) we can mark the range C5 to C7 (green arrow). Now Excel knows the places that can be manipulated. All other numbers in our table are always only the effect of the animal frequency.

let’s move on to the conditions. We cannot manage an infinite number of chickens and ducks on the farm and are bound to our capacities. In order for them to be taken into account in the solver calculation, we add all the conditions in the “Subject to constraints” area. To do this, we click the Add button.

A secondary condition “add dialog window” opens. On the left we enter the cell reference, in the middle a mathematical equation and on the right the constraint. At this point we have to work very carefully and not make any mistakes, otherwise the solver calculation will give wrong results.

The first constraint is that the sum of all surfaces must not be greater than our capacity for surfaces. All results below the capacity are feasible, everything above it is not feasible. For this reason, in cell reference we select cell C18 with the sum of all used surfaces, as equation we select “less than or equal to” (<=) and the constraint is the total available surface in C19. To confirm the constraint, we click OK.

Since we are adding more constraints, we click Add to immediately create the next constraint. The next constraint is that the sum of all costs in D18 must be less than or equal to the capacity in D19.

Things that are completely logical for us, must now and then be explained in detail to Excel first. This also applies to solver calculations. Unscrupulous as Excel is, it would not be shy to present us 1.75 cattle and 0.5 ducks as an optimized result, which would be rather tragic for our farmer, since half a duck equals one dead duck. We have to explain to Excel that we only tolerate whole numbers as input for our animals. Therefore, we add more constraints: Number of ducks (c5) int Whole numbers. And we repeat this for the chickens and cattle.

Objects that are completely logical for us, must now and then be explained in detail to Excel. This also applies to solver calculations. Unscrupulous as Excel is, it would not be shy to present us 1.75 cattle and 0.5 ducks as an optimized result, which would be rather tragic for our farmer, since half a duck equals one dead duck. We have to explain to Excel that we only tolerate whole numbers as input for our animals. Therefore, we add more constraints: Number of ducks (c5) int Whole numbers. And we repeat this for the chickens and cattle.

In the end, we have 5 constraints that Solver will take into account.

Note: for some solver calculations, additional, avoidably logical, constraints are useful. For example, that all inputs must not only be integer, but also positive. This is especially true for the optimization of cost functions

We click the Solve button further down.

We click OK on Results.

And this is how the optimal result looks like: 50 chickens, 2 cattle and no duck generate 6,500 €. In table 3 we can examine the result more closely: we see that the area was only barely used. The farmer will use only 95m² of his 500 ². The bottleneck was the low daily budget of only 50€. He cannot afford more animals. At this point we can only advise the farmer to increase the daily budget.

Assuming that the farmer has received a bank loan and now has more financial leeway, what would the result be if he now had €3,000 per day at his disposal? We adjust the value in cell D19 and increase it to 3,000. Now we run the solver calculation as we did again (step 4). The system has remembered the settings, which is why we click on solve right away.

The new result: 50 cattle and 150,000 € turnover, a huge difference from the 6,500 € from the previous calculation.

In detail we see that now the area is the bottleneck factor, every square meter is claimed for cattle. The credit for the farmer is too high because in our calculation he does not need more than 1,000 € per day.

The figures such as “costs” and example calculations are highly simplified in order to explain this complex subject as simply as possible.

Usually, the target value search is not simply applied to the revenues. A profit equation is created that offsets total revenues against total costs. This profit equation is the actual target equation for solver optimization. Feel free to enrich our example with a profit equation and calculate a new result; or create your own example right away. If you understand the principle of the solver calculation, you can also arrange the tables as you like and don’t have to stick too closely to this example.