Break-even + Target profit analysis with Excel Goal Seek – How To

 Break-even point is simply the point of activity where entity is making no profit or loss. Therefore, break-even analysis in simple words help determine that what value of certain variable (or variables) under given situation will give zero profit.

This analysis can become cumbersome if it involves many figures that can be of variable, semi-variable and fixed behaviour. Reason is that with change in activity costs and revenues might change too. This require recalculation and may involve guess work as well.

To make this super quick and easy, Excel has builtin tool called Goal seek that does this job for us.

Consider the following situation:

Few words about above data:

Demand is calculated as a function of price and max demand with the equation:

D = Max demand – [Price x elasticity factor ‘f’]
Selling cost is calculated as percentage of revenue
Fixed cost remains constant irrespective of activity

To calculate break-even point using goal seek we need to tell Excel to bring profit to zero (or any other value) by changing certain value. We have one limitation though i.e. only one variable can be changed at a time out of the whole scenario i.e. it can either be price, max demand, cost of sales per unit etc. Lets say we can change price of our product while the rest remains constant as it is not in our control.

Here are the steps:

Go to data tab > click what-if analysis button > click goal seek. A dialogue box will appear. In set cell mention the address of cell with profit figure. Change ‘To value’ field to 0 as we want to determine break-even. In by changing cell mention the cell address containing price per unit. Click OK

Excel will take some time and might come up with the solution. Yes might!

Though Excel did its job, but not exactly. Even this simple financial model kinda broke it. But lets try with 0.01

And this time it worked great.

Lets give it another try and this time with a target profit. Target profit, as the name suggests, is the profit we are aiming to generate. So instead of pushing zero in goal seek ‘set value’ we will give it a better number, suppose 10,000.

It worked flawlessly! So can we assume that under complex financial model its better to do target profit analysis than break-even analysis with goal seek? Can’t say for sure. Whatever works!

Though we have a powerful goal seek took available to use that is helping thousands of analysts around the world on daily basis for years! But it does have serious limitations. In next few article we will learn better and more advanced uses of Excel in financial modeling process.

Let me know what calculations do you make using goal seek and how reliable it is for you in comments.