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

 

[daleholden]

HI Eric

This post is for discussion and a reflection on what I said in post #19 . It appears I have made it more complicated but on reflection what is the difference if I said I want the profit on (F) Total Price?

It is an identical profit whether you look at F or G in my mind. If you use F to calculate the profit does that make it easier?

Apparently I misunderstood.

 

[Eric W]

One way or the other, I think you're making this overcomplicated. Originally, I was calculating the profit based on the product price. Now I'm basing the profit based on the Sale price. But you'll notice, the formulas are exactly the same in both cases! The only difference is the lookup table. I look up the product price from column Q when I base the profit on product price, and I look up product price from column P when I base it on Sale price. So the only difference is that your tiers are a bit smaller.

If you want to base it on Total Price instead of Sale price, you can do that the same way. Take the ROUND off all the formulas in row 2, then set H2 to 5, then click Goal Seek, and set F2 to 50 by changing A2. Repeat for the other rows. (If you don't take off the ROUNDs, the Goal Seek might not converge. You can replace them once you find all the inflection points.)

So as you see, whether you base it on Product Price, Total Price, Sale Price, or even Unit Price, it pretty much boils down to the same thing: looking up Product Price in a table, with various sized tiers and values for each tier. If you think about it, it pretty much has to be like that, since all those values are related to each other. If the relationship changes, like if the VAT goes up to 22%, then the tiers/values would also have to change. Which is kind of a strong argument for just using Product price as the lookup value, since it is the base value before all the other additions. No need for a complicated process to find the inflection points.

Make sense?

 

[daleholden]

Hi

There is no way i could use just Product Price i would lose money on nearly every transaction. In the vast majority of cases, eBay Fees & PayPal fees are a lot more than i make.
Also, big-ticket items the eBay Fees are £150 and PayPal are £45 if i am only making £99 then i lose money so that is a none starter.

I would be happy to use Total price as shown in F if i use this do i still need to use inflection points?

 

[Eric W]

Somehow I'm not getting my point across. These formulas calculate the Sale price after all fees have been calculated and included. So on row 2 in the example from post 20, the price was 1000, Paypal was 44.73, eBay was 165.84, VAT was 200, and you wanted a profit of 125. Add all those up and you get the price you need to sell it at, 1535.57, which is G2. So you if buy something at 1000, and sell it at 1535.57, you'll cover all the fees and make your 125.

So, yes, the assorted fees/taxes came to 410.57, and you made just 125. But you MADE 125, you did not lose 125-410.57.

Let's ask a simple question. I bought something for 500, and I want to sell it and net a profit of 75. How much do I have to sell it for to cover cost, tax and fees? That's a very easy question to understand, and it's exactly what the formulas from post 11 do. The formulas are dependent on the cost.

Now let's ask your original question: I'm selling something for 500 pounds, and I want 75 pounds of that to be profit. How do I make sure that's true? Well, obviously the cost must be less than 500. Also, less than 425. So we try 400. Does that work? 400 * 20% = 80 = 480, add in fees for eBay and Paypal, and we're over 500. So try 300. Cost + tax = 360, add in fees and we're under 500. So try 350. It turns out that 351 is about right in this scenario. That's your original question, and it's an iterative process, and you see how complicated it is. But look at it from the other angle. If you say you want a 75 pound profit from a cost of 351, that's EXACTLY equivalent to your original question. It's the same as the question in the previous paragraph, except that you want a profit of 75 at a cost of 351 instead of 500. So to make it the same, just adjust the table to your liking.

If I still haven't convinced you of the equivalency of using cost vs. one of the prices, you can still use F or G as the value. I explained how to create the inflection points in post 22. But if you ever change the values in column S, you'll need to recalculate the inflection points. You wouldn't have to do that if you stuck with cost. (Another way to view the inflection points, is that this is the cost where I jump to the next tier.)

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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	500.00	100.00	600.00	22.89	84.50	707.39	782.39	75.00				20%	2.90%	9%		1	1	50	5
3																32	51	100	10
4													PayPal Fixed			64	101	200	20
5	351.00	70.20	421.20	16.88	62.12	500.20	575.20	75.00					0.20			127	201	300	25
6																195	301	400	35
7																259	401	500	50
8																318	500	750	75
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

<tbody>
</tbody>

Sheet1
​

Worksheet Formulas Cell Formula B2 =A2*$L$2 C2 =A2*(1+$L$2) D2 =G2*$M$2+$M$5 E2 =$N$2*G2*(1+$L$2) F2 =C2+D2+E2 G2 =(H2+$M$5+C2)/(1-$M$2-$N$2*(1+$L$2)) H2 =VLOOKUP(A2,$Q$2:$S$18,3) B5 =A5*$L$2 C5 =A5*(1+$L$2) D5 =G5*$M$2+$M$5 E5 =$N$2*G5*(1+$L$2) F5 =C5+D5+E5 G5 =(H5+$M$5+C5)/(1-$M$2-$N$2*(1+$L$2)) H5 =VLOOKUP(A5,$P$2:$S$18,4) <tbody> </tbody>

<tbody>
</tbody>

The H2 formula looks up the product price from column Q to get the profit desired. H5 looks up the product price from column P to get the 75 profit desired. The inflection points here are based on the amounts in G, so if you want to use F, you'll have to recalculate them. I removed the ROUND from the formulas too, and just formatted the cells with 2 decimals.

 

[daleholden]

HI Eric

Thank you for your detailed explanation and i now think from reading all the posts again the Inflection Point is not possible ( little history & background). If you go back to my sheet i linked you will see col E eBay Charge varies on each item this can range from 5 to 11% also with a max of £20 on some items (i added the sheet to show categories). This is worked out in my sales sheet with look up as my live data has an SKU and a category attached to it. So I bring across the correct %; also not so much but PayPal can vary from month to month but I have this covered.

I think from what you are saying if the % values change as in eBay FVF Inflection points have to be recalculated. So this looks like a none starter as well. I think I need to look at your solution in post #11 or #16 this may be the correct way forward; if it will work with different eBay FVF which it looks like it should.

Somehow I'm not getting my point across.

 

[Eric W]

I went back and looked at your original sheet, and I checked out the second tab with the eBay fees. I also noticed that there is a table for the VAT, so that could vary too. This ramps up the complexity considerably. You'd need a separate table for each category, and add another column for every item, saying what category the item is in. And possibly another column saying what VAT percentage to use. Calculating inflection points for all those tables would be a nightmare, if not impossible altogether. The easiest workaround I can see is to use the setup from post 11, and set the eBay percentage to the maximum percentage, which appears to be 11%. Same for the VAT, which is 22%. The formulas will calculate the selling price based on those percentages, so in many cases, the fees calculated will be higher than they really are, so when eBay takes out a smaller fee, you'll actually make a bit more than the profit you expected from the table.

But I don't know if that will be enough for you. You might want/need to keep track of accurate fees. If so, the next step would be to use VBA to calculate everything. Possibly an on-demand macro that calculates the entire sheet for you at once, possibly a UDF (User defined function) that calculates a row at a time. I'll think about it a bit more, and if that's of interest, let me know.


By the way, you don't need to quote my entire message back to me every time, it just takes up space on the page.

 

[daleholden]

Hi Eric

Would it be easier if you saw my live sheet and how it works now or me send you a video so you can see my current setup

 

[Eric W]

Forum rules don't allow taking the question offline. If you can post a link to your sheet, or to a video, that's acceptable.

 

[daleholden]

HI Eric

Sorry for the delay but i have been thinking about this and i think i have made it far to complex.

So I have done a working sheet see here which is more simple.

I need Excel to check the new percent profit table on the ALL Fees tab starting K8

Come back to Profit and fill out L2 set the price

Also just as a double check fill out M2E Pro to see it is working

I have done the rest via vLookup

Dale

 

[daleholden]

Hi Eric

I have formatted the sheet very similar to my live sheet with the relevant tables.

I am picking up the Unit Price and i have a dropdown of the different categories.

I use this formula to pick up the correct charge for the eBay FVF H2 =IFERROR(MIN(VLOOKUP(K1587,AllFees!$G$2:$H$68,2,0)*J1587,VLOOKUP(K1587,AllFees!$G$2:$I$68,3,0)),0)*1.2

For the PayPal Fee G2 i use =SUM([@[Sale Price]]*AllFees!$B$4+0.2)

But we still need to work out how to set the correct % and Sale Price before adding G2 & H2 on?

If this is possible?

Hence Chicken & Egg


Dale