Recording Amazon Sales in QuickBooks Online | Multiple Methods

0:42

Okay, before we dive into the meat of this, I want you guys to notice that I did put some effort into making my PowerPoint look good. I call this out because I'm an accountant, so this is not naturally easy for me, and I spent way too much time on a Friday night trying to make this look good. So shout out to those putting out YouTube content on everything, whether it's from recording Amazon sales in QuickBooks or whether it's making great PowerPoints.

So before we jump in, I just want to say I'm probably going to be unpopular with some of my peers who also do ecommerce accounting when I say this, but I'm going to be presenting three different methods, and some of them are not considered best practices if you are a great accountant and you really know what you're doing.

However, I'm assuming that some of you who are joining this training are actually ecommerce sellers trying to figure out how to run businesses while you're also trying to figure out how to do accounting. So I want to make this approachable, and I want to make it something that you can tackle at the stage of business that you're at, and then show you how to build upon that. I want to show you the roadmap of leveling up and not just overwhelm you right out the gate with methods that are complex enough to kind of scare you off.

1:46

The other thing I want to do is I want to say kudos for being here, because you're either an ecommerce seller who realizes that your accounting is really important and you want to figure out how to get it right, or you are an accountant who's serving ecommerce sellers and you're trying to figure out how to better serve your clients. You've realized that ecommerce accounting is fundamentally different than other types of accounting and you wanted to get better at it. So whichever reason you're here for, I applaud you for being here.

So no more counting outs, I'm just going to dive in, let the cards fall where they may. But I can tell you, I have enough experience in this to say that there are genuinely different reasons why you would use different methods at different points in the game.

2:27 Method one, the deposit as income

So the first method I'm going to talk about is just the simple cash deposits recorded as income. And the reason why I want to talk about this one first is because, first of all, it's the least time consuming one and there's a lot of pros to this one. And second of all, if you have an accountant who doesn't know anything about ecommerce accounting, this is probably the method they're defaulting to. So I wanted to teach you guys how to spot accounting that could be leveled up.

So let's talk about this one for just a second. Before I get into that, really quick I just want to set the stage for this.

So Amazon deposits every two weeks. There's no rhyme or reason to those every two weeks. It's not like it follows month end or anything like that. Also, what you're seeing when you see that deposit hit your bank account is the summary of a lot of different activity that's been happening over the prior two weeks. So it's everything from sales and FBA fees, but it also includes things like sales tax that may have been collected, shipping income that was collected, shipping fees that you pay to FBA, warehousing inventory costing with keeping your stuff there, all that kind of stuff. That's all lumped into this one deposit, and there's not a lot of information around that if you don't go kind of looking for it.

3:41

So the first method we're going to talk about is basically just taking that deposit that hit your bank account and just calling it sales income from Amazon. Like I said before, those of you who are watching this video who know that there's better ways of doing it, I'm going to get to that, so give me just a second.

I want to first start by talking about why you would use this method. I would use this method if I was a small ecommerce seller and I was trying to build a business, and at this point in the game I'm driving revenue, figuring out how to do marketing, figuring out how to source my product, figuring out how to design a great product, figuring out how to work with my vendors. All of that is significantly more important than spending hours digging in to try to get my ecommerce accounting correct. So unless I'm prepared to put resources at it right away, or I'm prepared to attach tools to it right away, I ain't got the time to dig into this, and therefore this is a legitimate reason. But I want you to know what the pros and cons are.

4:36

So back to the pros. Pros of this are it's fast and easy. There is no learning curve involved in this, it doesn't require any special skills, and at the end of the day your net income number is correct.

So people use their accounting for different reasons. Ultimately what I want to see you guys do is I want to see you use your accounting to make good decisions, to inform everything that's happening in your business, to be able to decipher where you're making money, where you're losing money, and use that to level up as a business owner.

But at the early phases of the game, when you're making say less than a hundred thousand dollars a year in revenue, a lot of people are just using their accounting to provide to the tax accountant at the end of the year. They know they have a tax obligation to the IRS, the taxman, and they have to provide something to show how their business did. This method is a hundred percent adequate if that's what you're using your accounting for, because your net income number will be correct. The question about how much money your business made, how much money it lost, it will be correct in this methodology.

5:41

So what are the cons? Why would you not just use this? Cons are, it doesn't give you a lot of detail. There's a whole lot of information that's just lost, a lot of it very important information. You have mega timing issues that exist by using this methodology, and it really skews your margins.

So it's not a great method if you're using this, like I said, to make decisions. But let's go in and talk about this one first, because it has its place. It really does. Everyone else be damned, it has its place.

