Cost-Volume-Profit (CVP) Analysis

Cost-volume-profit (CVP) analysis is a simple way to test how different sales volumes, sales prices, and costs will affect your business’s net operating income (profitability). It is also used to calculate the break-even point, which is the point at which total costs equal total revenues. In other words, it is when your business’s operations begin earning a profit. You can also use CVP analysis to calculate the number of units you need to sell to achieve a certain after-tax profit target. There are a few assumptions that CVP operates on; if your business situation falls outside any of these assumptions significantly, then CVP may produce skewed results.

1. CVP assumes that your prices remain the same throughout the period you are calculating for. If you are using CVP to forecast over several months or years, you will most likely break this assumption. This does not mean that CVP is useless, but if your prices fluctuate, rise, or drop significantly and you sell a significant number of units at these deviating prices, don’t expect CVP to produce realistic results.
2. Basic CVP analysis assumes you sell only one product or service. I have included a workaround to this in the sheet titled, “Schedule for Multiproduct.” It asks you to input unit volume, sale price, and variable expense per unit for each product, and then calculates the weighted average amount for each of these items and plugs it into the CVP analysis. If you only sell one product, simply delete this sheet and plug in the numbers for your product into the “CVP” sheet. However, even with this workaround, the model assumes your sales mix stays the same. In other words, if 50% of all products you sell is product #1, the model assumes this will be true for the entire period.
3. CVP assumes you operate within the “relevant range” for the entire period. You can think of the relevant range as the range of activity (usually measured by units produced/serviced) at which your fixed expenses remain relatively unchanged from month to month. Let’s suppose my autoshop can service up to 1,000 cars per month. Whether I service 1 or 999 cars in the month, I must pay for rent, insurance, any fixed salaries, the telephone bill, and so forth. However, as soon as I cross that 1,000 car threshold, I need to open up a new shop. Now, my fixed expenses take a big step up to account for the rents of both facilities, insurance for both shops, etc. You can see how jumping to a new relevant range would skew the results of CVP significantly.

I have included a simple contribution income statement for you to see how the numbers flow through the income statement, but it is not necessarily needed to conduct CVP analysis.

With a basic understanding of CVP, here are the general instructions that you may also find on the CVP Excel template. I hope you find it helpful!

General Instructions:

The time period you use to calculate each input does not matter, as long as you use the same time assumption for every input. For example, if I expect 1000 monthly unit sales, I must calculate total monthly fixed expenses.

If you sell multiple products, fill out the schedule for multiple products on the next worksheet, and the following three line items will be automatically populated for you.

Unit Sales: This is the number of units you expect to sell at a particular sale price in B9. If you offer services, this is the number of services you expect to sell at a particular sale price in B9. If the number you input is less than the break-even point, your business is not profitable during that period.

Selling Price Per Unit: This is the price you charge per unit.

Variable Expense Per Unit: Variable expenses are expenses that directly vary based on the number of units you produce. Common variable expenses include raw materials, direct labor, sales commissions, transaction fees, billable labor, shipping costs, packaging costs, etc. Add up the total amount of variable expenses you expect to incur for the unit number you inputted into B8 and then divide by the number you put into B8.

Target Profit After Taxes: this is the target profit leftover from business operations that can either be disbursed to owners or held in the company for reinvestment. This is not necessarily an estimate; this can be whatever you would like it to be. This number will flow through to Unit Sales to Acheive Target Profit, which will allow you to see how many units you must sell to acheive this number.

Tax Rate: this is the effective tax rate you think you will pay on profits. If your business does not pay corporate income tax, you can input 0% here.

Total Fixed Expenses: These are expenses which do not vary directly based on the number of units you produce. Common fixed expenses include telephone and internet, insurance, marketing, salaries, office equipment, etc. These expenses usually remain relatively stationary from month to month, and usually only increase in large “steps” when the business makes significant expansions.

Once you have made each input, the formulas will automatically feed the information through to the contribution income statement, CVP graph data section, and will then display on the graph.

Contribution Margin: This is essentially the portion of your revenues that is leftover after variable expenses have been deducted. Contribution margin must exceed fixed expenses each period, otherwise you are at a net operating loss.

Your break-even point is the number of units you must sell for your total costs to equal total revenues. It is essentially when your business begins becoming profitable. On the graph, it is represented as the intersection of the green (total expenses) and blue (sales dollars) line.

Unit Sales to Achieve Target Profit is the number of units you must sell (following the current sales mix) to achieve the after-tax profit goal you set in B12.