Calculating variable costs for minimum 10% margin but also always a minimum value

[crwilson84]

4 years ago with the help of these forums, we managed to get a formula for us which we've been using ever since in our profit calculation sheet for e-commerce.


It was this thread here: https://www.mrexcel.com/forum/excel...r-calculating-variable-costs.html#post3978738 where the key formula we needed was created.


The formula

=IF(G6="","",CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1))

works out our Amazon Minimum pricing on the row, and generally always keeps it around the 10% minimum profit mark taking into account all the surcharges and fees. We've never really understood how this worked, but it does - everything in the first brackets is obvious, add cost, postage costs, surcharges, fees - then we're unsure how it works by dividing by (100%-40%), if anyone can explain this that would solve the 4-year mystery!


The main point, we now want to change this formula or our sheet so it basically does :


Generate the Amazon Min price to get it near 10% margin (which is currently does using the above-mentioned formula) BUT if the VALUE we're getting is less than £0.80, then increase the generated minimum value UNTIL the minimum takings is £0.80.
For example, Amazon Min Price is generated from all costs and fees to near 10%, the money actually made from this is £0.76. Because it's £0.76, increase the generated Amazon Min Price until this hits £0.80, regardless of how much it raises the margin by (it would really only be 1 - 3% in practice)

Ideally, we would want this in just one formula, but there may be so many steps to this I expect there may have to be a macro here, but if that could be automated too that would be fewer steps for staff members as the idea is total automation of prices which we currently have, we just now also want to ensure despite the margin being 10%, we're at least making £0.80 per order.

Thanks

 

[iliace]

So could you do something like this:

=IF(G6="", "", MIN(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))

 

[igold]

Not trying to answer your entire thread but perhaps your 4 year itch. In retail pricing, if you know how much an item costs you and you know how much gross margin you want to generate then to solve for the selling price with the desired margin and known cost, you would take the cost of the item and divide it by the reciprocal of the desired margin. For example...

If an item costs you $15.00 and you want to make a 40% gross margin on that item then to figure out the correct selling price the formula would be:

$15/(100% - 40%) = $25

In your example you are generating a 60% gross margin assuming the first half of that equation is giving the cost of the item.

I hope this helps.

 

[crwilson84]

So could you do something like this:

=IF(G6="", "", MIN(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))

Unfortunately not, this just seems to return 0.80 every time: https://prnt.sc/j7ocan

 

[crwilson84]

Not trying to answer your entire thread but perhaps your 4 year itch. In retail pricing, if you know how much an item costs you and you know how much gross margin you want to generate then to solve for the selling price with the desired margin and known cost, you would take the cost of the item and divide it by the reciprocal of the desired margin. For example...

If an item costs you $15.00 and you want to make a 40% gross margin on that item then to figure out the correct selling price the formula would be:

$15/(100% - 40%) = $25

In your example you are generating a 60% gross margin assuming the first half of that equation is giving the cost of the item.

I hope this helps.

This makes sense thanks!

 

[igold]

Happy to help. Thanks for the feedback.

 

[Eric W]

I believe iliace meant:

=IF(G6="", "", MAX(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))

 

[iliace]

Unfortunately not, this just seems to return 0.80 every time: https://prnt.sc/j7ocan

Ugh... sorry. I get these backwards all the time. Should be MAX, not MIN:

=IF(G6="", "", MAX(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))

 

[Hiten Vakharia]

Hi Crwilson84,

Could you please share the spread sheet as I am unable to open it from the forum

Cheers,
Hiten

 

[crwilson84]

Hi All,

Apologies for resurrecting this again. I'm afraid the suggested formula didn't work:

Original Formula: =IF(G6="","",CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1))
Suggested Formula: =IF(G6="", "", MAX(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))



Forget about the formula above and I'll simplify this, here is what I'm looking to do:


EXAMPLE 1:

An item costs you £10 and ideally, you want to sell it on Amazon for MAX £29.99.

MAX Selling Price: £29.99
Less VAT @ 20%: £5.00
Less Amazon Fee @ 15%: £4.50
Less Item Cost: £10.00
Less Postage Cost: £2.50
Less Fulfillment Cost: £2.50
Total Profit: £5.49 (Margin 21.98%) *Margin percentage calculation is [Profit / (Selling Price less VAT)] eg. [£5.49 / £24.99]

The original formula from the top of this thread was designed to return a Selling Price that would make a minimum of 10% margin. What I'm trying to do is amend the formula to return a MIN Selling Price for either a 10% margin or a profit of £1.00, whichever is higher.

So the example here would return the suggested MIN selling price:

MIN Selling Price: £25.00
Less VAT @ 20%: £4.17
Less Amazon Fee @ 15%: £3.75
Less Item Cost: £10.00
Less Postage Cost: £2.50
Less Fulfillment Cost: £2.50
Total Profit: £2.08 (Margin 10.00%)



EXAMPLE 2:

MAX Selling Price: £4.99
Less VAT @ 20%: £0.83
Less Amazon Fee @ 15%: £0.75
Less Item Cost: £1.00
Less Postage Cost: £0.75
Less Fulfillment Cost: £0.25
Total Profit: £1.41 (Margin 33.90%)

If using the formula to calculate a Selling Price for 10% margin, the profit here would only be £0.28.

MIN Selling Price: £3.34
Less VAT @ 20%: £0.56
Less Amazon Fee @ 15%: £0.50
Less Item Cost: £1.00
Less Postage Cost: £0.75
Less Fulfillment Cost: £0.25
Total Profit: £0.28 (Margin 10.14%)

So I want the formula to notice that the profit is < £1.00 and instead re-calculate to suggest a Selling Price that will return a £1.00 profit, not a 10% margin. eg:

MIN Selling Price: £4.39
Less VAT @ 20%: £0.73
Less Amazon Fee @ 15%: £0.66
Less Item Cost: £1.00
Less Postage Cost: £0.75
Less Fulfillment Cost: £0.25
Total Profit: £1.00 (Margin 27.33%)



And there it is. Hopefully, someone knowledgable and talented can help.

Kind Regards,
Craig