Spreadsheets for Small Business 101: Expenses
Okay, so you’ve read about how important it is to track everything as you go along, but you’re not sure where to start. Below, you’ll find my suggestions for starting out with spreadsheets to track your sales, running “profit,” and other information that will be helpful as you go along. I am NOT AN ACCOUNTANT. These are merely my suggestions having survived the first year of business and having dealt with taxes and awkward spreadsheets. I take no legal or financial responsibility should you choose to follow my example.
1. Keeping Your Records
This is the most important step, because if you lose the spreadsheets for whatever reason, these records will be how you recreate everything. You will also need these if the IRS comes auditing! Keep everything! Ideally, keep track in both paper and digital form. Depending on the size of your business, you may want a whole filing system or a single binder will suffice. Starting out, I found that the binder system worked better for me because I could quickly flip through things.
Basically, invest in a large binder and a ton of plastic paper protectors. Is it a little wasteful? Yeah. Will it keep all of those odd-sized receipts from falling out and getting lost? Definitely. I think it’s worth it. So, in your beautiful binder, simply stuff all of your receipts in paper protectors in chronological order as you go. Print out the digital ones from online orders and stick them in there too. You don’t want two different sets of receipts here, you want two duplicate sets of receipts. If you order business supplies along with personal items, highlight the business supplies and write down the total for those items only elsewhere on the receipt. In general, highlight the important/relevant information on receipts before you put them in. It will make things easier to find later.
As you are creating this beautiful binder, also create a digital copy. I like an external hard drive because the data won’t get lost if your computer crashes or gets damaged/stolen. Dropbox, Apple’s Cloud, etc. all work nicely too if you prefer cloud-based storage. Flash drives are fine, but I always worry that they will get lost. Still, if you work in many different locations, flash drives might be even more efficient. Wherever you decide to store it, create a naming system that works for you. I prefer to name files by DATE, then COMPANY. This way, they are in the same order as in my binder (chronological).
Truthfully, once you’ve created the system, you won’t use it much. It will be tempting to abandon the project altogether, I know. DON’T! Like I said, if something goes wrong or you get audited, you will be glad you made these two copies. Plus, some accountants may want to see the actual receipts rather than your spreadsheets (which you’ll make next).
2. Organizing Your Data
This step is really more for you than for anyone else; but if you do it right, it can become an invaluable tool for tax time. At the most basic level, it helps you see whether your business is making money in real terms (which is different than whether or not your business “shows a profit” in tax terms). This is the spreadsheet that you will feel in your bones. As the owner, you are elated each time someone buys something and you get money. You are also probably a bit morose when you spend a huge chunk of money on insurance or equipment. All of those things appear on this spreadsheet.
For this, I highly recommend GoogleDocs because of its cloud-based nature. Excel works just fine, but again there is the issue of losing this data. I also find that I work in several locations, so I can update this at a market on my iPad or even on my phone if need be. Wherever you decide to locate this information, back it up regularly. Copies, copies, copies!
Now to the nuts and bolts … if you are at all familiar with using spreadsheet software, this will make sense, but the screenshots below will help you out if you get confused. Basically, create six (6) columns. You will label these something like: Date, Expenses, Payment Received, Profit, Tax Category, and Description or Details. Use whichever terms make the most sense to you (and/or your accountant). Set each column up with the appropriate formatting (using Date formatting for the Date, Dollar formatting for the money categories, etc.).
Next, set up your formulas. You won’t want to have to do all this math yourself, and after a long day of selling, your math skills are probably a little suspect anyway. Now, as sad as it may seem, start with $0.00 in the first cell of the “Profit” column. You haven’t made any money yet, so your profit is zero. What’s even sadder is that this is the last time in a long time that you’ll see black ink. The formula you create for this column looks something like: =D2 -B3+C3. This means that whatever you place into the “Expenses” column will be subtracted from your running total and whatever you place into the “Payment Received” column will be added to your running total. For the first year of your business (at least), this will pretty much always result in a negative number. You can choose whether or not you want it to appear red (I do; it’s motivation).
Now to the tricky part–the “Tax Categories” column. I recommend using categories either provided to you by your accountant or the
categories I will show you below. My category titles come directly from the Turbo Tax for Business and Home 2012 software and from my mother, who is actually an accountant. Personally, I can never remember what all of these categories are supposed to include, so I created a separate sheet in my document that includes reminders of what each of these categories mean.
If you can remember off the top of your head, more power to you! Because I’m a bit OCD, I put them in alphabetical order (whatever order you write them down here will be the order they appear in your drop-down menu later). You will notice that you only created categories for your expenses. This is on purpose. Ways of organizing your Payment Received will be discussed in Spreadsheets for Small Business 102. Basically, the logic behind these categories is that Turbo Tax (and the IRS) categorize some things ahead of time, but each business has its own unique expenses that go into “Other Misc. Expenses.” When you fill out your taxes, you’ll want to type these in separately so the IRS can see that you’re not just tossing a big number in there to get a bigger refund. For my business, those unique expenses are things like “Charity,” “Market Registration,” “Casual Labor,” and “Shipping.”
Once you’ve created all of your categories (with or without your helpful reminders), it’s time to import that information into your main spreadsheet. This is what’s known in GoogleSpreadsheets as “Data Validation.” Basically, what we’re doing here is creating a drop-down menu for you to choose from when you’re labeling each of your entries. Seriously, this will be life saving when you sit down and try to do your taxes because the IRS wants you to group all of your business expenses into what seem like random categories to a layperson. If you try to retroactively relabel everything, you will waste hours (trust me on this). If you set up your records using the system the IRS will use at tax time, it will all be crazy easy. To create this drop-down menu, highlight the entire column by clicking on the top-most cell. Next, go to Data -> Validation -> Criteria -> Items From a List -> Create List from Range -> SheetTaxCategories!A2:A25 -> Save.
Finally, each time you create an entry, give it a description. I personally prefer to write down both the company and a short description of the product for expenses (ie. Soap Making Resource: Soap Molds or Container Supply: Lotion Tins). Mostly, this is so that you can mentally link up the receipts you’ve stored so nicely with this spreadsheet. For payment received, you can label these in any way that makes sense. I prefer to label them based on what I call “Location Type.” For me, these are things like Markets & Fairs, Etsy, Wholesale, Personal Network, etc. If you want to be truly detailed, you could also distinguish between different markets or wholesalers.
One last note on what goes on here … EVERYTHING! PayPal charges you $0.89? That’s a Banking Fee. Etsy Shipping costs you $5.45? That’s Shipping. Your DBA license costs $50? That’s a Business and Legal Fee. You borrow $5.00 from the till to buy yourself lunch at a show? That’s a Draw. You tip a volunteer porter at a market $3? That’s Casual Labor. If money goes in or out, it shows up here. All of it, every day, no matter how small.
3. Tracking Your Mileage
Very few of us own business-only vehicles. If you do, there are plenty of other ways to track this expense for taxes. But if you only have one vehicle that you use for both personal and business use, tracking mileage is definitely the easiest way to go. First of all, you must own the vehicle. It cannot be a vehicle you borrow occasionally or rent (that is a different kind of expense). If you own it, it’s super simple from there. Open up a new sheet in your Business Records document. Name it “Mileage” or something similar.
Create three (3) columns. Label them “Date,” “Event/Destination,” and “Miles/Mileage.”
Each time you drive for an exclusively business purpose (attending a craft show, delivering goods to the post office, shopping for supplies, etc), create an entry. If you make a trip as part of a larger errand trip, only use the portion of the trip that was spent on the business. To calculate your mileage, use either your car’s odometer OR a mapping software like Google Maps, Mapquest, TomTom, etc. Whichever you choose, pick one and stick with it. Do not try to combine GoogleMaps with Mapquest data. You will get the most accurate records if you use the same software each time. Consistency is key.
Now remember, you’ve got to get there AND get home. If you just drive to and from, this is easy. Simply double the mileage. 36.6 miles x 2 = 73.2 miles. If you do multiple stops along the way, simply add more destinations until you’ve created a loop and plug the number into your spreadsheet.
At the end of the year, simply SUM all of the mileage to get your total. This will be a simple number that you’ll enter into your taxes for a deduction.
Doing this means that you do NOT track gas expenses or maintenance costs for the vehicle. You only need to track the miles you drive for the business. Don’t you just love simple?!
For more information on tracking sales with spreadsheets, check out Spreadsheets for Small Business 102: SalesSource: baltimorebumblecrafts.com