# Increase Percentage / Margin by 5% - Logic

#### leeandoona

##### New Member
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.

Last edited by a moderator:

#### shg

##### MrExcel MVP
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### leeandoona

##### New Member
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

##### New Member
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

##### MrExcel MVP
To whom are you responding?

#### leeandoona

##### New Member
To whom are you responding?

You! Sorry two responses at same time of reading.

#### shg

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

I think you are misunderstanding your cost elements.

Replies
1
Views
56
Replies
3
Views
372
Replies
1
Views
269
Replies
2
Views
349
Replies
1
Views
912

1,137,154
Messages
5,679,908
Members
419,862
Latest member
Bluewings666

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back