Book Blogs & Friends

January 11, 2011

ReSelling Tool - Excel

New Files for 2011

I will be completely honest with, bookkeeping as a reseller is challenging. It's not straightforward in any way possible because we are dealing with buying products that we often don't get receipts for. Small businesses usually make purchases and enter data into their computer. When they sell product it is relatively easy data entry as well. At the end of the year it's a pretty simple equation of math.

With ReSelling it gets a tad complicated. For example, last Saturday I went to an estate sale and bought $25 worth of inventory. I didn't get a receipt since this is not common practice at garage sales, yard sales or family run estate sales. Many items were not priced which meant that either I had to write them down right away or memorize them. Luckily for me my short term memory is pretty good, but it does require me to process everything rather quickly when I get home.

Early on in my career as a ReSeller I knew that I needed something to help keep track of all my data. Dave being the Microsoft Excel wizard that he is, created a spreadsheet for me. I've used this for the last four years and let me tell you it's made my life much easier. Especially when tax season comes around and I have to hand my numbers in to our accountant.

My Business Excel file includes 8 tabs (at the bottom of the the first page). Each tap represents a part of my business. These are:

eBay

Books (through Amazon, Alibris, Barnes & Noble)

Etsy

Fees & Subscriptions

Gas Mileage

Inventory

Business Expenses

Travel Expenses

Let's take eBay as a first example. Within the eBay tab I have 11 columns. Each one has a name:

Item Name (ex.Hugo Pott Spoon Lot)

Cost (Price I paid)

Sold (Final sold price on eBay)

Shipping Cost (what I paid to ship the item)

Shipping Paid (what the buyer paid for shipping)

Insurance (if I pay to insure the item)

Paypal Fee (I add the fee that Paypal charges for the transaction)

eBay Listing Fee (fee eBay charged to list the auction)

eBay Final Fee (percentage of selling price that eBay charges)

Purchased From (I like to make a note of where I purchased the item)

CA Tax (amount of California tax that was charged)

When an auction is listed I add the name of the item, cost and listing fee to my excel file. If the item sells I will add the rest of the details when I ship the item.

I have similar columns for Books, Etsy and this year, my Little Shop.

My Fees & Subscriptions page is quite different. The columns are:

Venue

Subscription (monthly or yearly)

Months Jan-Dec (each column header has a different month)

If I use Endicia (my postage program) as an example, I have $15.95 entered under every month. If I paid something by yearly subscription I would enter it once during the month that it was paid.

The Gas Mileage Tab has these four columns:

Where/What (I add what town/area I went to and what I did there-some days it's thrifting, other times it is dropping off packages at the post office drop box)

Date

Miles Start

Miles Finish

It's best to have a book in your car to write down this information. It's really important to collect this data since gas prices are rising. Some new reports have suggested that it might be $4 by summer.

Business Expenses has five columns:

Item Purchased

Cost (price paid)

Date

Reason (Since I have a few businesses I note what I will use it for like Bookselling, Filing, ATG)

Where (Many of my supplies are bought at yard sales - since I don't have a receipt I like to make note as to where it was purchased)

Inventory includes fifth columns:

Item

Cost

Purchased

Destination

Sold

As an example, I'd add Kitschy Trivit under Item, $2.00 under my cost, Black Cat for the Estate Sale company that I purchased it from and Etsy under Destination. I have the inventory file so that I can make sure to know how much I paid for an item. I would then put it in a box marked "to be photographed" and eventually list it on Etsy. When it sells I then can find it in excel (using the FIND function to search for the keyword that I am looking for) and make sure the correct info goes into my Etsy file page. I would then type SOLD on the fifth column so that it is easy to know how much inventory I have left at the end of the year.

Travel Expenses includes food, hotel, rental car, transport costs and includes five columns.

Item

Cost

Date

Where

Reason

Last year I went to a bloggers conference in San Francisco. If I use a dinner out as an example I would add "dinner at so and so restaurant" under item, $18.50 under cost, October 8th for the date, San Francisco under Where and Blogger's Conference for Reason why. All receipts would go in my Travel Expense file in my filing cabinet.

I can't stress enough how important it is to gather as much information for this line of business. If you do get audited you want both a paper trail and great record keeping files such as this one. Excel is really good to work in because you can let it do the math for you. I'd highly suggest taking an excel class at a local community college if they offer it.

Again as a disclosure statement, I'm not an accountant or a lawyer. This is merely how I keep track of all my expenses and sales. I did want to share this with you to inspire you to make it a goal to begin the bookkeeping side of ReSelling.

If you want to save time I'm offering this ReSelling Excel File for $8.00. The file (emailed to you) would include all the tabs and columns mentioned in this post as well as an additional Misc tab in case you have another venue of ReSelling that you do. It would require you to have a version of Excel on your computer. You would simply Save the file to your computer and it would be ready to use immediately.

If you are interested in purchasing this please contact me through the Contact button.

Wow, this is a really amazing run-down of the records side of the business! I was going to write up some posts detailing the records we've kept, but you've covered nearly everything.

We pretty much have every single excel tab you have (we use a combination of Filemaker Pro and excel). A few things ppl might want to also include - for inventory, it can be helpful to include the "box number" or "location" in inventory, especially if you have a lot of items stored away. Also, I've found it helpful to include "condition", "weight", and "dimensions". Including this info right away when you get home from purchasing saves time listing later on.

We also do purchases for Ebay, Etsy, etc. all on one sheet, with a column for "sold at". Probably should check w/ tax guy, but we do all the fees associated w/ all the diff selling venues in an "Expenses" sheet. Because believe you don't have to tie the listing fee to a particular item - all that matters is the total monthly fees. This is helpful, so you don't have to keep track of relistings, moving an item from Etsy to your main shop, etc. But, I'm not an accountant either...

Wow...these are such amazing tips. Thanks so much for your insight! This is my 1st full year as a reseller & I'm struggling with the book keeping aspect of the biz so this post was definitely an inspiration.
Enjoying your blog & thanks again!
Cheers...Lara

I am impressed by your organization! I know I don't write off half of what I could if I'd only keep track, but then it would probably show my "business" at a loss and there are only so many years the IRS will let you do that ;) I really stopped by to make sure that you received the e-book and candles that you won in my give-away last month. Hope you are enjoying everything!
:)Heidi

Thank you for sharing so many details. This has been on my mind a lot since the first of a new year. I would love to have a system in place before I even get to listing items for sale. Van shared her system a short bit ago and the Excel link will brush up those dormant skills I had after a class too many years ago.
With working on my massive clean up efforts I am uncovering many old "treasures" from a previous lifestyle that have to go.

Thanks for this, Selena! I'm collating a number of ideas for tracking things and planning to rope my darling husband into helping my work out the Excel formulas to make my own. I really appreciate you sharing yours, and the comments are helpful, too.

Thanks for these great tips Selena! I too would like to know what I should do with the items I'd like to sell but did not keep the receipts for when I bought them. I know about what I paid for them. Should I just add it to spreadsheet and put them up for sale? Thanks!

I am looking for a professional, paid, course in Excel that will be thorough and explain all of the functions needed for financial reporting. I do not need VBA-- but in depth training of sorting/pivot tables/etc are needed. A full one, or two day course is what I envision.