Hi Howard,

Have you tried using Goal Seek? I presume your sales figure is 'Turnover'. When you use Goal Seek to find where net profit = 0 it returns a turnover figure of 436,000.

Mynda

Hi Mynda

Thanks for the reply

I can use Goal Seek to find what the Sales figure (turnover) is where Net profit = 0

i just cannot understand why my breakeven formula  being fixed costs/Gross Margin % does not give  me the correct Breakeven

Hello,

The term ”break even” is for me how many units you need to sell to cover the costs. With this in mind you should use following formula:

Fixed cost / (Price per unit - Variable cost per unit) = Break even point in units

As I normally don’t work with queries like this I did a search on this topic and found several sites with good information, one of many stated this on their site.

Calculating your break-even point

There are a few basic formulas for determining a business’s break-even point. One is based on the number of units of product sold and the other is based on
points in sales dollars.

• To calculate a break-even point based on units: Divide fixed costs by the revenue per unit minus the variable cost per unit. The fixed costs are those that do not change no matter how many units are sold. The revenue is the price for which you’re selling the product minus the variable costs, like labor and materials.

  Break-Even Point (Units) = Fixed Costs ÷ (Revenue per Unit – Variable Cost per Unit)

• When determining a break-even point based on sales dollars: Divide the fixed costs by the contribution margin. The contribution margin is determined by subtracting the variable costs from the price of a product. This amount is then used to cover the fixed costs.

  Break-Even Point (sales dollars) = Fixed Costs ÷ Contribution Margin

  Contribution Margin = Price of Product – Variable Cost

Thanks for your input Anders

My Gross profit (Gross Margin) is Sales - Variable costs
Contribution margin is Gross Profit (gross margin) /Sales
Breakeven = Fixed costs/Sales

However, when to set the Net Profit to zero, by changing the sales value, I get 436,000, yet my breakeven formula gicve me a value of 398,361

i have attached my sample data

Hello,

It has passed many years now since I worked with such financial data, so please correct me in case I am overlooking some aspects. Based on your numbers in your sample file your total costs are 436,000 = 108,000 (fixed) + 328,000 (variable), so I would say that you have your financial break even there. But that does not tell anything of how many units you need to sell.

As I understand it you use the other calculations when determining the number of units you need to sell to cover all costs.

Hi Howard,

I think what we're all forgetting, me included, is that when you change the Sales value, so too does the Variable Costs value need to change, since it is directly correlated to the sales.

At 398,361 sales the variable costs reduces to 290,361, which is breakeven since it covers the 108,000 in fixed costs. i.e. the variable costs are 72.89% (1-27.11%) of sales.

So, your breakeven formula is correct.

Mynda

Hi Mynda

Thanks for your input

I certainly did forget that variable costs change as sales volume changes

When I make my variable costs to be Sales * (1-0.271111), my data table works out 100%

Regards