 QuickBooks Online 2023, e-commerce, Amazon sales, manual journal entry method, Excel example. Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 2023. Here we are in our QuickBooks Online Test Company file using the accountant view as opposed to the business view you can toggle between the two views by going to the cog up top, switch the view down below. We've been looking at e-commerce situations where we are selling inventory but not on ground in a store but rather online in the web using third-party applications for example such as a Shopify or an Amazon for example. In prior presentations we looked at some examples that mainly focused on the structure of a Shopify structure. Now we'll focus more in on an Amazon type of situation noting that the general concepts are going to be much the same no matter what the third-party platform is because we have a similar structure of needing to take the sales kind of information from the third-party platform into the QuickBooks. However, Amazon could have different structures related to you know how you're tracking inventory and whatnot and it could have different structures with who's responsible for example with the sales tax and as a result some of the reports on the Amazon could be a little bit longer than with the Shopify type of situation so we'll see a few more categories when we think about our journal entry in the an Amazon type of situation but note the basic support accounting instruction by clicking the link below giving you a free month membership to all of the content on our website broken out by category further broken out by course each course then organized in a logical reasonable fashion making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems PDF files and more like QuickBooks backup files when applicable so once again click the link below for a free month membership to our website and all the content on it kind of ideas that you have available to pull the data in are much the same we can either just try to use the bank feeds or we can try to use a manual method to pull the information from the Amazon and put it in with a journal entry as well as with the bank feeds in combination with the bank feeds we can use the QBO commerce which has a similar connection method which we'll talk about in future presentations I could do the same thing with Amazon as with Shopify and then we have the third-party applications that could do a similar process as well such as an A2X all of these methods will be utilizing bank feeds within them as well so we'll talk about this in Excel first so we can kind of see it in a more transparent way using the manual method noting that when we use these integration softwares they're going to do a similar thing as we're doing with the manual method it's just that it's going to automate the grouping so we'll still have to deal most likely with these clearing accounts and whatnot unless we try to use some kind of third-party software that pulled in every transaction which most people recommend against because it kind of bogs down the information in QuickBooks over time possibly and because it could be redundant information I'm going to build this again in Excel from scratch so if you don't want to build it from scratch you can kind of follow along once we have it constructed but I want to build it from the ground up so you can you can work with the Excel as well if you so choose so we just got a blank Excel sheet I'm going to zoom in a bit holding control zooming in so I'm scrolling up to 205 let's go up to like I don't know 235 I'm going to select the whole sheet with the triangle up top I'm going to right click on this sheet and format the cells then I'm going to go to currency to start off with and I like to go with the negatives here and get rid of the dollar sign we'll keep the decimals I'm just going to put my data on the left so I'm going to name the sheet like Amazon payout example example and I'm going to make this a little bit wider and let's say that I'm going to select let's I'm probably going to need it wider than that I'm going to select these two cells for my header home tab and font group I'm going to make this black and white as is my custom for header situations and then we'll put some of our data so I'm going to kind of copy and paste the data as we go in go into this I'm just going to say the first component is going to be Amazon payout pays out basically every two weeks and so I'm going to imagine the payout is 308 dollars and 47 cents that's going to be the payout by the way I've got this whole thing selected home tab and bolded font group and bold and there's brackets around this if you want to add the brackets you can go to the font group and put the borders around the whole thing so now I'm going to add the details so we are imagining a similar situation as we did with the Shopify in that we were pulling our information I'm just going to go to a Shopify website but we're imagining now we're doing a similar process with like an amazon website in that we could see the payout information we can go to the amazon website and see the payout information from the amazon website and I believe they pay out like every two weeks so the payout is going to be including a bunch of different sales hopefully if we're selling a bunch of stuff it's not coming in sale by sale it's got a bunch of different sales that are being grouped together in that payout what we would like to do is take the information from amazon and break it out so that we can then see the detail and then we're going to enter a journal entry in a similar fashion so that's what so now we're going to imagine we're going into amazon in a similar fashion as we did with like a Shopify pull out the detail of this particular payment so that we can see all of the line items within it now amazon again because amazon might be taking care of a lot more things uh than possibly a Shopify where you might be responsible for more items than the amazon might have more line items that you're going to have to deal with and say well where does this line item go and so uh so you might have to do a little bit of research but once you figure it out you can have a template of all of the line items where these where the amazon charges and whatnot can go so for example i i'm just going to make up some stuff here that might be on like an amazon report i got commissions for amazon and we're going to say these are these are the order item fees uh 90 dollars and 26 cents and then i'm going to just i'm going to copy these over so i don't have to you know watch me type it commission for amazon refund item fees i'm going to say that's 16 dollars we're going to say that we have a line item for uh a disposal uh other transactions 17 cents we've got a line item for fba per unit fulfillment fee 87 we've got a line item for principal amazon order item fee uh so that's the order item price and then we're going to say we have another one for principal amazon refund item price so refund we're going to say we have another one for refund commission amazon refund item fees let's make that a little bit larger so we could see it something like that and so then we've got another one for sales tax service fee now amazon and something like a Shopify for sales tax which we might get into a little bit later could have different rules on the sales tax for because amazon for example uh in some cases might be more responsible for sales tax in most uh states but there could be some states where they're not responsible for sales tax in which case you might be responsible for sales tax if you qualify to be subject to sales tax in the state and so on and so forth so that's the whole so we could dive into that later but there's that we got another line item uh shipping amazon order price we've got another line item we're gonna say is shipping amazon order promotion and another line item that we're gonna say is shipping charge back we're gonna say another amazon line item which is going to be shipping tax amazon order order item price another line item we're gonna say a storage fee other transaction another line item we're gonna say is tax amazon another line item tax amazon refund item price and then balance of the settlement and i'm gonna sum this up to some so this is going to be a sum formula equals the sum of all this stuff and that comes out and this should uh this should equal this number up top so i'm gonna change this number up top and i'm gonna make it positive by saying negative of this number and that's going to be our our deposit so if we just if we if we just allowed the deposit to happen and i've got a bunch of empty empty rows up top let me delete some of these rows i'm gonna put my cursor on column or row one down to row five right click click the selected rows and delete them so if we just waited for the deposit to happen we would just be entering the deposit and we wouldn't be taken into account all those different stuff that is happening down here which is you know there's more more there's a whole bunch of potential line items that can go on in an amazon store again depending on how your amazon store is going to be set up so if we don't take all this into account we're just gonna have that one line item we'll show we'll show an example of that now so let's basically build our little spreadsheet and we'll imagine that we're just going to wait till the deposit happens and then we'll do our journal entry method so that we take all of this other stuff into consideration so i'm going to build our same uh our same kind of worksheet to to see this in excel to to post this out in excel so i'm going to make this skinny i'm going to say these are the accounts this is going to be the debits and credits i'll make this one a little bit lighter i've got the hiccups i'm going to then make this black and white home tab font group black and white i want to make this the same skinny f needs to be the same skinny size as c so i'm going to put my cursor on c home tab format paint it to f and then let's build our trial balance i'm going to call this i'm going to put it down here accounts and then i'm going to call this the beginning trial balance and then these are going to be the journal entries and then this is going to be the end trial balance and i'm going to this needs to be an r trial tile balance we're flooring a bathroom or something with the tiles font black white center i'll make this black and white i'll make the accounts a little bit larger and then i'm just going to list out the accounts uh that we might use in this so i'm just going to list out checking checking account amazon payments clearing now we won't use all of these accounts using the cash method but when we convert to the the journal entry method we will use more of these accounts all right so amazon clearing what else do we have inventory inventory amazon sales tax payable equity amazon sales space space because i don't want it to repeat stuff refunds cost of goods sold amazon seller fees and charges amazon fba fees amazon shipping okay so there it is and i'll make the equities the middle so i usually make that like blue to to show that that's between the balance sheet and the income statement home tab font group i'm going to make it dark blue and white so that we can see this is basically our balance sheet on top of our income statement so we can see this all nice and transparent in one space i'm going to put a bunch of zeros here because there is nothing in our beginning balance we probably don't even need this column but i like to do the whole thing so that we could see the whole thing so i'm going to then sum this up this way so we're going to put our entries in here beginning balance plus the entries is the ending balance which is just the ending balance because we don't we don't have anything in the beginning balances but still i'm going to copy this down then same formula all the way down looks nice and then this is going to be the total debits and credits i'm going to sum this up equals the sum or famous sum formula sum it up and then i'll copy this across with the fill handle put an underline here maybe home tab font group underline and this is going to be our net net income boom all right and then we'll sum this up the net income is just everything from equity on down revenue minus expenses not including the total on the debits and credits i'm going to copy that across putting my cursor on the fill handle copy this across i know i'm doing this fast but we've done it before and it's not an excel course so i just want to map it out fairly quickly here and then i'm going to put some borders around this whole thing home tab font group borderize it let's make this middle column blue because that's where our data input is going to be that's what i like to do make it blue font group drop down i like the light blue which isn't here so i'm going to go to this thing standard colors that light blue right there that's what i'm going to use light blue and then down here i wanted to turn like red if it's not in balance which should be in balance with a zero so if it's anything other than zero although i'll give it a two a two dollar plus minus range uh to turn red if it goes outside that range so i'm going to say conditional formatting if it goes greater than two make it red and then conditional formatting if it goes if it goes wait that's the wrong one less than negative two make it red but if it's between negative that's not between where's my between thing but if it's between between there it is negative two and two then i want you to make it green because then we're good to go that's what green means right that's what green means so if it goes to three turns red if it goes to negative three turns red but if it's still under two we're good to go all right yo good to go all right yo okay uh all right let's make this let's say this was was uh negative or let's say this was negative three that's going to be negative numbers or income so i'm going to do another conditional formatting down here at the bottom i'll make this home tab font group let's make this black and white that's going to be my default i don't want it to turn red when it's negative because negative is actually good for a credit balance on net income so i want to right click on these cells format paint this thing and i don't want this negative thing there to turn red just for these cells turn that off don't do that and then i'm going to make it black and white and then and then i want it to be green when it's negative just to be sure that negative is good just so i can make sure i know that so i'm going to go up top and then say if this thing is greater than zero i don't want to use the same green fill because then it kind of gets confusing so i'm going to say i want to use a custom thing and just use the font color and i want to make it this green like that and then it didn't do it what happened k pas oh let's do it again i undid it i think i'm going to say if this is if it's less than if it's less than if it's less than zero that's when i want you to do it we want you to say then it's going to be uh custom and then font and then i want you to make it green okay that's what i want to see that's what i want to see all right then i'm going to delete this and then let's make this give us some some uh blue so i'm going to make this bordered and uh blue so there we have that okay okay so there we have it so so notice that it if i was so now i've got my little worksheet here let's do the simple method and then we'll do the more complex method in a following presentation so if it was just if we just waited till something cleared the bank then all we would have for our transaction would be using the bank feeds most likely this thing hits the checking account for the total of this amount the other side we just put into amazon sales our revenue account the credits are going to be negative and so what would happen in quickbooks we would just say it would do this increase the checking account and then the sales would go up in a credit direction which is actually good that's why i have a green number net income would go up by that and then and then at the at the end of of the year so that could be an easy thing to do right we don't have a lot of detail but it's like okay i can balance my checking account and my sales doesn't have all the detail but at least my net income would should be in essence right you know might we have timing differences we don't have all this detail but hopefully my net income uh is correct and maybe i don't even have to deal with sales tax because possibly amazon is the one that's responsible depending on your circumstances so so but you might get a 1099 like at the end of the year let's say you got a form 1099 at the end of the year let's say this was your year's worth of revenue and it says your revenue should be let's say 601.71 and so i'm gonna so the problem with that the easiest thing to do then is to say okay well if i make my income statement having this is my revenue uh then am then iris might get mad at me because and question me because they're gonna say i got a 1099 says that top line is revenue and we could we could do the simple estimate assumption thing and say well maybe the 1099 is just right and the difference is simply all this other stuff and instead of breaking all this other stuff out i'll just call it amazon charges and fees right so you could and so that could be the easiest thing to do right we could all right well then i'll just say that uh we'll just call it amazon charges and fees and then amazon revenue and we'll just say that the difference is going to be equal to what it says on the 1099 i assume i assume is correct minus this 39188 and so i'll just make my and if i did that i'll just make my amazon sales go up by this amount and then i'll make my net charges go up so there's no impact on the net income it's the same but now my revenue line lines up and hopefully you know the iris doesn't get mad at me or anything like that so that's one simple method you can use just like with the Shopify store if you had both Shopify and amazon you're probably going to use a similar method for both of them you can use a you know a nice easy method but still what don't you have using this method you you you don't have a lot of detail for your you know for taxes you don't have a lot of you know it's an estimate that's happening if you have to deal with sales taxes then you got it you're gonna have to find some way to deal with the sales tax unless amazon is the one that's responsible for the sales tax depending on your situation situation and obviously all these fees and charges uh could be useful information uh when you're doing internal bookkeeping and trying to think about what products you should be purchasing and where you should be selling different items if you have different stores for example should i sell it on amazon or elsewhere what should my price points be and all that kind of good stuff so let's just set up the worksheet now and imagine the second method so i'm going to put my cursor on column c the skinny and i'm going to go over to column j right click on the whole thing and copy it and i'll paste it right here on column k so now we have the same stuff over here i'm going to put my cursor on the on these items and delete and then i'll blueify this one right click and blueify it and then delete this stuff the second method let's go ahead and hide this stuff that we just did so we can see our new stuff right next to our data i'm going to put my cursor on column c drag over to column j cj there's cj right there and right click and hide that stuff so the second method would be that we try to we try to break we try to get this information from amazon for each payout every two week payout or whatever they do it and then try to break out in a journal entry this information and use a clearing account to then put it into uh that'll match our checking account so that might look something like this i'll do this fairly quickly i'm going to say i'm going to try to match up these line items so i'm going to say the first one is going to go to amazon amazon seller charges and fees i'm going to have some accounts that are repeated now you might group these differently i'm just giving an example here because all of these line items that come on an amazon sales report sheet might not have its own account in your general ledger you might group some of them together and the groupings that you decide are best could differ depending on what you think is is most important so i'm just gonna give some examples of how you might you know break this stuff out so i'm going to say the next one is going to be i'll put it into the same thing seller amazon fees and i'm going to pick this one up and then i'm going to say the next one is going to be let's say amazon fba and that's going to be the uh 17 hold on a second let's actually put that one in i'm just going to put that on the same one amazon seller fees for the 17 and then i'm going to go to the amazon fba what happened there i made it it's white because it's the other formatting i'm going to format paint this boom okay and then this one is going to be the 87 and then i'm going to put this one in the amazon uh fba so wait a second this is going to no this is going to be amazon sales hopefully i'm lining all these up doing this fairly quickly here amazon sales 701 and then amazon refunds so we had amazon refunds where's the amazon refunds oh i just called them refunds okay let's put amazon refunds amazon refunds all right and and that's going to be i'll just start copying these down i'll copy that down boom and then i've got uh fees more fees and seller charges and more fees and sell seller charges i'm going to say bringing these two down and then we've got amazon shipping so amazon shipping i'm going to say these three are amazon shipping that doesn't work you can't do that what are you doing amazon shipping and then i can copy these down amazon shipping and then amazon sales tax collection and so that's going to be that one and then we've got amazon uh fba fees i'll copy that down oh wait a sec that's not the one you copy down pull it together man pull it together and then sales tax amazon sales tax collected sales tax payable i think i'm going to put here and then again amazon sales tax and there we have that so i'm going to copy that down boom boom and then we finally have the amazon clearing account okay all right let's make all this blue i'm going to blueify this blue borders and the clearing accounts the negative sum of all of this stuff which comes out to my payment so there's our there's our journal entry now i hopefully i mapped this out all correctly so this is coming from there if i messed anything up you know i apologize but this is the idea right and if you want if you map it differently uh on some of these line items that you think it should be mapped differently then you can choose a different mapping and so on and this is a similar concept that you will you will see with the software if you use like quick books connect for example that they'll give you some suggested accounts to to tie these out to buddy but you can change them and customize them either if you do them manually or with the software all right let's post this out hopefully i have this right so i'm going to let's do this one at a time i'm going to say here we go here we go here we go now i could do all three of these at the same time so i'm going to say this is going to be fees amazon fees i'm going to say this is this plus this plus this whoop not that this one and then we're on the fba fees so amazon fba this is going to be let's say equal to that boom if oh no hold on a second amazon you're going too fast this equals the fba fees let's do let's do it and then i'll mark it after so amazon sales is going to equal that one and then i'll make that green amazon refunds is going to be right here it's going to equal that one and then i'll make it green and then amazon seller fees and charges is here double clicking on it because something's in it plus this plus this enter and greenify those amazon shipping equals this plus this plus this enter greenify those and then amazon sales tax payable is here equals uh that and greenify and then amazon fba fees double click on those plus this one enter greenify and then amazon sales tax payable double clicking on it because something's in it plus this plus this enter greenify and then the clearing account is here i'll say equals the clearing and that brings the debits and credits back down to zero with some possibly some rounding in it but and so then so here's our net amount so the net amount notice doesn't tie out exactly to what we had before the 391 88 because we had some you know balance sheet item with the sales tax up top but uh that's that's a general idea but now we've got the sales the sales the refunds which are contra sales accounts and then we've got the amazon seller fees the fba fees and the and and the shipping that we collected this is shipping that we collected income and then we're gonna have the shipping expenses possibly that will be on the expense side of things and uh you could argue whether these should be some of these should be included in cost of good sold uh or not but this is the the general idea to break out the detail so you can see this could be useful for kind of decision making uh and give us more detail and again it could help us with the sales tax if we have to deal with the sales tax uh and and that could depend on your current situation with amazon because for some states and in some situations they might be dealing with the sales tax which could make it easier in some cases on your end but obviously they charge for all that all that easiness so then we have this in the clearing account now we could have deposited this directly into the checking account because it should match what actually goes into our checking account and then we can use the matching mechanism to tie it out although if we did that in quickbooks we might not be able to use a journal entry but rather like a deposit form we could still use a deposit form and make a journal entry out of it in essence but uh the clearing account is a good conceptual framework so then we're going to see it go into the checking account and when it hits the checking account we'll see it happening on the bank feeds which will end up with a journal entry that looks like this i'm going to blue a fi down here and that's just simply going to be an increase to the checking account and a decrease to the clearing account for the net deposit so then once we see that clear using the bank feeds in quickbooks we would say this is going to increase amazon payments is going to decrease and we take it out of the clearing account and put it into the bank account that gives us the more detail now again if this is overwhelming to construct this note you might say well i'm going to do it the easy way i just want to connect amazon to quickbooks using the quickbooks connect app for example well quickbooks connect is still going to basically do this right it because it's not going to pull in all the data one at a time that would be even worse it's going to use this clearing account concept method to summarize the the transactions because that's the thing to do it's just trying to automate doing that it's going to try to pull in the the information from amazon and group it together in a similar fashion and it'll try to create some of these accounts so we can use their custom accounts that they they basically set up so it could be easier in that way but you're still going to have to deal with usually a clearing account and and deal with that same conceptual concept of grouping these transactions together putting them into one journal entry pulling them into a clearing account and then using the bank feeds to pull it from the clearing account into the checking account so we'll do that next time