Increase Percentage / Margin by 5% - Logic

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
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
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

leeandoona

New Member
Joined
Oct 13, 2016
Messages
45
OK I'm losing this one . . . :)

What is the calculation that you actually want to perform ?

:LOL: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?!:eek: 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
Joined
Oct 13, 2016
Messages
45
:biggrin: 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
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows
To whom are you responding?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows
... Sell and Cost based percentages that are derived from each other.

I think you are misunderstanding your cost elements.
 

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top