How to Do a Breakeven Analysis in Excel

Launching a new product or business idea feels like navigating without a map, but a breakeven analysis is the compass that shows you exactly where “profitable” is. It tells you the magic number you need to hit - the point where you’re no longer losing money and start making it. This guide will walk you through, step by step, how to perform a powerful breakeven analysis right inside Excel.

First, What Is a Breakeven Analysis?

A breakeven analysis determines the exact sales goal - either in the number of units or dollar amount - that you need to achieve to cover all your costs. The moment you sell one more unit than your breakeven point, you’ve officially started making a profit.

To calculate it, you’ll need to understand three core components of your business costs:

Fixed Costs: These are the expenses that stay the same no matter how many products you sell. Think of them as your baseline operating costs. Examples include rent for your office, monthly salaries, software subscriptions (like your website hosting), and insurance premiums.
Variable Costs: These costs fluctuate directly with your sales volume. If you sell more, these costs go up, if you sell less, they go down. This includes raw materials to make a product, shipping fees for each order, and sales commissions.
Sales Price Per Unit: This is simply how much you charge a customer for a single product or service.

Understanding this point is crucial for setting smart pricing, creating realistic sales targets, and validating whether a new business idea is even financially viable before you invest too much time and money.

Setting Up Your Breakeven Calculator in Excel

Let's build a simple, dynamic breakeven calculator using a relatable example: starting a business that sells custom-printed t-shirts. Follow along with these steps to get your own calculator built.

Step 1: Structure Your Spreadsheet

Open a new Excel sheet. The key to a good spreadsheet is keeping your assumptions (the inputs) separate from your calculations (the outputs). This makes it easy to change a number later without breaking your formulas.

Set it up with the following labels in column A:

Input Assumptions:

Fixed Costs (per month)
Variable Cost (per unit)
Sales Price (per unit)

Next, leave a blank row for spacing, then add your labels for the outputs:

Calculations:

Contribution Margin (per unit)
Breakeven Point (in units)

Step 2: Enter Your Business's Data

In column B, next to your labels, enter your data. Your input cells should contain hard-coded numbers. Your calculation cells should contain formulas.

For our t-shirt business example, let's assume:

Fixed Costs: You have to pay $2,000 per month for your workshop rent and a Shopify subscription. Enter 2000 into cell B2.
Variable Cost: Each blank t-shirt plus the cost of printing costs you $10. Enter 10 into cell B3.
Sales Price: You plan to sell each shirt for $25. Enter 25 into cell B4.

Your spreadsheet should now look something like this:

Step 3: Calculate Contribution Margin and Breakeven Point

Now, let’s use formulas to get our answer. This is where the magic happens.

Calculate Contribution Margin

The contribution margin is the amount of revenue from one sale that's left over to "contribute" to covering your fixed costs. The formula is: Sales Price per Unit - Variable Cost per Unit.

In cell B6 (next to "Contribution Margin"), enter the following Excel formula:

=B4-B3

Excel will calculate this for you. In our example, it’s $15 ($25 - $10). This means that for every t-shirt you sell, you have $15 to put towards your $2,000 monthly fixed costs.

Calculate Breakeven Point (Units)

The Breakeven Point formula divides your total fixed costs by your per-unit contribution margin. It tells you exactly how many units you need to sell to cover those fixed costs.

In cell B7 (next to "Breakeven Point"), enter this formula:

=B2/B6

Excel will show the result: 133.33. Since you can't sell a third of a t-shirt, you’ll need to sell 134 shirts each month to break even. Selling the 134th shirt officially puts you into profitable territory.

Going Deeper: Visualizing Profit and Loss Scenarios

Knowing your breakeven point is great, but what happens at different sales levels? A data table can help you visualize the relationship between units sold, total revenue, total costs, and profit.

Create a Data Table

Find some empty space in your sheet and create column headers: Units Sold, Revenue, Costs, and Profit/Loss.

Under the "Units Sold" column, create a few different sales scenarios. Start with 0 and go up in increments, making sure you pass your breakeven point. For instance: 0, 50, 100, 150, 200.
Revenue Column: Use the formula = (Units Sold) * (Sales Price). If your first unit scenario (0) is in cell A10, the formula would be =A10*$B$4. Be sure to use absolute references ($B$4) for your sales price so the cell reference doesn't change when you drag the formula down.
Costs Column: The formula is = (Fixed Costs) + ((Units Sold) * (Variable Cost)). The Excel version would be =$B$2+(A10*$B$3). Again, use absolute references for your fixed and variable costs.
Profit/Loss Column: Simply subtract costs from revenue. If Revenue is in B10 and Costs are in C10, the formula is =B10-C10.

Drag the formulas down for all your "Units Sold" scenarios. You'll instantly see at which point your Profit/Loss number goes from negative to positive - right between 100 and 150 units, just as our analysis predicted!

Visualizing the Breakeven Point with a Chart

An even better way to see this relationship is with a line chart, often called a cost-volume-profit (CVP) graph. It makes the breakeven point immediately obvious.

Highlight the data in your newly created table, including the "Revenue" and "Costs" columns and their headers. (You can also include "Units Sold,” but we'll manually set that as our horizontal axis).
Go to the Insert tab on Excel's ribbon and choose a "Scatter with Straight Lines" or "Line" chart.
Excel will generate the chart. You should see two lines: one for Total Revenue and one for Total Costs.
The point where these two lines intersect is your breakeven point. It's the "aha!" moment where revenue generated is exactly equal to the costs incurred. Any point to the right of the intersection is profit. Any point to the left is a loss.

Using Goal Seek for Fast Answers

What if you wanted to find the exact breakeven number without building a chart or calculating it manually? Excel’s Goal Seek feature is perfect for this.

Let’s say you want to know how many units you need to sell to achieve a specific profit, like $1,000. Goal Seek can work backward to find the answer for you.

First, make sure you have a cell with a profit calculation formula (like in our data table). Let’s use the profit calculation for selling 150 shirts, which is in cell D13 in our table.
Go to the Data tab, click on What-If Analysis, and select Goal Seek.
A small box will appear with three fields:
- Set cell: Select your Profit/Loss cell (e.g., D13).
- To value: Enter the goal you want to achieve. For breakeven, this value is 0.
- By changing cell: Select the input cell you want Excel to adjust. In this case, it's the "Units Sold" cell for that same row (e.g., A13).
Click OK. Excel will run through thousands of possibilities in a split second and change the value in your "Units Sold" cell to the exact number required to break even: 133.33.

Final Thoughts

Conducting a breakeven analysis is a fundamental exercise for understanding the financial levers of your business. Using Excel, you can quickly move from simple formulas to insightful charts and powerful "what-if" scenarios, giving you the clarity needed to make smarter pricing, cost, and sales decisions.

While Excel is fantastic for one-off analyses, real-time business decisions require live data. That’s where we wanted Graphed to come in. You can connect your marketing platforms like Facebook Ads (a variable cost) and your sales platforms like Shopify or Salesforce (your revenue source) in a single click. Instead of exporting CSVs, you just ask questions like, "Show me our daily breakeven point based on this month’s Shopify sales and Google Ads spend." We instantly build a live dashboard that updates automatically, saving you hours of manual spreadsheet work. To get back your time, give Graphed a try.