How to Calculate Break-Even in Excel
A break-even point is a financial tool that allows a company to forecast the amount of product it must sell to break even or reach zero dollars of income. A break-even analysis can show how many units of product it must sell or how many dollars of revenue it must earn to break even. Calculating a break-even point in Microsoft Excel requires inputting the necessary information to calculate the break-even point and then inputting a simple formula.
Open Microsoft Excel on your computer.
Type in "Fixed Costs" into the A1 cell in Excel.
Type in "Sale Price" into the A2 cell.
Enter the "Variable Cost Per Unit" into the A3 cell.
Type in "Break-Even Units" into the A4 cell.
Enter "Break-Even Sales" into the A5 cell.
Enter the fixed cost per month of the business into the B1 cell.
Type the selling price per unit into the B2 cell.
Enter the cost of each unit into the B3 cell.
Type in the following formula into the B4 cell:=B1/(B2-B3)Press "Enter." Excel will calculate how many units you must sell per month to break-even.
Type in the following formula into the B5 cell:=B4*B2Press "Enter." Excel will calculate the revenue per month required to break even.
Tips & Warnings
- Type in the fixed cost per year of your business into the B1 cell to get the annual break-even point.