# How can I calculate break-even analysis in Excel?

You can figure out your contribution margin ratio by taking the contribution margin per unit and dividing it by the sales price. Therefore, given the fixed costs, variable costs, and selling price of the water bottles, Company A would need to sell 10,000 units of water bottles to break even. A key component of performing break-even analysis is to understand how much margin or profit is being earned from sales after subtracting the variable costs to produce the units. The selling price minus the variable costs is called the contribution margin. The break-even point is where an asset’s market price equals its original cost.

- Using the break-even point formula above we plug in the numbers ($10,000 in fixed costs / $120 in contribution margin).
- Besides his extensive derivative trading expertise, Adam is an expert in economics and behavioral finance.
- That allows the put buyer to sell 100 shares of Meta stock (META) at $180 per share until the option’s expiration date.
- Access and download collection of free Templates to help power your productivity and performance.
- Break-even analysis helps determine at what point profit kicks in by considering all costs and revenue from sales.

In contrast to fixed costs, variable costs increase (or decrease) based on the number of units sold. If customer demand and sales are higher for the company in a certain period, its variable costs will also move in the same direction and increase (and vice versa). Break-even analysis in economics, business, and cost accounting refers to the point at which total costs and total revenue are equal. A break-even point analysis is used to determine the number of units or dollars of revenue needed to cover total costs (fixed and variable costs). Break-even analysis is a tool used by businesses and stock and option traders. Break-even analysis is essential in determining the minimum sales volume required to cover total costs and break even.

The information required to calculate a business’s BEP can be found in its financial statements. The first pieces of information required are the fixed costs and the gross margin percentage. If the stock is trading at $190 per share, the call owner buys Apple at $170 and sells the securities at the $190 market price.

## Tools to plan, fund, & grow your business

Companies use break-even analysis to determine what price they must charge to generate enough revenue to cover their costs. Revenue is the total amount of money earned from sales of a product while profit is the revenue that’s remaining after all expenses and costs of running https://www.quick-bookkeeping.net/difference-between-internal-audit-and-external/ the business are subtracted from revenue. Assume an investor pays a $4 premium for a Meta (formerly Facebook) put option with a $180 strike price. That allows the put buyer to sell 100 shares of Meta stock (META) at $180 per share until the option’s expiration date.

Profitability may be increased when a business opts for outsourcing, which can help reduce manufacturing costs when production volume increases. In cases where the production line falters, or a part of the assembly line breaks down, the break-even point increases since the target number of units is not produced within the desired time frame. Equipment failures also mean higher operational costs and, therefore, a higher break-even. When calculating break-even quantities, it is important to account for taxes, which are a real expense that a company incurs. Taxes do not vary directly with the revenues; instead they are usually calculated on taxable profits.

A variable cost would include inventory or raw materials involved in production. Break-even analysis helps companies determine how many units need to be sold before they can cover their variable costs but also the portion of their fixed costs that are involved in producing that unit. The total fixed costs are $50k, and the contribution margin ($) is the difference between the selling price per unit and the variable cost per unit. So, after deducting $10.00 from $20.00, the contribution margin comes out to $10.00. For example, if a product sells for $200 each, and the total variable costs are $80 per unit, the contribution margin is $120 ($200 – $80). The $120 is the income earned after deducting variable costs and needs to be enough to cover the company’s fixed costs.

## Business Breakeven Points

The break-even point is the number of units that you must sell in order to make a profit of zero. You can use this calculator to determine the number of units required to break even. Finally, you can use your break-even analyses as part of any forecast scenarios that you explore. By changing numbers in your formula, you can test different types of prices and quantities based on perceived consumer interest.

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. Now that we know what break-even analysis consists of, we can begin modeling it in Excel. The two sales invoice template most useful are by creating a break-even calculator or by using Goal Seek, which is a built-in Excel tool. Please note that this can be either per unit or total or expressed as a percentage. Exciting business insights and growth strategies will be coming your way each month.

By using NOPAT, you incorporate the cost of all actual operations, including the effect of taxes. For the rest of this section, we use the first formula to calculate the break-even point. Now that you understand break-even points and break-even analysis, you’ll be able to put them to work for your business. Remember, this is just a piece of measuring business performance and there are other valuable metrics you should be tracking. You can do this manually with spreadsheets, leverage budgeting and accounting software, or better explore future performance with LivePlan’s performance tracking and forecasting features.

## Maximizing your break-even point formulas

Costs may change due to factors such as inflation, changes in technology, or changes in market conditions. It also assumes that there is a linear relationship between costs and production. Break-even analysis ignores external factors such as competition, market demand, and changes in consumer preferences. 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.

## Understanding Breakeven Points (BEPs)

In effect, the analysis enables setting more concrete sales goals as you have a specific number to target in mind. An unprofitable business eventually runs out of cash on hand, and its operations can no longer be sustained (e.g., compensating employees, purchasing inventory, paying office rent on time). Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets. In accounting, the margin of safety is the difference between actual sales and break-even sales.

A break-even analysis can be used to continuously audit and fine-tune your pricing strategy. If you find sales are missing expectations, you can reference this calculation to easily understand what quantities must be sold if you decide to adjust the price. At that price, the homeowner would exactly break even, neither making nor losing any money. As we can see from the sensitivity table, the company operates at a loss until it begins to sell products in quantities in excess of 5k. Upon doing so, the number of units sold cell changes to 5,000, and our net profit is equal to zero, as shown below in the screenshot of the finished solution.