How to QUICKLY Catch Up On Your Etsy Bookkeeping Without Missing Major Tax Deductions

As I am writing this post there are 42 days left to get your taxes done for 2020 and if you are like some of my Etsy and Handmade students, I know that you have not even started doing the bookkeeping for your Etsy or Handmade Store yet…

Well have no fear…. I am here to tell you how you can catch up on your bookkeeping SUPER fast and do it WITHOUT missing MAJOR tax deductions. 😊

Now, before I get into the goods, I NEVER, EVER recommend waiting until the last minute to get your bookkeeping done. In fact, I recommend doing it once a month and there are two reasons I recommend this.  

The first reason to keep up with this monthly is to avoid this feeling of impending doom that you may have right now. There is nothing worse then staring down hundreds and thousands of transactions and different bank, credit card, PayPal and Etsy reports, trying to figure out just exactly where to start.

The second reason and probably more important reason to do this monthly, is that when you do your bookkeeping every month, you will actually be able to SEE inside of your business.

What I mean by “seeing” inside your business is that once a month when you sit down to do your bookkeeping you will be able to produce USEFUL and INSIGHTFUL reports that can tell you how your business is doing and use these reports to GROW and SCALE your business.  

In my opinion, having knowledge about YOUR business is what separates the shops barely holding on and the shops that are thriving. 

So, if you are one of those sellers who likes to wait until the last minute and you have not done your bookkeeping ALL YEAR and want to get prepared for taxes here is how we can do this… (but if you’d rather me help through this, check out my courses for Etsy sellers here.)

This whole process is going to come down to what system you want to use to get your bookkeeping done.

As long as I am alive, I will ALWAYS recommend using an automated and cloud system like QuickBooks Online (especially the self-employed version for Etsy Sellers). I personally believe that using an automated system like QuickBooks Self Employed or QuickBooks Online is THE easiest, fastest and most ACCURATE way to keep track of your financial data.

But with that being said, I do not think I could convey how to get caught up quickly in this article using QuickBooks Online (I do show you this in the course though 😊), so I am going to resort to using Microsoft Excel or even Google Sheets and tell you how to catch up quickly, as much as it pains me…

Now just follow along STEP by STEP so that you can catch up quickly…I am going to break this down in steps for you… so what better place to start then with Step 1

Step 1. Gather Your Financial Data

The most important part of catching up on your bookkeeping quickly is first to make sure that you gather ALL of your financial data. What I mean by this is that ANYWHERE business activity takes place, you will want to make sure that you have it at your disposal as we walk through. The way that we can do this is be just being aware of ALL of the accounts that we will need to account for. Some common places where your data will be are the following:

• Etsy Data (shows all of your Etsy Sales, Etsy Fees, Shipping, Advertising)
• PayPal Data (shows all of your PayPal Sales and PayPal Fees, maybe even some expenses if you “pay” out of PayPal)
• Bank Account (shows all of your business expenses and potentially some personal expenses and depending on where you sell will either show you your TRANSFERS from PayPal and Etsy OR it will show you your Sales)
• Credit Card Account (shows all of your business expenses and potentially some personal expenses)
• Venmo, Cash App, Stripe, Square (will show all of your sales if you sell “outside” of Etsy or on your own site)
• Any other accounts that you make financial transactions with…

Now, a VERY important thing that I emphasize in my course and in this article will be this; PayPal, Etsy, Venmo, Cash App, Stripe and Square should be treated like BANK ACCOUNTS.

I will repeat this, PayPal, Etsy, Venmo, Cash App, Stripe and Square should be treated like BANK ACCOUNTS!

I will explain what I mean by this here using Etsy as an example…

When you sell a product on Etsy, the money that you receive goes INTO Etsy and THEN the money transfers TO your bank account.

So, let’s say that you sell a cup for $10….

When someone pays you the $10 it goes into Etsy’s account AND THEN Etsy takes it’s cut and it also takes any other fees that you have charged and then they TRANSFER you the NET amount.

Does that make sense?

So, of the $10 of sales that you have, only about $7.50 makes it into your bank account.

This is VERY important to understand as I continue to show you how to do this!

