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]

Can you confirm/clarify how you are calculating VAT? It looks like your values are ~16.7% of selling price, not 20% as indicated.

20% of 29.99=6.00, not 5.00
20% of 25.00=5.00, not 4.17

and similar for the other three examples.

 

[shg]

VAT is a 20% markup from cost to cover the 16.67% of the price owed to the Gov.

 

[iliace]

VAT is a 20% markup from cost to cover the 16.67% of the price owed to the Gov.

Thanks shg, that does provide some context. I haven't dealt with VAT for over a decade, so don't know how it works these days. And where practical reality does not match the question, I am really only trying to address the question in Excel terms. I certainly cannot comprehend how a "20% markup" covers "16.67%" that goes to the government. Where does the rest go?

Nonetheless, as far as I can tell, the basic answer to the question as stated (outside of calculation midsirections) this is an algebraic solution.

Min Price = (Item Cost + Postage + Fulfillment) / ((1 - [Desired Margin]) - ((Amazon Fee) / (1 - [VAT %])) * (1 / (1-[VAT %]))

For Excel calculation purposes, you would use a construct like this:

=MAX(0, MIN(1, [insert Min Price formula here]))

To make sure you never have anything below 0, and that your minimum margin is always above 1.

 

[shg]

In this formula,

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

E6:O6 are fixed costs, and the 40% is the sum of price-based percentages (of which VAT would be 16.67%, the target margin is 10% (as stated), Amazon fees, and anything else that is a percentage of the price).

The general formula to arrive at a price is

Sell = ((1 + sum(cost-based percentages)) * direct cost + other fixed costs) / (1 - sum(price-based percentages))

 

[crwilson84]

For Excel calculation purposes, you would use a construct like this:

=MAX(0, MIN(1, [insert Min Price formula here]))

To make sure you never have anything below 0, and that your minimum margin is always above 1.

Many thanks for the suggestion. Unfortunately, this formula changes the MIN Value to £1.00 and not the margin value (profit made).

I really appreciate all the help - is there any other options?

 

[crwilson84]

In this formula,

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

E6:O6 are fixed costs, and the 40% is the sum of price-based percentages (of which VAT would be 16.67%, the target margin is 10% (as stated), Amazon fees, and anything else that is a percentage of the price).

The general formula to arrive at a price is

Sell = ((1 + sum(cost-based percentages)) * direct cost + other fixed costs) / (1 - sum(price-based percentages))

Is there any way in Excel, within the one cell, to find out the answer to this formula and then do a further formula based on the answer? Is my query impossible without using 2 or more cells?