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:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
So could you do something like this:

Code:
=IF(G6="", "", MIN(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Happy to help. Thanks for the feedback.
 
Upvote 0
I believe iliace meant:

=IF(G6="", "", MAX(0.8, CEILING((E6+F6+L6+M6+N6+O6)/(100%-40%),0.1)))
 
Upvote 0
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.
:confused:

Kind Regards,
Craig
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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