Formula for a sale price which has circular references

[liam1983]

Hi all, hope you are well.

Have a problem that I need a solution for, we have one at the moment which uses a macro but with the data volume it takes a long time and a lot of resources to run.

The basic explanation of the problem is that we receive a purchase price for a product, we need to get to a selling price which has a commission fee and a VAT liability which changes based on the gross selling price. Working from selling price backwards is obviously easy, the issue is working forward from buy price, variable costs which are a percentage based on sale price and a target margin make it.

I will give a layout of the problem and columns as an example, figures used for round numbers where possible (hope the alignment used at time of writing post stays this way)

[Cost] [margin] [commission @12%] [VAT] [net sale price] [gross sale price]
[6.06] [2.50] 1.44 - 2 - 10 - 12

We need to be able to change target margin to suit cost of goods, at the moment the macro we have cycles through selling prices until margin matches that in the target column, that is fine when you have a handful of data to crunch, when there are thousands of rows it takes hours and seriously effects the performance of a high powered PC, the formulas we write which in theory work do contain two circular referneces.

One is the commission percentage of the gross sale price, the other is the VAT amount at 20% of the gross sale price. As they adjust the gross selling price needs to go up to cover these two values to still keep the target margin.

We use a goal seeker macro that we set a target profit and it changes the selling price until the target profit is matched.

Thanks for your help all

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F
1	Cost	Margin	Commn	VAT	Net	SPrice
2	Required	41%	12% of SP	20% of SP	SP-VAT	SP
3	6.06	2.48	1.17	1.94	11.65	9.71
4	Check	41%	12%	20%
Sheet1

Cell Formulas
Range	Formula
B3	B3	=41%*A3
C3	C3	=13.64%*SUM(A3:B3)
D3	D3	=20%*F3
E3	E3	=SUM(A3:D3)
F3	F3	=SUM(A3:C3)
B4	B4	=B3/A3
C4	C4	=C3/F3
D4	D4	=D3/F3

 

[liam1983]

Check this and revert -

Book1
	A	B	C	D	E	F
1	Cost	Margin	Commn	VAT	Net	SPrice
2	Required	41%	12% of SP	20% of SP	SP-VAT	SP
3	6.06	2.48	1.17	1.94	11.65	9.71
4	Check	41%	12%	20%
Sheet1

Cell Formulas
Range	Formula
B3	B3	=41%*A3
C3	C3	=13.64%*SUM(A3:B3)
D3	D3	=20%*F3
E3	E3	=SUM(A3:D3)
F3	F3	=SUM(A3:C3)
B4	B4	=B3/A3
C4	C4	=C3/F3
D4	D4	=D3/F3

Thanks for this, we do not work on a margin percentage we have a fixed value which runs in bands so this does not work for us sorry, we have tried it before, the figures given in the OP were only as an example.

 

[SanjayGMusafir]

Thanks for this, we do not work on a margin percentage we have a fixed value which runs in bands so this does not work for us sorry, we have tried it before, the figures given in the OP were only as an example.

How does that makes a difference. You change the formula for margins to a fixed figures. It shall calculate and give you the figures you asked in the OP.

You should have tried that first. Anyhow try now and revert.

 

[liam1983]

Those figures are just examples, we have different fee structures, different target margins, different buy prices.

In your example the figures are off, the margin, commission, VAT and end sell price are all differnt to that caluclation in my example which is exactly the issue I am trying to avoid.

 

[SanjayGMusafir]

Those figures are just examples, we have different fee structures, different target margins, different buy prices.

In your example the figures are off, the margin, commission, VAT and end sell price are all differnt to that caluclation in my example which is exactly the issue I am trying to avoid.

We need to be able to change target margin to suit cost of goods

In that case that is the best I could help you. I replied exactly to the needs of your OP.

Hope this thread catches eye of someone who could help you in a better way.

 

[liam1983]

Thanks for your effort, it is appreciated but it doesn't reply exactly to the OP.

The OP Explains that the fee and the VAT are a percentage of the final selling price but to work out the final selling price we need to make sure all deductable amounts (cost, margin target, commission, VAT) are correctly calculated and then deducted whilst keeping the target profit at the target, those percentage amounts are circular i.e they change based on the final sale price, this is where the problem occurs, your formula lost 35p on the selling price (our initial example was £12, yours came in at £11.65).

This is where the conundrum comes in, it is one we solved with a macro but it is very heavy to use and why I am seeing if there are a fresh and smart set of eyes to look at this problem for a formula we can replace the macro with.

 

[felixstraube]

Let me get this straight.

Your inputs are:
Cost (money amount)
Margin (money amount)
Commission (money amount)
VAT percentage: 20%

And you want to calculate the Gross selling price?

Can you tell me how each of these are calculate?
Margin is a percentage of what? the cost? or where do you get this amount from?
Same for commission. Is it a percentage of something or is it a fixed value? where do you get this from?
Net Sales Price is the sum of Cost + Margin + Commission?
VAT is 20% of Net Sales Price?

And can you provide more sample data?

 

[liam1983]

Let me get this straight.

Your inputs are:
Cost (money amount)
Margin (money amount)
Commission (money amount)
VAT percentage: 20%

And you want to calculate the Gross selling price?

Can you tell me how each of these are calculate?
Margin is a percentage of what? the cost? or where do you get this amount from?
Same for commission. Is it a percentage of something or is it a fixed value? where do you get this from?
Net Sales Price is the sum of Cost + Margin + Commission?
VAT is 20% of Net Sales Price?

Hi!

Commission is percentage of the final sale price, in the example given 12% - This changes based on final sale price as it is a percentage on final value.
VAT is also a percentage amount of final sale price.

The problem is we work from cost & margin then want to work out what the percentages would be of a yet unknown final selling price (because they include the percentage of that final selling price and so adjust as the figure calculates).

Hope that makes sense, difficult to write in words.

 

[Alex Blakenburg]

The missing piece is still how you come up with the margin.

we do not work on a margin percentage we have a fixed value which runs in bands

If you have a table of bands please provide it. Which value is used to look up the band (hoping it is cost )
Currently
Sell = (Cost + Margin) / 0.68