Formula for calculating eBay selling price

[eaxlns]

Hi folks,
I'm not sure if this has been posted before or if there is a guide on how to do this on this forum, so apologies if there is.
I want a formula which I can use to work out my selling price on eBay, assuming I want to make a 15% profit of the selling price, and the following are my costs:

Item cost £1.72
Shipping £0.87
eBay fees 10% of final selling price
PayPal fees 2.9% + £0.20 of final selling price

Many thanks

 

[shg]

Profit is measured on revenue, not revenue minus miscellaneous costs.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Sell-Based Percentages[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

eBay​

[/td][td]

10.00%​

[/td][td]B2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

VAT​

[/td][td]

0.00%​

[/td][td]B3: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

PayPal​

[/td][td]

2.90%​

[/td][td]B4: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

Margin​

[/td][td]

15.00%​

[/td][td]B5: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Fixed Costs[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

Post & Packing​

[/td][td]

$0.87​

[/td][td]B7: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

PayPal​

[/td][td]

$0.20​

[/td][td]B8: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Cost[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

Direct Cost​

[/td][td]

$1.72​

[/td][td]B10: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Sell Price[/td][td="bgcolor:#CCFFCC"]

$3.87​

[/td][td="bgcolor:#CCFFCC"]B12: =(B10 + SUM(B7:B8)) / (1 - SUM(B2:B5))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Check:[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

eBay​

[/td][td="bgcolor:#E5E5E5"]

$0.39​

[/td][td]B15: =B2 * B12[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

VAT​

[/td][td="bgcolor:#E5E5E5"]

$0.00​

[/td][td]B16: =B3 * B12[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

PayPal (variable)​

[/td][td="bgcolor:#E5E5E5"]

$0.11​

[/td][td]B17: =B4 * B12[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

Post & Packing​

[/td][td="bgcolor:#E5E5E5"]

$0.87​

[/td][td]B18: =B7[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

PayPal (fixed)​

[/td][td="bgcolor:#E5E5E5"]

$0.20​

[/td][td]B19: =B8[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

Your Net​

[/td][td="bgcolor:#E5E5E5"]

$2.30​

[/td][td]B20: =B12 - SUM(B15:B19)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

Profit​

[/td][td="bgcolor:#E5E5E5"]

$0.58​

[/td][td]B21: =B20 - B10[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

Margin​

[/td][td="bgcolor:#CCFFFF"]

15.00%​

[/td][td="bgcolor:#CCFFFF"]B22: =B21 / B12[/td][/tr]
[/table]

 

[Slpygry]

On a related note, I spent a lot of time once working out the same type of analysis for another eBay seller. His setup was much more complicated, with different types of products getting different fees and tax rates. I finally concluded in that case that the only solution was to create a UDF that would start at a base sale price, probably the cost times a percentage, then would increment by 1, checking all the fees and taxes, and quit when all constraints were met. There just were no fixed formulas that covered all situations for him.

Eric - It has taken me two days of research to find a response like the one you've offered here (create a UDF). I am also attempting to calculate a Sell Price with costs that include a fee based on the Sell Price. I have found an online calculator at Selling Price Calculator that will do it one item at a time. I have also found that the Excel function "Goal Seek" will do it, one at a time. However, I have about 45K items that I'd like to calculate; and I cannot find anywhere how to re-create what CalculatorSoup does or how to re-create Goal Seek.

I am very open to using a Macro, or Excel Formula's, or even something in PowerQuery. (Most of my work for the 45K items is in Power Query.)

If possible, do you have advice, or spreadsheets or coding that you can send my way? Or even the "Type of Programmer" (title) that I would need to hire that can re-create the sell price calculator?

Thank you,
Karri

 

[Eric W]

Welcome to MrExcel!

I glanced at the Calculator Soup site, and there's nothing there that Excel can't do. If you've developed a Solver model that can find the answer for you, it shouldn't be too hard for someone to analyze it and create a macro. (Or use PowerQuery, but that's not something I'm good at.) I'd suggest you open a new thread (more likely that someone will see it than adding on to a very old thread), present your model, then ask if someone can convert it to a macro. I think there's a decent chance someone here will take a shot at it. If not, MrExcel has a Consulting Services option. Look for that on the MrExcel Publishing drop down. I don't know anything about price or schedule, but I'm sure they'll let you know.

Good luck!

 

[Slpygry]

Welcome to MrExcel!

I glanced at the Calculator Soup site, and there's nothing there that Excel can't do. If you've developed a Solver model that can find the answer for you, it shouldn't be too hard for someone to analyze it and create a macro. (Or use PowerQuery, but that's not something I'm good at.) I'd suggest you open a new thread (more likely that someone will see it than adding on to a very old thread), present your model, then ask if someone can convert it to a macro. I think there's a decent chance someone here will take a shot at it. If not, MrExcel has a Consulting Services option. Look for that on the MrExcel Publishing drop down. I don't know anything about price or schedule, but I'm sure they'll let you know.

Good luck!

Eric - you are fantastic! Thank you for seeing the very old message and for responding with actionable options. I very much appreciate it.

Karri