Increase Percentage / Margin by 5% - Logic

[leeandoona]

Quote taken from this thread: https://www.mrexcel.com/forum/excel-questions/889707-increase-percentage-margin-5-logic.html

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.

 

[shg]

More generally,

Sell = (Direct Cost * (1 + Cost-Based Percentages) + Fixed Amounts) / (1 - Sell-Based Percentages)

Cost-Based Percentages include any adders proportional to the cost of the item

Fixed Amounts would include shipping and the like

Sell-Based Percentages include margin, sales tax, ..., anything proportional to the sell price for the item

 

[leeandoona]

OK I'm losing this one . . .

What is the calculation that you actually want to perform ?

Sorry. I'm useless at explaining this!

So in essence, I simply need to automatically have a retail price adjusted to meet a specific percentage based on its original pretax cost but having taken into account all the related deductions for that sale to leave that net margin and the to repeat this many times for different costs. The issue is, depending on the selling price, this drives how much is deducted in related fees/commissions (hence all that math in the middle) and so I'm using Goal Seek to make the calculation over and over again down tens of thousands of rows.

Ideally there would be a simplistic math sum that always gave the right percentage/net amount at the end. Kinda like:

Cost + Shipping X Markup (-all deductions) = fixed net %

The problem as I see it, is to work out the net you need to know the retail price and to work out the retail price you need to know what the deductions will be and to work those out...you need to know the retail price so its circular. I'm always coming back to having to adjust the markup up or down while watching the net until it hits the right number, but then I go up to the next cost price and the calculation from the previous will no longer give the right amount as the cost (and therefore retail and fees) have now all increased. If you see what I mean?! It isn't event an incremental change, it has no specific pattern other than the markup goes down as the cost goes up to get the same net amount.

 

[leeandoona]

Yes! That's exactly it!!! The issue is that it has both Sell and Cost based percentages that are derived from each other. So as I adjust one, the other goes out of whack and visa versa. So what you're saying is if I use the Sell-Based Percentages and then just deduct the Cost-Based (fixed stuff) I should get a regular and pretty much accurate result?

 

[shg]

To whom are you responding?

 

[leeandoona]

To whom are you responding?

You! Sorry two responses at same time of reading.

 

[shg]

... Sell and Cost based percentages that are derived from each other.

I think you are misunderstanding your cost elements.