What is a CVP Chart in Excel?

A Cost-Volume-Profit (CVP) chart is a powerful tool that visually maps out your business's path to profitability. It quickly answers one of the most fundamental business questions: "How much do I need to sell to stop losing money and start making it?" This article will walk you through exactly what CVP analysis is and how to build a clear, insightful CVP chart step-by-step in Microsoft Excel.

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

What Exactly Is CVP Analysis?

Before building the chart, it's helpful to understand the components that go into it. CVP analysis is a financial tool used to understand how changes in costs (both fixed and variable) and sales volume affect a company's profit. It helps you pinpoint the sales level you need to achieve to cover all your costs - a point known as the break-even point.

There are a few key ingredients you'll need to know:

• Fixed Costs: These are the expenses that stay the same no matter how many products you sell. Think of things like rent for your office or workshop, monthly software subscriptions, and salaries for your full-time staff. Whether you sell one item or one thousand, these costs remain constant.
• Variable Costs: These costs are directly tied to your production volume. For every product you sell, you incur a certain amount of variable cost. Examples include raw materials, the cost of the product itself if you're a reseller, packaging, and shipping fees. The more you sell, the higher your total variable costs.
• Selling Price Per Unit: This is simply the price at which you sell a single unit of your product.
• Sales Volume: The total number of units you sell. This is the "volume" in Cost-Volume-Profit.

A CVP chart brings these elements together in a single graph, making it incredibly easy to see the relationship between them and to identify where your business becomes profitable.

Why Bother Creating a CVP Chart?

While you can calculate your break-even point with a simple formula, a visual chart provides much deeper context. Here are a few reasons why a CVP chart is so valuable:

• It clearly identifies your break-even point. The chart visually shows the exact point where your total revenue line crosses your total cost line. You can immediately see the number of units you need to sell to get there.
• It illustrates your profit and loss zones. The chart is split into two distinct areas: the loss zone (where costs are higher than revenue) and the profit zone (where revenue surpasses costs). This provides a quick visual reference for your financial performance at different sales levels.
• It helps with scenario planning. What happens if you increase your price by 10%? What if your material costs go up? A CVP chart allows you to quickly model these scenarios and see the immediate impact on your profitability and break-even point.
• It calculates your margin of safety. The margin of safety is the gap between your current sales and your break-even sales. It's essentially your financial cushion. The chart makes it easy to visualize how much your sales can drop before you start losing money.

Getting Your Data Ready for Excel

The first step is to gather the core numbers. Let's imagine we're running a small business that makes and sells hand-poured candles. Here’s the data we need:

• Selling Price Per Unit: We sell each candle for $25.
• Variable Cost Per Unit: The wax, wick, jar, fragrance, and label for each candle cost a total of $10.
• Total Fixed Costs: Each month, we pay $2,000 for our workshop rent, website hosting, and marketing software.

With these three numbers, we have everything we need to build our chart in Excel.

How to Create the Data Table in Excel

To plot a line chart, you need a range of data points. We’ll build a small table that calculates our revenue, costs, and profit at different sales volumes.

Step 1: Set Up Your Assumptions

Open a new Excel sheet. It's good practice to list your key assumptions at the top so you can easily change them later for scenario planning.

In cells A1 to B4, enter the following:

• A1: CVP Assumptions
• A2: Selling Price per Unit
• A3: Variable Cost per Unit
• A4: Total Fixed Costs
• B2: $25
• B3: $10
• B4: $2,000

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: Create the Column Headers for Your Data Table

A few rows below your assumptions, create the headers for the data table that will drive your chart. In cells A7 to E7, type:

Units Sold | Sales Revenue | Fixed Costs | Total Costs | Profit/Loss

We've included a separate column for "Fixed Costs" here. While it's part of the "Total Costs" calculation, plotting it on the graph as its own line provides a useful visual anchor.

Step 3: Fill in the "Units Sold" Column

In the "Units Sold" column (A8 downwards), enter a range of potential sales volumes. Let's start from 0 and go up in increments of 50 to see a good range. Enter 0, 50, 100, 150, 200, 250, and 300 in cells A8 through A14.

Step 4: Calculate the Rest of the Columns with Formulas

Now, we'll use formulas to populate the rest of the table. Using cell references with absolute references (the $ signs) for your assumptions allows you to change a value in your assumptions block, and the entire table will update automatically.

• In Cell B8 (Sales Revenue): Type the formula to multiply the units sold by the selling price.

=A8*$B$2

• In Cell C8 (Fixed Costs): This number doesn't change. We'll simply reference our fixed costs assumption.

=$B$4

• In Cell D8 (Total Costs): This is your total variable cost plus your fixed costs.

=(A8*$B$3)+$B$4

