## What Is Break-Even Analysis Excel Function?

The break-even analysis in Excel involves determining the point at which the total cost incurred equals the total earnings. Graphically, the break-even point is the coordinate where the total revenue and total expense curves cut each other.

Users can use the break-even analysis Excel function when estimating the pricing of items to ensure the revenue exceeds the costs.

For example, the first table contains the unit price, cost per unit, and fixed costs details.

And, based on the first table data and the units sold data in the second table, we populate columns B:D in the second table. We can refer to the column headings in the second table for the applied formulas in the respective columns.

The above table shows the break-even point based on units sold, which occurs when the units sold are **25**. It is where the costs and revenue are equal.

Further, the below plot helps perform the break-even analysis in Excel visually.

The above break-even analysis plot corroborates our findings based on the profit data in the table. And the cost and revenue curves meet when the units sold are **25** at a unit price of **$180**.

Thus, the inference is that if we wish to achieve the break-even point, we must sell **25** units, and selling more than 25 units will earn us profits.

##### Table of contents

###### Key Takeaways

- The break-even analysis in Excel helps assess the break-even point when the total earnings equal the total expenses. Also, it is possible to perform the analysis visually using a
**Line**chart, where the intersection point of the total revenue and cost curves becomes the break-even point. - Users can conduct the break-even analysis to determine factors such as safety margin, target profit, and changes in expenses and decide whether to produce or purchase.
- We can perform break-even analysis using the
**Goal Seek**feature from the**Data**tab, the break-even table, and the applicable break-even formula.

### How To Do Break-Even Analysis?

We can perform the break-even analysis in Excel using the following methods:

**Goal Seek feature****Break-Even table****Break even analysis formula in Excel**

Let us see each method with an example.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#### Example #1 – Using Goal Seek

The following example explains how to conduct a break-even analysis in Excel using the **Goal Seek** feature from the **Data** tab.

Consider the situation where we plan to sell a product. And assume that we are aware of the variable cost per unit and the overall fixed costs.

Suppose we estimate the feasible sales volumes (**Unit Sold**), and based on the prediction, we fix the product price (**Unit Price**).

So, here is how to use the **Goal Seek** feature to determine the break-even unit price in cell D3, such that the profit is **$0**.

**Step 1:**First, select cell D5, enter the formula to find the unit price and unit sold product, and then, press**Enter**. It will give the total revenue.

*=D3*D4*

**Step 2:**Next, select cell D7, enter the below formula to find the product of the unit sold and cost per unit, and then, press**Enter**. It will give the variable costs.

*=D4*D6*

**Step 3:**Then, select cell D9, enter the below formula to deduct the variable and fixed costs from the revenue, and press**Enter**. It will give the profit gained.

*=D5-D7-D8*

**Step 4:**Now, click**Data**→**What-If Analysis**→**Goal Seek**option to open the**Goal Seek**window.

**Step 5:**Update the three fields in the**Goal Seek**window, as shown below:

As the target profit value is **$0**, the first and second fields are the absolute reference in excel to cell D9 containing the profit value and **0**, respectively.

Further, the requirement is to achieve **$0** profit by changing the unit price. So, the last field is the absolute reference to cell D3, containing the unit price.

**Step 6:**Once we click**OK**, the**Goal Seek Status**window will open.

And clicking **OK** will close the **Goal Seek Status** window.

Thus, the final table will appear as shown below. Also, it includes a column showing the formulas used in column D cells.

The unit price changes from **$30** to **$34 **(**$33.75**, to be precise), and the total profit becomes **$0**. Hence, if we estimate the sales volume as **80**, the unit price should equal or exceed **$34**. Otherwise, we will face losses.

We may set the above table as a **break even analysis in Excel template** when we need to determine the break-even point based on the unit price of a product.

#### Example #2 – Using A Break-Even Table

Let us see an example of a break-even analysis in Excel using a break-even table.

The first table contains a product’s details about units sold, cost per unit, and fixed costs.

Suppose the second table contains unit prices. And based on the given details, we must calculate the total costs, revenue, and profit data to obtain the break-even price in the second table.

Then, we can apply the below-shown formulas and use the two tables as a **break even analysis in Excel template **to achieve the break-even point based on unit price.

**Step 1:**Select cell B6, enter the below formula to get the total costs for the specified unit price, and press**Enter**.

*=($B$3+($B$1*$B$2))*

**Step 2:**Select cell C6, enter the formula to determine the revenue as the product of units sold and unit price, and press**Enter**.

*=$B$1*A6*

**Step 3:**Select cell D6, enter the formula to calculate the profit as the difference between revenue and cost, and press**Enter**.

*=C6-B6*

**Step 4:**Select cells in the range B6:D6, and using the fill handle in excel, enter the formulas in the cell range B7:D15.

The above table shows that the revenue equals the total cost when the unit price is **$36**, which becomes the break-even price.

Let us show the costs, revenue, and profit curves in a break-even chart to confirm the break-even price.

**Step 5:**Select the cell range B5:D15 and select**Insert**→**Line or Area Chart**→**Line with Markers**chart.

Next, we shall modify the legend series for better understanding.

**Step 6:**Right-click the chart area and pick the**Select Data**option from the context menu.

**Step 7:**The**Select Data Source**window will open. Click on the first entry in the**Legends****Entries**field and click**Edit**.

**Step 8:**The**Edit Series**window will open, where we can update the**Series name**to show the corresponding legend as per the requirement.

Click **OK **to go back to the **Select Data Source** window.

**Step 9:**Repeat steps 7 and 8 to update the remaining legend series.

Next, we require the unit price on the X-axis to verify the break-even price.

**Step 10:**Click the**Revenue in $**legend series and click**Edit**in the**Horizontal Axis Labels**field.

**Step 11:**The**Axis Labels**window opens up. Enter the unit price cell range A6:A15 in the required field and click**OK**.

And clicking **OK** will close the **Select Data Source** window.

**Step 12:**Click the chart area to enable the**Chart Elements**option (‘**+**’ icon) and check the**Axis Titles**box.

**Step 13:**Update the chart title and axis titles using the respective elements in the chart area to get the below plot.

Initially, the revenue curve is below the cost curve, implying losses, with the profits being negative. Then, when the unit price is **$36**, the revenue and cost curves intersect, indicating a no-profit and no-loss scenario.

And after the break-even point (highlighted in the above image), the revenue curve improves and crosses the cost curve. It indicates that for unit prices above **$36**, the unit volume of **100** sold yields profits.

#### Example #3 – Using Break-Even Analysis Formula

Using formulas, we can determine the break-even point by parameters such as total sales, units, and unit price. However, we must apply the appropriate formula to perform an accurate break-even analysis.

For example, assume a product is sold at a firm. And we calculated the fixed costs at the firm as $50,000. On the other hand, the variable expense incurred while producing the product is $5 per unit, and we sell the product at a unit sales price of $15.

The requirement is to determine the break-even point of the firm by unit and display the result in cell C7.

Then, we can apply the below **break even analysis formula in Excel** cell C7 to achieve the required information.

**Break Even Unit = Total Fixed Costs / (Unit Sales Price – Variable Cost Per Unit)**

**Step 1:**Select the target cell C7, enter the below formula, and press**Enter**.

**=C5/(C3-C4)**

Thus, for the specified total fixed and variable costs and unit sales price of the product, the firm must sell **5,000** units of the product to recover expenses.

#### Example #4 – Creating A Break Even Analysis Calculator

Suppose we set the profit as $0. And we pre-forecast the units sold, the cost per unit, and the fixed costs.

Then, we can create a **break even analysis calculator Excel** to obtain the required break-even point by unit price.

For example, a calculator with the required formulas below will help us conveniently perform the break-even analysis in Excel.

**Formulas Explanation****Unit Price**: Divide the fixed costs by units sold and then add the cost per unit to the result.**Revenue**: Find the product of unit price and units sold.**Variable Costs**: Find the product of units sold and cost per unit.

**Step 1:**Enter the required units sold, cost per unit, fixed costs, and profit in cells C4, C6, and C8:C9, respectively.

Once we update the above mentioned data in the specified cells, the formulas in cells C3, C5, and C7 update the unit price, revenue, and variable costs.

So, we can now modify the pre-estimated values in the calculator to evaluate the unit price automatically and perform the required break-even analysis easily.

### Important Things To Note

- Ensure the source data contains valid numbers to perform the break-even analysis in Excel correctly.
- When creating the break-even chart for analysis, update the X-axis as the data series based on which you intend to determine the break-even point.
- We can perform a break-even analysis based on different parameters, such as units and unit price. So, ensure we use the appropriate break-even formula to conduct the analysis correctly.

### Frequently Asked Questions (FAQs)

**1. Where is the break-even analysis in Excel?**

The break-even analysis in Excel is not available as an inbuilt function. However, we can perform the analysis using the below methods:**• Goal Seek** feature in the **Data** tab.**• **Break-even table and chart.**• **Appropriate break-even formula.

**2.**

**How to calculate the break-even point by total sales in Excel?**We can calculate the break-even point by total sales in Excel using the below formula.**Break-Even Sales = Total Fixed Costs / (Contribution Margin)**

And the formula for **Contribution Margin** is as follows:**Contribution Margin = 1 – (Variable Costs / Revenue)**

For example, the table below contains the details of a firm’s revenue, variable costs, and total fixed costs.

Suppose the requirement is to determine the break-even point based on total sales to ensure the firm does not incur losses. Assume the target cell is D8.

Then, here is how we can calculate the required break-even point by total sales in cell D8.**• Step 1:** Select cell D7, enter the **Contribution Margin** formula, and press **Enter**.*=1-(D4/D3)***• Step 2: **Select the target cell D8, enter the break-even sales formula, and press **Enter**.*=D5/D7*

**3. What are the benefits of performing a break-even analysis in Excel?**

The benefits of performing a break-even analysis in Excel are as follows:

We can**• **estimate a product’s pricing.**• **review contribution margins.**• **automate calculations to ensure we keep a tab on the various factors influencing our expenses and revenues and remain profitable.

### Download Template

This article must be helpful to understand the **Break-Even Analysis In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Break-Even Analysis In Excel. Here, we explain methods to perform break-even analysis, with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply