Basic Excel Accounts and Book Keeping for eBay Sellers

Views 714 Likes Comments Comment
Like if this guide is helpful
This guide is designed to assist you to set up basic Accounts & Book Keeping for eBay Selling.

Although the guide is designed in mind for users intending to use a spreadsheet application you could easily apply the same methods to paper bookkeeping. It will look at

1) Creating a Spreadsheet
2) Sales Ledger
3) Purchase Ledger
4) Mileage
5) Update Regularly

The solution is intended for eBayers who are trading as sole traders and not VAT registered. Please note the advice offered is general and it is strongly recommended that you discuss your record keeping methods with your accountant to ensure it fits your requirements. However the solution offered should be sufficient for new eBayers starting out.

1) Creating a Spreadsheet

Firstly create a new spreadsheet and save it with the name "Accounts" in your documents folder. You will need to create three worksheets (If you are using MS Excel there will be three be default). Rename the sheets from Sheet1, Sheet2 and Sheet3 to Sales Ledger, Purchase Ledger and Mileage. (in Excel simply rightclick the tab at the bottom and select rename).

2) Sales Ledger - Money coming into your business

On the top row of the Sales Ledger Worksheet enter the columns Date, Item Number, Method of Payment, Sale Price, Postage Price, Total Sale Price, and PayPal Fee. You will probably want to adjust the height of the first row and underline it by putting a bottom border to separate the row from the rest of your worksheet

The Sales Ledger is where you will enter everything you sell into your accounts. We'll look at each column separately:-

Date - The easiest way to keep your books is using a method called "Cash Accounting". With Cash Accounting you ignore the date of the sale, or the date an invoice to you was issued. You track all payments in and out of your business on the date you received payments, or paid for purchases. This is a recognised method by Revenue & Customs and accountants and removes the worry of entering a sale into your accounts and then the buyer is a Non-Paying-Bidder. Because you only enter sales the day you are paid you only have to enter credits if you issue a refund after receiving payment.

Item Number - Normally this will be the eBay item number. You may occasionally have sales outside eBay (maybe to friends or family). In this case use either a PayPal Transaction ID, if you create your invoices use the invoice number, or get a simple receipt book with numbered pages, write a receipt and enter that number.

Sale Price - This is the amount you receive for the item only (not postage!)

Postage Price - This is the amount you receive for Postage Packing and Handling. It is important that you separate this cost as in the future you will want to analyse what you charge for postage and compare with what you pay for stamps, courier and packing supplies such as labels, bubblewrap, boxes, packing peanuts jiffy bags etc.

Total Sale Price - This is the total amount that the customer sends you. If there is a cost to receiving payment (such as with PayPal) you enter the total before deduction of costs, NOT the amount you actually receive after deducting costs.

PayPal Fee - You will need to add up all your PayPal fees to enter into your Balance sheet (if you create one) or direct onto your tax return. whilst you are in your PayPal account entering the other information about the sale onto your spreadsheet it is an ideal time to record this, rather than having to go back at a later date.

We'll now look at the sample data entered onto the sales ledger pictured above

The first entry (Row 2) is a sale, note the PayPal fee is entered as a minus as it's a cost, not a payment. note the use of PP as an abbreviation for PayPal, other useful abbreviations are cheq (Cheque) as in the second entry, PO (Postal Order), Cash, (real money), BT (Bank Transfer), NO (Nochex), CC (Credit Card), ES (Escrow) etc.

The third entry (Row 4) is a refund. When you issue a refund enter it as minus amounts, this means that when you total your columns you will end up with the exact amount you have received less the amount you have refunded. The one difference is that the PayPal fee is now entered as a plus - PayPal refund their fee if you refund your customer so although the refund is money out the fee is actually money in!

It is very important that you get in the habit of entering this information DAILY. It's very easy to fall behind with your accounts but if you religiously set aside time to update them you'll find Book Keeping becomes second nature. A good time to update your accounts is the same time as you print out your orders ready to pack!

3) Purchase Ledger - Money going out of your business

On the top row of the Purchase Ledger Worksheet enter the columns Date, Purchase, Source, Ref, Stock for Sale, Stationary / Other, Postage / Courier, eBay / Banking Fees and Rent / Phone / Utilities. You will probably want to adjust the height of the first row and underline it by putting a bottom border to separate the row from the rest of your worksheet

The Purchase Ledger is where you enter everything you spend on your business.

Date - As with the Sales Ledger if you use Cash Accounting enter the date that you actually pay for the purchase or service

Purchase - Enter a brief description of the item or service you paid for

Source - Enter the name of your supplier

Ref - This is a reference for your purchase. It may be the invoice number from your supplier, an eBay item number if you purchased on eBay, a Direct debit number for your phone bill etc. Occasionally you won't receive a receipt with a number so you may want to sequentially number these yourself and file in order so that you can easily locate them in the future.

You will only use one of the remaining columns of the worksheet per purchase. In theory you could simply have a single column "Amount" but it's preferable to split the columns so that you can total them and analyse your business expenses in the future. Simply enter the amount you pay in the appropriate column for that purchase.

Some example purchases are entered, but similar to your Sales Ledger if you receive a credit from a supplier enter this the day you receive your cheque as a minus amount. In the Purchase Ledger a positive amount is money you pay out, a negative amount is money you receive!

4) Mileage

For most small businesses it's not tax efficient to have a company car, it makes more sense to use you own car and simply claim an allowance for mileage.

Each trip that you make for your business enter the Date, Destination, Reason for the trip and the round trip mileage.

5) Update Regularly

Which ever format you decide to use for book keeping the most important thing you can do is to update it regularly. As soon as you fall behind you'll find it harder and harder to catch up. Putting a good accounting system in place will minimise the amount of time you spend book keeping but it will only be as good as the data you enter! Don't forget to keep all the invoices and receipts for purchases, and file them in date order - that way if you have a tax inspection or your accountant has a query you'll easily be able to locate them!

There is another  eBay Guide covering Book Keeping, Tax and Business Registration

The best advice regarding book keeping is see a professional. Generally an accountant will save you more money and time than they cost. The book keeping methods described above are as a guide only and may need adapting to your particular circumstances.
Have something to share, create your own guide... Write a guide
Explore more guides