posted on February 15, 2001 12:21:06 AM
Trying to make a spreadsheet to keep track of my book keeping. I am stuck when it comes to making Excel automatically subtract the Ebay fee for each item. The listing fee isn't hard to figure out, but they do all of that goofy stuff to get the FVF. Has any one found a way to do this? Is there some magic mathmatical formula?
I'd like to see the amount of profit I make on each item after all of the fees.
posted on February 15, 2001 01:28:03 AM
I have one set up that lists the auction #, the description, the date listed, date ended, final bid price, shipping charge, Cost, insertion fee, final value fee, actual shipping cost, additional processing fees, and then your total profit loss.
I had asked for one earlier last month but didn't find one that I liked so I made up this one.
Only real formula needed is the FVF and the rest is just adding and subtracting the different cells. I have my FVF set up as:
WHERE Bid Price is the CELL that contains your Final Auction Bid Price
You should be able to just copy and past it into a cell and it should work when you change Bid Price with your actual Cell that contains the bid price
BASICALLY, this says if the cell that has the final bid price is < $25.00, then take the bid price time 5%, otherwise take $1.25 + ((the final price - $25)* 2.5%)
Hope this helps you out...Only works up to the $1000.00 mark, didn't throw in the other % scale.
[ edited by moparmaniac on Feb 15, 2001 01:29 AM ]
[ edited by moparmaniac on Feb 15, 2001 01:30 AM ]
posted on February 15, 2001 01:42:53 AM
I use MSWorks database. It doesn't have a lot of bells and whistles. For example, doesn't calculate net sale minus listing + FVF.
One feature that's particularly helpful to me is to see the transaction status at a glance. My database tells me whether payment is overdue or deadbeat. The formula is below.
Basically, when the data is entered, the transaction status is set to "payment due." If payment is not recorded within 21 days, status goes to "overdue." At that point I send an NPB alert and record the date. If payment is not received within an additional 10 days, transaction status automatically goes to "deadbeat." At any point, when payment is recorded, status goes to "paid."
Here's the formula. Funky but it works. ("Amt. Recd.," "Date Ended" and "NPB Alert" are all different fields in the record.)
posted on February 15, 2001 07:31:25 AM
I have columns for Payment received and Date Shipped. I fill those in when payment comes in and I packthe item, copy/paste the info from my main spreadsheet to my invoice spreadsheet for printing, and then using the auto-filter, filter my main sheet to show only the lines that have blanks under "payment received". Since Close Date is my first field, I can easily see the oldest uncompleted transactions in a glance. Should I need to send followup reminders or NPB alerts, I record that information in the Date Shipped field, so the line remains "blank" in the payment received field, and I can track my activity on the account.
Then at the end of the month, I unfilter the list and re-filter to isolate the auctions closing that month - so I can do the bookkeeping entries for the month. All of my auction info is in that file, and I pull the numbers out and into my accounting software to see how I'm doing.
posted on February 15, 2001 10:14:20 AM
Thanks for the help guys! It works great! One other thing though, is there a way to make the formula move down the list? Do I have to change the formula for each row (D3, D4, D5, etc) to have it ouput on E3, E4, E5, etc? Or is there some way to automate this so that the formula adjusts itself for whatever row you are working on. Thanks again! You guys have been a great help! I'm very new to Excel.
posted on February 15, 2001 10:26:47 AM
I have the typical spreadsheet with all the bells and whistles (I write spreadsheets for a living)..but nothing beats at a glance color for seeing who has paid and who has been shipped. I make the first cell orange if buyer has paid and second cell purple if item has been shipped.
posted on February 15, 2001 12:15:05 PM
If your formula uses floating references, you can copy/paste the formula down the column just fine.
For example on row 5 in column t you have the following formula =d5*.05
d5 is a floating reference. If you click on the T5 cell, select Copy, go down to T6 and hit paste, the paste should make the formula '=d6*.05
If the d5 was $d$5, that's a locked reference, meaning copying/pasting will NOT change the references to the new corresponding cells. Get rid of the $'s in the reference, and you can copy it just fine.
posted on February 15, 2001 06:12:18 PM
If you want to copy the formula thru an entire column or row. Just select the entire column/row and the select "Edit" -> "Fill" -> "Down" or "Right" depending on whether it is a column or a row...
posted on February 15, 2001 08:04:07 PM
After looking at how your formula's were made, I was able to come up with a simple one to calculate the listing fees based on the start price of the auction. Here it is:
Change the F2 variable to match your needs, also the 1000 can be anything between 200 and up, depending on how high you start your auctions. Going to try to make one for Paypal now, I'll post it here if I'm successful. Thanks again!
posted on February 15, 2001 08:41:56 PMmusicman1313
You could do it the easy way and get eBay's new Seller’s Assistant Pro (Old AAPro) for only $15.99 per month ($191.88 per year) and it totals it all up for you on each item you sell in a profit/loss layout.
))