eBay Tracking Spreadsheet

[azzidodabass]

Hi guys,

I am putting together a simple spreadsheet so I can track the items I sell on eBay and see clearly the profits I am making.

First and simple - anyone know of any existing up-to-date spreadsheet templates out there for eBay that I can use?

My column headings are :- Allocated P&P, Winning Bid, Sub Total, Insertion Fee, Final Value Fee, Paypal Handling Fee, Actual P&P Cost, Overal Profit

I've dabbled in spreadsheets in the past. I know or can figure out most basic formulas but I am having trouble knowing where to start on knocking up a formula for Buy It Now Items. I've sorted standard auction fees out - thats just a simple 10% that eBay take, but the BIN fee is a little more complicated.

http://pages.ebay.co.uk/help/sell/fees.html#BIN

The formula would need to work on the Winning Bid column.I thnk I would need to formulas - one for All Categories and one for Technology. I could implement a new column to simply indicate whether its a Technology sale or not.

So for those willing to help out:-

Lets say the Winning Bid cell is A1, and the 'Is this a Technology Sale' (indicated by a Y or an N) as A2.

Many thanks for those who can help.

 

[azzidodabass]

Hi there,

Thanks for your help. While its not answered my question, you have actually provided me with more useful info on excel spreadsheets. I now know how to use lookups! Thanks

However, my problem is still that - when eBay work out the FVF on BIN items, they calculate it in the most awkward fashion (they use the same principle for Business sellers but I am not a business seller so it doesn't affect me). This is from the Fees page :-

For Buy it now items eBay take :- 5.25% of the initial £29.99 (£1.57) + 3% of the initial £30.00 - £99.99 (£2.10) + 2.5% of the initial £100.00 - £199.99 (£2.50) + 2% of the initial £200.00 - £299.99 (£2.00) + 1.5% of the initial £300.00 - £599.99 (£4.50) + 1% of the remaining balance of the final selling price

So my formula needs to break down the Winning Bid figure into these segments to work out its percentages for each segment. I am just struggling figuring out how to tell it to take 5.25% of the first 29.99, then 3% of the next 30.00 to 99.99 and so on.

Many thanks.

 

[azzidodabass]

If(A1<30,A1*0.0525,
If(A1<100,0.03*(A1-30)+1.57,
If(A1<200,0.025*(A1-100)+2.1+1.57,
If(A1<300,0.02*(A1-200)+2.5+2.1+1.57,
If(A1<600,0.015*(A1-300)+2+2.5+2.1+1.57,
0.01*(A1-600)+4.5+2+2.5+2.1+1.57)))))

Is the nested IF-function you need (i think!)

LOL I'd have never have been able to do that on my own. Thanks will give it a whirl and see how it goes.

So I take it this is quite a difficult formula to work out - something that a novice like me would never have figured out?

Thanks again guys, will report back with my results

 

[azzidodabass]

Ergh. Sorry to turn this into an actually eBay problem :-

Using your formula worked but I am not getting the right figure compared to eBay.

The item where there is a discrepancy : I sold a Laptop. I listed it as an Auction with a buy it now. The listing was ended early with the buy it now. There were 5 bids on the auction at the time of the BIN.

The buy it now was for 595.00. eBay have charged me a Final Value Fee of £39.97. Now, using eBay's Buy it Now fee structure - it comes to 12.59. So why have I been charged just short of 40 quid.

The only reference I can find is that for Auctions, the eBay FVF fees are 10% of the winning bid up to a maximum of £40. So the way I see it - in order to benefit from the cheaper BIN fees you have to offer ONLY a BIN figure - and not an auction, yet I can find no documentation on eBay about this.

Can anyone confirm if this is the case?

Many thanks