Solver Type Formula or Chicken Egg Query

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
235
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,338
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Is the required profit level determined by column C or column F?
 

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
235
HI it is determined by F which makes it difficult to work out. Because D & E are determined by what is in F
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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.
 
Last edited:

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
235
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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.
 
Last edited:

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
235
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.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Sep 22, 2002
Messages
235
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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"):

Code:
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.
 
Last edited:

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top