How to Do Bookkeeping for Small Businesses - FREE WORKSHEET

It's that time of year! Everyone is getting ready for tax season and I find many business owners don't know simple bookkeeping practices. Then what happens? They find themselves in a downward spiral of shame and anxiety because they have a deadline and they don't know the first thing about taxes and bookkeeping.

Well here is a disclaimer: I am not an expert, BUT I have been doing my own bookkeeping since I first started my business over 7 years ago. I chose this route instead of using bookkeeping software like QuickBooks because I like to KNOW my numbers and working with numbers makes me happy. That is the route I chose, but you don't have to. If you lack time and patience for bookkeeping, then I recommend getting QuickBooks to make your life easier.

However, in this blog post, I am going to show you how to do your bookkeeping with an excel spreadsheet. This is great if you lack the funds for bookkeeping software or just rather do it yourself. 

Just remember, I am NOT an accountant nor is this tax advice. If you are seeking advice for your own business please go to your accountant so you can get the most accurate information for you and your business. 

Ok, let's begin!

START WITH REVENUE

Now that you printed your worksheet, you need to start inputting all your revenue numbers in first.

There are probably a number of places you sell online, and I like to break them up that way so when I take a look at my numbers, I can see where the revenue is being generated from. 

So on your worksheet, you may need to write;

  • Etsy shop #1
  • Etsy shop #2
  • Amazon shop
  • Dot com
  • Holiday Markets
  • Wholesale accounts
  • Etc.,

Again, doing it this way instead of just one big chunk of a revenue number, allows you to understand how money is being generated in your business so you can make decisions based on those numbers. 

So for example, if you see that holiday holidays aren't generating enough revenue based on the time and fees you spend for them, then ditch it. 

If you are interested in finding out what your profit margin should be at click here to read the blog post.
 

Where do you find these numbers?

1. If you are looking for revenue numbers on Etsy, please do the following:

  1. Go to your sold orders
  2. Toward the bottom, download the CSV file
  3. Select Orders for "type" and select the month and year you are looking for
  4. After you download and open the file, the column "x" is order total (which includes shipping and taxes), you will want to add this this entire column up by selecting on an empty cell and typing in the following: =sum( then click on the first cell with the order total amount and drag it down to the last number. 
  5. After you do this, type this: ) And then hit enter.

You have now calculating your true revenue from the Etsy platform.

 

2. If you are looking to do it on Amazon, please do the following:

  1. Go to "view payments summary" on the dashboard
  2. select "Date range reports"
  3. Then select "generate report"
  4. Then select "summary", and the month you want and click generate.
  5. In a few moments you will be able to download the report. 
  6. Add up these two numbers: INCOME and SALES TAX totals

Now you have calculating your total revenue on Amazon. 

 

3. If you are looking to do it on your website, depending on your platform, the steps will vary.

But the same principle applies. Find the reports and add up your TOTAL revenue which must includes sales tax and shipping. 

If you are looking for revenue from other places such as wholesale accounts or holiday markets, it will all depend on which payment processor you used. If you used PayPal, you will need to download the statement for that month and do some simple math. 

 

CALCULATE EXPENSES

This section is the most time consuming and will be broken down into several section, so try to stay with me and focused.

1. Payroll

Let's start with payroll. Depending on what type of business you are will determine how you pay yourself. I personally am a corporation so I am an employee of my company. AT the end of the year, I will receive a W-2. You on the other hand, may just be paying yourself with checks, straight to your personal account. You will need to get with your tax accountant to find the best way for your business to handle payroll. 

Either way, you need to calculate payroll expenses and that seems simple enough. Hopefully you have your own business bank account so you can just pull up the statement for that month and find the payroll expense and input it into your excel spreadsheet. You're done! Just do this for each month and that part of the expenses is all set. 

 

2. Outside Services/Contract Work

There may be times where you need to hire a contractor to do work for you. Maybe a designer that regularly works for you? Or a virtual assistant? A photographer? If this is the case for you, you need to add up those expenses and plug it into this cell under the corresponding month and you're all done! The best way to keep track of this is to make sure everything goes out of ONE account. I still save receipts for everything, but since I charge everything to this ONE business account, my bookkeeping practices have becomes very stress free. I recommend doing this so when you are trying to find expenses for a certain category, you can look in one place instead of 5 different places. 


3. Office Supplies

This category will include everything you have purchased for your office that helps you run the business. 

Things that I buy or that are commonly found in this category are:

  • pens
  • printer paper
  • ink
  • folders
  • notebooks
  • binders
  • tape
  • trash bags (we go through a lot of paper here, lol)
  • trash cans
  • highlighters

