Break even point calculator excel – Break Even Point (BEP)
Do you know your Break Even Point? Use our free break even point calculator designed in Excel to find out
Break Even Point Analysis BEP
Whilst we were in the middle of updating our Increase Profit Software, we discovered that it would be a great idea to also have a free business tool, which analyses a business’s break even point or BEP.
But before I introduce this new free business tool, I thought it best to explain what break even (or break-even) point is.
How to calculate a business break even point
A business’s break even point is the balance point of neither making a profit nor a loss. It is where the gross profit generated from a business’s sales, is equal to its fixed overheads.
However, there are differing opinions as to what fixed overheads are for the purpose of break even point. Some definitions only include those costs that the business would incur even if it was closed down.
However, a more meaningful fixed costs summary would include the business’s average running costs. This figure would exclude all those costs directly related to costs of sales. If you manufacture, this would exclude your raw materials and other direct costs associated with making your products.
Your cost of sales is an easier to identify total where your business simply buys in goods, simply for resale.
Simple break even analysis template
To calculate your break even point, you can either do this as an average across all of your products, or use our Break Even Analysis Calculator, to work out individual products sales. This is a simple break even analysis template, which is easy to use, but also an extremely useful business management tool too.
If you are going to use the average method, you need the following:
Estimated Monthly Fixed Costs
_____________________________________ = Break Even Unit Sales Per Month
Average Per-Unit Sales Price – Average Per Unit Cost
So For example:
If your average monthly fixed costs were £10,000, and your Average Per-Unit Sales Price was £100, with an Average Per-Unit Cost of £40. The monthly break even unit sales would be:
£10,000
___________ = 167 (rounded to the nearest unit sold)
£100 – £40
This would make your total monthly sales revenue £16,700 per month, needed to break even. This is useful to know, so your business must sell more that 167 units in order to make a profit, using the average sales and cost prices.
1. Average Sales Price (or Average Per Unit Revenue)
This is the amount on average you receive for every product or service that you sell. Where you sell more than one product or service, you will need to work out an average selling price across all of your products and services. If you want to save time on working this out, but you already have the individual selling prices ,for each of your products or services, you can use our free Break Even Analysis Calculator to do this for you.
2. Average Per Unit Cost
This is either what it costs you to manufacture each unit, or what it costs you to buy it in before you sell it. If you sell services in your business, then this is what it costs your to deliver the service. Where you have multiple products or services, then this number is the average across all of your products or services.
Again to sale time on this calculation, you can download our free Break Even Analysis Calculator tool.
3. Monthly Fixed Costs
Fixed costs may have different definitions. In some cases this is defined as those business costs which a business would incur, even if it were not to sell a single product or service. In working out your break even point, we recommend that these costs include expenses like rent, rates, insurance. telephone, wages and salaries, stationery costs, etc. These costs are normally referred to as your operating expenses.
A other way this is referred to is “Below The Line” expenses, which are those expenses a business incurs which fall below the gross profit, where gross profit is calculated by deducting Cost Of sales from Sales or Revenue.
Free Break Even Analysis Calculator Template Tool
We do however, recommend that you try out our free break even tool, as this gives you more features or options to play around with. This free business tool allows for up to 100 separate products, and allows you to run as many break even scenarios as you need.
You can also test the effect of price increases or decreases on your break even point.
With our free tool, you’ll also be able to do the same for price changes associated with your unit costs too.
You can also take a look at what happens if you stopping selling certain product or service lines. This is particularly useful where you have one or two very low margin product or service lines.
When deciding on whether or not to sell a particular product or service, you need to be mindful of the impact that by selling or not selling one product or service, how this impacts on the sale of other products or services.
The important this to note is that this free business tool has been created using an Excel Template, but you don’t need to have an expert knowledge of using Excel to use it. We have kept this really simple, so that it is easy to use and quick to get started.