Log In
Register
Home
December 8, 2016
Offline
1) I have tried to compute the breakeven sales, but it appears to be incorrect
2) I have also tried to compute what the Net profit would be based on various sales values using data tables
I have attached sample data.
It would be appreciated if someone could kindly check my formula's and amend and advise where I went wrong
See Answer
July 16, 2010
Offline
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
December 8, 2016
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
December 8, 2016
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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.
July 16, 2010
Offline
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
Answers Post
December 8, 2016
Offline
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
1 Guest(s)
