How can I calculate break-even analysis in Excel?

Break-even analysis is the study of what amount of sales, or units sold, is required to break even after incorporating all fixed and variable costs of running the operations of the business. Break-even analysis is critical in business planning and corporate finance, because assumptions about costs and potential sales determine if a company (or project) is on track to profitability.

As a simple example, let's take a lemonade stand. No matter how much lemonade you sell, you still need to pay the same amount of rent each month. So, rent is a fixed cost. Compare this to your expenditure on lemons: You will only buy the amount of fruit you need to satisfy the lemonade demand you predict for your business. In summer, this may be more lemons; in winter, fewer lemons. So that makes lemon purchases a variable cost. Variable costs rise in proportion to your business. 

With the above example in mind, break-even occurs when:

Total Fixed Costs + Total Variable Costs = Revenue

• Total Fixed Costs are usually known; they include things like rent, salaries, utilities, interest expense, depreciation and amortization. 
• Total Variable Costs are tougher to know, but they are estimable, and include things like direct material, billable labor, commissions, and fees.
• Revenue is Unit Price * Number of units sold

With this info, we can solve any piece of the puzzle algebraically. Before illustrating that, there are a few points worth mentioning.  

The first is that, within the formula, there is disagreement as to what the best equalizer is. The standard definition is revenue, but the problem with using revenue is that saying "we need to sell X amount to cover costs" excludes taxes, which are a very real expense. In business planning, you should always consider what operating income after taxes looks like, and that measure is called Net Operating Profit After Tax (NOPAT). By using NOPAT, you incorporate the cost of all actual operations, including the effect of taxes. However, the widely understood definition uses revenue, so that is what we'll use. 

The second critical point is that the core of this formula is contribution margin, which plays a critical role in operating leverage. 

The third thing worth noting is that each part of the equation – total fixed costs, total variable costs and total revenue – can be expressed as "Total," or as a per unit measurement depending on what specific break-even measure we require. This is explored more thoroughly in our Excel example. 

Break-Even Sales

When looking at total sales the measurement is in dollars ($), not units: 

Break-even Sales = Total Fixed Costs / (Contribution Margin)

Contribution Margin = 1 - (Variable Costs / Revenues) 

Note that this can be either per unit or total, since it's expressed as a percentage.

Break-Even Units

When looking at total sales, the measurement is in units, not dollars. Depending on the data you have, you may need to translate total dollar values into per unit values:

Break-Even Units = Total Fixed Costs / (Price per Unit - Variable Cost per Unit)

Break-Even Price

Here we are solving for the price given a known fixed and variable cost, as well as an estimated number of units sold. Notice in the first two formulas, we know the sales price, and are essentially deriving quantity sold to break-even. But in this case, we need to estimate both the number of units sold (or total quantity sold) and relate that as a function of the sales price we solve for. 

Variable Costs Percent per Unit = Total Variable Costs / (Total Variable + Total Fixed Costs)

Total Fixed Costs Per Unit = Total Fixed Costs / Total Number of Units

Break-Even Price = 1 / ((1 - Total Variable Costs Percent per Unit)*(Total Fixed Costs per Unit))

Essentially, all of these formulas can be considered as a form of payback period analysis, except that the "time in years" is effectively how long it takes to generate the required number of sales in the above calculations. 

Now that we know what break-even analysis consists of, we can begin modeling it in Excel. There are a number of ways to accomplish this. The two most useful are by creating a break-even calculator or by using Goal Seek, which is a built-in Excel tool.

We demonstrate the calculator, because it better conforms to financial modeling best practices stating that formulas should be broken out and auditable. 

By creating a scenario analysis, we can tell Excel to calculate based on unit. (Note: If table seems small, right-click image and open in new tab for higher resolution.)

Or based on price:

Finally, we can easily build a sensitivity matrix to explore how these factors interact. Given various cost structures, we can see a range of break-even prices from $28 to $133.

(All three of these things are covered extensively in our Investopedia Excel for Finance course.)