We offer rebates on volume, so in an example I came across sales above £970k earns a 3% kick back, sales above £989.4k achieve a 4% kick back on sales volume.
If average margins are at 40% then the rebate would be higher than the margin earn't on the sale:-
| Actual | |
| 4% | |
| Sales | 989400 |
| Margin | 395760 |
| Rebate | 39576 |
| Additional Margin between threshold | 7760 |
| Additional Rebate | 10476 |
| Cost to the business | 2716 |
There must be a formula which can calulate the minimum sales level to achieve whereby the margin is equal to that of sales. By playing with the numbers sales would have to be £997k, I need a formula to actually calculate this for me - any ideas welcomed.
Related resources
Replies (4)
Please login or register to join the discussion.
Nested if statements
I think you would need nested IF statements, or a selection of formulas in a series of cells - (always good to show your workings).
Perhaps draw the problem out as a decision tree on paper first.
1) Are the sales enough to get commission level A
2) If so, are they also big enough to get to level B
Use ones or zeros to answer these questions.
Then, in your formula, you can have a series of mulitplications building up to the result.
etc.
Then, try your model out with some dummy data (pick nice round numbers).
Then, try it with real life data.
Rather depends
on exactly how you want to model it but the basic formula is:
Break even sales = Initial sales x (margin - initial discount) / (margin - new discount)
or = Initial sales x old effective margin/new effective margin
So, in your example, 970,000 x (40% - 3%)/(40% - 4%) = 996,944
