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

 

[RoryA]

Is the required profit level determined by column C or column F?

 

[daleholden]

HI it is determined by F which makes it difficult to work out. Because D & E are determined by what is in F

 

[joeu2004]

I believe the formulas you want are:

D2: =ROUND(F2*2.9%+0.2,2)
E2: =ROUND(F2*9%,2)
F2: =ROUNDUP((C2+G2+0.2)/(1-2.9%-9%),2)
G2: =VLOOKUP(C2,$P$2:$R$18,3)

G2 is the required (minimum) profit.

F2 is derived algebraically as follows:

F2 = C2 + F2*2.9% + 0.2 + F2*9% + G2
F2*(1-2.9%-9%) = C2 + 0.2 + G2
F2 = (C2+0.2+G2) / (1-2.9%-9%)

PS.... According to your table, the profit on 100 is 10, not 20.

 

[daleholden]

I believe the formulas you want are:

D2: =ROUND(F2*2.9%+0.2,2)
E2: =ROUND(F2*9%,2)
F2: =ROUNDUP((C2+G2+0.2)/(1-2.9%-9%),2)
G2: =VLOOKUP(C2,$P$2:$R$18,3)

G2 is the required (minimum) profit.

F2 is derived algebraically as follows:

F2 = C2 + F2*2.9% + 0.2 + F2*9% + G2
F2*(1-2.9%-9%) = C2 + 0.2 + G2
F2 = (C2+0.2+G2) / (1-2.9%-9%)

PS.... According to your table, the profit on 100 is 10, not 20.

HI Joe that is my whole point it is not £100 when you add on PayPal Fee & eBay FVF and this is where the chicken egg comes in.

Product cost to me £100 so we know when FVF & PayPal is added on it takes it over the £100 limit. So we know we have to make £20 profit.
So how can i work out exactly how to make a clear £20 profit knowing what the eBay FVF and PayPal fees are???

 

[joeu2004]

HI Joe that is my whole point it is not £100 when you add on PayPal Fee & eBay FVF and this is where the chicken egg comes in.

Then your table is labeled incorrectly. CGS stands for cost of goods sold, and typically that does not include "operational" costs such as shipping, service fees, etc.

And you seem to be unaware of the "chicken and egg" problem with the PayPal and EBay fees as you defined them, namely in terms of the end-user price. That is the "chicken and egg" problem that I understood, and my formulas address that.

As for the problem you want to solve, I think it requires an iterative algorithm. After applying my formulas, if the profit based on the calculated sale price is not the same as the profit based on the "unit price" (CGS), recalculate using the calculated sale price as the "CGS". Repeat until the two profits are the same. I would implement that algorithm in a VBA function (UDF).

With the wide brackets that you specify in the table, I think it will require only one or two lookups and calculation. However, it might be possible to define brackets so narrow that the algorithm never terminates.

PS.... Although I suggested implementing an algorithm in VBA, I actually prototyped it in Excel. So if we could prove (TBD) that it requires no more than two iterations, it would not be difficult to design a two-stage calculation in Excel, without relying on VBA.

Caveat lector: As I look now, Dale has changed is the original file so that the values in A2:C2 are no longer consistent with the OP and even the comments in the file. Also, Dale inserted a column; consequently, my formulas no longer match the Excel file design.

 

[daleholden]

HI Joeu2004

Please accept my apologies I was not intending to be rude or ungrateful. If my reply came across like that I am very sorry.

(History) Basically, I currently just add a 10% on the final fee to work out a profit. But when I sell high-value items my profit is too high and not fair on the buyer.

I am trying to come up with a way of keeping profits fair but worthwhile. I thought a fixed fee across a price table might be fair.
I guess i could have done a sliding scale with percentages, but I went with the fixed fee.

Sorry for changing the table I was just trying to make it more clear and helpful to people.

My algebra & vba is very basic so not 100% sure if i fully understand.

Dale

Then your table is labeled incorrectly. CGS stands for cost of goods sold, and typically that does not include "operational" costs such as shipping, service fees, etc.

And you seem to be unaware of the "chicken and egg" problem with the PayPal and EBay fees as you defined them, namely in terms of the end-user price. That is the "chicken and egg" problem that I understood, and my formulas address that.

As for the problem you want to solve, I think it requires an iterative algorithm. After applying my formulas, if the profit based on the calculated sale price is not the same as the profit based on the "unit price" (CGS), recalculate using the calculated sale price as the "CGS". Repeat until the two profits are the same. I would implement that algorithm in a VBA function (UDF).

With the wide brackets that you specify in the table, I think it will require only one or two lookups and calculation. However, it might be possible to define brackets so narrow that the algorithm never terminates.

PS.... Although I suggested implementing an algorithm in VBA, I actually prototyped it in Excel. So if we could prove (TBD) that it requires no more than two iterations, it would not be difficult to design a two-stage calculation in Excel, without relying on VBA.

Caveat lector: As I look now, Dale has changed is the original file so that the values in A2:C2 are no longer consistent with the OP and even the comments in the file. Also, Dale inserted a column; consequently, my formulas no longer match the Excel file design.

 

[joeu2004]

PS.... Although I suggested implementing an algorithm in VBA, I actually prototyped it in Excel. So if we could prove (TBD) that it requires no more than two iterations, it would not be difficult to design a two-stage calculation in Excel, without relying on VBA.

Arguably, this algorithm is well-suited for a system of circular-reference cells in Excel. However, I deprecate the purposeful use of circular references because:

1. Once enabled for that purpose, we lose the detection of unintended circular references.

2. I don't believe Excel implements multi-cell circular references well. I have seen inconsistent results because Excel quits the circular recalculations "in the middle" (of a circle? wink).

 

[daleholden]

I have a list of some prices and would like to auto price them. Goal Seeker seems a good solution if your doing a couple but not if you want to do 1000's

 

[joeu2004]

As for the problem you want to solve, I think it requires an iterative algorithm. After applying my formulas, if the profit based on the calculated sale price is not the same as the profit based on the "unit price" (CGS), recalculate using the calculated sale price as the "CGS". Repeat until the two profits are the same. I would implement that algorithm in a VBA function (UDF).

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

Basically, I currently just add a 10% on the final fee to work out a profit. But when I sell high-value items my profit is too high and not fair on the buyer. I am trying to come up with a way of keeping profits fair but worthwhile. I thought a fixed fee across a price table might be fair.

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.