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]

@Alex Blakenburg i really think that the problem is solved with posts 18 and 19. But ok, waiting for the feedback from from @liam1983

 

[Alex Blakenburg]

@felixstraube, using your Cost & Margin figures the formula I provided produces the same Selling price. There are merits in having a template such as you have produced but I don't believe this solvable without tackling the margin issue.

Thanks for this, we do not work on a margin percentage we have a fixed value which runs in bands

Your template assumes a % which the above indicates doesn't apply.

 

[felixstraube]

He never replied to when I asked how he gets those (margins)...
Anyway this actually is pretty simple either way. High school math I would say.

Lets say again that we have:

c: cost
m: margin
k: commission
s: gross sale price, our unknown

And the equation to get s is this:

s = (c + m + k) * 1,20 (1)

1,20 because of the VAT 20%.


Case 1: Margin is a fixed value, and commission is a percentage of the gross price. Then we have

m (just a fixed value)
k = kp * s where kp is the percentage (from our example 12%)

Now we replace that in equation (1). we get:

s = (c + m + kp * s) * 1,20

We solve for s and get

s = (c + m) / (1/1,2 - kp)


Case 2: Margin and commission are percentages of the gross price. When we have

m = mp * s where mp is the margin percentage (from the example 20,833333% obtained from dividing 2,5 in 12)
k = kp * s where kp is the percentage (from our example 12%)

Now we replace that in equation (1). we get:

s = (c + mp * s + kp * s) * 1,20

We solve for s and get

s = c / (1/1,2 - mp - kp)


In this moment i only have a web version of excel so i will just show this image and the formulas:

Formula in F4 is:

=(A4+B4)/(1/1,2-$C$2)

Formula in F10 is:

=(A10)/(1/1,2-$C$8-$B$8)

No need for recursive calculations or solver...

 

[liam1983]

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 ?

Morning Alex, Gross Price is inclusive of 20% VAT that is correct.

Regarding the margin it is a fixed amount we determine at our discression, our decision is influenced by many things including buy price, market value and required return it is not a percentage of anything, it is a target we choose I could not give a definitive answer on this as it is very varied. We cannot use a percentage multiplier per your example as when the fixed costs increase (purchase price or margin) the percentage will vary and thus will not remain a usable calculation (we've tried in the past).

I do feel a bit like the enemy here, sorry if I am frustrating you but the fixed costs are not the factor in my question, the issue is that of calculating the percentages on the end result and the fact they are variable based on that end result (price) so they go round in a circle.

I have attached the basis of the calculation on an image, Cells D & E are circular based on the value in F but F requires the values in D&E to calculate the value.

Hope this makes some sense?

Thanks

Liam

 

[felixstraube]

@liam1983 did you read my last post?

 

[felixstraube]

Where do you get the value for the margin from? Is it fixed at 2.5 for every product?
You never answered that question.

If it is a fixed value then would this be what you are looking for?:

Book1
	A	B	C	D	E	F
1			12%
2	Cost	Margin	Commission	Net price	VAT (20%)	Gross sale price
3	6.06	2.50	1.44	10.00	2.00	12.00
4	10.00	2.50	2.10	14.60	2.92	17.52
5	15.00	2.50	2.94	20.44	4.09	24.53
6	20.00	2.50	3.79	26.29	5.26	31.54
7	30.00	2.50	5.47	37.97	7.59	45.56
8	40.00	2.50	7.15	49.65	9.93	59.58
9	50.00	2.50	8.83	61.33	12.27	73.60
10	60.00	2.50	10.51	73.01	14.60	87.62
Sheet1

Cell Formulas
Range	Formula
C3:C10	C3	=(A3+B3)/(1/1.2-$C$1)*0.12
D3:D10	D3	=SUM(A3:C3)
E3:E10	E3	=D3*0.2
F3:F10	F3	=(A3+B3)/(1/1.2-$C$1)

If not, can you show us the data you are working with?
And the expected results.

 

[Alex Blakenburg]

This is obviously going to be the same underlying calculation but trying to tailor the layout according to the information you provided.

If you copy this in, then go to B3 and in the Name box where it says B3 Copy in VAT_Rate (this is using that named range)

20240110 Reverse Engineer Selling Price liam1983.xlsx
	A	B	C	D	E	F
1	Calculation Template
2
3	VAT Rate	20%
4
5
6
7	Product	Cost Input	Margin Input	Reseller	Commission %	Gross Selling Price
8	Item1	6.06	2.5	Johnny	12%	12.00
9	Item2	73.28	30.23	Johnny	12%	145.11
10	Item3	96.42	39.78	Orange Co	14%	196.44
11
12
13
14
15	Reseller	Commission
16	Johnny	12%
17	Orange Co	14%
Without Table

Cell Formulas
Range	Formula
E8:E10	E8	=VLOOKUP($D8,$A$16:$B$17,2,FALSE)
F8:F10	F8	=($B8+$C8) / (1- (VAT_Rate/(1+VAT_Rate)) - $E8)

Named Ranges
Name	Refers To	Cells
'Without Table'!VAT_Rate	='Without Table'!$B$3	F8:F10

Table names don't translate well via XL2BB but when I used tables the calculation looked like this:

=([@[Cost Input]]+[@[Margin Input]]) / (1- (VAT_Rate/(1+VAT_Rate)) - [@[Commission %]])

 

[felixstraube]

@liam1983 Can you share you file? Or a sample of it? Upload it to a cloud storage and share the link here?