How to Set Up Your eBay Sales Spreadsheet

Good record keeping is essential for any business. Otherwise, how would you know if you’re really making any money or not?

One of the most inexpensive ways to track your sales is with an excel eBay sales spreadsheet.

Here is a breakdown of the columns your eBay sales spreadsheet should include and why. I’ve provided formulas where needed, however, if you decide not to use some of these columns or you rearrange the columns, the formulas will be different and you’ll need to adjust for accuracy.

Recommended Tips:

Inventory Item Number

If you’re selling a lot of items, you’ll find it’s easier to keep track of everything if you assign your own item numbers and track them in a separate inventory spreadsheet. When an item sells you’ll then be able to find the item easier and then move it over to your sold spreadsheet.

Format & Duration Type

We like to track how the item was listed at the time of the sale. Therefore, we will put “30 day fixed”, “7 day auction”, etc in this column. At the end of the month, we’ll be able to track which format and duration were most effective for us.

State & Country

This is really optional but we like to see where our items are going and which states seemed to be most popular.

Sold Date

This data makes tracking how many items sold on a particular day much easier. It’s nice to watch for patterns, most popular days of the week, etc.

Listing Title

We can quickly see which item sold by looking at the listing title.

eBay Item Number

Yet another way to identify your item sold if you need to find your item quickly.

Final Sale Amount

This the final amount that the item sold for, not including shipping (unless your item was free shipping).

Shipping Amount

Not only will this help you track how much you collect for shipping but also how many of your sold items were free shipping.

Sales Tax

You are charged fees on all money collected, including sales tax. Therefore, be sure to have a column so you can add sales tax to your total amount received.

Total Received

If you have your formula properly set, you shouldn’t need to touch this column.

The formula for this cell should look something like this: =SUM(H6:J6) adding your final amount, shipping, and sales tax together.

See the example on the right…

eBay Final Value Fees

You can also set this column up as a formula. Since most category fees are 9.15%, this is how we calculate the fees across the board.

The formula for this cell should look something like this: =+K8*$L$4 multiplying your total received amount by 9.15%.

See the example on the right…

PayPal Fees

You will need two columns for PayPal fees if you sell internationally because the fee structure is different based on currency.

If your item sold in the USA, the formula for this cell should look something like this: =+$N$2+SUM(K6*$N$3) multiplying your total received amount by 2.9% and adding $0.30 transaction fee. Then you’ll need to simply put 0 (zero) in the international column.

See the example on the right…

If your item sold internationally, the formula for this cell should look something like this: =+$N$2+SUM(K6*$N$3) multiplying your total received amount by 4.4% and adding $0.30 transaction fee. Then you’ll need to simply put 0 (zero) in the USA column.

See the example on the right…

Listing Fees

If you paid any additional fees for your listing (insertion fees, listing upgrades, etc.) record them in this column.

Actual Shipping Cost

This is where you need to record how much you spent to ship the item.

Delivery Confirm & Insurance

Create a separate column for each of these to record any additional shipping upgrades, if needed.

Product Cost

You should have a separate inventory spreadsheet where you record how much the item cost you to buy. You want to record that amount in this column.

Total Costs

Now we’re going to use another formula to add all these columns together and record all of our costs.

The formula for this cell should look something like this: =SUM(L6:S6) adding final value fee, PayPal fee, listing fee, actual shipping cost, delivery confirmation/insurance, and product cost.

See the example below…

Sales Tax (again)

The reason why sales tax is in here again is because we showed collecting it but we’re not going to be keeping it as part of our profit… we’ll be paying that amount to the state. Therefore we need to subtract that amount from our total received so that our profit isn’t skewed in the next section. You can set up this column to automatically populate what you typed earlier by using a formula similar to this: =+J6

See the example below…

Profit/Loss

It’s finally time to find out if we made a profit or loss.

The formula for this cell should look something like this: =+K6-T6-U6 subtracting the total costs and sales tax from your total received amount.

See the example below…


Profit Margin

To calculate your profit margin, you’ll need a formula that looks like this: =+V6/K6 dividing your profit/loss amount by your total received.

See example below…


Return on Investment (ROI)

To calculate your ROI, you’ll need a formula that looks like this: =+V6/S6 dividing your profit/loss by your product cost.

See the example on the right…

 

We hope this helps you determine which columns you should include on your sales spreadsheet.

Please let us know if you have any questions or run into trouble setting up your spreadsheet. We’re glad to help!

About the author

The Family Pickers