Software

10 steps to creating a simple break-even template in Excel

Determining the break-even point for your products gives you valuable insights into how business is performing. Here's how to set up a break-even profit model.

Knowing the right price to charge for a product or service can make or break your business. Part of that decision process is often a break-even analysis. The break-even point (BEP) is the point where costs equal revenue (sales). At this point, the product has profit, but you're covering your costs. In other words, anything over the BEP is profit; anything under is loss.

You'll need a few variables to calculate the BEP:

  • Price per unit
  • Cost per unit
  • Fixed cost (a constant that doesn't change, regardless of the number of units produced)
  • Variable costs (costs associated with each unit so it varies with the number of units sold)

When determining the BEP, keep in mind that it isn't a magic number. It's a best-guess point that provides insight into how profit (and loss) changes as your sales go up and down. In this article, we'll build a BEP profit model in 10 steps. Along the way, we'll assume a lot about your financial expertise -- this is an Excel lesson, not a financial business lesson.

Note: The sample Excel workbook used in this walk-through is available for download.

1: Create tables for recording costs

There are two sets of costs, fixed and variable. (There's also semi-variable, but for our purposes, two is adequate.) We'll use Excel's table feature to store this data, making customization a bit easier (you won't have to update cell references).

Use Figure A as a guide to create both tables in two sheets, named FixedCosts and VariableCosts. You can fine-tune them to fit your organization. Start by entering the labels. Then, create a table by selecting any cell in the data range, clicking the Insert tab, clicking Table in the Tables group, checking the My Table Has Headers option, and clicking OK. In 2003, use lists, the precursor to tables (on the Data menu).

Figure A

These two tables record fixed and variable costs.

2: Enter the BEP labels and formats

Using Figure B as a guide, enter the labels to create the BEP analysis sheet named BreakEven. I've used a fill color to indicate input cells, but it isn't necessary for this technique. However, you should apply the following numeric formats:
  • Currency: C2, C5, C6, C8, C9
  • Numeric with no decimals: C3, C11

Figure B

You'll use this sheet to accept user input values for unit costs and units sold and generate the BEP.

3: Create range names

Once you have the labels in place, you can create range names to use in formulas. To create the first two, select B2:C3, click the Formulas tab, click the Create From Selection option in the Defined Names group, and then click OK. Repeat this process with B5:C6, B8:C9, and B11:C11. (Excel will embed underscore characters between the words in the names.)

4: Enter BEP formulas

Now you're ready to enter the formulas that will generate the BEP value. Table A identifies each formula and its cell and offers a brief explanation.

Table A

These formulas will generate the BEP value.

5: Enter costs

Return to the cost tables and enter the appropriate values for your new product. Feel free to alter these tables as necessary. Figure C shows the values we'll use to illustrate how this part of the template works.

Figure C

Enter cost values.

The template doesn't care whether you're using values that represent monthly costs, annual costs, or some other period costs. Just be sure the values consistently represent the same period. These are monthly figures. When you enter the units sold value (next), you'll also want to enter a monthly value.

6: Input BEP variables

Once you have costs to evaluate, you can enter your best guess unit price and unit sold value to generate the BEP. Figure D shows the results of entering 15 and 500, respectively. The BEP point is 134, which means you must sell 134 units to incur no losses during the period.

Figure D

Enter a unit price and units sold value.

7: Enter labels for a sales analysis sheet

At this point, you might consider your template done. But you can enhance the template by providing a broader picture -- a sales analysis sheet. Figure E shows such a sheet, named SalesAnalysis. Again, the fill color indicates input values, which you can alter to refine the picture to reflect your product. The unit sales row uses the Numeric format with no decimal points. The rest of the value grid uses Currency.

Figure E

Enter the labels and formats for this final sheet.

8: Enter sales analysis formulas

Use Table B to enter the appropriate formulas into column B. After entering the anchor formulas in column B, copy them to the remaining cells in the grid, C2:L5.

Table B

Enter these formulas into column B.

9: Use the grid

Using the finished grid, you can make a few determinations:

  • The BEP is low during the period. You might consider lowering the price to increase sales.
  • Increasing the price will lower your BEP.

Both statements are generic, but the analysis provides insight you need to make the best decisions.

10: Finish the template

With the formulas in place, you're ready to finish the template. First, delete all the input values in both costs tables and in BreakEven!C2:C3. Next, you might want to enable protection to protect your formulas, as follows:

  1. Select BreakEven!C2:C3.
  2. Right-click the selection and choose Format Cells.
  3. Click the Protection tab.
  4. Uncheck Locked.
  5. Click OK.

To turn on protection for this sheet, do the following:

  1. Click the Home tab.
  2. From the Format drop-down in the Cells group, choose Protect Sheet. (In 2003, Protection is on the Tools menu.)
  3. In the resulting dialog, uncheck Select Locked Cells. That way, protection will help guide the users to the input cells.
  4. Click OK. You can add a password if you like.

Repeat the above process for the SalesAnalysis sheet. First, unlock B1:L1. Then, enable protection for the sheet. Users don't have to change the unit values in row 1, but they should be able to.

Saving the file as a normal workbook file is fine. Users can change any of the input values, as required to generate a new BEP, and then exit the workbook without saving. If users will be saving their input values, using the template format might be helpful. The sheet, as is, doesn't require many error handling functions, such as IfError(), but you might want to add those later, after customizing it.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments