Chapter 17 · 17.22
The Solver: let Euro-Office work out the best mix or plan
Goal Seek (see Working backwards with Goal Seek: which price gives a 40% margin?) adjusts one cell until a result is right. But often you have more knobs to turn, and limits you need to stay within. How many loaves and how many pies should you bake on Saturday for the most profit, if the oven only runs for 16 hours and you only have 120 kilos of flour? The Solver answers questions like these. Few people know that Euro-Office has one.
The example: Saturday production
Bakkerij De Korenaar sets up the problem in a worksheet like this:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Bread | Pie | Maximum | ||
| 2 | Profit per item | 1.20 | 6.50 | ||
| 3 | Oven time (min) | 6 | 25 | ||
| 4 | Flour (kg) | 0.5 | 0.3 | ||
| 5 | Number | 0 | 0 | ||
| 7 | Total profit | =SUMPRODUCT(B2:C2,B5:C5) | |||
| 8 | Oven time used | =SUMPRODUCT(B3:C3,B5:C5) | 960 | ||
| 9 | Flour used | =SUMPRODUCT(B4:C4,B5:C5) | 120 | ||
| 10 | Maximum pies | =C5 | 25 |
Row 5 holds the quantities the Solver may choose; you start with zeros. SUMPRODUCT multiplies per product and adds up: profit per item times quantity, oven time per item times quantity, and so on. Column E holds the limits: 960 minutes of oven time, 120 kilos of flour and at most 25 pies, because there are no more orders than that.
Step by step
- On the Data tab, click Solver. The Solver parameters window opens.
- Under Set objective, choose the cell with the total profit: B7. Under To, choose Max: the profit should be as high as possible.
- Under By changing variable cell, choose the quantities: B5:C5.
- Under Subject to the constraints, click Add. In the Add constraint window, enter B8 under Cell reference, choose <= and enter E8 under Constraint. Click Add for the next constraint, or OK when you are done.
- In the same way, add B9 <= E9 (flour) and C5 <= E10 (pies).
- Leave Make unconstrained variables non-negative ticked: this stops the Solver from choosing negative quantities. The Solving method is Simplex LP.
- Click Solve.


After a moment, Solver results appears: "Solver found a solution. All Constraints and optimality conditions are satisfied." Choose Keep solver solution and click OK. With Restore original values, the quantities go back to what they were.

The result: 25 pies and 55.83 loaves, with a profit of 229.50 euros. The oven is then exactly full, and there is still plenty of flour. Pies earn more per minute of oven time than bread, so the Solver first bakes as many pies as have been ordered and fills the rest of the oven with bread.

What the Solver in Euro-Office cannot do
- Linear problems only: the only method is Simplex LP. GRG Nonlinear and Evolutionary from Excel are not available; Euro-Office itself reports that they are not yet supported. Your objective cell and constraints may therefore only add and multiply by fixed numbers. IF, MAX or multiplying two variable cells together will not work.
- No forcing whole numbers: a constraint can only be <=, = or >=. The int (integer) and bin (0 or 1) options from Excel are missing. That is why you get 55.83 loaves. Round down yourself (55) and check that all constraints still hold; when you round down, they usually do.

Pitfalls
- The objective cell must be a formula that depends (via other cells) on the variable cells, and it must be on the active worksheet.
- If you open the Solver again, the objective, variables and constraints are still there. With Reset all, you start with a clean slate.