Alright, so Step 1, make sure that you gather all of your financial data and BE AWARE that Etsy, PayPal, Square, Stripe, Venmo and Cash App should be treated like BANK ACCOUNTS…the sale takes place INSIDE each of these accounts and then is TRANSFERRED to your ACTUAL bank account.

Step 2. Download ALL of your Financial Data

Ok, so now that we are aware of ALL of the financial data that we need to gather, the next step in this process is to download ALL of this information into a spreadsheet.

This step is pretty easy but there are a couple of things to be aware of…

All banks, credit cards and payment processors will allow you to “export” your financial data into one of the file options above. Typically, what you need to do is search on your institution’s website for the area where it shows you all of your activity for a given time period.  In this case, the time period we want to see is from “1/1/2020 to 12/31/2020.”

Below is an example of how you can find this “export” area on a Chase Bank Account

So, you will want to find this area of “exporting” of ALL of your accounts.

Now, since I specifically deal with a lot of Etsy Sellers, here is the report you will want to download in Etsy. All you have to do is go to your “Shop Manager” then “Settings”, then “Options” then click the “Download Data” option scroll down to where it says “CSV Type” and select “Etsy Payment Sales” and a pro tip, you can actually download the ENTIRE year if you just select the year and leave the “Months” field blank. Here is what it looks like…

You just simply want to follow these steps to download ALL of your financial data for ALL of your accounts. Again, each account that you have will give you an option to export or download all of this information into an Excel, Google Drive or Numbers format for a given period of time, in our case “1/1/2020 to 12/31/2020”…

Just another pro tip here, please MAKE SURE that when you download the information that you are downloading the ENTIRE year, I have seen people miss months and months of information because they did not download the right information…just another reason to do this monthly 😊

On to step 3…

Step 3. Organize your Financial Information

Ok, so now that we have all of our financial information downloaded into an excel, google sheets or numbers file the next thing we want to do is we want to go through each one of these files and begin to organize them. This is the longest step of the process and probably the most tedious, but if you can get this done and follow along with me, you will be well on your way to completing your entire bookkeeping for the year in record time.

I am going to share a screen shot below, but there are some excel “tricks” that will make your life WAY easier when you are on the “Organizing” step.

At the end of this exercise, we want to organize it in a way so that we ONLY have three columns of information. We only want to have:

1. The date
2. The description
3. The amount (positive or negative)

When you download the information from your bank it will spit out  5-6 different columns most of them are useless to us… all we really want are the date, the description of the transaction and the amount. Here is what Chase “spits” out when I export the data…

Can you see how there are rows like “details”, “type”, “balance?”

We do not need any of these, all we really need is just the “Posting Data,” “Description” and the “Amount” columns.

Delete all of the extra columns in here that you do not need.

After I do that the next thing I want to do is the put a filter on the headers or “top row.” Now, for anybody unaware of this I am going to insert a screen shot below, but you will want to put a filter ON EVERY one of your downloads, it will make your life WAY easier once we move onto Step 4.

Now, all you need to do is repeat this process for EACH of the accounts that you have and again the two steps to do this are…

1. Remove all columns except for date, amount and description
2. Add a filter to the top header where it says date, amount and description

Before I move onto the next step, I want to make you VERY aware of something and this is VERY important…

Do you see how in the excel above all of the deposits are positive numbers and all of the expenses are negative numbers? Some bank accounts when you export will split out the deposits and the expenses as TWO separate columns. This is NOT good for us so if you fall into this situation what I recommend is putting a filter on the columns and then sorting by the deposits and by the expenses and making the expenses NEGATIVE numbers.

What this does is it helps out in Step 4 when we go to actually start “naming” transactions.

If you want further instruction on this or on any of this, check out my website where I have videos of EXACTLY how do to this…

Step 4. Naming the Transactions

Ok, so if you are following along, we have now completed steps 1, 2 and 3 of the process which were gathering, downloading and organizing all of the data that we will need to complete our bookkeeping in record time!

The next step in this process is that we want to begin to “name” the transactions in each excel spreadsheet and let me explain what I mean by “name” …

To me, “naming” means giving each transaction(s) on the spreadsheet a consistent and appropriate “category.”

