How to Make a Break Even Point Graph in Excel

Creating a break-even graph in Excel is one of the best ways to transform a simple calculation into a powerful visual tool for your business. It allows you to see, not just calculate, the exact point where you stop losing money and start making it. This tutorial will walk you through setting up the numbers, building the data table, and creating a clear, easy-to-read break-even chart from scratch.

Understanding the Core Components of a Break-Even Analysis

Before jumping into Excel, it’s helpful to understand the three key pieces of information you'll need. Your entire analysis is built on these numbers, so getting them right is the most important step.

1. Fixed Costs

Fixed costs are the expenses that stay the same every month, regardless of how many products you sell. Think of them as the base costs of keeping your business open.

• Rent for your office or warehouse

• Salaries for your non-production staff

• Monthly software subscriptions (e.g., Shopify, HubSpot, Adobe Creative Suite)

• Insurance premiums

• Website hosting fees

For our example, let's say a small e-commerce business selling custom-printed mugs has $3,000 in monthly fixed costs.

2. Variable Cost Per Unit

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

• Raw materials (the blank mug, the ink)

• Direct labor involved in printing one mug

• Shipping supplies for one package

• Transaction fees per sale (e.g., Stripe or PayPal fees)

In our example, the cost of a blank mug, ink, and packaging for a single order is $5 per mug.

3. Sale Price Per Unit

This one is simple: it’s the price you charge a customer for a single product. For our mug business, let's say they sell each custom-printed mug for $20.

Step 1: Calculate Your Break-Even Point in Units

Now that you have your three core numbers, you can calculate your break-even point with a straightforward formula. This tells you exactly how many units you need to sell to cover all your costs.

The formula is:

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

The part in the parentheses, (Sale Price Per Unit - Variable Cost Per Unit), is also known as your "Contribution Margin." It’s the amount of money from each sale that's left over to help pay down your fixed costs.

Let's plug in the numbers from our mug business example:

• Total Fixed Costs: $3,000

• Sale Price Per Unit: $20

• Variable Cost Per Unit: $5

$3,000 / ($20 - $5) = $3,000 / $15 = 200 Units

This result means our e-commerce store needs to sell exactly 200 mugs in a month to cover all costs. Selling the 201st mug is where they start generating a profit.

Step 2: Set Up Your Data Table in Excel

Knowing you need to sell 200 units is great, but a graph makes this information more dynamic. The first step to creating the graph is to build a data table that shows how revenue and costs change at different sales volumes.

First, set up your core assumptions in a clearly labeled section at the top of your sheet. This makes it easy to change your numbers later (for example, if your rent increases) without having to redo all your formulas.

It should look like this:

• B2: Sale Price Per Unit | C2: $20

• B3: Variable Cost Per Unit | C3: $5

• B4: Fixed Costs | C4: $3,000

Now, let’s create the main data table. Set up five columns:

• Units Sold

• Fixed Costs

• Total Variable Costs

• Total Costs

• Total Revenue

Column A: Units Sold

Start this column at 0 and increase it in steps. A good practice is to include several points before and after your break-even point (200 units). For instance, you could start at 0 and go up by increments of 50, all the way to 400. This ensures your graph gives a complete picture of the "loss" and "profit" zones.

Column B: Fixed Costs

This is the easiest column. The fixed costs don't change, so you’ll just reference the Fixed Costs cell from your assumptions section. In cell B7 (your first data row), enter the formula $C$4. The $ signs make it an absolute reference, meaning it will always look at cell C4, even when you drag the formula down. Drag it all the way down your column.

Column C: Total Variable Costs

This calculates your cumulative variable costs based on how many units you’ve sold. The formula is Units Sold * Variable Cost Per Unit.

In cell C7, enter the formula =A7*$C$3. Drag this formula down.

Column D: Total Costs

This is your fixed costs plus your total variable costs. The formula is simply Fixed Costs + Total Variable Costs.

In cell D7, enter =B7+C7 and drag the formula down the column.

Column E: Total Revenue

This shows your total income generated from sales. The formula is Units Sold * Sale Price Per Unit.

In cell E7, use the formula =A7*$C$2. Drag this one down as well.

When you're finished, your data table should look something like this:

Units Sold | Fixed Costs | Total Variable Costs | Total Costs | Total Revenue0 | $3,000 | $0 | $3,000 | $050 | $3,000 | $250 | $3,250 | $1,000100 | $3,000 | $500 | $3,500 | $2,000150 | $3,000 | $750 | $3,750 | $3,000200 | $3,000 | $1,000 | $4,000 | $4,000 <-- Break-Even Point250 | $3,000 | $1,250 | $4,250 | $5,000...and so on

Notice how at 200 units sold, Total Costs and Total Revenue are both exactly $4,000. That’s our break-even point showing up in the data.

Step 3: Create the Break-Even Chart in Excel

With your data table complete, generating the chart only takes a few clicks. The visual impact comes from seeing the "Total Costs" and "Total Revenue" lines cross.

1. Select Your Data: Highlight the columns for Units Sold, Total Costs, and Total Revenue. You can do this by clicking the header for "Units Sold," then holding the CTRL key (or Cmd on Mac) and clicking the headers for "Total Costs" and "Total Revenue".

2. Insert the Chart: Go to the Insert tab on Excel’s ribbon. In the Charts section, click on the icon for Scatter Chart. Choose the option called Scatter with Straight Lines.

3. The Result: Excel will automatically create a chart. You should see two lines appearing. One line, starting at your fixed cost value on the Y-axis and sloping upward, represents your total costs. The other, starting at zero and sloping upward more steeply, is your total revenue.

The exact spot where these two lines intersect is your break-even point. To the left of this intersection, the cost line is higher than the revenue line - this is the loss zone. To the right, the revenue line is higher than the cost line - this is the profit zone.

Step 4: Formatting Your Chart for Clarity

A good chart tells a story without needing extra explanation. A few small formatting tweaks can make your break-even analysis much easier for anyone to understand at a glance.

• Add a Chart Title: Click on "Chart Title" at the top and give it a clear, descriptive name like "Monthly Break-Even Analysis."

• Label Your Axes: Charts without labeled axes are meaningless. Click the + icon next to the chart and check the box for "Axis Titles."

  • Label the horizontal (X) axis: Units Sold

  • Label the vertical (Y) axis: Revenue / Costs ($)

• Check the Legend: Excel should automatically add a legend, but make sure it correctly identifies the lines for "Total Costs" and "Total Revenue." If not, you can edit it by right-clicking the chart, choosing "Select Data," and editing the series names.

For an extra level of detail, you can also add your 'Fixed Costs' line to the chart. Right-click the chart, choose 'Select Data,' click 'Add,' and then select your 'Fixed Costs' column as the values. This will create a flat horizontal line, visually showing the cost floor your revenue needs to overcome.

Final Thoughts

By following these steps, you can move beyond a simple number and create a clear, visual representation of your business's financial viability. Gathering your cost data, structuring it in an Excel table, and plotting it on a scatter chart lets you instantly see how changes in sales volume impact your bottom line.

While building this analysis in Excel is a great skill, the process of gathering data, populating spreadsheets, and updating charts can become time-consuming, especially as your business grows. This is exactly why we built Graphed. By connecting directly to sources like your Shopify store, QuickBooks account, or even just a Google Sheet where your data lives, we let you ask for a break-even analysis in plain English and get a live, interactive dashboard back in seconds - no formulas or chart formatting required.