It's another eBay pricing structure question...

[divster27]

I can't seem to quite find the right way of doing this, and have asked a couple of other people to take a look with no joy so far.
I always think that whenever there are rules, anything is possible, but we shall see!

Attached is my eBay pricing spreadsheet – each column has a note against to describe what it is.

I’m trying to work out the price is should sell the item at if I want to make 5% profit (Column T) and 10% profit (Column T) – 2 different columns.

This is where I need the formula to be.

Because eBay has lots of % charges, it makes it a bit difficult to work backwards.

I'm trying to find the correct Sell Price (Column D), based on a fixed profit % (5% or 10%) and for that figure to be in Colum T

I have put the correct answer in T16 and U16 for the first row.

I have calculated these manually using Goal Seek - I need a formula in T16 and U16 that calculates this for me.

Does this make sense?

Some comments in the cells shown in this image, as not sure they'll be obvious in the Mini Sheet

MrExcelPrice.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
15	CountOfDealer Price	Dealer Price	Purchae Price	Sell Price	9.9% eBay Fee	30p FVF	0.35% Regulatory operating fee	2% International Fee	All Fees	Advertising at 10%	Purchase Price + Fees	Profit	% Profit							Sell Price if Profit 5%	Sell Price if Profit 10%
16	216	£1.25	£1.50	£4.99	£0.49	£0.30	£0.02	£0.10	£1.09	£0.60	£3.19	£1.80	36%							£ 2.72	£ 2.94
17	160	£1.49	£1.79	£5.99	£0.59	£0.30	£0.02	£0.12	£1.24	£0.72	£3.75	£2.24	37%
18	2857	£4.99	£5.99	£13.99	£1.39	£0.30	£0.05	£0.28	£2.42	£1.68	£10.08	£3.91	28%
19	1245	£5.55	£6.66	£14.99	£1.48	£0.30	£0.05	£0.30	£2.56	£1.80	£11.02	£3.97	26%
20	8098	£6.99	£8.39	£19.99	£1.98	£0.30	£0.07	£0.40	£3.30	£2.40	£14.09	£5.90	30%
NewTBL

Cell Formulas
Range	Formula
G16:G20	G16	=D16/100*0.35
H16:H20	H16	=D16/100*2
I16:I20	I16	=SUM(E16:H16)*1.2
J16:J20	J16	=(D16/100*10)*1.2
K16:K20	K16	=C16+I16+J16
L16:L20	L16	=D16-K16
M16:M20	M16	=L16/D16
C16:C20	C16	=B16*1.2
E16:E20	E16	=(D16/100*9.9)

 

[Micron]

I must not be understanding because my answer seems too simple. You have the sum of your purchase price plus all fees as 3.19 (K16) and want a formula to calculate a the marked up price that makes you 10% on that? Then it is simply K16*1.1? Fifteen percent would be K16*1.15. 5 percent: K16*1.05. Sorry if I've missed the goal completely but you haven't had a response all day so I thought, 'why not try?'.

 

[divster27]

Hi @Micron - thanks for having a crack!

It's not that simple sadly - eBay take loads of fees (all the previous columns)

So if I charge £4.99 they take 10% as a variable fee, 30p fixed fee, 0.35% fee (for???), 2% fee if it's an international sale, and then a 10% (this can change but not above) fee if i use their promotional tools

So their fees change based on the start price - and thus the profit margin changes too.

it's horrible trying to work it all out sensibly

 

[Micron]

So your problem is that if you initially calculate a 5% markup, all the fees increase and eat into that 5%, so you must at first add more than 5% to end up at 5% profit? That plus some of the fields are variable and depend on other factors such as the use of eBay provided tools/services?

Maybe it would help to think of it as a currency conversion issue. Let's say that $Cdn is worth 75% of $US. I can't just add on 25% because the 25% of my dollar isn't worth 25% of a US dollar. It takes 30% to make up that 25%. So $1.30 makes up $1.00 US. So your initial selling price plus all the fees is your $Cdn and your $US is that amount plus 5%. Thus your factor may end up being 8% for what would be a standard block of fees. For items using extra services, you'd need another calculation that ends up at maybe 11%. I'm thinking that the trick is to calculate what percentage the fees are for how an item is sold, and figure out how much eBay eats into that as a percentage. Then if you added your profit margin target to an item, you know you'll only get x% of it, so you'll need to add more, as I did for the currency conversion. A sheet where you drag down formulas might show you where the target for a given percentage gives you the final asking price.

