Solver Type Formula or Chicken Egg Query

[daleholden]

OK, guys, I have a headache trying to work this out and need some help.


I have a product that costs me £100 looking at the table to the right I want to make a profit that fits the table. But the profit is it to include eBay FVF & PayPal Fee. So we are looking at a Chicken & Egg scenario. I need a goal seeker kind of formula that can workout what to charge as an eBay Sale Price but that includes the Total Cost Price eBay FVF and PayPal Fee.
I am sure there is a way around it but cannot see how to do it.


For this PayPal would be 2.9% + 0.20p
Ebay FVF would be 9%


So I am looking for a formula that will work out an eBay Sale Price that gives me £20 profit and works out the C2,D2 and E2 as a total price.


Hope that makes sense.


PS I want to apply this formula to 1000's of entries to fit the table opposite.

See worksheet here

 

[Eric W]

Maybe I'm missing something, but I didn't find that it needed an iterative calculation at all. Consider:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Product Price	Vat	Unit Price	PayPal %	eBay FVF %	Total Price	eBay Sale Price	Profit	GS Profit			VAT	Paypal %	eBay FVF %			CGS From	CGS To	Profit
2	236.00	4.72	240.72	9.14	33.28	283.13	308.13	25				2%	2.90%	9%			1	50	5
3																	51	100	10
4													PayPal Fixed				101	200	20
5													0.2				201	300	25
6																	301	400	35
7																	401	500	50
8																	501	750	65
9																	701	1000	99
10																	1001	2000	125
11																	2001	3000	150
12																	3001	4000	175
13																	4001	5000	200
14																	5001	6000	225
15																	6001	7000	250
16																	7001	8000	300
17																	8001	9000	325
18																	9001	10000	350

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B2 =A2*L2 C2 =A2*(1+L2) D2 =G2*M2+M5 E2 =N2*G2*1.2 F2 =C2+D2+E2 G2 =(H2 +M5+C2)/(1-M2-N2*1.2) H2 =VLOOKUP(A2,$Q$2:$S$18,3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put your price in A2, then the C2 formula finds the cost with VAT added, then the H2 formula finds the desired profit from the table, then the G2 formula determines the price needed to obtain that profit, including all the other costs. The other formulas are left intact so that you can see the various costs. If you add up C2, D2, E2, and H2, you get G2. But the G2 formula does not reference C2, D2, or E2.

 

[daleholden]

Actually, that algorithm (at least my implementation of it) is flawed.



Try the following, returning to the original design of your Excel file (remove the new column F labeled "total price"):

B2, vat:    =ROUND(A2*20%,2)
C2, cost:   =ROUND(A2+B2,2)
D2, paypal: =ROUND(F2*2.9%+0.2,2)
E2, EBay:   =ROUND(F2*9%,2)
F2, price:  =ROUNDUP((C2+G2+0.2)/(1-2.9%-9%),2)
G2, intended profit:
  =VLOOKUP(ROUNDUP((C2+VLOOKUP(C2,$P$2:$R$18,3)+0.2)/(1-2.9%-9%),2),$P$2:$R$18,3)

Essentially, I incorporated my assumption of a two-stage calculation: the first stage estimates price, using the profit lookup based on CGS; and the second stage determines profit, using the lookup based on the first stage value.

Experiment with that. My experiment seem to work, so far.

Hi that is looking good can you edit E2 =ROUND(F2*9%,2) to =ROUND(F2*9%,2)*1.2 this is to add VAT on. I have tried adding it and it breaks it. If it is easier I have provided a link that allows editing the sheet.

Also when i move it my main sheet which is more complicated due to PayPal & Ebay FVF having lookup tables would you be able to assist me to implement your solution please? I would provide you a link in PM i will do all the work but just need a guru to oversee what i am doing is correct.

 

[RoryA]

I would provide you a link in PM i will do all the work but just need a guru to oversee what i am doing is correct.

All discussion needs to be in the open boards please, in accordance with the rules.

 

[daleholden]

All discussion needs to be in the open boards please, in accordance with the rules.

Apologies i did not intend to break rules i am happy to discuss here but did not want to get an open link to a private working workbook.

 

[daleholden]

HI Eric

Hi that is looking good can you edit E2 =ROUND(F2*9%,2) to =ROUND(F2*9%,2)*1.2 this is to add VAT on. I have tried adding it and it breaks it. It makes the selling price wrong, please can you make it work so i can add 20% VAT on to E2?

Dale

 

[Eric W]

I'm a little confused? The formula I had in E2 had the 1.2, but not the ROUND. If you want to include the ROUND, the formulas would look something like:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Product Price	Vat	Unit Price	PayPal %	eBay FVF %	Total Price	eBay Sale Price	Profit	GS Profit			VAT	Paypal %	eBay FVF %			CGS From	CGS To	Profit
2	236	4.72	240.72	9.14	33.28	283.14	308.13	25				2%	2.90%	9%			1	50	5
3	23	0.46	23.46	1.16	3.59	28.21	33.21	5									51	100	10
4	5200	104	5304	186	691.95	6181.95	6406.95	225					PayPal Fixed				101	200	20
5	444	8.88	452.88	17.11	62.96	532.95	582.94	50					0.2				201	300	25
6	12000	240	12240	423.28	1575.6	14238.88	14588.88	350									301	400	35
7																	401	500	50
8																	501	750	65
9																	701	1000	99
10																	1001	2000	125
11																	2001	3000	150
12																	3001	4000	175
13																	4001	5000	200
14																	5001	6000
15																	6001	7000	250
16																	7001	8000	300
17																	8001	9000	325
18																	9001	10000	350

<tbody>
</tbody>

Sheet3​

Worksheet Formula s Cell Formula B2 =ROUND(A2*$L$2,2) C2 =ROUND(A2*(1+$L$2),2) D2 =ROUND(G2*$M$2+$M$5,2) E2 =ROUND($N$2*G2*1.2,2) F2 =C2+D2+E2 G2 =ROUND((H2+$M$5+C2)/(1-$M$2-$N$2*1.2),2) H2 =VLOOKUP(A2,$Q$2:$S$18,3) <tbody> </tbody>

<tbody>
</tbody>

 

[daleholden]

Hi Eric

A few people have been in tweaking the sheet so not sure! All i needed was to add 20% VAT to E2 when I tried adding *1.2 to your original forumula it didn’t seem to work.
I just want it to work with adding the 20% VAT on E2

I'm a little confused?

 

[Eric W]

So again, I'm a bit confused. Do the formulas from post 16 do what you want? If not, why not?

 

[daleholden]

HI Eric

It is not working as it should the sale price is £1505.45 so looking to the table it should make £125 profit.
The formula is assuming it is £1000 so giving a £99 not sure if you can fix it!

Product Price	Vat	Unit Price	PayPal %	eBay FVF %	Total Price	eBay Sale Price	Profit
£1,000.00	£200.00	£1,200.00	£43.86	£162.59	£1,406.45	£1,505.45	£99.00

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Apparently I misunderstood. I thought the profit you wanted was based on the cost of the product, not the final selling price. Yes, if you want to do it that way, it could be an iterative process. However, you could pre-calculate the inflection points (the places where the profit jumps from one tier to the next), store them in a table, and use the table. For example:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Product Price	Vat	Unit Price	PayPal %	eBay FVF %	Total Price	eBay Sale Price	Profit	GS Profit			VAT	Paypal %	eBay FVF %		Inflection point	CGS From	CGS To	Profit
2	1000	200	1200	44.73	165.84	1410.57	1535.57	125				20%	2.90%	9%		1	1	50	5
3	31	6.2	37.2	1.62	5.31	44.13	49.13	5								32	51	100	10
4	32	6.4	38.4	1.83	6.08	46.31	56.32	10					PayPal Fixed			64	101	200	20
5	1333	266.6	1599.6	58.16	215.85	1873.61	1998.61	125					0.2			127	201	300	25
6	1334	266.8	1600.8	59.04	219.13	1878.97	2028.97	150								195	301	400	35
7																259	401	500	50
8																318	501	750	65
9																486	701	1000	99
10																637	1001	2000	125
11																1334	2001	3000	150
12																2033	3001	4000	175
13																2731	4001	5000	200
14																3430	5001	6000	225
15																4128	6001	7000	250
16																4826	7001	8000	300
17																5504	8001	9000	325
18																6202	9001	10000	350
19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =ROUND(A2*$L$2,2) C2 =ROUND(A2*(1+$L$2),2) D2 =ROUND(G2*$M$2+$M$5,2) E2 =ROUND($N$2*G2*(1+$L$2),2) F2 =C2+D2+E2 G2 =ROUND((H2+$M$5+C2)/(1-$M$2-$N$2*(1+$L$2)),2) H2 =VLOOKUP(A2,$P$2:$S$18,4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

If you ever want to change the profit table, you'll need to recalculate the inflection points. To do so, just use the Goal Seek. For example, to calculate the second tier, I just put 5 in H2 (overriding the formula), then started Goal Seek, setting G2 to 50 by varying A2. The result was 31.62531, which I rounded up to 32. Anything with a cost below 32 would result in a final selling price under 50 with a profit of 5, so therefore anything above that would be in the next tier at the next profit level.

Hopefully, I've got it right this time!