BreakEven Calculator for excel

BreakEven Calculator for excel

So you have a general idea of how much you’d like your business to earn each year – and you’ve got a viable product or service. But what you may lack are solid numbers and targets to ensure that you both break even and prosper.

Our break-even calculator is a simple tool you can use to work out how much you need to sell in order to achieve your desired financial return. BreakEven Calculator for excel

What does the break-even calculator do?

The break-even calculator allows you to plug-in the following data:

• Your desired financial return for the year BreakEven Calculator for excel
• Annual overheads
• Per unit pricing
• Per unit labour and material costs
• The number of weeks you intend to work each year

After plugging in your numbers, the break-even calculator automatically determines:

• Total cost per unit
• Total gross margin per unit
• Number of units you need to sell each year to reach your target
• Total amount of sales you need to achieve in order to reach your target BreakEven Calculator for excel
• Number of units you need to sell each week in order to reach your goal

Why use the break-even calculator?

The break-even calculator helps you define your businesses financial goals for the year and experiment with various factors to see how they affect the outcome. For example, you may realise that your margin is too thin or that you’ll need to ramp up sales dramatically in order to reach your goal. After inputting your numbers, use the calculator to see how certain changes can affect your bottom line. Examples of where you can make changes include:

• Overheads – would it make sense to move to cheaper office space? Can you work out of your home or garage for the first year? What happens if your rent goes up? The break-even calculator will show you how many more units you’ll need to sell each year to make up the difference.
• Labour costs – can you automate any portion of production? For example, buying or leasing a laser cutter or label applicator could reduce your labour costs. Such a move will increase your overheads, so use the break-even calculator to see which option is best for your business.
• Materials costs – is it possible to negotiate discounts with your suppliers? If you don’t quite have the volume required to qualify for volume discounts, ask about discounts for early payments. Consider shopping around or using alternative materials to reduce your costs, and then use the break-even calculator to see if it’s worth switching. If your costs go up, you’ll need to either raise your prices, reduce your overheads, or make up the difference through volume sales.

Experiment with different scenarios

Testing different scenarios to see what happens – such as changing your per unit price or lowering wages or overheads – can help you decide if adjustments are needed. Even if overheads, wages, materials costs, and prices remain stable throughout the year, having a weekly target (number of sales per week) can keep you on track.

How to use the break-even calculator

Our break-even calculator is an Excel spreadsheet. Download our break-even calculator and open it in Microsoft Excel or a compatible program. The spreadsheet will open directly to the main tab (Break-even). A second tab (Help) is available should you need any assistance. BreakEven Calculator for excel

Step one

Enter your desired financial return in the first cell (E7). Note that this figure is for the entire year. For example, if you want to bring in $250,000 after expenses, enter $250,000 in this cell. BreakEven Calculator for excel

Step two

Enter the cost of your overheads for the year. This includes your fixed costs such as rent, Internet access, utilities, equipment leases and non-production payroll. At this point, the break-even calculator will display the gross margin required.

Step three BreakEven Calculator for excel

Enter your current, or best guess, price per unit (in cell E15).

Step Four

Enter your costs for both labour and materials. Pay attention to the calculations the spreadsheet makes at this point. Consider:

• How your costs and gross margin per unit relate to your pricing?
• Whether your margin is too small or too much?

At this point, don’t adjust them just yet. You’ll want to first establish your baseline using your existing prices and costs.

Step five

Enter the number of weeks per year you intend to work (in cell E29). If you regularly shut down for two weeks in July and another two weeks in December, you would enter 48 weeks here. Factor in any holidays as well.

Step six

Analyse the figures in the ‘Results’ section. Here you’ll see the total number of units you’ll need to sell for the year in order to reach your desired financial return. You’ll also see the number of sales required in dollars, as well as the number of units you’ll need to sell each week to stay on track.

Step seven

Make appropriate changes to any of the input boxes if you feel you need to.

Step eight

Now that you have your baseline, print out your results. You’ll likely want to refer back to these figures as you experiment with different scenarios.

Next Steps

• After you download thebreak-even calculator (link to calculator), test different scenarios to see what happens when you make changes.
• Contact yourNAB Business Banker to discuss your financials going forward.

[center][b][color=red]You are not VIP Member[/color][/b]
[i]Please contact Skype or Gmail for VIP membership and be able to download this item.[/i][/center]