Excel Formula Help?

[Durham Fox]

I need to create a formula in excel that will calculate how much I need to increase a customers discount by to negate an increase in RRP.

Scenario:

Dave is a customer and gets varying discounts on different product areas (with multiple products, all priced individually) he buys from me:

Daves Discounts
All dry goods 45%
All fresh goods 35%
Homeware 65%
Frozen Foods 55%

I've decided to increase the base cost of all of my stock by different amounts in the above areas.

RRP Increases
All dry goods 5%
All fresh goods 3%
Homeware 6%
Frozen Foods 5%

Dave is such a good customer I don't want to pass on the increase in prices to him so have agreed to increase his discounts to negate the change.

How do I work that out in excel?

Example - a item in dry goods has an RRP of £1 and Dave would normally buy this at 55p (ie 45% discount). I'm increasing the RRP to £1.05. What should his discount be now to ensure he still pays 55p? and more importantly, how do I calculate that so I can apply it to each product area?

 

[Matt_B]

You want something like 100-(old discounted price/new price) if I understand correctly

 

[Durham Fox]

You want something like 100-(old discounted price/new price) if I understand correctly

You don't I need to work in percentages. To save pricing up all 22,000 items individually, just for Dave.

 

[Matt_B]

but that should give you the percentage you need to offer on each item to retain the old price. For instance 100 -( (55p / 105p)*100) will give you 48%.

I realise now I forgot the * 100 in the first one.

 

[spionfox]

If you are increasing all of your RRP's by different amounts then a standard formula to get back to the original cost won't work will it?

 

[doolochfox]

I think you will just need to add the inverse to the price rise.
(Original Price X Increase) X (100/(100+ increase) X (1 - discount)

( 1.00 X 1.05 ) X (100/(100+ 5 ) X (1 - 0.45 )