Break Even Analysis / Cost Volume Profit Analysis Excel Model

Description
Managers must make decisions about sales volume, pricing and costs and are concerned about the impact of their decisions on profit. Therefore, they need to understand the relations among revenues, costs, volume and profit. Cost-volume-profit, or CVP, analysis provides managers with information for decision making.

What is CVP?

CVP analysis explores the relationship between sales revenue, cost and their effect on profits. Also known as Break-even analysis.

Break Even Analysis

The break-even point can be defined as:
the level of activity at which a business makes neither a profit nor loss.

OR

the point where total sales revenue equals total expenses (variable and fixed)

OR

the point where total contribution margin equals total fixed expenses.


Key Model Outputs:

Contribution margin

Contribution margin ratio

Break-even point

Target profit

Margin of safety

Operating Leverage

Break Even Chart

Multiproduct Analysis


Assumptions and Limitations of CVP Analysis

CVP analysis is based on a number of simplistic assumptions about cost behavior which undermine the model’s effectiveness

1.Selling price is constant. The assumption is that the selling price of a product will not change as the unit volume changes. This is not wholly realistic since there is usually an inverse relationship between price and unit volume. In order to increase volume it is often necessary to drop the price. However, CVP analysis can easily accommodate more realistic assumptions.

2.Costs are linear and can be accurately divided into variable and fixed elements. It is assumed that the variable element is constant per unit and the fixed element is constant in total. This implies that operating conditions are stable and there are no major changes in worker efficiency. It also implies that the fixed costs are really fixed. When there are large changes in volume, this assumption becomes tenuous. However, if a manager is able to estimate the effects of a decision on fixed costs, these estimates can be explicitly taken into account in CVP analysis.

3.The sales mix is constant in multi-product companies. This assumption is invoked in order to use the simple break-even and target profit formulas in multi-product firms. If unit contribution margins are fairly uniform across products, violations of this assumption will not be important. However, if unit contribution margins differ a great deal, then changes in the sales mix can have a big impact on the overall contribution margin ratio and hence upon the results of CVP analysis. If a manager can predict how the sales mix will change, then a more refined CVP analysis can be performed in which the individual contribution margins of products are computed.

4.In manufacturing companies, stocks do not change. It is assumed that everything the company produces is sold in the same period. Violations of this assumption result in discrepancies between financial accounting profit and the profits calculated using the contribution approach.