#### 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: