Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

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:

ABCDE
1BreadPieMaximum
2Profit per item1.206.50
3Oven time (min)625
4Flour (kg)0.50.3
5Number00
7Total profit=SUMPRODUCT(B2:C2,B5:C5)
8Oven time used=SUMPRODUCT(B3:C3,B5:C5)960
9Flour used=SUMPRODUCT(B4:C4,B5:C5)120
10Maximum pies=C525

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

  1. On the Data tab, click Solver. The Solver parameters window opens.
  2. Under Set objective, choose the cell with the total profit: B7. Under To, choose Max: the profit should be as high as possible.
  3. Under By changing variable cell, choose the quantities: B5:C5.
  4. 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.
  5. In the same way, add B9 <= E9 (flour) and C5 <= E10 (pies).
  6. Leave Make unconstrained variables non-negative ticked: this stops the Solver from choosing negative quantities. The Solving method is Simplex LP.
  7. Click Solve.
Adding a constraint
Cell reference B8, less than or equal to, Constraint E8: no more than 960 minutes of oven time
The Solver parameters window
Objective B7 (Max), variable cells B5:C5 and the three constraints

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.

Solver results
Solver found a solution; choose whether to keep it

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.

The best mix
The quantities in row 5 have been filled in: 25 pies and just over 55 loaves

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.
The options for a constraint
In the Add constraint window, you can only choose &lt;=, = and &gt;=

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.

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…