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]

Well, I like that you have table-ized everything. It's easy to look up the relevant VAT rate, or eBay rate, or Amazon rate, etc. However, I think it's gotten a bit beyond what we can handle on a Q&A board. 10 sheets, dozens of tables, thousands of items? You might need to invest in a consultant.

My original approach was to use algebra to find the Final Price, since everything else was a constant that could be looked up. However, once you add details like fees which have a fixed percentage, but also a cap, and fees that have a percentage based on the Final Price, it becomes much murkier. I can't think of a non-iterative way to calculate that. It's a non-continuous function.

If I had to proceed from here, I'd write a macro that starts at cost+VAT, figures out the fees and what's left over for your profit, and quits when everything works and increments by 1 if it doesn't.

I wish you the best of luck, but I doubt I'd be able to fully grasp the complexities of your requirements via this forum, so I'm going to bow out from here.