Break-even price!
Discussion
Help me chaps, I am thinking too much about this and can't see the wood for the trees!
I am trying to calculate a break-even price in Excel but can't work out the formula (being thick basically).
I have a cost which is constant.
I have a sales price but the net sales price is reduced by constant fees but also % discounts on the gross price.
I was trying to add back from the cost but the problem I have is that the sales + discounts etc is a moving target (i.e. the discount changes when the sales price changes).
How do I get around this to give me my break-even price?
I think I need to mulitply out the sales price that at a given % of discount would equal the cost plus contant fees....but can't translate that into formula!
Help!
I am trying to calculate a break-even price in Excel but can't work out the formula (being thick basically).
I have a cost which is constant.
I have a sales price but the net sales price is reduced by constant fees but also % discounts on the gross price.
I was trying to add back from the cost but the problem I have is that the sales + discounts etc is a moving target (i.e. the discount changes when the sales price changes).
How do I get around this to give me my break-even price?
I think I need to mulitply out the sales price that at a given % of discount would equal the cost plus contant fees....but can't translate that into formula!
Help!
donz29 said:
Can you use a table with the reduced sales prices in and then link to your formula?
Good point - so in another sheet just list out sales prices from £1-£10,000 and work out the reduced sales prices, then say if the reduced sales price equals the cost, the break-even price is the original sales price?You need to use Goal Seek, within the Tools menu.
Write a simple staged sheet that takes Price as an input cell, reduce it by the discount in the next cell, reduce it by the constant fees and then by the fixed cost. That will give you a cell with the profit in. If you then use Goal Seek to bring profit back to zero by changing the price, the resulting price will be your break-even.
Simples!
Write a simple staged sheet that takes Price as an input cell, reduce it by the discount in the next cell, reduce it by the constant fees and then by the fixed cost. That will give you a cell with the profit in. If you then use Goal Seek to bring profit back to zero by changing the price, the resulting price will be your break-even.
Simples!
tone said:
You need to use Goal Seek, within the Tools menu.
Write a simple staged sheet that takes Price as an input cell, reduce it by the discount in the next cell, reduce it by the constant fees and then by the fixed cost. That will give you a cell with the profit in. If you then use Goal Seek to bring profit back to zero by changing the price, the resulting price will be your break-even.
Simples!
Cheers - I have Excel 2007 which does not have a Tools menu! Where will I find it in 2007 version?Write a simple staged sheet that takes Price as an input cell, reduce it by the discount in the next cell, reduce it by the constant fees and then by the fixed cost. That will give you a cell with the profit in. If you then use Goal Seek to bring profit back to zero by changing the price, the resulting price will be your break-even.
Simples!
Thanks.
Surely all you need to do is have a seperate field for the sales and discount amount and incorporate this into your net figure formula via a cell reference. Then when the discount changes you simply update the figure and the formulas will update accordingly.
It would probably be easier if you add some figures to your post, with some example calculations your trying to achieve.
It would probably be easier if you add some figures to your post, with some example calculations your trying to achieve.
Worked it out using Goal Seek.
E.g.
Put in a sale price of 1 in cell A1, fixed costs in A2, % discount in A3 (being A1 x 10%), then cost in A4, giving profit / loss in A5:
1 0.05 (0.17) (23.09) (22.21)
Then ask Goal Seek to change A5 to zero by changing A1. End result:
26.64 0.05 (3.60) (23.09) 0.00
Awesome, thanks guys.
E.g.
Put in a sale price of 1 in cell A1, fixed costs in A2, % discount in A3 (being A1 x 10%), then cost in A4, giving profit / loss in A5:
1 0.05 (0.17) (23.09) (22.21)
Then ask Goal Seek to change A5 to zero by changing A1. End result:
26.64 0.05 (3.60) (23.09) 0.00
Awesome, thanks guys.
Neil H said:
Surely all you need to do is have a seperate field for the sales and discount amount and incorporate this into your net figure formula via a cell reference. Then when the discount changes you simply update the figure and the formulas will update accordingly.
It would probably be easier if you add some figures to your post, with some example calculations your trying to achieve.
Problem is I do not have the sales figures yet so had to make some up to get my break-even sales figure for a given cost + fixed and variable discounts.It would probably be easier if you add some figures to your post, with some example calculations your trying to achieve.
Legend83 said:
Worked it out using Goal Seek.
E.g.
Put in a sale price of 1 in cell A1, fixed costs in A2, % discount in A3 (being A1 x 10%), then cost in A4, giving profit / loss in A5:
1 0.05 (0.17) (23.09) (22.21)
Then ask Goal Seek to change A5 to zero by changing A1. End result:
26.64 0.05 (3.60) (23.09) 0.00
Awesome, thanks guys.
See, I told you it was 3.E.g.
Put in a sale price of 1 in cell A1, fixed costs in A2, % discount in A3 (being A1 x 10%), then cost in A4, giving profit / loss in A5:
1 0.05 (0.17) (23.09) (22.21)
Then ask Goal Seek to change A5 to zero by changing A1. End result:
26.64 0.05 (3.60) (23.09) 0.00
Awesome, thanks guys.
Gassing Station | Business | Top of Page | What's New | My Stuff