Breakeven Analysis with Excel

Excel allows the user do a breakeven analysis through the use of the Goal Seek function. It is performed to obtain the total where the sales income is equal to the total costs.

The problem is not with fixed costs, but with variables in the expenses. In theory the projection is thus easy, but in the real world there are fixed and variable costs. Sales revenue is almost always a variable. As such the Goal Seek tool in Excel is excellent for doing a breakeven analysis.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Variable expenses increase according to units sold. To obtain an accurate assessment the gross profit for the total sold should be equal to the total of the fixed costs. The calculation is complicated by factors such as variable costs which do not change by equal totals for each of the units that are sold.

It is possible to assess the total for such a scenario through the usage of multiple columns. Since Excel can be used to reproduce the model if the absolute referencing tool is utilized for the formula, there is an answer to the complicated task. If you don’t know how to use the absolute referencing tool, you can make use of the Goal Seek function in Excel.

We provide tips and guidelines for using Goal Seek in the breakeven analysis in Excel in our training section. If you need advanced training to become a power user and thus ensure accurate spreadsheets and financial calculations, book for a training course or order our CD-Rom courses for your company to ensure that the breakeven analysis can be done with confidence.