Basic Accounting: Creating a Spreadsheet of your Stock

Click image for full-size view

As you begin to accumulate enough books that you don't remember all of them, the prices you paid for them, etc, it becomes necessary to keep an accurate list of your stock and pertinent details.  For this problem, I have used a simple spreadsheet program such as OpenOffice Calculator, which is a free, open-sourced software that I recommend.  Many readers may be more familiar with the very similar Excel Program from the Microsoft Office Suite.  My instructions here work for either program.

I have 17 columns of information for each book, some of which are filled out upon acquisition and some upon sale.

 

Description of the Book

Column A and B (Book Name and Author, respectively) are straightforward and filled out at acquisition--I prefer to put author last name first and put works like "The" and "A" after the rest of the title.

Column C is status, which is a shorthand for me to know what position this book is in.  Is use the letter codes: "A" for Acquired (a book which has been purchased but not put into an online sales program), "AC" for Academic (books which I hope to sell to textbook companies and have not put online), "P" for Posted (e.g. on the Amazon Marketplace), "E" for Ebay (and not Amazon, my usual default), and "S" for Sold.

Column D is the ISBN, which is filled out at acquisition; for books before 1966 I write "Predates ISBN" and for those after 1966 for which I cannot find an ISBN I write "Unknown ISBN."

After these four columns, I typically switch to Columns O, P and Q which are "Type," "Condition" and "Description," respectively.  For "Type" I use letter codes: "HC" = Hardcover, "SC" = Softcover, "TB" = Textbook, "MM" = Mass Market Paperback, "OT" = Other.  Condition Codes are pretty well established (I went over them here), but for reference are "N" = New, "LN" = Like New, "VG" = Very Good, "G" = Good, "A" = Acceptable, and "P" = Fair.  The final section, "Description" is a one to two sentence thumbnail of the book's physical condition.  Here is an example:

"Straight and tight spine. Slight wearing on spine jacket. Name on inside cover"

Descriptions are used in all of your postings and having this here helps you save time.

Calculating Your Minimum Price

Column E is the weight, in ounces, as measured by my kitchen scale.

"E Ship" (Column F) and "E Fee" (Column G) are for "Estimated Shipping" and "Estimated Fees."  This is a rough number that I put in so that I can estimate my minimum price (column M)-which is the lowest price I could charge for the book and still make a profit.  Estimated Shipping is based upon the weight plus a few ounces for shipping material compared to the USPS Media Mail Rates (don't forget to add $0.75 for tracking).  Estimated Fees is based upon the Amazon Estimated Fees and Pricing; as Amazon tends to be the most expensive site I use, I base my minimum prices upon it.

Column H, "Cost," is pretty straightforward: how did I pay for the book?

With Column A - H completed, I am able to calculate Column M (Minimum Price) and begin posting the book online.  Column M actually contains a formula which reads (for the first row):

=SUM(F3:H3;3.99)

The $3.99 is the automatic shipping rate Amazon adds to all books.  As rows F-H are written in negatives, this will typically produce a negative result.  You need to mentally delete the negative signs in Row M which will give you an estimate of the lowest price you can post this book on Amazon and still break even.

With all of the previous columns filled, you have all of the information needed to post the book online and begin selling.

 

After the Sale

Columns I - K are filled out after the sale.  Column I is for the actual internet fees for sale and J is for the actual shipping costs.  Like F and G, they are written in negative numbers.  Finally, Column K is for the sale price of the book.  With this data inputted, you should be able to calculate your profit in Column L.  Here is a formula you can put in that will do it automatically for you:

=SUM(H3:K3)

The only remaining column, N "Site," is for you to note on what website you sold the book as a reference.  Don't forget to change column C, Status.

 

Uses and Other Info

This spreadsheet allows you to keep track of how many books you have pending and how many you've sold (you might find it convenient to add a column for the date sold as well), where you've sold them and what your profits have been.  This can be done automatically for you by putting a little formula into line L2:

=SUM(L3:L244)

These formulas have also been used in lines F2, G2, H2, I2, J2, and K2 to produce ongoing totals of your expenses and profits.

Best of luck!