How To Create A Simple Break-Even Analysis Using Excel
Business performance can be measured by a lot of things, but nothing can say a lot about how your business performs than a break-even analysis.
A break-even analysis determines your break-even point (BEP), which is the point at which the total cost and total revenue of the business are equal. At this point, your business is neither experiencing a profit nor a loss. This means that any figure that goes above the BEP is profit and anything that goes below it is loss. Being in the BEP determines that your business is adequately covering your costs from what it is earning, but your profit is zero.
What to Consider When Making a Breakeven Analysis?
To create a break-even analysis, a number of factors must be considered. These are the price per unit of a product or service, opportunity costs (or cost per unit), fixed cost (constant figure that remains the same regardless of the number of units produced), and variable costs (which are costs that change depending on the number of units sold such as raw materials).
Creating a Breakeven Analysis in Excel
Computing your BEP gives you a deeper, more informed idea on how your business is doing. This process is not as complicated or as difficult as it seems. In fact, below is a detailed guide on how to compute for a BEP using Excel.
Related: Also see our review of the Free Breakeven Analysis Template for Excel
1. Create a table for your costs
The costs of producing a certain number of units of products or providing services can be determined by two factors: fixed and variable costs. Create a table in each sheet for these factors and name them Fixed Costs and Variable Costs. Then, list the costs that you incur in your production in one column, the amount in the other, like below:
Enter the labels and then create a table by choosing any cell in the data range, click on the Insert tab, choose Table in the Tables group, check My Table Has Headers, and then click OK.
2. Label and format your BEP
Enter the labels to create your BEP Analysis sheet, which you will name as “BreakEven.” Then, set the numeric format to Currency for C2, C5, C6, C8, and C9, like the table below. Format C3 and C11 as well to Numeric without any decimal places.
3. Assign Range Names
Once you have created your labels, it’s now time to create range names, which you will use in your formulas. To do this, select B2:C3, click on the Formulas tab, click Create From Selection under Defined Names, and click OK. Do this as well for B5:C6, B8:C9, and B11:C11.
4. Enter Your Break-Even Formulas
Once you have assigned range names, you can now enter the formulas for your BEP values. Look at the table below for your reference:
5. Enter your costs
In your costs tables, Variable and Fixed Costs, enter the values that correspond to your labels to list what your expenses and other costs are. You can modify your table, add or subtract values and labels, depending on your own preferences and operations. The guide below shows you what your costs tables should look:
Note that the periods for each of your costs must be the same. For example, if you are typing in your monthly shipping costs, the other costs must be recorded over a monthly period and this must be consistent across all values.
6. Input your BEP variables
Now that you have entered all your costs, you can then enter your desired unit price and unit sold to generate your BEP. This will show if you are within your break-even points or are exceeding or not meeting it.
Like in the table below, you can enter $25 for Price Per Unit and estimate that you will be able to sell 1,000 units over a period of a month. The variable costs will be considered and the template will automatically compute for your BEP, as shown:
7. Create a comprehensive sales analysis
Once you have determined your BEP, you can go ahead and create a more comprehensive sales analysis sheet. This will show you how many units you need to sell to meet your profitability targets, and see how much your costs and profits are as your sales increase over a given period.
In the example table below, you can see the Unit Sales from 0 to 100 and these figures can be modified for your own company and production capabilities. Your Profit and Loss figures will automatically be reflected, as shown:
Ensure that your price and sales cells are formatted with Currency and the Unit Sales cells have Numeric formatting.
8. The sales analysis formula
To create a sales analysis, the following formula must be set in anchor cells (B2:B5) and copied to corresponding cells in the worksheet (C2:G5):
9. Use the finished grid
Following the formula, you can use the grid to make your analysis to come up with the following:
- If the BEP is low, you can consider lowing the price to increase sales.
- If the price is increased, the BEP is low.
You can come up with your own determinations and even include these in your annual, quarterly or monthly sales reports and presentations.
10. Finish your template and come up with a professionally created analysis
Once you have assigned formulas to corresponding cells, you can finish your template and reuse this for all your break-even and sales analysis needs. Simply delete all the input values in the costs tables and in your break even table (BreakEven!C2-C3).
You may also want to protect your formulas by doing the following steps:
- Select BreakEven!C2:C3.
- Right click on this selection and click Format Cells.
- Choose the Protection tab.
- Uncheck Locked.
- Click OK.
- Turn on the protection for the sheet by clicking on the Home tab.
- Go to the Format drop-down menu in cells group and choose Protect Sheet.
- Uncheck Select Locked Cells to guide users to where they need to input their information in the appropriate cells.
- Click OK.
- You also have the option to add a password to your sheet.
You can also do this for your Sales Analysis. Simply unlock B1:G1, and then enable the protection for the sheet.
This template is fully customizable, and you can add error handling functions, as well as many others.