Calculating a minimum price based on variables

Hi,

I am working on a spreadsheet to work out a minimum sales price based on starting with a cost price, allowing for RRP, Carriage charges (which are dependent upon RRP) and a fee based on the final sales price. The final sales price should be a minimum of 6% gross margin.

I am struggling to build the formula that will get me to the minimum selling price (with the 6% gross margin).

Enclosed is a brief spreadsheet example. As you can see its in order to set prices but not go below an acceptable level after allowing for VAT, Carriage and fees.

The Yellow fields can be changed. The grey cells are currently auto calculated and the red highlighted cell is the one I'm struggling with.

If anyone could point me in the right direction I would be very grateful.

Paul

I know little of most of these terms, so I interpreted your request as needing a formula that would give you the price that, if made the selling price (J3), would allow Q3 (profit) to equal N3 (minimum required). Working with that understanding, I did some algebra and came up with the following:

=IF(B3>=125/6,(-B3-6.5)/(M3-(41/60)),(-B3-3.13)/(M3-(41/60)))

Again, though, I'm just doing the math, I might be completely misunderstanding the goal. If I'm wrong, dflak is your man/woman/airplane(?).

Originally Posted by dflak

This will actually take VBA if I understand the requirement. The price you want to sell at has to cover the Amazon fees. Yet the Amazon fees are calculated based on the price you sell at. This is called a circular reference. Whatever formula you come up in cell P3 will depend on the contents of cell P3.

If I interpreted how things should be applied, the UDF in this workbook should work. It is called SellPrice and it is invoked with the following arguments.
SellPrice (TradePrice , MyPrice, Margin, VAT, Amazon Fee)
TradePrice and MyPrice are entered as currency
Margin, VAT and Amazon Fee are entered as decimals (6% = 0.06).

This is really close but it doesn't seem to include the carriage costs. Probably my fault for not explaining properly. I like the idea of the VBA but I'm not great in this area to be able to develop it.

Thank you for the pointer though! Very much appreciated.
Paul

Doubling the M in my original formula returns a selling price that would return a 6% profit after Amazon fees are removed. That would be:

=IF(B3>=125/6,(-B3-6.5)/(2*M3-(41/60)),(-B3-3.13)/(2*M3-(41/60)))

Maybe that helps? It would help to know what value you expect in P3 on your sample if everything works correctly.

Originally Posted by dflak

I blew it. I needed iteration to make the price come out right. I struggled with the math and wound up with a solution that oscillated around the solution. So I dropped back and did a brute force methodology.

This will work, but I will try for the elegant solution on Monday.

Please ignore earlier reply and thank you again. I do believe its close and love the VBA approach. I'm terrible at VBA (must be my age) and my only comment is that it doesn't seem to include the carriage costs and changing the "Selling Price" (yellow highlighted cell) seems to impact the red highlighted cell which in theory should return the optimal selling price after allowing for item cost, carriage costs (F3), net VAT costs (K3) and the required margin (M3).

Very grateful for your input and help across the pond.

Paul

Originally Posted by CAntosh

Doubling the M in my original formula returns a selling price that would return a 6% profit after Amazon fees are removed. That would be:

=IF(B3>=125/6,(-B3-6.5)/(2*M3-(41/60)),(-B3-3.13)/(2*M3-(41/60)))

Maybe that helps? It would help to know what value you expect in P3 on your sample if everything works correctly.

Thank you for the input. The first algebraic equation has worked and provides a selling price that achieves the net 6% Margin - Thank you!! I'm intrigued by the doubling of M???

Basically my aim is to identify the selling price of an item that after allowing for trade cost, VAT, Carriage costs and 15% fees still achieves a 6% profit margin

Thank you again!!

Paul

Glad I could help! I wasn't sure if the 6% Amazon fees should be deducted prior to the profit determination. Doubling the M allows for 6% for Amazon and 6% for the profit column. I was trying to mix my initial understanding of the question with what I was getting from dflak's work in case I wasn't interpreting the goal correctly. I'm glad it wasn't necessary.

