How to Do a Break Even Analysis in Google Sheets

Knowing your break-even point is one of the most fundamental financial exercises for any business, whether you're selling coffee, software, or handmade crafts. It’s the magic number where you're no longer losing money but haven't started profiting yet. This article will walk you through, step by step, how to perform a break-even analysis using a tool you already have: Google Sheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is a Break-Even Analysis?

A break-even analysis calculates the point at which your total revenue exactly equals your total costs. In other words, it's the number of units you need to sell, or the amount of revenue you need to generate, to cover all your expenses without making any profit.

Why bother? Understanding this number is critical for making smart business decisions. It helps you:

• Set realistic sales goals. Knowing you need to sell 500 widgets a month just to break even gives your sales and marketing teams a clear baseline target.
• Price your products intelligently. See how different price points will raise or lower your break-even point, helping you find a balance between market demand and profitability.
• Assess the viability of a new idea. Before you invest in a new product or service, a break-even analysis can tell you if the sales volume required to be profitable is even achievable.
• Manage costs effectively. By clearly defining your fixed and variable costs, you can easily spot areas where you might be able to cut back.

The Key Components of Your Analysis

Before you jump into Google Sheets, you need to understand the three core pieces of data you'll be working with:

1. Fixed Costs: These are expenses that don’t change, regardless of how many units you sell. They're the costs of keeping the lights on. Examples include rent, monthly software subscriptions (like your CRM or e-commerce platform), salaries, and insurance.
2. 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. Examples include the cost of raw materials, shipping fees, packaging, and credit card transaction fees for each sale.
3. Sales Price Per Unit: This is simply the price at which you sell one unit of your product or service.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Break-Even Analysis Formula

The math behind this is surprisingly straightforward. The primary formula you'll use calculates the number of units you need to sell to break even:

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

The part of the formula in parentheses, (Sales Price Per Unit - Variable Cost Per Unit), is what's known as the Contribution Margin Per Unit. This is a very useful metric on its own - it tells you how much money each sale "contributes" towards covering your fixed costs and, eventually, generating profit.

For example, if you sell a T-shirt for $30 and the variable costs (blank shirt, printing, shipping) add up to $10, your contribution margin is $20. That means every T-shirt sold brings in $20 to chip away at your fixed costs like rent and software subscriptions.

How to Build Your Break-Even Calculator in Google Sheets

Let's move from theory to practice. We'll build a simple calculator using the example of an online store that sells premium coffee mugs.

Step 1: Gather and Organize Your Costs

First, open a blank Google Sheet and label the three main sections for your inputs: Fixed Costs, Variable Costs Per Unit, and Sales Price. Be as thorough as possible when listing out your costs.

Lay it out something like this:

Fixed Costs (Monthly):

• Shopify Subscription: $40
• Adobe Creative Cloud: $55
• Email Marketing Tool: $30
• Internet Bill: $75
• Rent for Small Studio Space: $600

Variable Costs (Per Mug):

• Blank Mug: $4.50
• Custom Printing: $2.00
• Shipping Box & Packaging: $1.75
• Payment Processing Fee (approx. 3%): $0.75

Sales Price Per Unit:

• Premium Coffee Mug: $25.00

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Set Up the Formulas in Google Sheets

Now, let's translate this into our spreadsheet. Create a clean structure. Use one column for the labels and the next for the values.

1. Sum Your Costs: Place your lists in Google Sheets. Create a cell for "Total Fixed Costs" and use the =SUM() formula to add them up. Do the same for "Total Variable Costs Per Unit."
2. Calculate Contribution Margin: Designate a new cell and label it "Contribution Margin Per Unit." The formula here is your sales price minus your total variable costs. Using cell references, it would be something like =B17-B11.
3. Calculate Break-Even Point in Units: This is the big one. Label a new cell "Break-Even Point (Units)." The formula will be your Total Fixed Costs divided by your Contribution Margin. Using cell references from our example, it would look like this:

=B6/B19

Google Sheets will calculate this for you. In our coffee mug example, it's $800 / $16, which equals 50 units. This means the store needs to sell 50 mugs each month to cover all costs.

1. Calculate Break-Even Point in Revenue: Knowing the number of units is great, but knowing the sales revenue is also useful. Create a cell labeled "Break-Even Point (Revenue)." The formula is simply your break-even units multiplied by your sales price per unit: =B21*B17. In our case, that's 50 * $25 = $1,250.

Visualizing Your Break-Even Point with a Chart

A chart helps you see the relationship between costs, revenue, and profit at a glance. It makes the concept click for anyone you share it with.

Step 1: Create a Data Table For The Chart

Below your main calculator, create a small table with four columns: 'Units Sold', 'Total Revenue', 'Total Costs', and 'Profit/Loss'.

• Units Sold: Fill this column with a range of sales numbers, starting from zero and going well past your break-even point. For example: 0, 20, 40, 50, 60, 80, 100.
• Total Revenue: The formula is Units Sold * Sales Price. Make sure to use an absolute reference (with $ signs) for the sales price so you can drag the formula down. The formula for the first row would be =A25*$B$17.
• Total Costs: This formula is a bit more complex: (Units Sold * Total Variable Costs) + Total Fixed Costs. Again, use absolute references for your variables so you can easily fill the column: =(A25*$B$11)+$B$6.
• Profit/Loss: A simple calculation of Total Revenue - Total Costs.

Step 2: Insert and Customize Your Break-Even Chart

Now for the fun part:

1. Highlight the 'Units Sold', 'Total Revenue', and 'Total Costs' columns in your data table.
2. Go to Insert > Chart from the top menu.
3. Google Sheets will usually default to a line chart, which is perfect for this.
4. You can now see the visual representation of your break-even point - it’s where the blue 'Total Revenue' line crosses over the red 'Total Costs' line.

Customize your chart with a title like "Break-Even Analysis" and make sure the axes are clearly labeled. It's a powerful visual tool that instantly tells the story of your business's path to profitability.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Beyond the Basics: Using Goal Seek for Scenario Planning

What if you want to work backward? For example, "How many mugs do I need to sell to make a $1,000 profit for the month?" or "If I want to break even at 35 units, what would my price need to be?"

For this, you can use a powerful add-on called "Goal Seek."

How to Use Goal Seek

1. Install the Add-on: Go to Extensions > Add-ons > Get add-ons. Search for "Goal Seek" and install the official one offered by Google. You'll need to grant it permission.
2. Define a Profit Cell: First, you need a dedicated cell in your main calculator that computes profit. We'll simply create a Profit cell that references the break-even point in units. For example =(B21*B17) - ((B21 * B11) + B6). This cell will show $0 until the units sold go past the break-even point. However, Goal Seek will use this cell to work its magic.
3. Run Goal Seek: Go to Extensions > Goal Seek > Open. A sidebar will appear with three fields:
4. Find the Solution: Click "Solve," and Goal Seek will run through iterations until it finds the input value that achieves your goal. It will tell you that to earn a $1,000 profit, you'll need to sell about 113 mugs.

This transforms your simple calculator into a dynamic scenario-planning tool, allowing you to model different outcomes without tedious manual adjustments.

Final Thoughts

Running a break-even analysis is an essential planning task that brings incredible clarity to your business operations. By using Google Sheets, you can build a flexible, reusable model to guide your pricing strategies and set meaningful sales targets, turning vague financial goals into a concrete action plan.

Putting together an analysis like this is powerful, but you often have to pull data from many different places. Your sales figures are in Shopify, your website traffic in Google Analytics, and your inventory costs might be in another spreadsheet entirely. We built Graphed to remove that painful process of manually wrangling data. By connecting all your marketing and sales sources in one place, you can simply ask questions in plain English - like "what was our break-even point based on last month's Shopify sales and Facebook Ads spend?" - and instantly get dashboards that update in real-time, no spreadsheets required.