We want to give the individual transactions names so that at the very end of this exercise we will be able to say I have “x total amount” of “sales”, I have “x total amount” of advertising expenses or I have “x total amount” of “office supplies.”

People can and do get VERY tripped up on this part of the process. Some people want to go in such detail that they will have 150 different “buckets” or “categories”. Some people are so vague that they will only have 5 “buckets.”

Here is what I recommend…

“Name” the transactions however YOU see best. If you just want to get this over with quickly, call ALL of your Amazon charges, Office Depot charges and Staples charges as “office supplies.” Call ALL of your “Michael’s” or “Hobby Lobby” charges as “materials,”, call ALL of your sales (regardless of where you make them) as “Sales.”

If you want to go into more detail, you can do something like this…

Let’s say for example that you sell through your own website and also through Etsy, what you can do is make two different “categories” and you can name all of your Etsy Sales as “Etsy Sales” and you can name all of your Website Sales as “Website Sales”. This way, at the end if you want to see a breakdown of “Ok, I did $2,500 of Etsy Sales and I did $4,500 of Website Sales” then this information will be available to you.

Does that make sense?

A couple more notes on naming before we move on…

I wrote an article a while back on here about what to do with personal expenses as I know this is something a lot of Etsy and Handmade Sellers struggle with…

“What do I do with my personal expenses”.

If while going through this process you realize that there are PERSONAL expenses in here, what I would do for this is to simply name them “Personal Expenses”, this way at the end you will remember to NOT include these as business expenses or income.

The next thing that you are going to run into is this and this is a DOOZY….

TRANSFERS!!

Do you remember from above when I mentioned that each account should be treated like its own separate bank account? Remember how I said this was SUPER important to understand?

This is where it comes into play….

Let me explain how we can handle these transactions….

Let’s say that you downloaded your Etsy Seller report, OR you downloaded your PayPal or Stripe report if you sell outside of Etsy.

Inside EACH of these reports you are going to want to label ALL of the income in here as “Sales”, inside each of these accounts is WHERE the sale takes place and then once you have enough sales or whenever your schedule is to withdraw the funds to your bank the money is simply TRANSFERRED into that account.

So, how does the transfer factor in?

Glad you asked 😊

On the BANK account side (the excel we downloaded directly from the bank) you are going to see deposits from either Etsy, PayPal or Stripe in here, once we get to THESE transactions, we are going to want to call these “transfers”, now these transfers are NOT income and they are NOT expenses, they are just simply transferring back and forth and have NO tax consequence since the actual income is picked up on the individual account side…

The reason that we want to account for it like this is that in the individual accounts you are not only going to have “sales” you are also going to have “fees”. You ever wonder why what makes it into the bank and what Etsy says you made are completely different? It’s because Etsy takes their cut BEFORE they transfer that money to your bank! If you just simply called the transfer as income then you are missing out on the Etsy fees and shipping being charged to you.

Another common “transfer” that you will run into is on the credit card side. This is going to follow the same exact procedures as above, just in reverse. So, for example, the expenses themselves take place within the credit card itself and when you go to PAY the balance on the card, the actual payment is JUST A TRANSFER, the actual payment itself is NOT AN EXPENSE. I will repeat that, the actual payment is NOT AN EXPENSE. The actual expenses are “within” the card and account itself!

Does that make sense?

So now that we have the more difficult transactions out of the way and we understand that we want to make the categories and buckets consistent and appropriate the next thing we want to do is take advantage of using this filter that we put on in step 3.

The reason why the filter is so important is because when we are going through if you “sort” by the description column excel will place THE ENTIRE YEAR worth of data in alphabetical order. When it is in alphabetical order, I can now NAME a TON of transactions the same by making a new column and just dragging the name down. So, when it is sorted alphabetically, I can call ALL of my “Facebooksad568383” charges as advertising.

Instead of individually looking for the Facebook charges, I can just simply sort alphabetically and once I get there, I can name ALL of them advertising and eliminate hours and hours of work.

Here is an example of this…

Now, all I need to do is repeat this process for each of my excel spreadsheets and I can move along to step 5….