Originally Posted by dflak

It would help if you can walk us through the calculation.

As for age, I'm pushing 68. VBA is a relatively new trick for this old dog .

You put me to shame. I'm 54 so perhaps I need to go back to school.....!

Ok, so here is the calculation..

Selling Price needs to achieve a 6% Gross Profit Margin) after deducting the following:

Trade Cost of Item (excluding VAT)
Carriage costs (excluding VAT)
15% Fees (no VAT)
Net VAT due (which is 20% of the trade cost plus 20% of the net carriage cost, deducted from the VAT ude on the selling price - calculated as selling price divided by 6)

The algebraic equation from CAntosh does seem to provide the correct figure so would love to understand how to do this in VBA -
=IF(B3>=125/6,(-B3-6.5)/(M3-(41/60)),(-B3-3.13)/(M3-(41/60)))

Thank you so much for your time. I have been battling with this for some months and have been amazed by such generous responses.

Its way past nighttime for me over here but will check back tomorrow.

Thank you again!!
Paul

Hi again,

I thought I had it but now not quite. Please could you take another look at the enclosed. I've corrected a couple of small calculation issues around VAT (UK Sales Tax) and now the equation doesn't quite work and I cannot see how/why. Based on what I believe if I purchase the enclosed item for £10 (plus 20% VAT), pay carriage fees of £2.61 (plus 20% VAT), sell the item for £21.06, paying £3.51 VAT and Amazon fees of £3.16, I would then make a profit of £1.78 which equates to a margin of 8.46%.

Therefore would it be possible to re-check the equation so that I can identify the 6% margin please?

Updated sheet enclosed.

Thank you again.
Paul

.this time with the attachment.....................

I have spoken to my accountant who has said I'm over complicating things and to just look at everything excluding VAT.

Therefore I have started to tweak the excel calculation/ equation but can't quite get it right. On the enclosed spreadsheet the red highlighted cell (where the equation sits) should result in £21.06 using the example but I can't quite see the issue with the math?

Please could you double check?

Many thanks.
Pau

I have spoken to my accountant who has said I'm over complicating things and to just look at everything excluding VAT.

Therefore I have started to tweak the excel calculation/ equation but can't quite get it right. On the enclosed spreadsheet the red highlighted cell (where the equation sits) should result in £21.06 using the example but I can't quite see the issue with the math?

Please could you double check?

Many thanks.
Paul

To recap, I am attempting to find the selling price necessary to achieve the margin set in cell B13 based upon cost price, fees and carriage costs.

I have the following formula which originated kindly form CAntosh but now that I have tweaked the file to exclude VAY (UK Sales Tax) the calculation doesn't quite work

=IF(B4>=125,(-B4-5.42)/(B13-(41/60)),(-B4-2.61)/(B13-(41/60)))

Please would someone be kind enough to point me in the right direction?

Many thanks in advance.
Paul

It works!! Thank you so much.

I like the idea of using an IF statement re carriage (in case prices change). Currently we expect them to be £3.13 (£2.61 ex-VAT) or £6.50 (5.42 ex-VAT) for most of this year but being able to accommodate different values here could be useful if that is possible.

This is extremely useful thank you so much for your efforts and help.

Paul

My pleasure! If you want to substitute B6 out of the equation, you can replace B6 in the post 17 formula (again using the template from posts 15 and 17) and substitute the terms down to:

=(-B2+(B2*B3)-(IF(1.92*(B2-(B2*B3))>39.99,6.5,3.13)/6*5))/((5/6)*B13-(5/6-B7))

Which should generate identical returns to my formula in post 17 while referring only to your yellow/variable cells. If the IF formula for carriage costs changes regularly, it might be easier to stick with the solution in post 17, because you'd just need to update B6 rather than updating the formula above, but that's up to you and your users. Based on current information, the two formulas should return identical results.

Excellent - thank you so much for all your help!

Best wishes.
Paul