Calculate a discount % based on a % and a margin %.

Hello, very interesting!

Quote: "a formula to calculate a discount to apply to the price in order to achieve a certain profit margin (in %) based on my purchase discount.

Is it in relation to the amount of your purchase discount or in relation to your purchase price (discount included)?

If I have a purchase discount of 60% and I want a margin of 30%, what discount should I give?
If I have a purchase discount of x% and I want a margin of y%, what z% should I give?

Another way to say it :)

Hello,

Your problem seems to be more about math. Calculate your formula with a paper and a pencil, then put it in your Excel spreadsheet.

LR

For everything else, that's what I did for 3 weeks instead of automating.
I had done everything on a system of equations with several unknowns, but now I'm stuck on the mathematical writing of my question :) since it involves relationships or coefficients based on percentages.

Hello Kubinator.

Your image is very nice, but not very usable.
Please send us the Excel file itself. To do this, go to https://www.cjoint.com/ to create the link, which you will paste here.
If needed, write down the formula you have in mind on paper and scan it...

But most importantly, give us a numerical example: On a purchase of €100, what final selling price do you want to achieve? Is it really €52?
In that case, the formula would be =A1*(1-60%)*(1+30%) and the discount rate would be =(1-60%)*(1+30%) which is 52%.

Best regards.

Retirement is nice! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)

Well, I tried, I'm getting close to the solution (mathematical)

Concrete example:

If the price is 100€ and I have a purchase discount of 60% and I want a margin of 20%, what sales discount should I apply to the price for my client? (the answer is 50%)

100€ - 60% = 40
100€ - 50% = 50
40 / 50 = 0.8 (20%)

T - x% = A
T - y% = V
V / A = M

So if I remember correctly from my classes:

(T - y%) / (T - x%) = M
T - y% = M / (T - x%)

And now I'm lost trying to find y. In my opinion, the -60% etc., I should account for them as x0.40 (the decimal equivalent) to see more clearly...

It's taking shape lol, then I can set T and x in a table so that it calculates my y automatically.

I've found the error!! I reversed it ;)

(100 x 0.40) / (100 x 0.50) = 0.80

(100 x 0.40) / 0.80 = 100 x 0.50

I just need to transform it into an equation with the corresponding data for the columns :)

I'm summarizing :)

Well, I tried, I'm getting closer to the solution (mathematical)

Concrete example:

If the price is 100€, I have a purchase discount of 60% and I want a margin of 20%, what sales discount should I give on the price to my client? (the answer is 50%)

100€ - 60% = 40
100€ - 50% = 50
40 / 50 = 0.8 (20%)

T - x% = A
T - y% = V
V / A = M

So if I remember correctly from my classes:

(T - y%) / (T - x%) = M
T - y% = M / (T - x%)

And here I get lost trying to find y. In my opinion, I should account for the -60% etc., in x0.40 (the decimal equivalent) to make it clearer for me...

It's taking shape lol, then I can set T and x in a table so that it calculates my y automatically

I'll take the same numbers again:

100€ - 60% = 40
100€ - 50% = 50
40 / 50 = 0.8 (20%)

Let's use coefficients instead of %

100€ x 0.40 = 40
100€ x 0.50 = 50
40 / 50 = 0.8

(100 x 0.40) / (100 x 0.50) = 0.80
(100 x 0.40) / 0.80 = 100 x 0.50

All that's left is to transform it into an equation with the corresponding data in columns :)

(T - x%) / M = T - y%

It's almost done :)

End:

(T - x%) / M x T = -y%

Winner Kub!! :)

I have a purchase discount of 60% and I want a margin of 20%, what sales discount should I give to my client based on the price list? (The answer is 50%)
Ah! I think I've finally understood your issue; but it wasn't easy!
The formula is =(PD-MG) / (1-MG)
or in your example =(60%-20%) / (1-20%)
which gives a sales discount relative to the supplier's price of 0.5 or 50%.

Best regards.
--
Retirement is nice! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)

I arrived at RA/MV = RV if I remember your abbreviations correctly :)

RA: Purchase Discount
MV: Selling Margin
RV: Selling Discount

Thank you for the help

Hello,
I didn't see your reply, sorry.

I have done various checks and it seems that what I did is correct.
I checked it with Excel, with my accounting software, and even on paper with a calculator ;)

Here is briefly where I have arrived; of course, later on, I complicated things a bit too, my table allowed me to work on another spreadsheet with “=VLOOKUP(D1157,'Calcul par groupe %'!A4:G79,2,0)” to make my task easier.

Thanks again for your help