Backwards Calculation of Discount to Achieve a Certain Amount

[PWH1968]

Hi Everyone,

Ok here is my latest conundrum that I am hoping you can help me with as I am losing my mind (and there’s not much left!).

Basically what I am trying to do is to:

• take a product list price and apply a % discount that has been negotiated with a customer (I’ve got that bit done); then
• I will multiply the product price by a quantity and number of months to get a projected contract value (I’ve got bit done also)

However I have now found out that we need to pay a % levy of the contracted value (1%), which will obviously reduce the contract value which the business doesn’t want - but the customer has already been presented with the ‘price’.

So my challenge is, how do I calculate what additional discount I need to request from the business. I have tried to show this below:

 

[Dave Patton]

T202211a.xlsm
	B	C	D	E	F	G	H
2		Units	Months
3	10	100	24	80.00%	4,800.00	0.01	4752
4	10	100	24	79.80%	4,848.48	0.01	4800
5
9f

Cell Formulas
Range	Formula
F3:F4	F3	=B3*C3*D3*(1-E3)
E4	E4	=1-(F3/0.99)/(B4*C4*D4)
H3:H4	H3	=F3*(1-G3)

 

[PWH1968]

T202211a.xlsm
	B	C	D	E	F	G	H
2		Units	Months
3	10	100	24	80.00%	4,800.00	0.01	4752
4	10	100	24	79.80%	4,848.48	0.01	4800
5
9f

Cell Formulas
Range	Formula
F3:F4	F3	=B3*C3*D3*(1-E3)
E4	E4	=1-(F3/0.99)/(B4*C4*D4)
H3:H4	H3	=F3*(1-G3)

Thank you so much Dave - greatly appreciated

 

[PWH1968]

Thank you so much Dave - greatly appreciated

Sorry Dave another question based on this approach please.

What would the formulae be to calculate the difference between E4 to E3 (i.e.0.2020%) and then it to the figure from E3 but all in the same cell please?

 

[Dave Patton]

You can use Goal Seek
I list the steps of what you would do on row 4.
I describe what I did on row 6

select H6 which intially showed 4752 (see H4)
go to Data What-if Analysis Goal Seek
set value to 4800
by changing E6

T202211a.xlsm
	B	C	D	E	F	G	H	I
2		Units	Months
3	10	100	24	80.00%	4,800.00			initial
4	10	100	24	80.00%	4,800.00	1.00%	4752	revised
5
6	10	100	24	79.80%	4,848.48	1.00%	4800
7
9f

Cell Formulas
Range	Formula
F3:F4,F6	F3	=B3*C3*D3*(1-E3)
H4,H6	H4	=F4*(1-G4)

 

[PWH1968]

You can use Goal Seek
I list the steps of what you would do on row 4.
I describe what I did on row 6

select H6 which intially showed 4752 (see H4)
go to Data What-if Analysis Goal Seek
set value to 4800
by changing E6

T202211a.xlsm
	B	C	D	E	F	G	H	I
2		Units	Months
3	10	100	24	80.00%	4,800.00			initial
4	10	100	24	80.00%	4,800.00	1.00%	4752	revised
5
6	10	100	24	79.80%	4,848.48	1.00%	4800
7
9f

Cell Formulas
Range	Formula
F3:F4,F6	F3	=B3*C3*D3*(1-E3)
H4,H6	H4	=F4*(1-G4)

Thank you yet again Dave - you are a life saver

 

[Dave Patton]

Thanks for the feedback