How to Do a Break Even Analysis in Excel

Knowing exactly when your business or new project will turn a profit can feel like a mystery. A break-even analysis cuts through the guesswork, giving you a clear, data-backed answer to the most important question: how much do you need to sell just to cover your costs? This article will walk you through exactly how to calculate this critical number and visualize it using Microsoft Excel.

What Is a Break-Even Analysis (and Why Does It Matter)?

A break-even analysis identifies the point at which your total revenue equals your total costs. This is the "break-even point" - the moment you are no longer losing money, but also not yet making a profit. Think of it as the financial starting line for profitability.

But it's more than just a number. Conducting this analysis is a fundamental business practice that helps you:

• Set intelligent pricing: It reveals how your sales price per unit directly impacts the volume you need to sell to be profitable.
• Manage costs effectively: By separating fixed and variable costs, you gain a clear understanding of your cost structure and where you might be able to find savings.
• Forecast profitability: Before launching a new product or service, you can forecast exactly how many units you need to sell to make it a worthwhile venture.
• Make informed decisions: Wondering if you can afford to run a discount or invest in a new piece of equipment? A break-even analysis provides the financial context needed to make that call confidently.

In essence, it turns abstract financial goals into concrete sales targets your team can work towards.

The 3 Key Components You Need for Your Analysis

Before you can open Excel, you need to gather three core pieces of information about your business. Getting these right is the most important step, as the accuracy of your analysis depends on them.

1. Fixed Costs

Fixed costs are the expenses you have to pay regardless of how many products you sell. They are the consistent, predictable costs of keeping your business operational. Even if you sell zero units in a month, you still have to pay these bills.

Examples of fixed costs include:

• Rent for your office or warehouse
• Salaries for administrative staff (not tied to production)
• Insurance premiums
• Monthly software subscriptions (e.g., Adobe Creative Cloud, CRM software)
• Property taxes
• Utilities like internet and phone bills

2. Variable Costs Per Unit

Variable costs are expenses that are directly tied to producing or selling one additional unit of your product or service. If you sell more, these costs go up. If you sell less, they go down.

Examples of variable costs include:

• Raw materials (e.g., the coffee beans for a coffee shop)
• Production costs (e.g., the cost to print a t-shirt)
• Packaging and shipping fees
• Sales commissions
• Transaction fees (e.g., Stripe or PayPal fees per sale)

3. Selling Price Per Unit

This is the most straightforward component: it's the price you charge a customer for one unit of your product or service. This is your gross revenue per unit, before any costs are factored in.

Understanding the Break-Even Point Formula

With those three components in hand, you can use the classic break-even formula. It's simple but incredibly powerful.

The formula to find your break-even point in units sold is:

Break-Even Point (Units) = Total Fixed Costs / (Selling Price Per Unit - Variable Costs Per Unit)

The part in the parentheses - Selling Price Per Unit - Variable Cost Per Unit - is known as the Contribution Margin Per Unit. This represents the amount of profit each sale "contributes" towards paying off your fixed costs. Once all fixed costs have been covered, this contribution margin becomes your net profit per unit.

How to Perform a Break-Even Analysis in Excel: A Step-by-Step Guide

Now, let's put this into practice. We'll use a simple business example: a company that makes and sells artisanal candles.

Step 1: Organize Your Data

First, open a blank Excel sheet and set up a clean table for your input variables. This makes your model easy to read and update later.

In column A, list your labels. In column B, enter the corresponding values. For our candle business example:

• Total Fixed Costs: $5,000 (This includes workshop rent, insurance, and marketing salaries for the month).
• Variable Cost Per Unit: $5 (This includes the wax, wick, glass jar, and label for one candle).
• Selling Price Per Unit: $20

Your Excel sheet should look like this:

Step 2: Calculate the Contribution Margin Per Unit

In a new cell (e.g., A4), type the label "Contribution Margin Per Unit". In the cell next to it (B4), you'll calculate the difference between your selling price and variable cost.

Type the following formula into cell B4:

=B3-B2

