 QuickBooks Online 2023, e-commerce, sales, simple bank feeds, only method. 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 on down below. Let's duplicate some tabs to put reports in like we do every time. Right-click in the tab up top to duplicate it. Right-click in the duplicated tab to duplicate it. Back to the tab to the middle as the one to the right's thinking. Going to the reports on the left-hand side, we want to choose the balance sheet. As that's thinking, we'll tab to the right reports on the left-hand side. This time the profit and loss, the P and the L, closing up the hand boogie, changing the ranging. Let's do this time from 11.01.25 to 11.30.25 and then run it. We'll put our information into that month which doesn't have anything in it thus far. Tabbing to the left, closing the hand boogie, same range that we're going to change to 11.01.25 to 11.30.25 and run it. And then we'll tab to the left in the prior presentation. We talked about setting up our bank feeds which is going to be one of the crucial integration processes. No matter what we're going to do with our other types of information that we're going to pull in. 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. From our third party platforms like the Shopify's, like the e-commerce and so on. So obviously on the bank feed side of things, if we're just looking solely at the bank feeds, eventually the sales are going to happen on the Shopify side here and there's going to be more stuff going on than just the money we got from the client because or the customer for the inventory that we sold in the online cloud platform because there's also going to be fees and stuff that is happening. However, at the end of the day, we're going to get some of that money that that was collected in the form of a deposit in our checking account, right? So now we've got our checking account over here and we can wait till it hits the checking account and that would be the simplest method to use. However, not having as much detail for it. Let's imagine we pull this information into an Excel worksheet. So I'm going to build an Excel worksheet with an example problem and then do some comparisons of the methods. So I'll build this out with you here. So we'll have a blank Excel worksheet and we're just going to put some data within it. This is typically how I format the Excel worksheets when doing this. I'm going to scroll in first. I'm going to hold control and zoom in on the scroll wheel. I'm zooming in down here. I'm at 280. I'm going to put my cursor on the triangle, right click on the entire selected area to format and my starting format is usually currency, bracketed and red negative numbers, no dollar sign. And let's keep the decimals this time to decimals so we can see the pennies. So let's say, OK, and I'm just going to call this like the finance report by day. So if we were using something like a Shopify type of situation, then we might have to go to these reports. One way we can look at this is take a look at these reports and try to tie out the same report to the payout period, which might be like a daily type of report. Now your Shopify store might have different reports depending on what plan you are in and then you could use similar methods for other types of systems like an Amazon. They pay out like every two weeks, so they're going to batch out the payments that happen. But the general idea is that there's going to be multiple transactions that are happening in the payout period, whether they pay out one day or whether they're going to be paying out every two weeks. And so when we pull the information into our system, if we're looking at the reports, we're going to group them together in conjunction or in alignment with all the transactions that happened during that timeframe. So let's imagine that we pull this information and recreate it here in Excel so we can see it kind of in Excel format. And we would have a similar situation. By the way, this is what we're going to end up with in a Shopify type of situation. Amazon sometimes does more of the, if you have an Amazon situation, they might do more of the kind of work on the Amazon side. There's a whole bunch of different classifications and whatnot that they could be adjusting your payment for in the report. So it could have a whole long list like this of adjustments that are made that we could take into consideration. So we'll take a look at another example in the future. This one's going to be a little bit more basic of an example to get the idea. So let's highlight these two. I'm going to make this home tab font group. I'm going to make it black and white is what I usually do for headers. And then let's make the whole thing bold because I think that helps home tab font and bold. And then I'm just going to say that the sales information. I'm going to make this bold as well for these two cells home tab font black and white. And then I'm going to say there's going to be gross sales. That's the top line sales discounts returns and then net sales. So this will be our sample example. So let's say that the gross sales came out to the 1624.89. And then the discounts we're going to say I'm going to say is a negative 16.99. And then we're not going to have any returns here. So I'll say zero here and I'm going to underline this home tab font group. Let's underline it and sum this up net sales. Then is the sum of these items. And then we could also have shipping and sales tax is collected. And so I'm going to add these in and say this will be 4656 for shipping those sales tax. Let's font group and underline this one so that we have then total sales, which is going to be then the sum. Let's say the sum of these items. I have two. Yeah, there we go. So the sum of those items. Let's put some borders around it. I like to put borders. So I'll select the whole thing. Home tab font group and I'll put some borders around this. Now note that this information, if I was to pull this from Shopify, you'll recall that we were looking at the analysis. We're looking at the reports in here and then we're looking at the range of the reports that would be applicable to match out the payment. And then we can take a look at the actual payments that are going out, which in Shopify happens to be under the finances and then the payments to pull the information here, which could include fees and there could be payments that are coming through the Shopify. Because when we get paid by the customers on like a Shopify, for example, they could pay us using the Shopify pay, which means that then the Shopify is going to be paying out from here or they can use some other third party payment processor like a PayPal that would be the other format that we're going to get paid, which means we're not going to get the money ultimately from Shopify, but that's where we're going to get money when people pay in that way from that third party of the PayPal. So that's going to be on this side of things. And notice in this format, you could see the payment provider is the Shopify payment the way this is formatted. So let's imagine that I'm going to go over here and say, okay, let me look at the payment side of things. I'm going to make this a skinny one and say we have payments. And then the payments, I'm going to make this one wider. Let's imagine that we have PayPal payments. So some people paid by PayPal payments standard, standard. And let's say that they paid us then the 1299.55. All right, so let's make this one a header. We're going to make it black and white guy by going to the home tab fought group black and white. And then we're also going to have the Shopify payments. So I'm going to say Shopify payments. And that's going to be let's say 370.36. So what's happening here is we have multiple customers that bought stuff from our online Shopify store. Some of them bought stuff using the third party payment processor of PayPal. So the money is going to ultimately then go through PayPal, which is also going to be adding some fees to it that we will have to deal with on the PayPal level, possibly with the PayPal Bank Feed App Connect thing. And then some of them paid with the Shopify pay and with like a credit card or something with Shopify, in which case they're paid through Shopify and then Shopify is going to add some of the fees on the payment. So the ones that actually were paid out from Shopify. We will have fees that were that were charged by them, right? So and then and then that money is going to come from them to our checking account, which we will see on the bank feeds. Okay, so let's say we have then the total payments. Total payments is going to be equal to the sum of these two. So there it is. I'm going to make a slight adjustment over here and imagine that we also had sales tax that we collected. And so let's add that into our equation of, let's say it was 15.45. So now we've got the total sales of the 1-669-91. And then the payouts that have taken place that are broken out between the PayPal and the Shopify information. And obviously when I'm thinking about total sales, we would have to be breaking this up for the sales tax because the sales tax is something that we collected that we're including in the total sales number here, but is actually not really sales from a tax perspective because we would have to be paying that out to the tax collectors. So we'll talk more about that in the future. So let's go into that. I'm going to format this and make this home tab font group and let's put some borders around it. And then let's put an underline here. So we're going to do with the underline. And then the amount that's going to be paid out by Shopify is going to have those fees. So now I'm going to put the payout here, the payouts from Shopify, the payouts from PayPal. We would see the fees from the PayPal side of things. So I'm going to say payouts on Shopify, black and white. And let's say that this is going to be the gross amount, which is going to be equal that 370-36. And let's say that the fees from Shopify are a negative 15.48. Let's say and font group and underline. That means the amount that's actually going to hit our bank account in the bank feeds is going to be equals to the sum of these two, the 354.88. And that's what we put into our bank feeds in our example over here in the banking. We would eventually see that amount pull in. But obviously there's a lot of stuff happening that in practice would be ideal to be able to break out. And that's where we get into different methods of doing that. And then on the PayPal side, we put the PayPal payment in there here as of the full amount of the 1299.55. But likely PayPal would actually be charging fees on their end. So the amount that actually hits our account might be less than that. And if you use the PayPal app, then it might give you this little calculation here to actually break out the fees on the PayPal side if you're using PayPal, which is kind of cool. All right, let's put some brackets around this. And then I'm going to build a little worksheet to see this on a journal entry standpoint so that we can do some comparisons between these different methods. If you don't want to build this in Excel, you could just follow along if you so choose. But I'll build this out so we can see what we're doing. I'm going to make this skinny column by going to the home tab, format painter and make a skinny column over here. And then I'm going to say that we're going to have our journal entries, journal entries. And I'll make this a little bit wider. We'll be here and let's make this headers black and white. And then I'm going to make another skinny column. So I'm going to highlight this skinny home tab, format painter and make a skinny I column. And then I'm going to make like a trial balance. So this is going to be our trial balance. And I'm going to say these are the accounts accounts. And then I'm going to have the beginning trial. I'm going to put on the second column balance. And then I'm going to have our journal entries posted in the middle and then the end trial balance. That's going to be our format. I'll bring this down to the bottom. I'm going to make this a little wider. And I'm going to center these items by going to the home tab alignment center. I'm going to make this whole thing black and white font group black and white. And then I'm going to add my accounts. I'm going to call it. I'm just going to type these in here fairly quickly checking. I'm going to have a pay pal checking. This is like our chart of accounts. Shopify pay pal clearing. This is where we deal with these clearing accounts. We'll look about later. Shopify payments clearing inventory sales tax payable equity. Shopify sales. Shopify. Shopify discounts. Shopify returns. Shopify shipping income cost of goods sold and fees and charges. I may not have spelled all that correctly, but hopefully I did. And then I'm going to say this will be the total debits and credits. And then I'll have my net income income. Now there's not going to be anything in the starting number. So I'm just going to put 00000 in our starting numbers here. 0000. And then I'll sum this up equals the sum. Just building a trial balance here equals the sum of these. There we have it. And I'll put an underline here. And then my ending balance is just going to be this plus the journal entry. So I'm going to use my sum equals the sum of these two. Enter. And I'm going to put my cursor on the fill handle and drag that formula down. So I'm just summing across like so. And then this is summing up. So I'm going to copy that across fill handle here. Copy that across. Boom. Okay. My midpoint is right here. So everything above that's the balance sheet. Everything below that is the income statement. Home tab font group. I usually make that like a dark blue and white so I could see that midpoint. And then let's put some brackets around this whole thing. Could always use some brackets to help out bracketing. And then let's make this. That's fine. And then let's make this middle column. This is where the data input is going to go. I usually make that blue font tab drop down. And I'm going to go to the more colors. And I'm in the standard color wheel. I'm going to use that blue. That's the blue I like to use. So there it is. And then my net income is going to be everything from equity down income and expenses. So I'm going to sum this up from here and copy that across like so. And then I could make this a little bit more fancy because if these are out of balance, it'll show that they're out of balance. I'm going to use conditional formatting to make this a little fancy. So I'm going to select these items, home tab, conditional formatting. And I'm going to say if this is less than or let's say greater than, if it's greater than let's say two, so we have some rounding, I want to make it red. And I'm going to do it again conditional formatting. If it's less than negative two, make it red. And then conditional formatting one more time. If it's between negative two and two, make it green. So now it's green. And if any of this turns into like three or negative three, it turns red. So movie being let's go back here. So now that we have our format, I'm going to put some formatting here so I can see the blue formatting. I'm going to go to the home tab font group, borders and blue. Now if I was just if I'm if I added all these accounts and I'm not going to use most of them in the first method and then we'll use them in the second method. Now if I'm just going to wait till something clears the bank, what's going to happen? The easy method. What's the easy method? We're just going to say, all right, checking account is going to go up by this amount that's finally going to get there after all that other stuff happened. And then on the other side, we're just going to record that to Shopify sales. So Shopify sales and boom, that's going to be the debit and the credit. So if you don't understand debits and credits, that's okay. It's it's going to you can see the the equaling process that's going to happen in the double entry accounting system. I'm going to post that into our ledger now, putting my cursor in L three. This equals the debit three fifty four eighty eight and then the credit is going to be down here equals the three fifty four eighty eight. So that of course increases our net income. Notice negative numbers are actually good on the income statement. So I'm going to try to conditionally format this. So I'm going to select this whole thing and say what I would like it to do. Let's let's right click on it. I'm going to format this thing. So so it's green when it's negative because I'm going to format it. And I'm going to say don't do that red thing. Don't do that red thing. And then I'm going to I'm going to make it standards usually be black and white. So I'm going to go down here and say let's make this black and white. But then I'm going to have it turn green if it's if it's a negative number because that's actually a positive net income. So I'm going to go up top because that's a credit, not a negative net income. So I'm going to say let's say if this is less than zero. Then we want you to not be red but be green custom for I'm going to do a custom one down here and just go to the font. And then I want to change the color to like this green. So it's going to be that green. And then we could say if it's negative it's going to turn red. But let's just keep it at that. I'm going to make these two black and white as well. So font group black and white. So there's our custom formatting. And then on the PayPal side of things what we will see ultimately is hopefully this amount coming into PayPal. So I'm going to say in the PayPal amount we're going to have this 129955 which will in practice probably have fees related to it. But let's just imagine that full amount comes in from the PayPal side. And the other side once again is just going to go to income. Just a nice easy transaction once it hits the checking account. I'm going to say negative of that number and let's post this what's going to happen. The checking account for PayPal is going to go up and then Shopify sales has something in it. So I'm going to double click on it go to the end of it and say plus and point to this 129955 and enter. I'm going to make the columns a little bit wider so I could see that last bit. And there's our transactions and that's as easy as it would be. That's all we would do. So if we were doing this method to wait till it comes through to hit the bank. Now the problem of course is that this revenue isn't exactly accurate because there's other things happening. If I look at the detail of the report we had the discounts that are happening. We had shipping that we might break out separately. And then if we have sales tax that we're responsible for we would have to come up with some type of method to make sure that we are in compliance with the sales tax and paying out the sales tax in that case as well. Also at the end of the year what we're probably doing this for one primary purpose is to get an income statement if we're a small sole proprietor business. So we can put it on the schedule C for our income taxes and we might get a 1099 from let's say we got a form 1099 that is set that says gross sales are 167.42. Let's just say or let's say that the gross sales they're saying are equal to like this number. Let's just say that's what we got the 1099 for. So the problem is if I if I highlight this I'll make this red and white that if I report on on my schedule C this number because it's less than the amount that's reported on the 1099 and this might be an insignificant or immaterial amount right here. But if we had larger sales that will become a material amount the IRS is going to say well hey it I'm I'm showing on this 1099 that we have over here that you had more gross revenue than you are reporting. So you know the easiest way to kind of deal with that is to say OK well the 1099 is correct. I'm going to assume the 1099 is correct and the difference are all these other things such as the fees over here and these other kind of categories discounts and whatnot. And of course I would still have to do deal with the sales tax because the sales tax would be something I'd have to pay for sales tax. But but the general idea for federal income taxes is we might then be able to say at the end of the year OK well I'm going to increase my Shopify sales to what the 1099 says because I believe that's going to be correct and I believe the difference is going to be fees right that would be the easiest kind of estimate or assumption to try to make. At the end of the year you might have something like well I'm just going to dump everything into fees for the difference between what the 1099 says and what what my Shopify sales are which we're going to say hold on that's going to be this minus and I'm just going to hard code this number one six five four point four three. So it's a 15 difference and I'm just going to put that into the sales. So now the sales so we're going to have the fees and the sales are going to go up. So now I'm going to say OK Shopify sales I'm going to increase it by the 1548 to match in essence the 1099 and then I'm just going to assume the rest. I'm just going to dump it all into fees although there could be a whole bunch of things that is compiling to make basically that difference. That would be the easiest kind of adjustment without all the detail to try to try to put in place. Now if you did this on in actually QuickBooks here let's just mirror the same thing in QuickBooks. So now in the checking account we see this amount that's coming in to the checking account and obviously the easiest thing to do without any integrations or anything other than the bank feeds is just to say I'm going to add this and I'm going to put it into the sales for for Shopify. So we're going to put it into I'm just going to say Shopify sales right I'm just going to put it into an income account. And so this is going to it's it's going to increase then the checking account and it's going to go into the revenue account. So let's just add it from the bank feeds and I'm going to go back on over to my balance sheet and check it out. So now the checking account would increase. So I'm going to go into the activity here and we have our deposit. It was done with a deposit form. So if I go into the deposit we see a deposit form was created and the other side is just going to income closing this back out scrolling back up. Exit that the other side on income tapping to the right running the report goes into the Shopify sales. So there's our sales amount and then the amount that was paid through PayPal would ultimately come through PayPal. It might not be for this full amount however because PayPal might have taken fees and likely would have taken fees on the PayPal processing side of things. But we'll just take it from here. And so once again it's going to go into the the income account for I'm just going to call it Shopify sales right. So it's ultimately going to come in to Shopify sales and I'm just going to charge it to Shopify sales and note that if you were using the PayPal commerce thing it might actually break out the fees that PayPal charges for you as well. But I'm just going to add that so we're going to say all right now on the balance sheet. If I run the balance sheet now we've got our checking account and we've got the PayPal acting in essence like a checking account with the bank feed set up. And so some this both came from the same place the Shopify store but part of it got paid through the third party payment processor PayPal and then on the profit and loss. If I run the report here we're going to say that we have this 1654 43 so it's quite easy to do. Now of course we have that sales tax issue so we would still need to to make sure that we have a system picked up so that we can pick up the sales tax. We might talk more about sales tax in a future presentation but that's going to be different depending on the different platforms you're going to using and what tax you're subject to with Shopify so we might talk about that later. But now let's just think about at the end of the year if you had that 1099 situation. So now we're saying OK the 10 I'm trying to do my taxes and the 1099 says I have 10669 91 of income and we can just basically do a journal entry and assume it's it's fees right the difference. I'm going to say OK I want my revenue to match at least to be as high as the 1099 which I assume is correct. And the and the difference I would assume would be the fees so that I am reporting something on my taxes that is at least as high as the reporting of the 1099 in gross sales because the 1099 does not report net sales. Typically it reports the gross right so I can go back on over and say alright let's let's go over here and make a journal entry. And I'm going to say at the end this would happen like maybe at the end of the year and you're going to say alright journal entry and we're going to say that this is going to be as of 1130 to five. So I think we're doing the month of November and we're going to say this is fees and charges. I'm just going to make an account called fees and charges tab. Now you could make this a cost a good sold or an expense account. I'm just going to make it an expense account here and then it's going to be fees and charges not advertising. I'll just say it's other business expense and then the other side is going to be Shopify sales. Alright so the fees and charges it's for that 15.48 and then you might say to adjust gross income to 1099 or something like that. And then over here the other side is 15.48. So there's our journal entry and I can say okay let's save it and close it. And what happens on the income statement we can go over here and say let's run this and say okay I just did the simplest thing possible to try to say report everything based on the bank feeds. I don't have all the detail of all the information being broken out but the 1099 I know is supposed to be reported on gross sales. So I can basically try to say the difference of all that stuff is going to be the fees. So we have something at least on the income statement that possibly we can match out to our schedule C and work with. Now obviously we're still missing a lot of we're still missing data that could be more detailed to help us with our internal kind of business decisions. And with the sales tax is all is always going to be an added kind of thing that we have to make sure that we have some type of system to be dealing with sales tax being a different type of thing. Depending on whether we're dealing with a Shopify situation versus an Amazon situation and whether we're dealing with sales tax in our location state or our subject to sales tax and other kind of locations as well. But that's the general idea and so next time we'll we'll map out a similar kind of thing here but imagine that we are going to then then break it out and and and use kind of a journal entry method to break out more of the detail using using a report method meaning we're going to imagine we look at a report and Shopify and then enter a journal entry and use the clearing accounts to as we go enter more detail as opposed to kind of kind of just adjusting to the 1099 like at the end of the year. So a bit more complex method that provides us more detail.