leeandoona
New Member
- Joined
- Oct 13, 2016
- Messages
- 45
Quote taken from this thread: https://www.mrexcel.com/forum/excel-questions/889707-increase-percentage-margin-5-logic.html
I have a similar conundrum to the one above but I can't make the solution above work for me because my challenge is slightly different. I wonder if you know how I would do the following:
A=Cost
B=Markup
C=Retail Price
D=Fees
E=Other Deductions
F=Gross
G=Net (as a percentage of F/C)
So in the example above (A) is 1.00 and I mark it up by (B) 2.48 (A*B)+2.99. This gives me (C) which is the retail price including shipping (2.99). (D) contains the fee percentage, in this case 15% and (E) contains a monetary value which represents (E) (C*D). This leaves me a Gross amount in (F) which is calculated as C-E-A-2.99. Finally I can see what my actual % of profit is in (G) Net as a percentage which is calcualted F/C.
The conundrum is. I want to always get that same percentage at the end and I want excel to automatically work out the markup amount to ensure the resulting percentage is 12% (or very close to it and never below it). If I try to do this, excel can't because the sum relies on a circular calculation. I need to just run down a list of cost prices and apply a desired markup figure to always get 12% at the end. Try as I might, I can't figure this out. Any help would be much appreciated? I have posted this seperately but didn't get any luck.
Thanks.
Not sure if this is what you're looking for or need.
Assuming:
A1 = Current Cost
B1 = Current Margin
C1 = Expected Cost (or Target Cost)
D1 = 5% (or Target Margin)
E1 = Your Sale Price (SP), which is fixed and known.
in C1, use:
=IF(E1/A1<1.05,(E1/1.05),A1)
in D1, use:
=((E1-C1)/C1)*100
Ignore the *100 in the D1 formula if you have the Cell formatted as percentage.
Let us know if this is what you're looking for.
I have a similar conundrum to the one above but I can't make the solution above work for me because my challenge is slightly different. I wonder if you know how I would do the following:
A=Cost
B=Markup
C=Retail Price
D=Fees
E=Other Deductions
F=Gross
G=Net (as a percentage of F/C)
So in the example above (A) is 1.00 and I mark it up by (B) 2.48 (A*B)+2.99. This gives me (C) which is the retail price including shipping (2.99). (D) contains the fee percentage, in this case 15% and (E) contains a monetary value which represents (E) (C*D). This leaves me a Gross amount in (F) which is calculated as C-E-A-2.99. Finally I can see what my actual % of profit is in (G) Net as a percentage which is calcualted F/C.
The conundrum is. I want to always get that same percentage at the end and I want excel to automatically work out the markup amount to ensure the resulting percentage is 12% (or very close to it and never below it). If I try to do this, excel can't because the sum relies on a circular calculation. I need to just run down a list of cost prices and apply a desired markup figure to always get 12% at the end. Try as I might, I can't figure this out. Any help would be much appreciated? I have posted this seperately but didn't get any luck.
Thanks.
Last edited by a moderator: