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

crwilson84

New Member
Joined
Oct 29, 2013
Messages
11
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
Code:
=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
 
Last edited:
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.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
VAT is a 20% markup from cost to cover the 16.67% of the price owed to the Gov.
 
Upvote 0
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.

Code:
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:

Code:
=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.
 
Upvote 0
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))
 
Last edited:
Upvote 0
For Excel calculation purposes, you would use a construct like this:

Code:
=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?
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

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
Back
Top