 QuickBooks Online 2023, e-commerce 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 and switch the view down below duplicating some tabs to put the reports in like we do every time. Right click in the tab to duplicate it and then we're going to right click the tab to duplicate it. Let's go back to that middle tab down to the reports on the left hand side and we want to open up the balance sheet to start off with as it's thinking. We'll tab to the right reports on the left hand side. This time let's open up the P and the L, the profit and the loss. Let's close the hamburger and change the range from 1101 to 5 to let's say 1130 to 5 and run it and then tab into the middle closing the hamburger. I'm going to go from 1101 to 5 to 1130 to 5 and run that one as well. Now in prior presentations we've been thinking about our 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. E-commerce situation where we're selling inventory but not on ground in a store rather online using some kind of application such as a Shopify for example and last time we thought about the easiest method to basically track the Shopify income as it pulls in to our system that being just waiting until the thing hits the bank account and using the bank feeds to enter it into the system and we saw that although that is quite easy it does have some limitations in that we don't get as much detail in terms of the actual items that are that are broken out where we have a net amount that's actually coming into our bank account. So we might want more detail for tax preparation to deal with sales tax issues and also to just have more information for our decision making purposes. So I'm going to do this in Excel first because Excel is kind of the most transparent way to see this and then we'll do a similar process in QuickBooks. Now note when we look at this process you can think of it we're going to think of it as a manual process first but this is what a lot of the applications are doing as well. So when we get into the apps including the integration the QuickBooks app integration what is it doing it's not pulling most of these apps and the ones most people recommend is not pulling every transaction in as though it was a sales receipt mirroring a perpetual inventory system like we would have in an on-ground shop instead it's summarizing the data in some way. So whether you're going to use an app or not it's useful to first think what would be the manual method to do if I was just going to try to pull the information in so we can understand what the apps are doing. So you could use this manual method so you don't have the integrations and the other apps and or you can you can use this as a stepping stone to understand what the apps are doing so that when you pull in the information with the app you can have some ideas of why it's and how it's doing what it's doing. Okay so so if we go to our Shopify the the general idea is if we have an online platform we're concentrated on Shopify now we might do an Amazon comparison in a future presentation but the idea would be that you get the information from the Shopify and we're not trying to pull in every single transactions so for example if I go to my analysis in the Shopify and I go into the reports and I'm going to take a look at the total sales reports here these are all the transactions that have happened obviously this is not much happening in this particular Shopify but that's all the stuff that's happening I don't want to pull in each of these individual items as a separate sale in QuickBooks for many of the reasons we talked about before it overwhelms the system and all that and I already have the information here what I want to do instead is to group this information together in some way shape or form so that I can have a lump sum pulling into my system now what is pulling into our system as we saw last time are the bank feeds so so that means that there's already a grouping going on by in essence Shopify that's going to tie out to the payouts that they're going to be giving us so that means that we want to tie out we can try to summarize all the data that happens per payout that we're going to get that we already said and saw that hits our bank account right so if we look at the payouts then if I go to like my finances up top and we go to the payouts I can go in here and say all right there's my payout so I want to basically look at all the data that ties to the payouts now I believe Shopify pays out like every day whereas something like an Amazon pays out every every two weeks or something like that but the general idea is we'd like to enter this so it ties into the payout so if I go into here for example I can see my my data the gross and the fees uh related to the payout that's happening here and then I could see the transactions that took place and the transactions numbers and the date that it took place here so when I go on over to my bank feeds for this particular example I integrated this example so this isn't a manual one but if but this one I used the commerce which we'll talk about later in order to connect the Shopify and if I look at the commerce tab that same data kind of pulls in to the to the orders and then the payout so here's the actual payout it also pulls in to the payout tab up here which we'll talk about in future presentations and then in the banking side of things I should see a deposit that's going to come through on the bank for this uh 715 right that we're going to tie into okay so that's the general idea so then I want to run a report on this side that's going to tie into the total this 870 before the fees because the fees are being charged here on the Shopify payout thing and in the analysis side if I go to the reports and if you have a higher level of of uh plan you might have better reports but if I go into the total sales tab and I run this thing for just that day and I look at the invoices to make sure I have the same batching of invoices I have one more invoice I believe this last one was not in there so this total is up to to the 480 on the gross and then we had uh we had shipping what brought it up we charged for shipping to 98970 minus that one dollar to bring it because this one wasn't included before and that gets us to that 870 number so so this we have a little bit different example in excel from these numbers to make it a little bit more complex but we're mirroring that kind of concept over here right so we have the sales side of things we have the sales and we had discounts in this example no returns here's the sales and there's the shipping and we imagine that we had sales tax collected in this particular example to get us to the sales the amount that we we uh pulled in 1669 91 we then imagined that we didn't just have the Shopify pay which is which is what we're using in our example but also connected I'm sorry that we didn't just have yeah just to shop but we also connected the PayPal right so then we have the PayPal in there and some of the payments were paid out with PayPal PayPal charges their own fees and then the rest was paid out from Shopify so that's the general concept and then in this screen this screen is basically mirroring the payout screen that we looked at we see that the we're charging that added 1548 in this case for the payouts that were for the Shopify sales now last time we did our little excel worksheet imagining we just wait till the payouts hit the bank account in this case we would have two payouts one from PayPal one from Shopify and we just said that the checking account is going to go up that the PayPal account is going to go up and then sales is going to go up for the other side of the transaction and then we made an adjustment to kind of tie into the 1099 imagining we got a 1099 at the end of the year however that's some estimates that are happening there and we're we're losing some more detail of the data if we want to add more detail of the data we could take this information where imagining basically came from the reports we looked at from Shopify and try to put that into our system with a journal entry so let's see what this it gets a little bit more complex let's see what this will look like and in excel and then we'll do the same thing in a quick book so what i'm going to do is i'm going to copy this part from f to m i'm going to right click and i mean i'm just going to well you could right click and copy and then i'm going to put that right here and right click and paste it and then i'm going to hide this stuff because i want to see my data on the left hand side going to put my cursor on f the skinny and go on over to m right click and hide all of that stuff and so now we've got a nice little worksheet that we can work on i'm going to just delete everything that's in it i'm going to make that red thing go away by by putting my cursor up here and go into the home tab font i mean format painter and paint brushy that away and then let's make this uh get rid of that data all right so now we've got our nice little system that we can we can put in here in a journal entry type of format so now what i want to do is think about a journal entry that's going to help me to to to grab all of the detail from these reports so we'll make and you can start to format just a generic journal entry when we do this in quick books and this again is similar to what apps some apps are doing when they're trying to integrate and pull in data into the system a similar kind of process here so we'll start off with the Shopify sales i'm not going to worry about debits being on top right i'm going to pull it in as i'm thinking about it right so i'm going to say well i know that i have a Shopify sales so let's just call that Shopify sales i'm going to say equals that one and the Shopify sales are the gross sales from our report i'm going to put the sales as a negative so i'm going to say negative of this number because revenue from a debit and credit standpoint is a credit and i'm representing credits with negative numbers i know that's a little bothersome to people but we have to deal with debits and credits when we do the journal entry so uh so that's what we'll do and i'm dealing with one column because that's the most efficient way to deal with it and negative numbers in excel so i highly recommend if you're an accountant thinking about formatting like that because it really saves space and then we've got the Shopify discount so Shopify discount and we said that one is going to be a debit so i have it as a negative number over here so i need to flip the sign i'm going to say negative of that number i'm just pulling in the data with formulas which is my habit of doing when i'm when i'm working to practice problem to have the data over here and then pull the information in with formulas okay so then so so this was the sales and then we had discounts which lower it's going to be kind of like lowering the sales right so we could we could record that separately as like a a uh contra sales line so here's my sales and then the discounts we could group them together here's my sales after the discounts right but the whole point is here we're breaking out some of the detail of these line items and then if we had uh if we had Shopify returns we didn't have any Shopify returns but just for the sake of keeping our journal entry with all the different components that could be in here we'll put that in there and then Shopify shipping income so now we're gonna say hey look this is what we our sales number was but then we had shipping income meaning we charged the client you know for the for the shipping so that's so we had to collect another 46 and that should be negative because it's going to be an income amount so it's going to be a revenue amount and then we had uh sales tax payable so let's say we had sales tax payable and so this is an amount that we charged the client for the sales tax but we're going to owe it to the government so we should in theory put it in the books on as a payable it shouldn't hit the income statement so we collected that from the client but it's going to go into our books as uh not revenue but rather a payable and then i'm going to say we have the Shopify clearing account Shopify uh Shopify clearing account now the clearing account is people question like why why do we need uh the clearing account because we're going to try to not put it directly into the checking account because notice that i'm getting this information from reports and and the Shopify clearing is is going to eventually clear out into the checking account but we're not there yet and before i do the clearing basically hold on i'll get into the clearing in a second before i do the clearing let's do the Shopify let's do the Shopify PayPal clearing account first so the PayPal clearing why do we need a PayPal clearing same kind of thing that we're looking at these reports which show that there was a payment that went through the payment portal of PayPal so we we someone got on our Shopify store they didn't use the Shopify payments they used the third party integration of PayPal so we expect then the deposit to come in through PayPal into our bank feeds although there could be fees related to that amount which we'll have to deal with on the PayPal level of things so instead of putting this directly into the PayPal checking account we could do that because then we can try to match the PayPal checking account to this report but it might not match exactly because of the fees on the PayPal level so what we're going to do is we're going to put this into a clearing account and then when the when the prop when the payment comes through the deposit comes through on the checking account will net out the clearing accounts the clearing account goes back to zero this is one of the most confusing components not only with this manual entry process but with a lot of the apps that people kind of recommend so you're going to have to deal with this concept and so it's useful to see it in excel so you can kind of see what's going on here so then we have Shopify fees so let's pick up the fees down here these are the fees you'll recall that happened on the payout report the fees from Shopify because these are the sales that were paid out with the Shopify paid not with the third-party PayPal integration and Shopify charged us you know the fees then which we which we're going to see come through before we get the payment that comes out so this payment should tie out exactly to what we get in our checking account whereas this payment to the PayPal most likely will not because PayPal is still going to hit us with some fees before it hits our bank account so we're going to say all right this one is going to be this 354 actually hold on a sec this is the 15 and I need to flip the sign so there it is okay and then I'm going to have the Shopify clearing account Shopify payments clearing account so this is the other one that eventually and that's going to be let's pull the number in that's going to be this number this is the other one that will eventually uh go into the checking account and we could put this one directly into the checking account probably because because this is the amount that exactly should hit our checking account because there shouldn't be any other fees because we're getting this information directly from Shopify not a third-party payment app like PayPal where the PayPal is going to hit us with fees so if I put this directly into the checking account then I probably once the deposit clears the checking account could use the matching mechanism in QuickBooks and that would probably work okay but this idea of using these these clearing accounts is usually a good practice because then if there is any discrepancies then we can deal with it on the clearing account level so we'll see what these clearing accounts do now if there was anything that was inconsistent or there's any variance or deviations because let's say this report was more complicated and there's some minor difference or something like that I'm going to do the negative sum this is my plug formula and plug any difference there there is no difference here debits and credits match which should be the case if we put these reports in correctly so they're so our our our journal entry at least is in balance right so let's let's post this out and see if we can make sense of this process all right so now we've got the sales the Shopify sales that's going to be posted to our trial balance which is basically our balance sheet on top of our income statement I'm going to put my cursor in T 10 and just say equals that 1624 89 and so there's that remember that's revenue by the way you can see it's green down here even though it's a negative number because we're talking debits and credits here and then let's do this one at a time I'll make this that's done I'll make it green just so we can see what what we're doing I sometimes do this with long journal entries so we're going to say okay Shopify discount that's going to be like a contra revenue account so it's on the income statement side it's going to affect net income and it basically brings the the net sales down so that's a debit decreasing the net income and then the Shopify returns we didn't have any but I'm going to put it here so that's green boom or greenify these two greenify and then we got Shopify Shopify shipping income this is what we charged for the shipping so we charged them that's going to bring up our income again because we have the income going up and then when we pay for the shipping we'll have to deal with the expense of the shipping right so this is still an income because we charged for the for the shipping of the clients increasing net income and then we've got the sales tax payable so this although we collected it from the client to pay for the sales tax which in theory is on them we're going to put it up here in a in our liability account sales tax we need to tax those seals okay so we're going to put that here that's still wrong that's still wrong crying out loud okay uh so we're going to we're going to put that here so this is going to be equal to the 1545 doesn't affect the net income and we're going to have to pay that out when we pay it out we'll just decrease the checking account and we'll pay that sales tax out so that allows us to kind of track the sales tax a little bit better notice we we can't really track the sales tax as nicely as we would like to within the QuickBooks system because we're not using the sales receipts in invoice forms so we have to come up with a way to deal with the sales tax okay so then then we've got the Shopify clearing account so this is the amount that's going to go into the checking account but we're how about what about the fees oh no that yeah the Shopify clearing account this is the amount that's going to go into the checking account but we're first going to put it into the clearing and this is the PayPal clearing the amount that the people paid with the third party PayPal rather than with the Shopify pay and then we have the fees so these are fees on the Shopify payments now remember that that people use the Shopify pay to buy stuff so we're going to go to the fees and where where did I put the fees okay here they are and notice you could put the fees into cost of goods sold or uh or you might put them put them down here I'm going to rename this this is why it messed me up it's I'm calling it fees and charges let's do that okay and then uh but or you can put it into some people there's debate on whether that should be in cost of goods sold or an expense but you know it's an expense you know a cost of goods sold is a type of expense so it's going to be a decrease the net income bottom line and then you got the Shopify payment clearing this is the amount that's ultimately going to go into our checking account not our PayPal account from Shopify for those sales that happened through the Shopify payment processor and then if we had any differences we would just dump the difference uh into our our sales Shopify sales there are none because we did it perfectly so so there we have it so there's the idea and then what's going to happen at this point in time you've got these two clearing accounts those are you might say well those are ugly like why do I have clearing accounts what is that what does that even mean that should be money the money is going to go into our checking account and our PayPal checking account but we didn't want to put these directly into those accounts because in case there's an a discrepancy which there almost certainly would be with the PayPal one because PayPal is probably going to charge us a fee so we're going to put them into the clearing account first and then use the bank feeds that are going to come through on on QuickBooks so now if we're in QuickBooks we're going to be in our bank feeds which we're going to have integrated so if I go into my banking up top and we would see these amounts come through on the bank side and as they did we're not going to assign the other side to income like we did in the prior example but rather we're just going to reduce the clearing account because we already assigned it to income this by the way is similar to the concept of using the undeposited funds or the funds to be deposited account if you've dealt with QuickBooks on that in other words if I look at my flowchart sometimes when we have the invoices and the sales receipt let's say we have the sales receipt we make sales and we need to group those sales in a grouping that is the same grouping that goes into the bank account so if I had 10 sales for cash I'm going to deposit them into the bank as one lump sum so I don't want to put them into my checking account as 10 separate payments because I won't be able to match that to the checking account therefore we put this into a clearing account which QuickBooks used to call undeposited funds and now it's called like payments to deposit or something and then when we make the deposit we take it out of that undeposited funds that clearing account serving its purpose of helping us to group the deposits into the checking account in such a way that they'll tie out to what's going to be on the bank statement so a clearing account different than a temporary account temporary accounts being income statement accounts that roll into the equity section the clearing accounts usually will go back to zero on a much shorter time frame than a than like the temporary accounts which close out to equity so so in other words what we expect to happen is once we see this information clear the bank I expect then to have say the the paypal let's go to the paypal checking and and say that we're actually going to see this amount come into paypal now and let's say it was for that one two nine nine fifty five now it's likely that there would be a fee so let's say it was more like let's say the amount that came in was like one two nine four or something that came into the to the paypal checking account so then we would have fees that we would have to possibly deal with at that point in time so we'd have more fees for the paypal which sometimes if you use the paypal integration paypal might might show those fees in the integration so I'm going to say this minus this are the fees and then we're going to say that is going to come out of the the paypal clearing account negative sum of these two right so now we're going to say okay I see this hitting my paypal account in the bank feeds cool paypal boom and this is how much we thought was going to hit the bank but I knew that wasn't right because paypal hit us with a fee of five dollars and fifty five cents before they deposited this amount into the bank so now we've got more fees and you might want to charge fees for different kinds of fees paypal fees versus other fees and whatnot can be useful so you can kind of track you know who's charging you more fees but but uh you know maybe you don't need to do that but that might be worth doing I'll just put in the fees right now and then Shopify paypal clearing if I double click on this one and then say plus that boom it goes back down to zero that's what a clearing account does we moved it from here up to the checking account and accounted for the added bit of fees that paypal hit us with so now we're going to say the checking accounts the same thing in the checking account this is the amount that was paid through the Shopify app we're going to imagine that we see this amount come into the checking account in the bank feeds and so now we're going to say okay cool the other side is going to come out of the Shopify payments and it should be perfect with no added fees because this is the Shopify payment portal and they we already saw their fees so we've got those picked up already so then I can go into the checking account and say bank feeds we'll pick that up in the checking check it out checking checked it and then we go to the Shopify clearing account and boom plus this one it goes back down to zero clears back out so now you can see this is a way that we we kind of get to the to the same end result for the most part although I added fees for the paypal up here then our last method but we were tracking the detail as we go and that possibly could help us with the sales tax too which is something that can muddy things up if I'm just waiting until something hits the bank so if I unhide over here unhide I'm going to unhide by going from e to o right click and unhide so you'll recall under the cash method I just kind of recorded everything as sales and then I made a little bit of an adjustment and just dumped everything into fees right to adjust it but over here and then and this amount obviously if paypal hit us with a fee would be lower from the fee because I tweaked it a little bit over here but on this side we said we get the detail right so this is actually so this so this amount here should be equal or greater to any 1099 that we get because the 1099 should be based on the gross sales number and we need to report something for taxes greater or equal to the 1099 generally otherwise we're likely to get you know the IRS might have questions about it right and but now we're more accurately breaking out the the discounts versus the shipping and stuff charges and then other charges which we could break out more specifically to paypal charges and Shopify charges and we can more easily break out our sales tax possibly helping us out with a dealing with our sales tax type of situation so you can see this could be helpful for federal income taxes obviously it could be helpful for sales tax and it could be quite helpful for your internal decision making because again most people are pretty good at Shopify when they're like when when they like really like these Shopify or online stores at picking products and whatnot but possibly not sometimes the they're not so good at to start off with the the deal dealing with minimizing costs like like shipping costs and fees and whatnot from different payment processors and which payment processor would be the best to use should we just have all the payment processors because that might accrue our sales or should we limit the payment processors because some of them charges way more fees than others and whatnot those kind of questions and then what kind of inventory should we be should we be purchasing is easier to kind of determine if you have a bigger a better breakout of your of your income numbers and then we'll talk about cost of goods sold which is the other half that we'll think about at a future point so it could help with of course internal decision making so this is the manual method but it also kind of mirrors what we'll talk about later which is the which is the some of the integration app methods and what the apps do and next time we'll put this into QuickBooks maybe