# Discount Calculation

1. ## Discount Calculation

Good day.
I have a customer who is demanding an additional 8% discount so he can make extra margin without increasing his current selling price. I would like to calculate how much additional stock he should purchase for me to break even with this additional 8% discount. It is not as easy as working on the wholesale price less 8% and the increasing the number of units at the new discounted price to give you the same value. To set the rules please see below example.

Cost is 100.00
Markup is 50%
Wholesale is this 150.00
8% discount is -12.00
8% discount final amount is 138.00

Profit is 50.00 but after discount is 38.00.

So how would you calculate this with an easy formula to show how many unit should be purchased to get to the same unit profit?

I hope I have made it clear what I am looking for. Not the simple:

20 * 50.00 = 1000.00 in profit thus 26.5 *38.00 will give the same profit in unit of 1000.00.

I am looking for the calculation that will bring me the break even point of making the additional 8% discount worth it.

I look forward to some creative thinking.  Register To Reply

2. ## Re: Discount Calculation

As best as I can tell, to offset an 8% discount you need to multiply the result by 1.086956522 to offset it. Beyond that I didn't get into the rest of the detail.  Register To Reply

3. ## Re: Discount Calculation

Upload a workbook with some sample data to help us to understand your requirement.  Register To Reply

4. ## Re: Discount Calculation

Perhaps my business and math skills are rusty, but it's impossible to both offer an 8% discount per unit and make the same margin on a per-unit basis. It's a mathematical impossibility. You can only determine the break-even point of how many extra units you have to sell to make the same per-order margin, which is exactly the "simple" calculation you did above.

If you're talking how much extra he needs to purchase for a given order size, all you do is subtract the original order size from the new order size (in your simple calculation above) which would be 26.5 - 20 = 6.5 extra units for you to make the same profit.

I'm not quite sure what you're looking for.  Register To Reply

5. ## Re: Discount Calculation

G'day

Once a pond a time I did make a fancy discount tool in excel like the one your asking for. Can't find the disc But I tested the brain cells and came up with this.....  Register To Reply

6. ## Re: Discount Calculation

sorry wrong formula  Register To Reply

7. ## Re: Discount Calculation

units to be sold = present selling units * markup % / (markup % -(1+markup %)*discount%)  Register To Reply

8. ## Re: Discount Calculation

as per your example = 20*50%/(50%-(1+50%)*8%)  Register To Reply

9. ## Re: Discount Calculation

I think it is:
50/38* qty  Register To Reply

10. ## Re: Discount Calculation

Goal Seek would be a great tool to achieve this task.

See the attachment.  Register To Reply

11. ## Re: Discount Calculation

Thank you for all the feedback. I have worked on the attached which I think is easier from a sales perspective as it gives the increase in a percentage.

Similar to the options given.

Thanks once again

Working.xlsxWorking.xlsx  Register To Reply