How to Make a CVP Graph in Excel

Seeing where your business breaks even isn't just a theoretical concept, it's a critical number you can pinpoint with a Cost-Volume-Profit (CVP) graph. Creating one in Excel helps turn abstract financial data into a clear visual roadmap showing exactly where profit begins. This guide will walk you through setting up your data and building a CVP chart step-by-step.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

First, What Is a CVP Graph?

A Cost-Volume-Profit (CVP) graph, often called a break-even chart, is a simple visual tool that financial analysts and business owners use to understand the relationship between costs, sales volume, and profits. It plots your total costs and total revenue against the number of units you sell.

The point where the Total Revenue line crosses the Total Costs line is the break-even point. This is the magic number - the exact volume of sales you need to cover all your costs. Sell more than this, and you're in the profit zone. Sell less, and you're operating at a loss.

Understanding this visual makes it much easier to answer questions like:

• How many units do we need to sell to start making a profit?
• What happens to our profit if we increase our prices?
• How will a rise in our material costs affect our break-even point?
• What sales target should we set to achieve a specific profit goal?

Before building the chart, you need to be clear on two types of costs:

• Fixed Costs: These are expenses that don't change no matter how many units you sell. Think of things like rent, salaries, software subscriptions, or insurance. Whether you sell 10 units or 10,000, these costs stay the same.
• Variable Costs: These costs are directly tied to the production of each unit. They include raw materials, packaging, and direct labor. If you sell more units, your total variable costs go up.

Step 1: Gather and Organize Your Core Financial Data

Before you even open Excel, you need a few key numbers. For this example, let's imagine we're running a small e-commerce business selling premium coffee mugs.

Here’s the baseline data you'll need:

• Sales Price Per Unit: How much you sell one product for. Let's say we sell each mug for $25.
• Variable Cost Per Unit: The cost to produce one product. This includes the blank mug, printing, and packaging. Let's set this at $10 per mug.
• Total Fixed Costs: The sum of all your fixed expenses for an entire period (e.g., per month). This includes our Shopify subscription, workshop rent, and marketing software. Let's say this totals $5,000 per month.

We'll use these numbers to build our model in Excel.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Set Up Your Data Table in Excel

This is where we'll lay the groundwork for our chart. The goal is to create a table that calculates revenue, costs, and profit at different sales volumes.

1. Create Your Input Section

First, it's a good practice to put your core assumptions in a clearly labeled section at the top of your sheet. This makes it easy to change them later and see how it affects your graph.

In cells A1:B3, set this up:

Sales Price Per Unit | $25 Variable Cost Per Unit | $10 Fixed Costs | $5,000

This keeps your variables organized and accessible.

2. Create Your Calculation Table

Now, let's create the columns that will drive your chart. Starting in row 5, label your columns as follows:

A5: Units Sold B5: Total Revenue C5: Total Costs D5: Profit/Loss

We’ll generate a theoretical range of sales - from zero units to whatever makes sense for your business. For this example, let's step up in increments of 100 units.

• In cell A6, type 0.
• In cell A7, type 100.
• Select both cells A6 and A7. Click and drag the small square (the fill handle) at the bottom-right corner of your selection down to about row 16. This will auto-fill the series, giving you values from 0 to 1,000.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

3. Enter the Formulas

Next, we’ll use formulas to populate the rest of the table. We’ll use absolute references (the dollar signs, e.g., $B$1) for our input cells so the formulas don’t break when we drag them down.

A. Total Revenue Formula

In cell B6, enter the formula to calculate revenue based on the units sold in A6 and the price in B1:

=A6*$B$1

Press Enter. Then, select cell B6 and drag the fill handle down to fill the column.

B. Total Costs Formula

Total Costs are your Variable Costs for all units plus your Fixed Costs. Variable Costs are calculated by multiplying the Units Sold (A6) by the Variable Cost per Unit ($B$2). Then, we add our fixed costs ($B$3).

In cell C6, enter this formula:

=(A6*$B$2)+$B$3

Hit Enter and drag this formula down to populate the whole column. Remember, your fixed costs are included even when you sell zero units!

C. Profit/Loss Formula

Finally, your profit or loss is simply your Total Revenue minus your Total Costs. In cell D6, enter this formula:

=B6-C6

Press Enter, and drag this down as well. Your data table should now look like this:

Step 3: Creating the CVP Chart

Now your data is ready, creating the graph itself is straightforward. For this example, we’re focused on visualizing revenue against total cost.

• Select the columns A5 to C16 on your table (Units, Revenue, and Costs).
• Go to the Insert ribbon at the top of the Excel window.
• Look for the Chart section and click the Scatter drop-down. It's generally the best option for a break-even chart, but you can also use line charts as they both provide similar visualizations.
• From the "Scatter" submenu, pick the "Straight Line and Markers" chart type. It will connect your data points with a clean line to model progression.

Excel will now generate a graph showing the data on your spreadsheet.

Tweaking the Chart for Greater Clarity

After Excel has generated the chart, it's time to polish it. A clean, well-labeled graph is dramatically more insightful.

Adding a Title & Axis Labels

The chart will pop up with a placeholder title. To be more descriptive, click the text box and enter a better name, like "Cost-Volume-Profit Analysis" or "Break-Even Analysis for [Your Business Name]". Next, label the axes. Use the "+" symbol on the chart's right-hand side to check the box next to "Axis Titles". Rename the Y-axis to "Dollar Value" and the X-axis to "Units Sold".

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Identifying the Break-Even Point

Your new graph shows two lines. The blue line is typically for Total Revenue, climbing steeply from the start, and the orange for Total Costs, beginning higher due to fixed costs. The point where the blue line intersects with the orange is your break-even point. In this case, they meet around 400 units.

How to Interpret What You See

Once styled, the chart tells a simple, clear narrative:

• Anywhere left of the intersection, where the orange (costs) line overtakes the blue (revenue) line, you are operating at a loss. The company is spending more than it earns.
• The exact intersection is the break-even point. At this price or volume, your costs are fully covered, but you're not yet making a profit.
• Anywhere to the right of the intersection, where the revenue (blue) line climbs faster than the cost line (orange), represents a profitable operation. Each additional unit sold contributes to your total earnings.

Your CVP analysis clearly shows that selling 334 mugs covers your business's monthly expenditures, and any sales beyond this will start generating profit.

Final Thoughts

Building CVP charts and break-even analyses in Excel are fantastic ways to visualize the financial health of sales activity. By mapping the relationship among total volume, profit, and costs, this one chart can offer critical insights needed to guide strategic pricing, inform marketing spend, and set achievable sales targets for your team.

Manually exporting sales data into Excel for chart updates can become tedious, especially as you pull together reports from multiple sources. This is where Graphed comes in. Just connect your data source and ask us to gain insights in real time, build live dashboards that stay current, and generate the same types of financial analyses discussed - all automatically, without needing complex formulas or wrestling with spreadsheets.