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

 

[felixstraube]

I'm pretty sure VAT is not calculated like 20% of Gross Sale Price. It is calculated over the Net Sales Price.

If your Net Sales price is 100 then you will have a Gross Sales Price of 120 (100 * 1,20).
It is not a 20% of Gross Sales Price, in our example 120 which would be 24

You didn't answer all my question from the previous post.
Putting you problem into words is the only way we could understand you and help you.

 

[felixstraube]

How does the Commission change depending on sale price? Do you have a table or how do you calculate the Commission percentage based on the Sale Price?

 

[SanjayGMusafir]

'm pretty sure VAT is not calculated like 20% of Gross Sale Price

To my knowledge it's total of items value + VAT that is Gross sales

So in a way Net Sales + VAT (on Net sales) is Gross sales in the OP terms. Though in accounts they are subtotal, VAT and Grand totals in Billing

 

[liam1983]

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

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

Hi Alex

On your calc "

Sell = (Cost + Margin) / 0.68

"

(Cost 6.06 + Margin 2.50) / 0.68 = £12.59

It is off from the example in OP so does not work sadly.

I have run calculations like both of the above examples from yourself and Sanjay previously, percentages in bands etc are ok but they just are not accurate enough.

We need to see other than the circular reference route where the commission and VAT liability change as the selling price if there is any way other than the Macro we currently use.

To Reiterate it is a difficult one as when you combine the cost with the target margin you do not actually know what the figure of commission and VAT is until you have a gross sale price as the commission and VAT are a percentage of the gross sale, this is where the circular aspect comes in.

The goal chaser Macro works perfectly however is an imperfect solution in terms of time and computer resources.

 

[liam1983]

How does the Commission change depending on sale price? Do you have a table or how do you calculate the Commission percentage based on the Sale Price?

Hi

In the OP I used a figure of 12% commission of final gross sale price, that is not fixed at 12%, it varies for reseller to reseller, it is just an example I gave with some nice round numbers to try and lay the problem out easily.

Thanks!

 

[felixstraube]

Can you share a sample of your data and what the expected result should look like since you are not answering my questions.
How are we supposed to help you if you don't tell us how the whole process works?
I really tried but can't understand the whole process, but there is always something missing.

 

[liam1983]

How does the Commission change depending on sale price? Do you have a table or how do you calculate the Commission percentage based on the Sale Price?

Hi

The percentage of the commission in this example will not change, the example is 12% of Gross Sales price, so the value of the caluculation (12% of gross sale) changes as a numerical value based on what the gross sale price is.

We will have other accounts where the commission may be 10 or 15% but that is not important to the writing of this problems solution which is why I am using the 12% for now.

What I mean is if 12% of the current gross sale of lets say £10 is £1.20 then once you lump on that £1.20 to the £10 the percentage value of the new gross (£11.20) goes up, this is where the circular part comes in because until we know the final sale price we cannot work out the commission owed however we cannot also work out it first as we need to do it en mass for many many rows of data. our current macro sees this and increases selling price until after the costs the target margin is met,

Thanks

Liam

 

[felixstraube]

So what you are saying is this.

if you have:
c: cost
m: margin
com: commission
s: sale price
a: margin percentage
b: commission percentage

the calculation is as follows?:

s = 1.2 * (c + m + com)

where

m = a * s
com = b * s

Is that it?

 

[felixstraube]

Ok if my assumptions from last post is correct then you can calculate all the values involved like this:
The percentage 20,833333333 results from dividing 2.5 in 12 from you example. If your margin percentage is different just input it in B1.

Libro1 (Recuperado).xlsx
	A	B	C	D	E	F	G	H	I
1		0.208333333333333	0.12
2	Cost	Margin (20.83333%)	Commission (12 %)	Net price	VAT (20%)	Gross Sales Price
3	6.06	2.5	1.44	10.00	2.00	12.00		Example margin	2.5
4	73.28	30.2310231	17.41306931	120.92	24.18	145.11		Example Gross Sale Price	12
5	10.56	4.356435644	2.509306931	17.43	3.49	20.91
6	96.42	39.77722772	22.91168317	159.11	31.82	190.93
7	93.58	38.60561056	22.23683168	154.42	30.88	185.31
8	62.60	25.82508251	14.87524752	103.30	20.66	123.96
Hoja1

Cell Formulas
Range	Formula
B1	B1	=I3/I4
B3:B8	B3	=A3/(1/1.2-$B$1-$C$1)*$B$1
C3:C8	C3	=A3/(1/1.2-$B$1-$C$1)*$C$1
D3:D8	D3	=SUM(A3:C3)
E3:E8	E3	=SUM(A3:C3)*0.2
F3:F8	F3	=A3/(1/1.2-$B$1-$C$1)

 

[Alex Blakenburg]

(Cost 6.06 + Margin 2.50) / 0.68 = £12.59

It is off from the example in OP so does not work sadly.

I should have taken more notice of @felixstraube's comment that your statement that VAT was "20% of your gross price" was incorrect.
By correcting the VAT % to be based on the VAT inclusive price, the VAT % becomes 0.2/1.20 = 0.166666667

And the equation becomes:
(Cost 6.06 + Margin 2.50) / 0.713333333 = £12.0000000056
1-0.166666667-0.12=0.713333333

Which still puts us back to the unanswered question of how is the Margin "Fixed" amount determined ?