Formula for calculating value at set %

[Jamboni]

Hi All, This is my first post and looking to get this set up for a report I'm loking to complete for tuesday to my boss.

I'm struggling to work out a formula that will calculate the New WSP of a product through calculating it at a set % of 50% in terms of gross percentage margin. For example the existing of a product ;WSP is £6.03, the retail selling price is £1.08 and the cost £4.33, which provides a cash profit % of circa 72%. What I want to be able to do is calculate a new wsp by Applying the 50% margin rate. I.e. the product above GP% would drop by 22%!!!


Any Ideas?

[TMS]

I don't understand the calculation figures outlined in the OP.

Perhaps you can clarify?

Regards, TMS

[Jamboni]

basically to use another example, a product costs 1.00, is sold for £1.30 to a shop, who sells to a customer for £1.70,therfore total GP% is 70%, 30% Wholesale margin and 40% retail GP% margin. I would like to find a formula that would work this out down to 50% instead of 70% pro rata at the same rate so that even though the % is lower it is split as proportianatly as before. Giving me a revised WSP and RSP; (cost does not change)

Hope this makes sense?

[TMS]

If the GP is 100%, then the WP is 43% and the RP is 57%

So, 70*100% = 70, 70*43% = 30 and 70*57% = 40 ... 30 + 40 = 70; Total price: 1.70

To apply that to a lower GP, just multiply by the percentages.

50*100% = 50, 50*43% = 21 and 50*57% =29 ... 21+29 +50; Total price: 1.50


This isn't really an Excel problem; it's basic maths/percentages ... hope you're not the Finance Manager

Regards, TMS

[Jamboni]

No, don't think You've grasped the orignal query,the gp% is not 100% as indicated above,Found another work around. Thanks for the response anyway.

[royUK]

And thanks for sharing your solution

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.


How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[TMS]

Actually, I think I have grasped the problem ... you just don't seem to be able to understand the answer.

I know that the GP is not 100% ... but it is 100% of the total profit. What you need is the percentage that is Wholesale and the percentage that is Retail.

So, 30/70 as a percentage = ... ? *
And, 40/70 as a percentage = ... ? *

see above for answers.

Apply those percentages to the lower figure (50) to get the lower WP and RP ... again, see above for the answers.

How DID you calculate it? Or, should I say, how did YOU calculate it. It would be useful if you would share as it is always helpful to have alternative approaches to check your answers.

Regards, TMS