In this blog I will present goal seek functionality and how it can be used in cost profit volume analysis.

First, let’s check goal seek with a simple example.

B2 plus C2 equals to D2. If you want D2 to be four, which value should be in C2 ?

Choose data – what if analysis – goal seek.

D2 should be four by changing the value in C2.

Excel counted correctly.

This is basic cost volume profit analysis. Sales is counted by sales volume multiplied by sales price. Variable costs are sales volume multiplied by variable costs. Margin is sales in money minus variable costs. Result is margin minus fixed costs. The result is slightly in negative territory. Margin percentage is margin divided by sales. Result percentage is result divided by sales.

How much should price per unit be that the result would be to reach the zero result (break-even) when all the other parameters are remaining as they are ?

The result is in F6 cell, that should be zero by changing the price in B3.

The result.

Let’s take bit more complex case.

What the sales volume should be that the result percentage is +5 % ? When the sales volume increases, also the variable costs are increasing linearly.

The values are with several decimals.

You can create other examples.