Depending on the type of business you're in, you may have more or less. If you are confused about a type of expense and whether or not it belongs on this part of the spreadsheet. To get the full list of the office supplies I order and to see if they are right for your business, click here.

 

4. Cost Of Goods Sold

Costs of goods sold or COGS is the expenses related to making your product. So if you own a stationery company like me, you will probably have the following under your COGS:

  • cardstock
  • envelopes
  • clear boxes
  • ink, etc..,

This also includes packaging for your product and not just the product itself. If you are purchasing things from your local store that have a mix between office supplies and things that go into making your product, I would recommend buying it in two transactions to make it easier. I tend to buy all my supplies for my office in the same places and my materials for my product in the same places so on my spreadsheet I have the list of the vendors on there. You can have the type of material if you wish. If you do it by type of material, then you will need to look at more then your bank statements to do bookkeeping because when you see a charge from amazon, you won't know how much of it was ink for your printer and how much was card stock for the stationery. So to make it easier for me, I just make sure that I purchase on separate receipts if its a different type of expense AND continue to purchase from the same vendors so I am organized. 

 

5. Advertising

If you are new in your business and do not spend a dime on advertising, you can skip this section. But if you use Etsy's promoted listings or advertise on Amazon, you need to fill this section out appropriately. 

Etsy's Ad Spend

If you are looking to find out how much you spent on advertising on Etsy, its simple!

  1. Just go to "your bill" under the finance section of your shop dashboard.
  2. Then select the month you are doing the bookkeeping for. 
  3. Then you will simply see a summary of how much you spent on for promoted listings that month. 

That's it! But here is what I recommend. On the second sheet of the worksheet I gave you, there is a place for you to fill out that ENTIRE bill. Later some of that will go under the expenses of online selling fees, and shipping paid. This is not required by tax accountants but I like to do this to see how much I spent in certain categories. SO the point is? Fill in the promoted listings number on the second tab of the worksheet. 

 

Amazons Ad Spend

If you are looking to find out how much you spent on advertising on Amazon, this is how I do it....

  1. Go to your bank statement and find it there.

Simple huh? I do it this way because Amazon must charge you the ad spend each month as a separate charge so its easy for me to find. 

If you would rather do it in a more official way, then do the following steps:

  1. click on "view payments summary" on the dashboard of your account
  2. then click on "advertising invoice history"
  3. Then click on the invoice for the dates you're doing bookkeeping for to see when it was received (billed)
  4. If it was billed in that month, then just write down that number

You're done!

If you are advertising by other means such as Facebook or Google, I would recommend logging into your ad account and they should have reports to show you how much you spent and when it was billed. If it was billed in that month you are doing bookkeeping for, then thats it! Just write it down and you're good to go.

Now that you input all the amounts for advertising, you want it to be reflecting on the maid sheet under advertising, correct? All you have to do is go to that empty cell where the final total should be and type this: =sum( then go to the cell where the information is and click on it, then hit the PLUS sign and then select the other cell where the other advertising amount is. Keep doing this until you have selecting all the advertising amounts and then select end bracket ) and hit enter. That will bring you back to the first page of the worksheet where the total has been added up for you. 

Still need help with this? Here is a video:

 

6. Car, Delivery, & Travel

This section is used if you travel for you business. So for me, I use it for my airplane ticket to the New York Stationery Show. I also use it for any travel within the city while I am there. I think this area is pretty straight forward. Just be sure to only use this for travel when it comes to your business. And no, you cannot put gas for your car here, even though you have used it to travel somewhere. The IRS gives you deductions if you track your mileage, so be sure to do that on a separate sheet of paper. 

 

7. Accounting and Legal

Again, another simple and straight forward area. I usually use this area only once a year, and that is when I pay my accountant for doing my taxes. However, if you had any legal work done such as; forming an LLC or filing for a trademark, this is the place to put that expense. 

 

8. Telephone and Internet

Here is the thing with this section, you can only write off this expense if you use it SOLELY for your business. That is the case with me. My home internet does not get used unless it is for business. We usually use our phones for personal internet uses. This also applies to my phone (with the occasional call to mom). I solely use it for my business so I use this as a write off. It's best to speak to your tax accountant to see whether you qualify to write this off as an expense. 

 

9. Online Selling Fees

Ok this area is something I made up to make sure I covered ALL areas of expenses and it also helps me see where the money is going. 

I have this broken up into section on the second sheet of your worksheets. There is one for the Etsy billing section AND for Amazon fees, and even one for your dot com. However, you may need to customize this section. Depending on what other platforms you sell on and have fees on. Also, if you use PayPal for invoicing, you will need to include that as a section so you can take into account those fees. 

