Break Even Analysis – Free Template and Formula for Small Business

In this article, we share with you how easy it is to complete your own break even analysis. We also provide you with a free break even analysis template, including formulas that will automatically calculate your break even point for you.

Once you have completed your break even analysis, you may want to put a professional business plan in place. You can do this for free using our free business plan template. However, if you want to make things easy for yourself, you could use a business planning tool. We recommend Liveplan, which comes with 500 business plan templates.

Visit LivePlan

Free Break Even Analysis Template and Formulas

screengrab-of-break-even-analysis-chart

Break Even Analysis Template in MS Excel
Break Even Analysis Template in Google Spreadsheet

(Note: If you download either doc, you can copy and
save as  your 
own and then edit the content as you wish)

What is Break Even Analysis?

Break even analysis works out how many unit sales you have to make for your sales revenue to equal your business costs. At this point you make no loss and you also make no profit, which is called the break even point.

Breakeven Point = Fixed Costs/(Unit Selling Price – Variable Costs)

If you increase unit sales beyond this point, you’ll start making a profit. If you are starting a business or working on a new pricing strategy, understanding when your business will break even and start to make a profit is an essential part of your business plan.

How to use the Break Even Analysis Template?

Your free break even analysis template will automatically calculate your break even point. All you have to do is enter your own sales and cost numbers. It will also provide dynamic visual charts to make this easier to understand. Just follow the simple steps below.

Step 1 – Click on One of the Free Download Options Above

If you want the dynamic visual charts to help you understand what is going on, download the MS Excel option, as it supports graphics and charts. Let’s assume you are downloading the MS Excel version, though you can follow the same steps to download the Google Spreadsheet template.

Step 2 – Download the MS Excel Spreadsheet

Your Excel spreadsheet will open in a new tab, which you can download to your desktop or save online. Once you have access to the document, click File in the top level menu within Excel and then Save as your own spreadsheet.

You can now see the first tab of the break even analysis template, which is called Break Even Caluculations. This will look like the screengrab I have inserted immediately below. (Note: If you’ve downloaded the Google version, you will not see graphs).

screengrab-of-break-even-analysis-template-including-formulas

Step 3 – Populate the Cells with Your Business Numbers

The template has cells with a white or a grey background. The white cells are where you need to enter your own numbers over the top of the example numbers that are currently in the spreadsheet. Be careful not to change any of the grey cells, because these contain the formulas that will help to automatically calculate your break even point.

Tips

Sales Price per Unit – think in terms of the average price of a sale. For instance, if you run a restaurant, what is the average bill? If you run a window cleaning service, what is the average price that you charge? If you’re a freelance consultant, what is the average client invoice?

Sales Volume per Period – you can choose any period you like as look as you are consistent. You could choose a week, a month or a quarter. If you are starting a new business, be cautious about over-estimating your sales. It would be better to under-estimate and find you are profitable sooner than expected. The alternative would be to over-estimate and find that you run out of cash or fail to make a profit.

Variable Costs these are costs, which vary with the volume of sales that you make. For instance, shipping costs will go up as unit sales increase and down as they fall.

Fixed Costs – also referred to as overheads, these costs are fixed. This means they do not vary as unit sales increase or decrease. For instance, you will still have to pay your rent whether you make any sales or not.

Insert Additional Variable and Fixed Costs – I have made room for you to insert more variable and fixed costs with cells that are highlighted in green. Be careful to make sure that all costs are accounted for or your break even analysis will not provide an accurate answer.

Step 4 – Review Your Break Even Analysis

You will see that the grey cells have automatically calculated your total sales, total variable costs, total fixed costs and net profit or loss for the period. They have also calculated your Break Even Point in unit sales. The formulas have also auto-generated the Unit Contribution Margin and Breakdown of Variable Costs Graphs.

If you scroll down, you will also see that a more detailed Sales Volume Analysis has also been auto-populated for you. Let’s say that you decided to set your time period in months. That would mean that in the example below, it took nine months to break even and start making a profit.

Step 5 – Click on the Second Tab in Your MS Excel Spreadsheet

The second tab in your spreadsheet is called Break Even Analysis Chart. Your version of this chart will of course be different. This is because it will reflect your business numbers from the first tab. Now it’s easy to also see visually your own break even point and when your business will start to become profitable.

Step 6 – Review Your Break Even Analaysis

Now you can review your break even analysis. You need to ask yourself the following questions:

  • Have I got my pricing strategy right?
  • How confident am I about achieving the sales volume?
  • Is my margin big enough?
  • Can I go leaner on any of my major costs?
  • Can I afford to bootstrap the business for as long as this?
  • Should I be exploring other sources of funding?

The answers to these questions may lead you to amend some of your numbers entered in the first tab. You may also want to look at a best-case sales forecast and a worst-case sales forecast. Then plan your business finances based on the worst case scenario, but set the best case-scenario for your sales team to achieve.

Step 7 – Get your Price Right

It’s important to recognise that break even analysis does not provide any insight into customer demand for your product or service. If your price is uncompetitive, or your brand is weak, your sales volume will suffer. We recommend pricing as competitively as you can until you have hit break even and started to make a profit. Afterall, your first goal is to establish your business as a sustainable going concern. Thereafter, you can consider raising your price to become more profitable.

The Juice Press

In this article we have provided you with a free break even analysis template and shown you how to use it in seven simple steps.

Once you have completed your break even analysis, you may want to upgrade your business plan using a small business planning tool. We recommend Liveplan, which comes with 500 business plan templates.

Visit LivePlan