Having said all that, I imagine that you've asked people who are a lot smarter than me and maybe even have experience with eBay so I'm probably just writing drivel instead of helping. Sorry if that's the case.

 

[divster27]

OK - i see what you're saying. (i think!)
Work out what % they take at each price point and work it from there
it may be drivel, but it's definitely a different way to look at the issue - worth a play

 

[StephenCrump]

You can solve using simple algebra:

	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4		Margin	20%	<-- You haven't explained this?
5		% Fees
6		ebay	9.90%
7		Regulatory	0.35%
8		International	2.00%
9		Advertising	10.00%
10			26.70%
11		$ Fees	0.36
12		Profit	5%
13
14
15			PP	SP	ebay	FVF	Regulatory	International	Advertising	PP+Fees	Profit	% Profit
16			1.500	2.72	0.32	0.36	0.01	0.07	0.33	2.59	0.14	5.0%
17			1.788	3.14	0.37	0.36	0.01	0.08	0.38	2.99	0.16	5.0%
18			5.988	9.29	1.10	0.36	0.04	0.22	1.12	8.83	0.46	5.0%
19			6.660	10.28	1.22	0.36	0.04	0.25	1.23	9.76	0.51	5.0%
20			8.388	12.81	1.52	0.36	0.05	0.31	1.54	12.17	0.64	5.0%
Sheet1

Cell Formulas
Range	Formula
C10	C10	=SUM(C6:C9)*(1+C4)
C11	C11	=0.3*(1+C4)
D16:D20	D16	=(C16+C$11)/(1-C$10-C$12)
E16:E20	E16	=C$6*D16*(1+C$4)
F16:F20	F16	=C$11
G16:G20	G16	=C$7*D16*(1+C$4)
H16:H20	H16	=C$8*D16*(1+C$4)
I16:I20	I16	=C$9*D16*(1+C$4)
J16:J20	J16	=C16+SUM(E16:I16)
K16:K20	K16	=D16-J16
L16:L20	L16	=K16/D16

	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4		Margin	20%	<-- You haven't explained this?
5		% Fees
6		ebay	9.90%
7		Regulatory	0.35%
8		International	2.00%
9		Advertising	10.00%
10			26.70%
11		$ Fees	0.36
12		Profit	10%
13
14
15			PP	SP	ebay	FVF	Regulatory	International	Advertising	PP+Fees	Profit	% Profit
16			1.500	2.94	0.35	0.36	0.01	0.07	0.35	2.64	0.29	10.0%
17			1.788	3.39	0.40	0.36	0.01	0.08	0.41	3.05	0.34	10.0%
18			5.988	10.03	1.19	0.36	0.04	0.24	1.20	9.03	1.00	10.0%
19			6.660	11.09	1.32	0.36	0.05	0.27	1.33	9.98	1.11	10.0%
20			8.388	13.82	1.64	0.36	0.06	0.33	1.66	12.44	1.38	10.0%
Sheet1

Cell Formulas
Range	Formula
C10	C10	=SUM(C6:C9)*(1+C4)
C11	C11	=0.3*(1+C4)
D16:D20	D16	=(C16+C$11)/(1-C$10-C$12)
E16:E20	E16	=C$6*D16*(1+C$4)
F16:F20	F16	=C$11
G16:G20	G16	=C$7*D16*(1+C$4)
H16:H20	H16	=C$8*D16*(1+C$4)
I16:I20	I16	=C$9*D16*(1+C$4)
J16:J20	J16	=C16+SUM(E16:I16)
K16:K20	K16	=D16-J16
L16:L20	L16	=K16/D16

 

[divster27]

Hi @StephenCrump ,

Will work my way through, but looks like this has potentially solved it.


The Margin number at the top - not sure what this is. I think you're referring to the Dealers Price and Purchase Price

The dealer's price to me is always shown as VAT exclusive - ie £1
The purchase price I pay is £1.20 (dealers price + 20% tax)

 

[StephenCrump]

I think you're referring to the Dealers Price and Purchase Price ...

No, it was the fact that you were hard-coding a factor of 1.20 to apply to all the fees. I should have realised from the £ signs that this was VAT.

It would be much better to have this as a parameter, rather than hard-coding 1.2's throughout your formulae.