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 need
- Income & expense statement