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