How to Make a Cost Volume Profit Graph in Excel

A Cost-Volume-Profit graph is one of the most powerful and intuitive tools for understanding your business's financial health. It visually maps out how changes in sales volume and costs impact your bottom line, making it easy to spot your break-even point and forecast profitability. This article will walk you through, step-by-step, how to build your own dynamic CVP graph in Microsoft Excel.

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 is a Cost-Volume-Profit (CVP) Analysis?

Before building the graph, it's helpful to understand the core components of CVP analysis. At its heart, CVP analysis looks at the relationship between five key elements to help you make more informed business decisions.

The components are:

• Fixed Costs: These are the costs that don't change, no matter how much you sell. Think of things like monthly rent, salaries for administrative staff, insurance premiums, or software subscriptions. Whether you sell one item or one thousand, this number stays the same.
• Variable Costs: These costs are directly tied to your production volume. They increase as you sell more and decrease as you sell less. Examples include the cost of raw materials, direct labor involved in production, and shipping costs for each unit.
• Total Costs: This one is simple: it’s your Fixed Costs plus your Total Variable Costs. As you sell more units, this number goes up because of the variable cost component.
• Sales Price per Unit: This is the price at which you sell a single unit of your product or service.
• Sales Revenue: This is the total income you generate from sales. The formula is simply (Sales Price per Unit) x (Number of Units Sold).

The CVP graph brings these five components together into a single, easy-to-read chart. This visualization makes it immediately obvious where you stop losing money and start becoming profitable — your break-even point.

Gathering Your Data for the CVP Graph

First things first, you need to pull together three essential pieces of information about your business. For this tutorial, we'll use a simple and relatable example: a small business that designs and sells custom tote bags.

Here’s the data we need:

1. Total Fixed Costs: Let's say our monthly fixed costs (studio rent, website hosting, design software) add up to $1,500.
2. Variable Cost per Unit: The cost for one blank tote bag, the ink, and the packaging comes out to $7 per bag.
3. Selling Price per Unit: We sell each custom tote bag for $25.

With these three numbers, we have everything we need to build our financial model and graph in Excel.

Step 1: Set Up Your Data Table in Excel

Now, let’s fire up Excel and structure our data. Building a clean data table first makes creating the graph much easier.

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

Create an "Assumptions" Section

Best practice in Excel modeling is to put your key input variables in their own section at the top of the sheet. This allows you to easily change them later for "what-if" analysis without messing with your formulas.

In a fresh sheet, set it up like this:

• In cell A1, type "Assumptions".
• In cell A2, type "Selling Price per Unit". In B2, type $25.00.
• In cell A3, type "Variable Cost per Unit". In B3, type $7.00.
• In cell A4, type "Fixed Costs". In B4, type $1,500.00.

Create the Main Data Table

Next, we’ll create the table that calculates our costs and revenue across a range of sales volumes. About two rows below your Assumptions section, create the following headers in a row (e.g., in cells A7 through E7):

Units Sold | Revenue | Variable Costs | Fixed Costs | Total Costs

Now we'll populate the data under these headers.

Step 2: Populate the Data

1. Populate the "Units Sold" Column

In the first column (A), create a range of potential sales volumes. Start with 0 in cell A8. Then, in cell A9, type a formula to increment it, like =A8+10. Click the small square at the bottom-right corner of cell A9 and drag it down to generate a series (e.g., 0, 10, 20, 30... fill it down until you get to around 200 or 300 units).

2. Write the Formulas for the Other Columns

This is where the magic happens. We'll use formulas that reference our "Assumptions" section. Using absolute references (with a $ sign) is crucial here. An absolute reference tells Excel to always look at that specific cell, even when you drag the formula down to other rows.

Starting in row 8 (alongside "0 Units Sold"):

• Revenue (B8): The formula is =A8*$B$2
• Variable Costs (C8): The formula is =A8*$B$3
• Fixed Costs (D8): This is always the same, so use $B$4
• Total Costs (E8): The formula is =C8+D8

Once you’ve input these formulas in row 8, select all four cells (B8 to E8) and drag them down to fill the rest of your table. Excel will automatically adjust the formulas for each row, and your table will populate instantly.

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 3: Calculate Your Break-Even Point

Before graphing, it’s a good idea to mathematically calculate your break-even point. This helps verify that your graph is accurate. The formula is:

Break-Even Point (in units) = Fixed Costs / (Sales Price per Unit - Variable Cost per Unit)

