eBay Tracking Spreadsheet

azzidodabass

azzidodabass

azzidodabass

Permabanned
Joined
6 Feb 2009
Posts
79
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.
 
It should be pretty simple with lookup tables. Here's something I threw up which should help you:
http://homepages.nildram.co.uk/~akiller/temp/ (2007 / 2003 excel versions)

Change the 'type' column between 0 and 1 to flip between auction/buy-it-now. When in buy-it-now mode change the category number to 0, 1, 2 or 3 to switch between the different fees - the data is held on the worksheet 'BIN Fees'.

You should be able to use this sytem to implement all of eBay's fees, including the final price addition onto those BIN fees.
 
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 said:
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
Click to expand...
:eek::eek::eek::eek::eek::eek:
 
azzidodabass said:
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
Click to expand...

Help. :confused:
 
azzidodabass said:
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.
Click to expand...

I've done something like this before for the same reason, might sound confusing if I try and explain it here, but I'll try.

Say an item sells for £42.50

You use an if statement, If selling price greater than 29.99, then that field value is 29.99, another collumn to work out 5.25% of that one.

Then another collumn with IF selling price is greater than 59.99 (30 extra) in this case NO, then have it subtract the 29.99 and work out 3% of that value.

If that makes any sense at all? Hope it helps :p
 
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!)
 
Recording a macro would probably be easier lol, or you could just break it down level by level, although it wouldn't look as neat a one function. Funnily enough I had to do a similar function at work on Thursday, which was easier written out before I did it in excel.
 
Grrrrr said:
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!)
Click to expand...

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 :D
 
azzidodabass said:
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 :D
Click to expand...

It's actually fairly straightforward, you need need to break it down logically.

If(A1<30,A1*0.0525, If A1 is less than £30 you simply need to take 5.25% of A1 and then the IF function ends. If A1 is more than £30 ignore this line and go to the next.
If(A1<100,0.03*(A1-30)+1.57, If A1 is less than £100 then take 3% of the difference of A1 above £30 and then add £1.57 (which is 5.25% of £30 from the previous line) and now the IF function ends. If A1 is more than £100 ignore this line and go to the next.
If(A1<200,0.025*(A1-100)+2.1+1.57, If A1 is less than £200 then take 2.5% of the difference of A1 above £100 and then add £1.57 and add £2.10 (which is 3% of £100 from the previous line) and now the IF function ends. If A1 is more than £200 ignore this line and go to the next.
If(A1<300,0.02*(A1-200)+2.5+2.1+1.57, etc etc!
If(A1<600,0.015*(A1-300)+2+2.5+2.1+1.57, etc etc!
0.01*(A1-600)+4.5+2+2.5+2.1+1.57))))) etc etc!

When using this in excel you will need to remove the line breaks so it's one continuous formula. It's easier to follow broken up though :)
 
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
 
You must log in or register to reply here.
Back
Top Bottom