EXCEL: Calculating Break Even

 In doing Feasibility Study and in an actual business operation , there is a need to determine how much revenue it will need to achieve a net profit of exactly Php 0. This is called break even. 

The business will estimate its fixed expenses and estimate the percentage of each of its variable expenses. Using those numbers, it can back into a revenue amount that will result in the break even.

The Figure below shows a break-even calculation. 

Column C shows fixed expense.

Step 1. Enter 0 into cell D18 to indicate zero net profit.

Step 2. Enter the fixed expense amounts in column D next to their labels in column B.

Step 3. Enter the percentage the company pays in commission in cell C7 (8%).

Step 4. Enter a percentage equal to 1 minus the expected gross margin in cell C4. In

this example, the company expects a 60% gross margin percent, so 40% is entered

in C4.

Formula for Gross margin:  Gross Margin =SUM(D7:D8)

Step 5. In cell D13, enter the formula for the operating margin :

Operating Margin =SUM(D15:D18)

The operating margin must be the sum of interest expense and other income and expense.

If we estimate the interest expense to be P465 and the other income and expense to be P1,368, then the operating margin must be P1,833 for the net profit to be zero.

Step 6. In cell D8, enter the formula for the margin net of variable expenses:

Margin Net of Variable Expenses =SUM(D10:D13)

This calculation is operating margin plus the fixed operating expenses. It will drive the revenue calculation.

Step 7. In cell D7, enter the formula for selling expenses:

Selling Expenses =ROUND(D3*C7,0)

We haven’t entered the revenue formula yet, so this will be initially zero for now. But once revenue is

entered, it will show the correct value.

Step 8. Enter the formula for cost of goods sold in cell D4. 

Cost of Goods Sold =ROUND(D3*C4,0)

Like the selling expenses formula, this will return zero until revenue is computed.

Step 9. Finally, enter the formula for revenue in cell D3. 

Revenue =ROUND(D8/(1-SUM(C4:C7)),0)

The revenue calculation divides the margin net of variable expenses by 1 minus the sum of the variable percentages. In the Figure the two variable expenses will be 48% (40% plus 8%) of revenue. One minus that number, 52%, is divided into the margin net of variable expenses to get the revenue.

If this company makes a 60% gross margin, pays 8% in commissions, and has estimated the

fixed expenses accurately, it will need to sell P16,935 to break even.

Best wishes!