Break Even Analysis

• Version
• 12'874 Downloads
• 13 KB File Size
• January 4, 2022 Updated
• 2 Number of comments
• ★★★★★

  ★★★★★
  Rating

Break even is used to define whether your business revenue can cover all of your expenses within particular time period. One month is a common period to measure it. Your company gains profit if your revenue is above break even point revenue. On the other hand, your company suffer loss if it failed to reach that break even point number. That's why some companies need to plan their products and sales carefully to avoid that loss. I created this simple break even analysis template to help you calculate it correctly. Still, there are many things that have to be considered to gain more profit and it could be different among different type of businesses.

There are logic that you have to understand before using this excel template. Financial people should already understand it well. In a more detail definition, break event point is calculated by comparing the amount of revenues/sold units with fixed and variable costs associated with those generated revenues/sold units. There are many different ways to apply this concept in Excel. Let’s take a look at a few of them as well as an example of how to implement it in the Excel template.

The break-even point formula is calculated by dividing the total fixed costs of production by the price per unit less the variable costs to produce the product.

Break Event Point in Units = Fixed Costs/(Sales Price per Unit - Variable Cost per Unit)

Since the price per unit minus the variable costs of product is also called the contribution margin per unit, the equation above can be written as follow :

Break Event Point in Units = Fixed Costs/Contribution Margin per Unit

By putting all required numbers, you can get a number of units that you must sell to cover all of your business expenses. You can convert this number to revenue number by multiplying it with its sales price.

Now, let's see how to use the template with above logic and equation.

How to Use Break Even Analysis Template for Excel

Fixed Cost Table

In this template, you can see production capacity is put on top of the table. This number corresponds with a number of employees, company space, machine investment (for depreciation), and many other expenses category that will be classified as Fixed Costs. Fixed cost means cost that you must expense monthly regardless of number of units being produced or sold. You must pay your employees regularly, doesn't it? You can modify current categories with yours. Type the production capacity in Units and fixed costs in Dollar (you can change to other currency from formatting cell menu). The excel formula will calculate its cost per unit at the left side of inputted amount.

Variable Cost Table

Then, you can put variable costs for the products. You must fill the amount in per Unit column. The excel formula will calculate the total cost needed to produce all units. Why this cost is considered variable? Because you won't expense money if you don't produce it. For example, to produce 1000 mobile phones you need to purchase 1000 boxes and print 1000 user guides. If you produce more mobiles phones, you need to purchase more. On the other hand, if you produce less mobile phones, you need to purchase less. There are many materials that can be classified in this Variable Cost category. You can modify current category in this template.

Break Event Point Result

After completing both Fixed and Variable Cost section, you can define your sales price target. It must be higher than fixed plus variable cost per unit. You can call the difference as Profit Margin. This is the value that you can adjust by considering all expenses as well as competitor price. You will get Break Even Points in both number of units and amount of revenue in dark green cells below targeted selling price row. You can select the cells to see where the formulas are linked. The formula format is similar with logic that has been explained in the second paragraph.

In conclusion it is clear that any units sold below calculated break even points will burden your company financially. That's why you must calculate it precisely when this break even points should be reached when you launch a new product. This Break Even Point analysis template calculates break even point only. You need a financial plan spreadsheet to plan more carefully and see it map in one year or particular period. Feel free to modify this template since it is fully editable. There are other criteria that you might need to put in this template to simulate your own products accurately.