6:10

Okay, so I'm going to go into my banking center here. This is just a sandbox that I created to kind of go through this.

So hopefully you have set up QuickBooks. I really hope you're using QuickBooks Online. If you're using QuickBooks Desktop, shame on you. Your business is in the cloud, you should be in the cloud, your accountant should be in the cloud. It's time to move into that world. Do not be using QuickBooks Desktop or another desktop tool of any kind if you are an ecommerce business.

So hopefully you've already figured that one out. You're either using Xero, QuickBooks Online, personally I prefer QuickBooks Online. But you've got your bank accounts and credit cards synced and now the data is flowing in.

So when you see this deposit come in, this is what it's going to look like in your bank feed center. It's just going to say something like Amazon settlements. And here you can just choose the category you want to select for it. In this case it says sale of product income, but I'm going to change this to Amazon sales. You can set whichever accounts, you can call this whatever you want to call it, but basically after you have it you just click add and it will hit your financial statements and you'll be off and running.

Okay, that's the first method. This is not a great method if you are over a hundred thousand dollars of sales and building and scaling and growing a business, because of all these things I just talked about. Timing issues, skewed margins, not a lot of detail.

7:43 Method two, breaking out the detail

So now that we've covered that as a base point for businesses that are doing under $100,000 a year, let's dive into the next level.

So the next level of accounting addresses some of these other issues. This is the one where we get into the details around accounting. So this is where we record Amazon accounting in detail, meaning that we break it out, not just one line item.

So some of the pros of this one is you get a much better level of detail, you get a lot more visibility into making better decisions, and there are no hidden fees. You'll see what I mean about that in just a second.

Some of the cons to this one are, it does require journal entries. I know you had a little bit of a heart attack there and your heart kind of skipped and you kind of wanted to go hide in your hole. But I'm going to walk you through it. I'm going to make it so simple and so easy that you're going to be shouting from rooftops that you make journal entries now like a pro. So don't let that scare you off.

It is a little bit more time-consuming, not a lot more time-consuming, than the other method. And unfortunately with this methodology the timing issues still exist. So I'm going to talk a little bit more about that in just a second.

8:40

First of all, let me talk about what this would look like. So if I'm using just the cash approach that we talked about just a minute ago, this is what would show. Basically I would have one deposit for one hundred nine thousand five hundred eighty dollars.

If I broke it out with more detail, this is what it would look like instead. So in this case, and these are actual numbers that we pulled from one of our actual clients who gave us permission, we have instead all of this level of detail. So Amazon sales, instead of being a hundred and nine thousand, were actually during this period of time one hundred forty seven thousand, and all of these other things happened. So we have shipping income that was collected, sales tax that was collected, FBA inventory reimbursement, FBA Amazon fees, chargebacks, refunds, shipping, promo rebates. There was actually a whole lot more items, this is just how we chose to map them into our financial statements. And all of that gave us a net deposit of a hundred nine thousand five hundred eighty dollars.

So you can see this scenario is a lot better information. It gives us a much better idea of what happened than this one does.

9:48

Now I want to tell you guys, one of the things I specifically called out about the first method is that you have skewed margins. So specifically what I mean by that is, if you're an ecommerce business the most important metric for you to watch is your gross profit margin. Gross profit margin is income minus cost of goods sold, and you want to see, is that margin trending up, is it trending down, is it holding steady, what's happening there.

So if one of the two pieces of that, the most important, is sales, and I'm recording Amazon sales at a hundred nine thousand instead of one hundred forty-seven thousand, that is a huge skew in margin, and it really affects my ability to see the trending and what that's really looking like. And so a huge case for going to this additional level of detail is actually this, that you can actually see the margins and you can see a lot more detail and information.

10:44 Pulling the statement and building the journal entry

So I want to show you guys what this looks like inside QuickBooks. So I'm going to start by pulling over, I want to show you this Amazon report. Oh wait, before I do that I have to show you this page that tells you how to get there.

So how do I get to this report? This is how you do it. Log in to your Seller Central account, click on reports at the top, click on payments, click on statement view, and then select the date range that you want to look at.

So what it looks like is this. Okay, so here at the top we have all of our reports. We go to payments, and when we click on payments, then that pulls up this. I can select the statement range that I want to look at, and this is what I get.

So this is a great report that Amazon gives us that makes it a lot easier for us to break out the detail without us having to get all crackly with the back end reporting and Excel documents and all that kind of stuff, which we're going to talk about in just a second. But this is a very approachable, very easy way of getting most of the way there with this.

So all of this is my order activity, refunds, shipping, selling fee transactions. This is ultimately what I see hit my bank account here, so this is the amount that's scheduled to transfer tomorrow, basically this eighty eight thousand eight ninety four.

12:22

So then what I'm going to do is I'm going to pull up a journal entry inside QuickBooks. So to get into a journal entry I'm just going to click on this plus button and I'm going to say journal entry. And this is where your heart starts to palpitate a little bit and you start to have a little bit of a come apart. But like I said, we're going to make this really straightforward and really easy.

So inside this journal entry window, we have down the left side the accounts that we're going to be using, and we have debits and we have credits. So I'm going to separate this off of here so we can see these two things side by side. All right, let's shrink this down a little bit so I can move it over like that.

I've actually already created this journal entry for you for the sake of time, so I'm just going to go into the recent journal entry and I'm going to select it. So I would typically be dating this whatever date the deposit was expected. So in this case it's the 28th, and that's what this would be dated.

13:29

So what I do is I go through all of this. Now all of these line items I can map however I want, so I can call them whatever I want inside QuickBooks. I can call them whatever makes sense to me. So I can either call all of the sales and all of the refunds and summarize here, or I can actually do individual line items. So in this case I actually did individual line items.

And the easy rule of thumb on this is that if it's in black I want you to put it in as a credit, and if it's in red you want to put it in as a debit. And you can call it whatever makes sense to you, but that's basically how you're going to enter it in, until when you get to the very bottom, when you indicate which bank account this is going to. You'll select the bank account that this is depositing into, and it should default to the amount that's actually here, if you've done all this correctly, if all of your debits and credits have been done correctly. And the rule of thumb is that credits are in black, debits are in red.

14:43 The P&L result and the timing problem

Okay, so I want to show you, after you put all this in, what it looks like inside your financial statements.

So I'm actually dating this a year in advance so that I can isolate the financial statement impact of that, so you can see what it looks like. So I'm going to come in here to reports. Because if you're not evaluating the financial statements, then what is the freaking point of all of this? Because if you never look at it and you're never using it to inform your decisions, you might as well just throw a bag of receipts at your tax accountant and ask him to come up with all of this. I'm not really recommending that, by the way. What I'm really recommending is that you actually evaluate your financial statements.

So I'm going to come in here to reports, I'm going to click on profit and loss, and then this is what's going to tell me how my business did, where I made money, where I lost money. Obviously this is just the Amazon activity, but if you had other channels you'd have other items below this. So I'm actually going to move this date range to that month that I just isolated this into.

16:02

So that journal entry shows up in the financial statements just like this. So now I can see how much I made in income, how much I made in cost of goods sold like categories.

Now keep in mind, this does not include cost of goods sold, which is a very, very important part of the financial statements. Cost of goods sold is not available to us on that report that I showed you. We have another video where we talk about cost of goods sold with Amazon and we'll link to that below. But this is just the sell side of things, just the Amazon activity side of things.

So we have all of our sales, discounts, all of these fees, then all of our advertising expenses, gives us basically a net income of 88,894. This is exactly what deposited in your bank account. And that's why my point is, with the first method, whether you start by just calling all sales this number, or whether you record it all in detail and your end net income is this number, the end result on the bottom line, meaning the net income, is the same.

And so utilizing that first method for preparation for tax purposes is completely adequate if that's all you're using it for. But if you're trying to use your financial statements to make good decisions, this methodology obviously gives you a much better result and a much better level of detail.

17:24

Okay, so let me get off my soapbox about that one for a minute and let's dive into the next one.

So the biggest problem with this method, the one I just showed you, continues to be primarily the timing issue. So what I mean by that is, if Amazon is depositing for me every two weeks and it's not lining up with month ends at all, then if I get a deposit and it comes in May second, then most of that revenue is actually April revenue. And if I'm trying to watch the trends of my business and I'm trying to close out a month and I'm trying to match cost of goods sold with my revenue, then I really care about period ending stuff.

Now the problem is that this nifty report that comes out of Amazon that summarizes that particular deposit worth of activity does not take into consideration cutoff at all. And what I mean by cutoff is all the stuff that happened, how much of this had to do with April, how much of this had to do with May, where's the cutoff from where that happened. That is not obvious here.

In order to get that you would have to go into the back end reports in Amazon, and you would have to sort it out according to the date, and then you'd have to sort it out according to the activity according to that date, to calculate period ending cutoffs.

18:37