• In Cell E8 (Profit/Loss): This is your sales revenue minus your total costs.

=B8-D8

Now, select cells B8 through E8 and drag the fill handle (the small square at the bottom-right of the selection) down to row 14. Your table will automatically populate with all the correct calculations.

Step-by-Step: Creating Your CVP Chart In Excel

With our data table complete, it's time to build the chart itself.

Step 1: Select Your Data

Highlight the data you want to plot. Click and drag to select cells A7 through D14. We will plot the "Units Sold," "Sales Revenue," "Fixed Costs," and "Total Costs" series. We don't need the "Profit/Loss" data for the primary CVP chart.

Step 2: Insert the Chart

Go to the Insert tab on the Excel ribbon. In the Charts section, find the Scatter (X, Y) chart type. Click on it and choose "Scatter with Straight Lines". A scatter chart is ideal because it properly maps the relationship between units sold (X-axis) and dollar amounts (Y-axis).

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

Step 3: Clean Up and Format Your Chart

The default chart Excel generates needs some improvement to be easily readable.

• Add a Chart Title: Double-click the chart title and change it to something descriptive, like "Cost-Volume-Profit Analysis for Candles."
• Add Axis Titles: Click the + icon next to the chart and check the box for "Axis Titles." Change the horizontal (X-axis) title to "Units Sold" and the vertical (Y-axis) title to "Dollars ($)."
• Verify the Legend: The legend should correctly identify the lines for "Sales Revenue," "Fixed Costs," and "Total Costs." If anything looks incorrect, right-click the chart, choose "Select Data," and make sure your data series are correctly configured.

Your chart should now clearly show three lines: a horizontal line for fixed costs, an upward-sloping line for total costs that starts at the y-intercept of your fixed costs, and another upward-sloping line for sales revenue that starts at zero.

Step 4: Find and Analyze the Break-Even Point

Your break-even point is the main event of this chart. It's the location where your Total Costs line intersects with your Sales Revenue line.

To find the exact number mathematically, you can use the break-even formula:

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

For our candle example:

$2,000 / ($25 - $10) = $2,000 / $15 = 133.33

This means we need to sell 134 candles to officially cover all our costs and start generating a profit. You can see on your chart that the intersection point happens right around the 130-140 unit mark on the x-axis.

Step 5: How To Interpret a CVP Chart

Beyond finding the break-even point, a CVP chart illustrates other financial trends for your business.

Break-Even Formula

A CVP chart helps with break-even planning which helps you to avoid future profit loss for your startup. For most small businesses, your break-even planning will consider all expenses against income for the total units you need to sell to reach your desired profit. For your fixed cost expenses, your CVP chart can reflect these for a monthly period, considering this cost on your total balance sheet to provide a projection. An important part of your CVP chart includes the non-labor costs directly related to the production of goods, assisting you in forecasting and optimizing these. Then, take all these costs into account to figure out how viable a new marketing campaign would be to boost sales, for example through PPC ad revenue spend, by calculating a balance of ad distribution and spend that your revenue and cost-margins can support for ROI.

Target Revenue

You can also create more ambitious target profit goals for your startup. To analyze with your CVP chart, these goals can be informed and determined by your business’s mission and what you feel you should be meeting according to industry standards for your unique products. Once you set these goals, apply your CVP chart to see what would be required to attain them in production costs and profit-margin growth.

Reduced Costing

A dedicated approach to CVP charting in your financial forecasting can help decide which costs you can reduce to meet desired revenue and target profits. Costing should include both your variable cost product development and the marketing methods that would increase desired profits.

Understanding Profit and Loss Zones

Everything to the left of your break-even point along the Y and X axes is your loss zone. This area visually represents that your business is operating at a net loss, often highlighted in red on a CVP chart.

The area to the right of your break-even point is where your business starts to profit. Finally, there is your sales line versus your cost line within the charting: your profit should appear above both for a complete and comprehensive picture of growth.

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 6: What Next to Gain Profit?

The final CVP charting step involves developing the production, marketing, and advertising sales plan over time to meet your profit desires. Once you feel content with how that planning is working for your business or if performance needs a greater boost, CVP chart analysis comes in for a renewed look to optimize all production and marketing steps for your new target profit revenue. Revisiting this model several times during the year can yield significant growth potential.

Final Thoughts

Creating a CVP chart provides you with a living financial model of your business, making it simple, effective, and profit-boosting! By understanding which costs to account for each period and comparing variable costs against market performance, your business can develop, promote, and profit from new or seasonal products. For instance, at Graphed, you can connect with our live and real-time business integration data from Shopify and request visualizations such as a chart illustrating a cost balance projection and analysis of product performance through the fourth quarter.