Do a Break Even Chart in Excel

Break-even analysis is a tool for evaluating the profit potential of a business model and for evaluating various pricing strategies. You can easily compile fixed costs, variable costs, and pricing options in Excel to determine the break even point for your product. This is the number of units that you need to sell at the price you set in order to break even.

Steps

Creating Your Variable Costs Table

1. Open Excel and create a new blank workbook. You'll be creating multiple sheets in this workbook to handle tracking all of your costs.
2. Click the "+" button next to "Sheet1" at the bottom of the screen. This will create a new blank worksheet.
3. Rename the new sheet to "VariableCosts." This sheet will house the table that tracks all of your product's variable costs, such as shipping, commission, and other costs.
4. Create header labels for the new sheet. To create a basic variable costs table, enter "Description" into A1 and "Amount" into B1.
5. Enter in the names of your business's variable costs in column A. Below the "Description" header, enter the types of variable costs you'll be dealing with for your product.
6. Leave column B ("Amount") blank for now. You'll be filling out the actual costs later in the process.
7. Create a table from the data you've entered. Turning the data into a table will make it easy to plug into formulas later:
   • Select all of the data, including the header row and the blank amounts, by clicking and dragging your mouse over all of the cells.
   • Click the "Format as Table" button. You'll find this in the Home tab. If you're using Excel for Mac, click the Tables tab, click the "New" button, and then select "Insert Table with Headers."
   • Check the "My table has headers" box. This will preserve the labels in the first row as header labels.
   • Click the "Table Name" field in the upper-right corner and name it "VariableCosts."

Creating Your Fixed Costs Table

1. Click the "+" button next to "VariableCosts" at the bottom of the screen. This will create another blank worksheet.
2. Rename the new sheet to "FixedCosts." This sheet will house all of the fixed costs for your product, such as rent, insurance, and other costs that don't change.
3. Create the header labels. Like with the Variable Costs sheet, create a "Description" label in cell A1 and a "Amount" label in cell B1.
4. Enter in the names of your business's fixed costs in column A. Fill out the first column with descriptions of your fixed costs, like "Rent."
5. Leave column B ("Amount") blank for now. You'll be filling out these costs after creating the rest of the spreadsheet.
6. Create a table from the data you've entered. Select everything you created on this sheet, including the headers:
   • Click the "Format as Table" button in the Home tab.
   • Check "My table has headers" to turn row 1 into headers for the table.
   • Click the "Table Name" field and name the table "FixedCosts."

Creating the Break Even Sheet

1. Rename Sheet1 to "BEP" and select it. This sheet will house your main BEP (Break Even Point) chart. You don't have to rename it to "BEP," but it will be easier to navigate your workbook if you do.
2. Create the layout for your break even sheet. For the purposes of this example, create your sheet using the following layout:
   • A1: Sales - This is the label for the Sales section of the spreadsheet.
   • B2: Price Per Unit - This will be the price you charge for each item you sell.
   • B3: Units Sold - This will be the number of units you have sold at the specified price in a set timeframe.
   • A4: Costs - This is the label for the Costs section of the spreadsheet.
   • B5: Variable Costs - These are the costs of your product that you have control over (shipping, commission rates, etc.)
   • B6: Fixed Costs - These are the costs of your product that you don't have control over (facility rent, insurance, etc.)
   • A7: Revenue - This is the amount of money selling your products generates before costs are considered.
   • B8: Unit Margin - This is the amount of money you make per unit after costs are considered.
   • B9: Gross Margin - This is the total amount of money you make for all the units sold after costs.
   • A10: BEP - This is the label for the Break Even Point section of the spreadsheet.
   • B11: Units - This is the number of units you need to sell to match your cost outlay.
3. Change the numeric formats for the output and input cells. You'll need to change the numeric formats for certain cells in order for your data to appear correctly:
   • Highlight C2, C5, C6, C8, and C9. Click the drop-down menu in the "Number" section of the Home tab and choose "Currency."
   • Highlight C3 and C11. Click the drop-down menu and select "More number formats." Select "Number" and then set "Decimal places" to "0."
4. Create ranges to use in the formulas. Select and create the following ranges to make your formulas work. This will create variables that can be plugged into your formulas, allowing you to easily reference and update these values.
   • Select B2:C3 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."
   • Select B5:C6 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."
   • Select B8:C9 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."
   • Select B11:C11 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."

Entering Your Formulas

1. Enter the variable costs formula. This will calculate the total variable costs for the number of items you sell. Click C5 and enter the following formula:
   • =SUM(VariableCosts)*Units_Sold
2. Enter the fixed costs formula. This will calculate the total fixed costs for your product. Click C6 and enter the following formula:
   • =SUM(FixedCosts)
3. Enter the unit margin formula. This will calculate the margin you make after variable costs have been considered. Click C8 and enter the following formula:
   • =Price_Per_Unit-SUM(VariableCosts)
4. Enter the gross margin formula. This determines the total amount you make for all of the units you sell after variable costs. Click C9 and enter the following formula:
   • =Unit_Margin*Units_Sold
5. Enter the BEP formula. This takes your fixed costs and compares them to your margins, informing you how many units you need to sell to break even. Click C11 and enter the following formula:
   • =IFERROR(Fixed_Costs/Unit_Margin,0)

Determining the Break Even Point

1. Enter your business's variable costs. Return to the VariableCosts table and fill out all of the costs related to your product. The more accurate you are here, the more accurate your BEP calculation will be.
   • Each cost in the VariableCosts table should be per unit sold.
2. Enter your business's fixed costs. Enter these costs into your Fixed Costs table. These are the costs of running your business, and should all be set to the same interval (for example, monthly costs).
3. Enter a price per unit. In the BEP sheet, enter an initial estimated price per unit. You'll be able to adjust this as you perform the calculations
4. Enter the number of units you want to sell. This is the number of units you intend to sell in the same timeframe as your Fixed Costs. For example, if your fixed costs includes monthly rent and insurance, the Units Sold will be the number of units sold in that same timeframe.
5. Read the "Units" output. The Units output cell (C11) will display the number of units that you'll need to sell in your timeframe to break even. This number will change depending on the Price Per Unit as well as your Variable Costs and Fixed Costs tables.
6. Make adjustments to the price and costs. Changing the Price Per Unit will change the number of units you need to break even. Try changing the price and see what happens with your BEP value.

Tips

• Increasing your selling price will allow you to reach your break-even point with a lower sales volume, but it may also reduce your total sales.

Warnings

• When entering your sales volume and your fixed expenses, make sure they are both based on the same period.

Related Articles

• Do Grouping Sum in Excel
• Use Summation Formulas in Microsoft Excel
• Use the AND Function in Spreadsheets
• Create a Writing Schedule in Excel

Sources and Citations

See also

• Create a Sin and Cos Circle in Excel
• Add a Header or Footer in Excel 2007
• Calculate CAGR in Excel
• Use Excel for Your Finances
• Parse Data in Excel
• Create Custom Lists in Excel
• Remove Leading or Trailing Zeros in Excel
• Remove Spaces Between Characters and Numbers in Excel
• Use Vlookup With an Excel Spreadsheet
• Create a User Defined Function in Microsoft Excel

Read more ...

What links here

• Make a Spreadsheet in Excel
• Create a Graph in Excel
• Label Axes in Excel