I also want to point out that Etsy has TWO fees. The first is the Etsy fee and the other is the credit card processing fee. That can be found in the CSV file you downloaded earlier. It is under the column Z. SO if you add up that entire column, that will give you the "CC processing" fees that you see on that second worksheet. You want to make sure to include it in your total when you are adding up all your online selling fees. 

 

10. Shipping Paid

This area is something I like to do just to see where my money is going. It is not required by the IRS to show how much you specifically spent on shipping. 

On the worksheet I provided, I have several areas in the second sheet that you can input shipping for Etsy, Amazon and your website. You may need to tweak this worksheet and add something else to it to make it work for you. For example, if you use PayPal's shipping feature, you may want to add a section for that. 

Looking for the amount of shipping you paid on Etsy?

  1. Go to your bill for that month
  2. Find the words "shipping labels"

That's it! You're done. 

 

Looking for the amount of shipping you paid on Amazon?

  1. Go to "view payments summary" on your seller dashboard.
  2. Click on "date range reports"
  3. Select "generate report"
  4. Select "summary" and the month you want
  5. Download report and go to the section that says "Shipping label purchases" and "Shipping label refunds"
  6. Add those two numbers together and you have your total shipping paid.

That's it! You're done!

 

11. Sales tax paid

Again, another one that is simple and straight forward. I pay my sales tax every quarter so this row will only get filled out when that expense has hit my bank account. By simply looking through my bank statements, I will find it and just type it in there. Boom! You're done. 

 

12. Assets Purchased

This is an important section. I don't know the verbiage but I know my accountant needs to know how much money was spent on assets. I consider my assets to be my computers, printers and office furniture. So I created a separate section to place this in. Be sure to use your bank statement or credit card statement to locate all your major purchases and type it in the spaces provided in the third page of the worksheet. Then you can be able add it all up so you can have to totaled on the first page. 

 

13. Education

Yay! I love this section for TWO reasons

  1. You can write off my courses that you take to help further your business along.
  2. I write off my Audible subscription because I love listening to books that help me be a better business owner.
     

So if you have any business expense that helps you learn in your business, whether it be a new trade or refining your current skills, you can write it off!

 

14. Food

The only time I use this area is if I am traveling for work and need to eat OR I am hosting a Mastermind meet up and needed to feed all of you. I feel like most of us won't need to use this area. But if you did, it seems pretty straight forward. 

 

15. Subscriptions

This section allows you to write in all the subscriptions you have for running your business. Some ideas are:

  1. Business Facebook Groups
  2. Cloud storage
  3. Paypal Payments Pro
  4. Shipping platform service such as
  5. Amazon Prime fee
  6. Marmalead Subscription , etc..,

Be sure to input these numbers on the third sheet of the worksheets and have them broken down that way so you know what you are spending on at a glance. 

 

That's all folks!

I did leave a few empty spots so you can add in your own things that may be relevant to your business. 

But before you go, I want to leave you with a few tips....

  1. Have a business account specifically dedicated to all your expenses and revenue. So when you download this bank statement at the end of the month to do bookkeeping, everything is in one place. 
  2. Be sure to use your statement and mark off things as you have entered them into your spreadsheet. I go by the process of elimination so once all the items on my bank statement have been marked off, then I know I have accounted for all of my expenses. 
  3. Be careful not to enter expenses TWICE. If you are accounting for your Etsy expenses, then you don't need to account for then again when you see the transaction on your bank statement. The same goes for Amazon because earlier I showed you how I take account for those expenses separately. 
  4. I am not a tax attorney so you must seek a professionals help when you have questions related to your business. The purpose of this blog post is to help you get started. 
  5. I do not account for inventory because I go through inventory so fast, so my accountant decided only to account for it as an expense. Again, you need to seek professional advice for tracking and managing inventory. 
  6. If you choose to use a bookkeeping software, please audit it carefully. You never know when it will decide not to import correctly and you will miss a ton of expenses which will cost you money. No system is perfect so always keep your eye on these automated systems to make sure everything is adding up and accounted for properly. It's best to do this at the end of each month while stuff is still fresh on your mind.
  7. You can save money on taxes by filing as an S-corp. Please contact your tax attorney for more information on this.
  8. Please save all receipts and file them by MONTH so you can refer to anything if something seems off or confusing from your bank statement. 
  9. Please be sure to document all miles traveled, you are able to write that off as an expense as well. 

 

GOT ANY QUESTIONS OR COMMENTS? LET ME KNOW BELOW, I'D LOVE TO CHAT!

Oh and I also have a private Facebook group you can join and ask questions and my awesome members and I will answer :) JOIN THE GROUP

CHECK OUT OTHER RELATED POSTS:

 

SEARCH THE BLOG