In addition to a filing system for paper documents and a system to track mileage, your business records should include a way to track financial information, tax categories, and inventory as well.

Tool #3: Set up an Excel spreadsheet to track all of the business numbers including book inventory, mileage, checking account balances, and expense categories. Okay, I know that sounds complicated but don’t worry. I’ll walk you through exactly how to set up a similar spreadsheet of your own. (Or you can download one here and get a jump start on the process.)

Why bother to put all these numbers in one place? To make filling out your Schedule C tax form a walk in the park. You could do things the normal way with a checkbook register that you balance regularly and a stack of receipts to track down, sort, and add up at the end of the year. Been there, done that. Not to mention that my totals never seem to add up the same way twice taking up more time and raising my blood pressure. However, with all the numbers in an organized spreadsheet, Excel will do the math for you.

First, open a new file and save it as something profound like “Business Records.” (I’ll give descriptions based on the Excel program, but know that other spreadsheet software has similar features you can adapt on your own.) In the first cell, add a label like “2017 Business Records” for clarity. As the years go by, you can either add additional sheets to this workbook or insert new columns at the beginning in order to start recording a new year’s expenses. That makes a label important later on. Here is the additional reading support for the labeling the product.

Skip a line, and then in row 3, put the following labels into individual cells across the way: date, type, description, amount, income, office, postage, website, advertising, training, travel, mileage, inventory. I have a separate column for each book’s inventory.

The point here is to create categories for every business expense to go into and those categories should easily translate onto the tax form. While postage is considered an office expense, I like to see how much I’m spending there separate from things like ink, paper, binders, and colorful sticky notes. Same goes for website expenses as a subset of the overall advertising category which also contains promotional items like business cards, bookmarks, and online ads. As you do your taxes, you may see the need for a contract labor column if you hire a virtual assistant, editor, or cover designer. I don’t claim my spare bedroom office on my taxes or depreciate the value of my laptop simply because it’s a hassle and I honestly use those for my day job and personal use as well. If you wish to claim those expenses, create a column to track the numbers. And once your income grows to the point you need to send quarterly tax payments, create a column to track those payments as well.

[Tweet “Can tax time ever be simple? Learn how! #BuildABusiness via @CandeeFick”]

Once your categories are in place, it’s time to make the spreadsheet pretty. Highlight the row of labels and add a bottom border. Make the labels bold type and adjust the width of the columns so they fit. Jump down about twenty rows and add another bottom border as a way to separate the individual items from the totals we will add later. Using that same highlight-and-border concept, add a vertical line between the “amount” and “income” columns and also between the “travel” and “mileage” columns. This should divide the spreadsheet into three sections: the checkbook, the expenses, and the other numbers.
The next step here is to format the cells. Highlight the cells in the date column and right click to format them to show your preference, then format the amount column and all of the next section (including the first cell below the total line) as currency with two decimal places and negatives shown in red and/or with parenthesis as you prefer, and the last section as simple numbers. When you insert additional rows as needed throughout the year, this formatting will now apply to each inserted cell.

Once the cells are pretty and formatted, it’s time to add a simple formula to total up each column. In the cell at the bottom of the “amount” column in the first section, type everything between the quotes: “=sum(D4..D24)” where the D represents the column and the numbers represent the first and last cells to be added together. When you insert rows between those two points, the formula will automatically adjust. Now copy and paste that same formula into each cell across the row since we will need a total for each column. (It will smartly change the letters for you as you go.)

Now that the spreadsheet is ready, it’s time to enter the numbers for the current year. (Ideally, you could start this process January 1st, but if it’s later into the year, you’ll need to do some detective work to track down the income and expenses.) The first section is treated like a checkbook register with the date, type of transaction (debit, check, deposit, etc), description, and amount. If your first line on row 4 is labeled as a beginning balance and you make sure to enter each new amount as a positive or negative number, then the total at the bottom of the column should be the same as your checkbook balance. This makes it easy to double-check and reconcile your checkbook with the bank.

So far, this seems like a glorified checkbook, but now it’s time to start making your tax preparation easier. For each amount entered in column D, place that same number into one of the columns in the next section. Was that an advertising expense or a professional membership? A writing class or craft book? Ink for your office printer? Income from a royalty check or private book sale? Make the decision now about how to categorize the expense and you will save tons of time at the end of the year trying to remember. And if you can’t categorize it, perhaps that’s a good reminder to make sure to only use your business account for true business expenses. Ideally, every entry in the checkbook section will have a number in the tax category section too.

Remember those MapQuest mileage calculations? If the mileage corresponds with a Post Office expense, just add the number of miles on that same line. If the mileage does not easily correspond to an expense, just insert a row into the spreadsheet for the appropriate date, add a description of the trip’s purpose, and add the mileage. Without a dollar amount to enter, it won’t affect the checkbook balance but you’ll still have an easy total of miles driven for business purposes.

The same strategy works for book inventory. When you get author copies, add a description and the number of books. If you paid for additional copies, use that expense line to record the number of books. Then do a little quick math to calculate your “cost of goods” and record that number a few cells below the totals line. (This number multiplied by your inventory will be important for your taxes.) When you send books out as prizes or to reviewers, add a description and negative number for the inventory. If the inventory changed thanks to the sale of a book or the entering of a contest, record that inventory change on the same line as that income or expense. Last point, when you start the current year’s spreadsheet with a beginning balance line, use that same place to record the beginning inventory since that number will also be used for taxes.

Congratulations! You are now an organized writer with detailed business records ready to conquer those tax forms.

(NOTE: If you found this post helpful, pass it on! The entire blog series is also contained in a single book here.)

The Author Toolbox: Keeping Excellent Business Records (Part 2)
Tagged on: