Expert Excel Help - Creating a Break-Even Analysis with Goal Seek
Microsoft Excel is crammed with neat tools to help you perform all kinds of business tasks. In this blog, we're going to look at how to create a break even analysis covering a simple business case scenario.
In the table below, we can see some projected figures covering a number of variables including Sale Price, number of Units Sold, Revenue, Manufacturing cost per unit, Variable Costs, Fixed Costs and finally, the all important Profit.
So far, our projection is not looking great; we are making a profit of minus $8,200!
Using a quick short-cut Ctrl 'backtick' (see the image below; the backtick is usually at the top left of the keyboard, just to the left of the number 1), all the formulas are revealed.
They are very simple:
Revenue = B3*B4
Variable Costs = B4*B6
Profit = B5-B8-B7
Now, by simply copying the original table and pasting it 4 times, we will create 4 scenarios; in each case, our intention is simply to break even, that is, to turn the loss of $8,200 into $0. We will do this by changing either the amount of units sold, their sale price, the fixed costs or the manufacturing costs. Let's start by selecting the target Profit cell, B22:
We then go to the Data ribbon, Data Tools pane and there select What-if Analysis and Goal Seek:
The goal we seek is to change the target cell, B22, to $0. The Goal Seek options box appears with Set cell already containing our B22 target with a To value of 0. We would like this to be achieved By changing cell $b$17, in other words, the Units Sold. Here, we simply click B17 and the details along with $ absolute references are entered into the cell. Then click OK.
The Goal Seek function then updates; we click OK again:
..and we are presented with the results. To break even, we will need to sell not 20,000 units but 31,884. In this way, we can realistically plan our sales strategy.
Alternatively, market conditions may allow for a change in the unit price itself. In which case we can start the process again by selecting our target profit figure and opening the Goal Seek function:
Profit this time is in E22, which we would like to be at $0, break even. This time we select the Unit Price cell, $E$16:
Click OK twice and we see that by upping the price from $2.49 to 2.90, we can break even for the same number of units sold.
The same process works for both Fixed Costs, which if we could reduce to from $22,000 to $13,000 would allow us to achieve our target. In the same way, reducing the Manufacturing costs per unit to $1.30 would also enable us to find our break even point.
For more help and advice for your business calculations, contact our experts. Further details covering the options under What-if Analysis can be found on this Microsoft Excel help page.
