Bookkeeping, accounting, tax preparation. Fun, fun fun.. not! Unfortunately it’s an unavoidable part of doing business 🙁 I originally used my Etsy Business Planner to keep track of income, expenses, fees, payments, bills etc. but when I hired an accountant a few years ago, they wanted everything digital not pen and paper. So I created some spreadsheets to help me track income, expenses and tax deductions and tested them for over a year. I’ve tweaked them along the way so that they’re the perfect tool for non-financial people like myself, who’d rather spend more time on their business (anyone else just want to make pretty things all day?), rather than the not-so-fun task of accounting.
I could’ve used an accounting program like MYOB but I just don’t find those programs user-friendly. At all. They’re also quite expensive. I considered using an online based accounting system, but to be honest, I don’t know how secure they are and I don’t like that I can only access them if I have internet access. So I chose Excel. I could’ve gone all out and created a ton of fancy spreadsheets with crazy formulas but I wanted to keep them super simple, so that my accountant could simply open them and find the exact numbers they need using easy to follow and logically designed spreadsheets.
I ended up creating 5 spreadsheets to track income and expenses. They were created in Microsoft Excel but you could also use them in Google Docs. I keep them saved in the one Excel file using a separate tab for each spreadsheet.
To enlarge the screen of the video below, click the square icon in the bottom right hand corner of the video (it will say ‘full screen’ when you hover your mouse over the icon). If you miss a step or need to re-watch you can pause, restart, rewind and rewatch the recording as many times as you need!
This spreadsheet is a breakdown of all your expenses so your accountant can classify them correctly. I usually classify where I think they should go, and if I get it wrong and my accountant needs to switch categories, it’s a quick and easy fix. Originally I had this in an Excel file by itself, with separate tabs for each expense category. But there ended up being a lot of tabs and some only had a few lines so it made sense just to combined them all.
The little arrows in the bottom right hand corners of the spreadsheet in the picture below are filters. If you click them you can filter the data so you only see the category you want e.g. Marketing expenses. I then highlight all of the cells for the month and copy that total (if you go to the bottom right hand corner in Excel it will tell you the total $ for those highlighted cells) into the ‘Income and Expenses’ tab (which I talk about below). You could also just keep a running total on that spreadsheet if you prefer.
After using the spreadsheet for a while, I added in a ‘date receipt filed’ column. Because my expenses are usually the same can be similar for me e.g. printer paper, this helps me avoid entering the same expense twice, and so I know when I’ve scanned the receipt so I have a digital copy, and filed away the original paper copy.
I use the notes column for anything really. If there’s an invoice number, I’ll put it in the notes column. If it’s a recurring expenses, I’ll also note that here. Then I can use the filter option to make sure I’ve paid it each month, and to file the receipt if the money for the subscription is automatically deducted from my account. If it’s an expense that was paid in USD using my travel card (so I don’t have to pay international transaction fees as I live in Australia), then I’ll also note that in this column.
This spreadsheet lets you see all of the months of the year on the one spreadsheet (I originally had it split into 12 spreadsheets – one for each month – but it is sooooo much better being able to see everything all at once). I can compare month to month performance side by side and see whether:
- Marketing campaigns were effective
- Peak periods (I sell planners so my revenue typically spikes in December, January and August) – this helps me plan new product releases for these peak periods
- Slow periods (this is when I work on big projects such as ecourses and a new book I’m releasing soon which is basically EVERYTHING I’ve done to grow my Etsy business)
I have the same categories as the tax deductions spreadsheet, and carry across the totals each month. You could also keep a running total in this spreadsheet (but then you’ll have to manually enter everythign into the spreadsheets twice).
I also have separate lines for revenue so I can track how much I’ve made in my Etsy shop versus my online store, and any other income sources such as ecourses.
Formulas will automatically calculate the total income and expenses for each month, as well as the average each month for the year (a big help for budgeting!)
If you have a blog for your shop, earn affiliate income, offer consulting or virtual assistant services etc. this spreadsheet was designed for payments that often have a delayed period from when the income is earned and when it actually appears in your account. I’m an affiliate of a few companies whose products I use such as BackBlaze (automatic file backup) and Photoshop. My favorite column on this spreadsheet is the ‘date income received’. so you can make sure the money actually ends up in your bank account or so you don’t forget to chase it up, especially if you earned it a couple of months ago.
This page automatically transfers the totals from the income and expenses tab. You can record your goal income and expenses at the start of the year and compare these to how your shop actually performed. A graph will automatically show whether you met your goals, fell short or exceed them, so you can track your business’ performance over time.
Use this spreadsheet to keep track of all the craft shows you go to throughout the year. You can record how much you spent, how much you revenue you earned and how much profit you actually made at the end of it, so you can see which shows were worthwhile and which you don’t need to go back to next year.
There’s also a section to record the date you added the mileage to your mileage log. I have a printable mileage log in the shop is you need one 🙂
I hope this post helps take some confusion and overwhelm over the book-keeping and accounting size of running a business. If you’d like to use these spreadsheets for your business, they can be purchased from my online shop or on Etsy. If you have any questions about the spreadsheets, I can be contacted via firstname.lastname@example.org