Excel will calculate this to be $15. This means for every $20 candle sold, $5 covers the direct cost of making it, and the remaining $15 goes toward covering the $5,000 in fixed costs.

Step 3: Calculate Your Break-Even Point in Units

Finally, we calculate the break-even point. In cell A5, type the label "Break-Even Point (Units)". In cell B5, you'll divide your total fixed costs by your contribution margin.

Type the following formula into cell B5:

=B1/B4

The result is 333.33. Since you can't sell a third of a candle, you should always round up. This business needs to sell 334 candles per month to break even.

Taking It Further: Sensitivity Analysis with Excel's Data Tables

Your break-even point isn't static. It changes if your costs go up or if you decide to change your price. Excel's "What-If Analysis" tool, specifically the Data Table feature, is perfect for seeing how these changes affect your bottom line.

Let's find out how different selling prices impact the number of candles we need to sell.

Setting Up the Data Table

1. Starting in a cell below your initial analysis (e.g., A8), create a column of potential selling prices. Let's list prices from $18 to $25.
2. In the cell directly to the right of your column heading and one cell above your list of prices (e.g., B7), you need to reference your original break-even point formula output. Simply type =B5 into this cell. This step is crucial and tells the Data Table what you want to calculate.

Using the What-If Analysis Tool

1. Highlight your entire data range, including the column of prices and the reference cell (in our example, A7:B15).
2. Navigate to the Data tab on the Excel ribbon.
3. Click on What-If Analysis and select Data Table... from the dropdown menu.
4. A small dialog box will appear. Because our alternate prices are in a column, we will use the "Column input cell" field. In this box, select your original Selling Price Per Unit cell ($B$3).
5. Click OK.

Interpreting the Results

Excel will instantly populate the table, showing you the new break-even point for each selling price. Now you have a powerful table for decision-making. For instance, you can see that if you drop the price to $18, you'll need to sell 385 candles to break even. But if you can raise it to $22, you only need to sell 295.

Visualizing Your Analysis: Creating a Break-Even Chart in Excel

A picture is worth a thousand numbers. A break-even chart visually represents the relationship between costs and revenue at different sales volumes, making the concept much easier for others (and yourself) to understand.

Step 1: Create the Data for Your Chart

You need to create a table that simulates your revenue and costs at various levels of production. Set up columns for: Units Sold, Total Revenue, and Total Costs.

1. Units Sold: Create a column of sales volumes, starting from 0 and increasing in reasonable increments (e.g., 0, 100, 200, 300, 400, 500).
2. Total Revenue: In the adjacent cell, the formula is = [Units Sold] * $B$3. The dollar signs create an absolute reference so your selling price doesn't change when you drag the formula down.
3. Total Costs: In the next cell, the formula is =$B$1 + ([Units Sold] * $B$2). This adds your fixed costs to the total variable costs for that volume. Again, use absolute references for your fixed and variable cost cells.

Step 2: Insert and Format the Chart

1. Highlight all three columns of data you just created (Units Sold, Total Revenue, and Total Costs).
2. Go to the Insert tab and find the Charts group. A good choice is a Scatter with Straight Lines chart or a simple Line Chart.
3. Excel will generate the chart. You can immediately see the "break-even" point where the Total Revenue line crosses the Total Costs line.

Add a title and axis labels to make it professional. The area where the costs line is above the revenue line represents a loss, and the area where the revenue line is higher represents profit.

Final Thoughts

Performing a break-even analysis in Excel moves you from hoping for profitability to building a clear plan to achieve it. Whether you are validating a new business idea, planning next quarter's budget, or optimizing your pricing strategy, it's an indispensable tool for understanding your business's financial heartbeat.

While an Excel deep-dive is perfect for this kind of detailed financial modeling, linking it to your live sales and marketing data can be a whole other challenge. We know that juggling data from platforms like Shopify, Google Analytics, and Facebook Ads to create comprehensive reports is time-consuming. That's why we built Graphed—our platform connects directly to your data sources, allowing you to create real-time dashboards and get answers using simple chat messages, so you spend less time exporting reports and more time making decisions that grow your bottom line.