Let's add this to our Excel sheet below our Assumptions. In cell A5, type "Break-Even Point (Units)". In B5, enter the formula:

=B4/(B2-B3)

Based on our tote bag example, the result will be 83.33. This means we need to sell 84 bags to actually start making a profit.

Step 4: Create the Cost-Volume-Profit Graph

Now that our data is perfectly structured, creating the graph takes just a few clicks.

1. Select Your Data: Highlight the columns you want to graph. You need your X-axis data (Units Sold) and your Y-axis data (Revenue and Total Costs). Select the entire "Units Sold" column, then hold down the Ctrl key (or Cmd on Mac) and select the "Revenue" and "Total Costs" columns. Tip: Make sure to include the header row in your selection!
2. Insert the Chart: Go to the Insert tab on Excel's ribbon. In the "Charts" section, find the "Scatter" chart type. Click on it and select "Scatter with Straight Lines." This chart type is ideal for CVP graphs.
3. Your Graph Appears: Excel will immediately generate the chart. You’ll see the "Units Sold" on the horizontal (X) axis and the dollar amounts on the vertical (Y) axis. The two lines represent your Revenue and Total Costs. The point where they cross is your break-even point!

Step 5: Format and Interpret Your Graph

A basic chart is good, but a well-formatted chart is great. Here’s how to make your CVP graph clear and professional.

Formatting Tips

• Add a Chart Title: Click on the default chart title and change it to something descriptive, like "Cost-Volume-Profit Analysis for Tote Bags."
• Label Your Axes: Great charts are never ambiguous. Click the Chart Elements button (the '+' sign next to the chart in newer Excel versions) and check the box for "Axis Titles." Label the X-axis "Units Sold" and the Y-axis "Amount ($)."
• Check Your Legend: Excel should automatically create a legend labeling your lines as "Revenue" and "Total Costs," since we included the headers in our selection. If not, right-click the chart, choose "Select Data," and assign the correct names.

How to Read the Graph

Your finished graph tells a simple but powerful story:

• The Break-Even Point: Notice where the Revenue line crosses over the Total Costs line. This is the moment your business is no longer losing money. In our example, it happens just past 83 units, confirming our calculation.
• The Loss Area: The triangular area to the left of the intersection is your loss zone. Here, the Total Costs line is above the Revenue line, meaning your expenses are higher than your income.
• The Profit Area: The area to the right of the intersection is your profit zone. Here, the Revenue line climbs above the Total Costs line, and the gap between them represents your growing profit.

This visual makes it easy to quickly estimate the profit or loss at any sales volume. Want to know how much profit you'd make selling 150 tote bags? Just find 150 on the X-axis, trace up, and see the financial distance between the two lines.

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

Using Your CVP Graph for "What-If" Analysis

The real power of building your CVP model this way is its ability to conduct instant scenario planning. Because all our formulas are linked to the "Assumptions" section, you can change an input value and watch the entire table and graph update automatically.

Try it out:

• What if rent increases? Change your Fixed Costs in cell B4 from $1,500 to $2,000. Instantly, the Total Costs line on the graph will shift upward, and your break-even point will move to the right (to 112 units). You can see that you now need to sell more bags each month just to cover the higher rent.
• What if material costs go up? Change the Variable Cost per Unit in cell B3 from $7 to $9. The slope of your Total Costs line will become steeper, squeezing your profit margin and pushing your break-even point higher.
• What if you run a sale? Change the Selling Price per Unit in cell B2 from $25 to $20. Your Revenue line will become less steep, and you'll see how much more volume you need to generate to be profitable at the new, lower price.

This dynamic tool transforms Excel from a static datasheet into a powerful instrument for strategic decision-making.

Final Thoughts

Building a CVP graph in Excel is a fundamental skill for anyone in business, from an entrepreneur launching a new product to a manager analyzing a department's financial performance. It provides a clear, visual roadmap to profitability, showing you not just where you are, but where you could go based on different strategic choices and market conditions.

While creating these models manually in Excel is a fantastic analytical exercise, keeping them updated can quickly become a challenge, especially when your data lives across different platforms like Shopify, Google Analytics, and your advertising accounts. This is exactly why we built Graphed. It securely connects to all of your essential data sources, allowing you to ask questions like "Show me a comparison of revenue vs. ad spend this quarter" or "Create a dashboard of our sales funnel from lead to close" in plain English. Graphed turns hours of data wrangling into a simple conversation, so you get live, automated insights without ever having to touch a spreadsheet formula again.