Step 5. Combining Each Excel Spreadsheet

Alright, so we are working our way through this now and we are almost finished! Give yourself a pat on the back if you have made it this far!

The last thing we want to do is actually take EACH of the Excel spreadsheets for each of the accounts WITH our consistent and appropriate “names” and combine them ALL into one large excel file.

The reason that we want to combine them into one big excel file is that we want to be able to compile all of our different data from all of our different reports into one concise file so that we have ALL of our sales, expenses and transfers from each of the different accounts in one so that we can sum everything up and realize just how much we made or lost during the year.

Now this is probably the easiest part of the process and how I would do it is like this.

I would open up a blank workbook in excel and I would just simply copy all of the data from each excel into this new workbook.

Now, an important note, the reason we needed to organize in Step 3 was so that when we combine everything at the end, we are only going to have 3 columns (and the one we added in step 4) and these columns will line up on the “combined” workbook.

Once we have all of the columns lined up and in one workbook the final step is to create what is called a “Pivot Table”. In order to insert a Pivot Table all, you need do to is select all of the data you have and then at the top of excel in the “Insert” section you will see an option for Pivot Table, all you do is simply click on this and it will create a Pivot Table for you in another tab on the worksheet. See the screen shots below…

Once you click the Pivot Table the next tab will look like this..

Now, the very last step is the select the Pivot Table fields!

Do you see on the right-hand side in the screen shot above there is an area to click the squares for the data that we want to see?

In here the only information that we want to see is the “category” and the “amount”.

When we click this what will happen is that this pivot table will now populate the SUM total amount of the individual “categories” that we set up.

This “sum” of the “categories” is going to be all of your sales and all of your expenses in a VERY, VERY simple profit and loss that you can use to prepare the taxes.

The only things you will want to note here is that we do NOT want to include the transfers OR include the personal expenses on the taxes.

Here is one more screen shot of what the Pivot Table will look like once we select only the “category” and “amount”

And that is all she wrote! It is really that simple that complete the bookkeeping for the entire year to begin to prepare for your taxes.

I hope that this article was helpful in giving you a little better understanding on how to QUICKLY catch up on your bookkeeping to file your taxes for your Etsy or Handmade Seller store!

One more note before I end…

If you think that this would be easier to “see” me to walk you through this STEP-by-STEP. I go over this ENTIRE process in my courses that I mentioned above. Not only do I go over how to do this in excel, but I show you how to you AUTOMATE this ENTIRE process so that you NEVER have to feel stressed or worried about doing this anymore.

I have THREE separate tax, accounting, bookkeeping and finance courses that are designed SPECIFCIALLY for Etsy and Handmade Sellers (including Shopify and if you sell on your own website). The courses go over what to do when you first start your shop, things like what Licenses and Permits you need to get and what to do about that whole Sales Tax thing,

We then go into a STEP-by-STEP exercise that you follow along with me as I work my way through a REAL-LIFE ETSY SELLER of mine. I show you how to set up her accounting system and how to keep track of the bookkeeping every month so that you can avoid feeling stressed out when it comes to the numbers and bookkeeping for your store. The best part about this is that you can actually get some practical and useful knowledge out of your numbers.

Finally after we go through the bookkeeping together, I then show you can read these reports, develop further analysis and metrics, including how how to properly price a product so that you ENSURE a PROFIT.

Probably the best thing I have with the course is that with the Intermediate or Advanced Course you get special access to my weekly “office hours” where once a week I hop on live to answer YOUR specific questions, “it’s like having an accountant on call.”

Plus hey, maybe it would help you have accurate monthly financials so that you can see inside of your business?

I am going to offer a TWENTY PERCENT discount to all of the readers of The Handmade Seller Magazine. All you have to do is click on my website link at the bottom and enter “THEHANDMADESELLER” at checkout for 20% off the all my courses. 😊

I am not sure if you follow me on Instagram (@etsyaccounting) but if you do, you know that I am HUGE fan of automating and using QuickBooks Self Employed and QuickBooks Simple and NOT really a fan of doing the bookkeeping in excel, but for those of you who do want to live in the stone age, this is how you can VERY QUICKLY catch up on your books!