DISCOVER
×

How to Calculate a Break Even Analysis Using Excel

Updated March 23, 2017

A break even analysis allows a company to calculate how much it needs to sell during the year to cover its costs and start turning a profit. You can determine the break even point in either units sold or sales revenue. Microsoft Excel is useful for performing a break even analysis because it allows you to change variables in equations easily.

Open a blank Excel spreadsheet and type "Average annual fixed Costs" in cell A1, "Per unit sales price" in cell A2, and "Average annual variable costs per unit" in cell A3. In cell A5, type "Break even in units," and in cell A6, type "Break even in sales." These are labels for the numbers and formulas that you will input in Column B.

Type "=your annual fixed costs/12" in cell B1. Replace the words "your annual fixed costs" with the fixed cost amount from your company's income and expense statement. Fixed costs are independent of fluctuations in a company's production level. For example, no matter how many widgets a company produces, the rent for its office space remains the same each month.

Type your product's per unit sales price in cell B2.

Type "=total variable costs/units produced" in cell B3. Replace the words "total variable costs" with the variable costs from your income and expense statement and replace "units produced" with the number of annual units produced. Your variable costs are annual costs that change depending on a company's production level. For example, hourly wages may increase if your workers produce more goods than normal in a year.

Type "=(b1/(b2-b3))" in cell B5 to calculate the break even point in units.

Type "=(b1-(1-(b3-b2)))" in cell B6 to calculate the break even point in sales revenue.

Things You'll Need

  • Income & expense statement
bibliography-icon icon for annotation tool Cite this Article

About the Author

Carter McBride started writing in 2007 with CMBA's IP section. He has written for Bureau of National Affairs, Inc and various websites. He received a CALI Award for The Actual Impact of MasterCard's Initial Public Offering in 2008. McBride is an attorney with a Juris Doctor from Case Western Reserve University and a Master of Science in accounting from the University of Connecticut.