ExcelInsider Team
A cost-volume-profit chart is used to understand the relationship between variable costs and revenue. Charts help visualize data so that the higher management and the decision makers understand the impact of the data and take informed decisions. Excel offers various methods for preparing charts and graphs, and with the appropriate values, creating a chart is straightforward. In this article, we will learn the steps you can follow to create a cost-volume-profit chart in Excel.
➤ Calculate the Total Costs, Total Contribution Margin, Revenue, and Profit using the following formulas:
=(C2*B2)+D2
=(A2-B2)*C2
=C2*A2
=G2-E2
➤ Replace A2 with the selling price per unit, B2 with the variable cost per unit, C2 with the unit count, D2 with the fixed costs, E2 with the total costs, and G2 with the revenue.
➤ Select column C to H, go to Insert > Charts > 2-D Line, and select the first one.
➤ Rename the Chart Title to Cost Volume Profit Chart.
A Cost-Volume-Profit chart is an important part of making financial decisions. Below you will find a step-by-step guide on creating a cost-volume-profit chart in Excel.
Steps to Create a Cost Volume Profit Chart in Excel
We have a table here with the selling price per unit, variable cost per unit, production units, and fixed costs. With this data, we will calculate the total costs, total contribution margin, the revenue, and the profit. Afterwards, we will create the chart using those values. Later, we will discuss the analysis of the cost-volume-profit chart as well.
Step 1: Calculate the Required Values
We need some more values to create the chart; these four columns are not enough. Here is how to do that:
➤ Add four more columns for total costs, total contribution margin, revenue, and profit.
➤ In the Total Costs column, write the following formula in E2 and autofill till E13:
=(C2*B2)+D2
➤ Calculate the total contribution margin in F2 cell using the following formula, then autofill:
=(A2-B2)*C2
➤ In the G2 cell, insert the following formula to calculate the revenue, then autofill till G13:
=C2*A2
➤ Finally, calculate the profit in H2 cell using the following formula. Don’t forget to autofill the column.
=G2-E2
Step 2: Create the Chart
Now that we have all the values we need, we can create the cost-volume-profit chart.
➤ Select the C, D, E, F, G, and H columns.
➤ Go to the Insert column of the ribbon, and head to the Charts group.
➤ From the icon of “Insert Line or Area Chart”, select the first chart of the 2-D Line.
➤ Now, click on the Chart Title label and change it to “Cost Volume Profit Chart”
Analysis of the Cost Volume Profit Chart
In the chart we just created, there are 6 lines for each column in the source table. The point where the revenue intersects with the total costs is the break-even point. At the same point, the fixed costs intersect the total contribution margin, which is also an indicator of the break-even point.
The line just below is the units, which keep rising from the beginning; however, the profit does not always increase when the unit rises. Instead, it matches the contribution margin and fluctuates. The company must decide which strategy to choose going forward. If it wants to increase sales, it should aim for the highest unit sales while maintaining the highest profit, which is $ 40,000 in revenue. It is not $42000, however, as the contribution margin is lower.
However, to gain a similar profit at maximum efficiency, the revenue of $36000 should be chased. The profit is still the highest at that point, but the company needs to spend fewer resources on that. In general, this is the optimal production route.
Frequently Asked Questions
What is the key factor in cost-volume-profit analysis?
There are several key components of a cost-volume-profit analysis. Those are the total revenue, contribution margin, variable costs, fixed costs, and the break-even point. All of those calculations are required to analyze the relationship between cost, volume, and profit.
What is the formula to calculate profit?
To calculate net profit, you have to subtract the sum of the expenses from the total revenue. The sum of expenses should include direct expenses, operating expenses, and indirect expenses. However, to calculate the gross profit, you only need to subtract the cost of goods sold from the total revenue.
What is the CVP analysis formula?
The analysis, by definition, cannot have a formula. However, a key indicator of the analysis is the break-even point. To find that out without the chart, we can use the following formula in Excel:
=A1/B1
Here, A1 is the fixed costs, and B1 is the contribution margin.
What are the key assumptions of CVP analysis?
The CVP analysis assumes that the selling price and the fixed costs remain the same for every entry through the range.
How do I calculate CVP?
CVP is not a single calculation, but rather a series of calculations that are required to do an analysis. Usually, you will need to calculate the total variable costs, total costs, contribution margins, total contribution margin, revenue, and the profit/loss as part of the CVP analysis. This might differ according to the company’s needs. For example, if the company does not sell products, but rather services, it will need to calculate the values differently.
Wrapping Up
In this article, we have learned how to create a cost-volume-profit chart in Excel. The Excel file containing the chart, along with the data, can be downloaded for free. If you have a moment to spare, leave your feedback in the comment section. We will see you soon with another Excel tutorial.
