Calculate a discount % based on a % and a margin %.
Solved
Kubynator
Posted messages
20
Status
Membre
-
Kubynator Posted messages 20 Status Membre -
Kubynator Posted messages 20 Status Membre -
Hello,
I run a business and I am busy modifying my IT system.
In short, I have supplier prices on which I have a purchase discount. For my sales, I base my prices on the supplier's price on which I apply a discount for my customer.
I am trying to find 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.
Since I am in a vast field with several suppliers, I don't want to perform calculations for everything and therefore I am looking for a formula that will help me later on in a macro or another tool.
The next part is more complex as I will integrate into my process the Pareto principle (or theory) ;)
Attached is a screenshot, which I hope will be clearer than my words.
Thank you for your help.
Kub
Configuration: Windows 7 / Chrome 37.0.2062.103
I run a business and I am busy modifying my IT system.
In short, I have supplier prices on which I have a purchase discount. For my sales, I base my prices on the supplier's price on which I apply a discount for my customer.
I am trying to find 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.
Since I am in a vast field with several suppliers, I don't want to perform calculations for everything and therefore I am looking for a formula that will help me later on in a macro or another tool.
The next part is more complex as I will integrate into my process the Pareto principle (or theory) ;)
Attached is a screenshot, which I hope will be clearer than my words.
Thank you for your help.
Kub
Configuration: Windows 7 / Chrome 37.0.2062.103
12 réponses
Well, I'm having trouble making a relatively readable image
If I have a purchase discount of 60% and I want a 30% margin, what discount should I give?
If I have a purchase discount of x% and I want y% margin, what z% should I give?
Another way to say it :)
If I have a purchase discount of 60% and I want a 30% margin, what discount should I give?
If I have a purchase discount of x% and I want y% margin, what z% should I give?
Another way to say it :)
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)?
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 :)
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
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.
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)
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.
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 :)
(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!! :)
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)
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)
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
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
