Chapter 17 · 17.15
Working backwards with Goal Seek: which price gives a 40% margin?
A spreadsheet normally calculates forwards: you enter a price and see the margin. Often you want the opposite. Which selling price gives a 40% margin? How much can I borrow if the monthly payment may be 500 euros? How many cakes do I need to sell to break even? For this, Euro-Office has Goal Seek: you enter the result you want, and Euro-Office finds the input that produces it.
The example: the price of a wedding cake
Bakery De Korenaar makes a wedding cake with a cost price of 48 euros and currently sells it for 60 euros excluding VAT.
| A | B | |
|---|---|---|
| 1 | Cost price | 48.00 |
| 2 | Selling price excl. VAT | 60.00 |
| 3 | Margin | =(B2-B1)/B2 |
| 4 | Markup | =(B2-B1)/B1 |
Give B3 and B4 the Percentage format. The margin is now 20%. Which selling price gives a 40% margin?
Step by step
- On the Data tab, click Goal Seek.
- In Set cell, choose the cell with the result you want to reach: B3 (the margin). This must be a cell containing a formula.
- In To value, type the result you want: 0.4. Don't type 40%, but the number: 40% is 0.4.
- In By changing cell, choose the cell Euro-Office may adjust: B2 (the selling price). This must be a cell with a fixed value, not a formula.
- Click OK.

Euro-Office calculates for a few seconds and shows the Goal seek status window with the message "Goal seeking with cell B3 found a solution.", the target value and the current value. The selling price in B2 is now 80.00. Click OK to keep that value, or Cancel to go back to 60.
Goal Seek searches in small steps and stops when the result is close enough to the target. That's why the cell actually contains 79.9999984, which is shown as 80.00 with two decimal places. If you want a round amount, type 80 in the cell yourself.

Margin or markup?
A common mistake. Margin is the profit divided by the selling price; markup is the profit divided by the cost price. With a cost price of 48 euros and a selling price of 80 euros, the margin is 40% and the markup 66.7%. If you add a 40% markup (48 + 40% = 67.20), you only get a 28.6% margin.
For the margin you don't really need Goal Seek: the price for a 40% margin is =B1/(1-40%), which is also 80. Goal Seek really comes into its own when the calculation is more complex.
More examples
- Borrowing: put the loan amount in B6 and in B7 the monthly payment at 4.5% interest over 60 months: =PMT(4.5%/12,60,-B6). For 20,000 euros, that's 372.86 a month. Goal Seek with Set cell B7, To value 500 and By changing cell B6 gives you the amount you can borrow.
- Breaking even: create a profit cell = quantity × (price minus cost price per item) minus fixed costs. Goal Seek with the profit set to 0 by changing the quantity gives you your break-even point.
Pitfalls
- Message The cell must contain a formula: in Set cell you chose a cell with a fixed value. Choose the cell with the formula.
- A rouble sign with PMT (see the image below): Euro-Office automatically gives the result of PMT a currency format, and that can be an unexpected currency sign, such as the rouble: 372.86 ₽. Choose the Currency format with the euro sign for that cell yourself (see Formatting numbers, dates and currency).
- Goal Seek overwrites your input cell. If you clicked OK by mistake, undo it with Ctrl+Z.
- Goal Seek only adjusts one cell and has no constraints. If you want to search with several cells at once, with limits such as "a maximum of 960 minutes of oven time", use Solver instead: see The Solver: let Euro-Office work out the best mix or plan.
- If you want to see a few variants side by side (for example three prices and their margins), put them in adjacent columns with the same formulas. That's clearer than running Goal Seek over and over again.