And then, so if I have a deposit that's spreading over the end of a month, then at the end of that month, whatever happened in April, Amazon now owes me money. So I might not have seen money show up in my bank account, but I do have a receivable from Amazon. It's like sending an invoice to a customer and waiting for them to pay. They still owe me the money even though I haven't seen it show up yet. Amazon still owes me that money for the April sales even if I don't see it show up until May.

So in a really best case scenario situation, where I'm really using my financial information to guide my decisions, I need a really good level of detail, and that requires cutoff, and yes the detail, all of this.

But also the other disadvantage with this methodology here is that I don't have a lot of control over how this is mapping. Maybe I want to see different fees broken out separately. When Amazon does all that stuff there's literally over 400 different line items of different ways that they could be bringing money in or taking money out and all that kind of stuff. So look at this for example right here, this other transactions, other. That's not an insignificant amount of money. That's basically, was that like 5% of the total deposit amount, just categorized into this other transactions, but I have no idea what it is.

19:55 Method three, using a sync tool

So if you want a much better level of detail and you want the cutoff, you either have to be prepared to dive into the back end deep reporting, or, and I'm going to give you this as a much better alternative, you need to attach a tool that will let you sync the two systems together.

So I want to show you what that looks like really quick. And the primary reason why I'm going to go into that instead of showing you this in depth is because this is really a whole nother video that would require like an hour of your time to just watch it, another many hours of your time to master it, and another many hours of your time each month to accomplish it. And I personally think that there are now tools that make this a very affordable thing to do. So I'm not going to show you how to do this right now, at least not in this video, but I'm going to show you how to find the tools that will help you do this.

20:41

So because I'm an accountant user, my apps are up here at the top, but in your file it'll be down here in the bottom of this list somewhere. So when I'm here in the apps and I click on the app center, I'm just going to start typing in Amazon, and it's going to bring up a list of applications that integrate with Amazon and integrate with QuickBooks.

So here right at the top of the list, these are several that we know, that we like, that we've used. Webgility, A2X. These are two of my favorites, but there's a number of other ones too.

So if you click on the app it'll actually take you in, it will give you an overview of it, usually has a quick video to show you how it works, and then another place that has pricing. Now this is the other reason why I want to show you that I don't want to go into how to break out that spreadsheet, because if you look here, if you're under 200 orders a month you're like $19 a month to have this done for you. And this is probably the most common package that we use with our clients, so up to 5,000 orders a month, $69 a month.

So this is still so much better, depending on if you're going to hire somebody to do this for you, this is so much better of a price than what you would pay them hourly to do this process for you, because it is not easy. That spreadsheet that I showed you a second ago, it's literally thousands and thousands of line items long and it's very overwhelming. So these tools are a really great way to make this happen a lot quicker and more easily.

22:12

So we have a whole nother video on A2X specifically. At the time that this video is recorded, an A2X video was already done, so we'll link to that video below. We'll probably also do one on Webgility shortly as well.

So if what you really want, and I highly encourage you to get to this point, is a point where you are getting the detail and you're getting the timing of it right, I would highly recommend you use a tool instead that will automate this process for you.

22:42 Which method fits your stage

So just in summary, some of the pros and cons of this particular methodology, and I call this the Amazon activity deep dive.

So the pros are that your margins are accurate, your timing is corrected, it becomes a source of truth, and it gives you a high level of insight.

So one of the common complaints we hear people say before they come and become our clients is they're like, you know, my accounting is not a source of truth for me. I go in there and I look at stuff, none of it looks right to me, I know it doesn't match the reports coming off my channels, I have no idea what's really happening. So by going deep dive into Amazon, and frankly every other channel, but specifically this one's about Amazon accounting, so by going deep into the channel activity, it becomes a source of truth for you, a place that you can then reference later on to get really good data.

23:28

Some of the cons with this method, basically it requires a highly sophisticated knowledge of Amazon reporting or an add-on tool. So in order to get this, it is definitely best practice to report this way, especially if you're at least over a million dollars a year revenue. You absolutely need to be doing it this way. I would highly recommend you consider this even if you're at half a million dollars a year in revenue, but for sure by the time you hit a million dollars in revenue you need to be looking at this methodology.

And yeah, the biggest con is you're either going to have to spend a ton of time and effort learning how to do this, or you're going to have to add a tool on to your tool stack, which is what I'd recommend anyways.

So just some resources that you have available to help you on this journey. Our blog, the QuickBooks app center where you can research apps that will do exactly this, our YouTube channel.

So I hope this was useful. If you found this useful, please like, subscribe, comment for more additional tips and tricks around ecommerce accounting stuff in the future. Thanks for joining us.