 QuickBooks Online 2023 e-commerce, QBO commerce integration with Shopify, Amazon, or eBay. Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 2023. Here we are at 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 the cog up top and switching the view down below. Let's duplicate some tabs to put reports in by right-clicking the tab up top and duplicating it. Right-clicking the duplicated tab and duplicating it again. Back to the tab to the middle, reports on the left-hand side. We're opening up the first one, the balance sheet. Let's go to the tab to the right, the reports on the left again. This time, the profit and loss, the income statement. Closing up the hamburger, let's run this for the full year this time. A 2025, 010125 tab, 123125 tab, running it. Going to the middle tab, same thing. Closing the hamburger, changing the range to the same. 010125, 123125, and running it. Let's go back to the tab to the left. We've been thinking about e-commerce situations where we're selling inventory but not on ground in a store. But rather online in the cloud with the help and use of third-party apps such as a Shopify or an Amazon. And thinking about ways that we can pull that information from the third-party app into our accounting software. In prior presentations, we thought about manually pulling that information as well as just simply using 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. The bank feeds to record our data. Now we want to move on to the QuickBooks online integration that they have in the QuickBooks online system. So if you open up QuickBooks online and you go down to the commerce tab, that's the integration that we're going to be taking a look at now. So the general concept is going to be much the same, very similar to what we have looked at before remembering that our methods for being able to pull this information in from a third party platform into QuickBooks is either number one, just integrating the bank feeds and not using any other integration, but you're going to lose data. If you do that, it won't be as detailed or you can use a manual entry system, which we have taken a look in prior presentations. And that system will be similar to the next system we're looking at now, which is QBO commerce, because the QBO commerce is doing what most people kind of recommend on the bookkeeping side for many people that use a third party commerce platform, which is don't pull in all of the information in order to have each sale recorded, to have each customer recorded, because it's going to overload the information in QuickBooks and in order to properly account for things like the inventory on a perpetual inventory system, you would have to set up all the items properly and so on. And it could get quite complex to set all that up and it might be redundant because you already have that information for the most part in the Shopify side of things. So the idea would be that we're going to consolidate the data in the similar process we did with the journal entries, but see if we can automate that process so QuickBooks is going to pull in that information and try to group it together for us. Now third party apps, I could do a similar type of thing like an A2X is quite popular. And again, if you're looking at third party apps, there's some apps that might group the data together in a similar fashion as we're talking now, like an A2X and there's some apps that possibly means that you can pull in all of the data and try to recreate the information in QuickBooks online and try to track the inventory, for example, on a perpetual inventory system. Again, most a lot of bookkeepers don't really recommend that for many people trying to integrate QuickBooks in the third party platforms because it could be complicated and bogged down the system. So if we look at the commerce within QuickBooks online, it's still relatively new as of the time of this recording, but it looks like they're trying to do the most simplified type of thing, which would make sense for QuickBooks online. They're trying to streamline and automate things. So it looks like they're doing what most people kind of recommend in grouping this information as they pull it in. But because they're trying to make it simplified, there may be less options in it as there would be if you use some kind of other integrations like an A2X or something like that. So first, I'm going to jump on over here to the sample company file, which you can find and you might want to open a separate window if you want to open these two files up at the same time. If you have two files open so that it doesn't mix up your log in so you can open an incognito window if using Google Chrome by selecting the three dots, new incognito window, type into the search engine QuickBooks online test drive, select the option that has into it in the URL, into it being the owner of QuickBooks, and that's how we get here. I just want to scroll down to the commerce section in here because it's not turned on yet. So if you have not connected commerce, then the connection process is generally the easy process. So if you have something like a Shopify or an eBay or an Amazon, then you need to connect to it. And to do that, I'll just read through this quickly here. Seamlessly connect your sales channel will automatically bring your orders and payouts so you can track everything in one place. You can check out the little video if you'd like to get more information on it there. Brief summary, match payouts with bank deposits, run reports and see trends at a glance. Stay on top of cash flow effortlessly. And then of course you can go through the process of connecting your channel, choosing the item that you want to connect. For example, we turned on a Shopify channel, bringing your past orders. So what this means, you can view sales trends and insights across sales. So how far back do you want to go? So it depends on the platform in terms of how limited oftentimes or how far back you can go to bring in the data. Oftentimes you would want to bring the data in for an entire year if you can. And so I'm going to say next and then connect your Shopify and a few quick steps will get your accounts synced. First, you'll choose how much of your Shopify data to share with us. Two, next we'll take you to Shopify to sign in your account and authorize in three. Then we start bringing in the transactions. So I will continue here, need consent into it needs consent here. This is where they're going to bring the data in from. And then we can say, okay, I agree and consent. And then you'd have to finally give them your Shopify email. They already have the dot Shopify here and you can't really get rid of that. When you type in your URL, make sure that you're accounting for the fact that this piece is going to be connected to the end of it. If you double it up, if you put Shopify.com twice in there, it won't recognize your URL. So that is that. So that's the connection process. So once it's connected, it's going to look something like this. Now we have a very basic information. This is our Shopify store. So we're imagining stuff is happening over here in our Shopify store. We're making sales over here. We're tracking the inventory on this side of things, at least from, from a unit perspective so that I can stock my inventory and have sufficient inventory to meet what I presume will be the demand in the future and whatnot. Remember that as we pull this information, as we've talked about with the prior methods, we're breaking out the concept of the sales and inventory happening at the same time. In other words, we're not using a perpetual inventory system. We're going to use a periodic inventory system. So we're mainly thinking then about this integration pulling in the sales side of things and we're still going to have to deal with the inventory side of things. It having its own issues because it's going to be tied not to the payouts but to an inventory flow assumption like a FIFO or weighted average. So we'll deal with that later. This integration isn't generally designed at this point and QuickBooks could give you more options in the future. But I would think that they're going to try to keep it simplified and therefore not and have the so therefore your inventory will be a separate kind of thing. We're pulling in the sales side of things. We'll talk about inventory later. Okay, so the sales are happening here and then we've talked about before our finance reports here and our analysis. So in our analysis, we have our reports which can give us the detail about the payouts that are going to be happening. And then we looked at our finance reports and our payouts to see to pull the information in. So these payouts right here represent multiple sales that are happening on the third party platform that are being grouped together in one particular payout. When I come over here into QuickBooks then I'm not going to have a system where I pull in every transaction and try to enter a separate sales receipt for every customer in every transaction. Not pulling in all that kind of information. We're only going to pull in the summary of the information that's going to tie into the payouts. And then we're not going to be dealing with a perpetual inventory system in that case as well. Okay, so then if I go down here into commerce, let's just take a look at the overview. We've got three tabs up top. I'll close the hand boogie overview and then the orders and then the payments. Now note that most of this stuff stuff if I go to the overview is just for appearance, meaning it's not actually pulling the information into the QuickBooks system. So it's kind of giving you a nice look that is similar to the look that you would see over here on Shopify. It's just mirroring the same data in a different kind of format over here, but it's not adding to your QuickBooks. So that's what this first page is basically doing. It's giving you kind of a summary of that information that's coming directly from your third party store like a Shopify. Then you have your channels over here. So here's the channel. Now some people could have of course Shopify and Amazon and ebay and you might have multiple ebay's and Amazon's and whatnot. So then you would list out the different channels would be listed on the right hand side. We've just got the Shopify. If you wanted to connect a sales channel, then you can go into the connect button up top and you can connect multiple sales channels. The number of sales channels that you can connect could be limited to the subscription that you have. And then you've got this, which will take you a shortcut to the transactions and the profit and loss and you can take a look at some tips on down below. Then, and by the way, if I look at my Shopify for like this analysis, you can see this analysis is similar to what they're pulling in to QuickBooks here. So in any case, then I can go into the orders. Now the orders once again are not actually the things that are going to be posted to our system. This is mirroring the orders that are over here that are just pulling in each individual order. If we were to record each individual order into QuickBooks, that would look something like it would pull these each individual orders and into like a sales receipt kind of thing. What's happening? It's just showing you some of the detail within QuickBooks. You don't have to go to Shopify to see some of this detail. So for example, if I go over here and I look at my reports, for example, and I look at my total sales and your reports could differ depending on what, you know, how much you're paying on Shopify, what tier you're in. But here's, you know, each of my transactions, the individual purchases that took place that are pulling in here 1001, 1002, 1003, and so on for those individual items. But again, these aren't actually recording anything. Then we've got the refunds and back to the sales order and then the payouts. So these are the payouts that are actually happening. So this gives you a summary of the payout. The payout represents the money that's coming out of Shopify and is going to eventually hit our bank account. So we expect the payout to eventually go into our checking account. That's going to be the idea. So for example, if I go to the Shopify store and look at my payouts, here's that 715 of the payout. Here's the 715 here. If I want to see the detail of that payout, it's pulling in the detail to help make the journal entry. And just like what we did over here with our journal entry when we had like the two, you know, the components of the journal entry that we pulled in from Shopify, it's doing the same thing here. We've got the sales side of things. Here's going to be the transactions that are included in that particular payout. And then we have the expenses side of things. And this is showing the Shopify expenses. So that's similar to what we had over here when we looked at the two reports that are going to be in play here. If you go into your analytics in like a Shopify and take a look at the reports and we look at the total sales report. Then I don't think it's including that dollar right there. So here's the, you know, the gross sales, the net sales and the shipping and so on. And then this, it's really not including that $1 right there. So it comes to 870. 870 is this amount right here. And then if I go back on over here and I go to my payouts, which is in the finance and the payouts, then we can see that all of those transactions are included in this payout and then they charged a fee for the payout fee, which is, you know, the fee for them to do the financing of it or to facilitate the transaction. And that comes out to that 155. So those are the two sides that QuickBooks nice and neatly pulls into this little review item here. So you can see it there, which is nice, but this still isn't posting anything. So then if I want to, you have transactions to review. If I go into those transactions, if I say go to those transactions, they then I, they took me up here from the commerce tab up to the banking tab. Now in the banking tab, we have our bank feeds, which we connected to the bank. So we expect that $7 to eventually hit the checking account. We would, we would see that, that amount that we were talking about 715. I think it was hit the checking account. It would be in here. And but we're trying to break out the detail of it in a similar way like we did with the, with the manual method. So if I go to the apps tab, this is where it's going to store that data. So now you've got your apps tab. Here's the Shopify stores. It looks similar kind of like to the bank feeds. If I had multiple channels, I can toggle between, you know, the multiple channels here. And then you've got a similar layout for review, reviewed and excluded. You can change basically your date range here. And here's the detail of the transaction. So it's got the sales, it's got the shipping income, and then there's the subtotal and then the selling fees. So you can see this lump sum is going to be the 715 that's going into our checking account, but it's breaking that information out basically kind of with a journal entry format here, not in the bank feeds, but in, in the app transaction. I can, I go to more of the detail here. Let's take a look at it in this case. And this takes us back to that detail page that we looked at before to give us the actual sales that took place and the expenses. Now, before I record this, let's just take a look at how we can adjust the mapping. If we, if we want to see what it's going to do when we, when we actually record this item. So let's go back down to the commerce tab and I'm going to close the hamburger and I'm going to go to the, to the overview. And each one of these channels, we're, is going to have a dropdown up top. So if I hit the dropdown, I can go into the settings. So let's take a look at the settings. So up top we've got the general settings, deposit account, the checking account, payment account, the checking account, payout confirmation. How would you like to confirm payments? This setting takes effect for future transactions. The default is manual because I'm going to say, Hey, look, I want to see these in that, that transactions tab. And then I want to manually say, okay, I approve of that transaction, pull it in. It's probably recommended to do that for a while until you get to the point that you are comfortable and then you can automate the transaction. Because like with the bank feeds, nothing's really affecting your financial statements until you pull it in from that feeds transaction. And so you probably want to double check it on a manual system and then pull it over. So this one says check each payout in for review, then confirm. And this will automatically confirm connect after. So this would be the disconnect if you wanted to disconnect here. If we go into the items, they have the sales items, which is going to be pointing to the default in QuickBooks sales Shopify item shipping Shopify shipping item and then discount Shopify discount. You can edit these items here and then change, change them here. And so I'll cancel that. These are going to be the defaults and then you've got the customers and vendors. Now note, we're not pulling in all the customers. All the customer data is going to be over here in your Shopify store. And that's probably where you want it because you're usually going to be a bulk seller. You're trying to sell a bunch of stuff on like a Shopify or an Amazon. You don't really need to know all the information about the customers other than put their email on your email list most of the time, right? So they're not so they need to have a customer. So they're just going to put it into a generic customer, meaning this is going to be one customer for all transactions that are going through your Shopify store. If you need to have more detail about a particular transaction, then you're not going to go to it in QuickBooks because we didn't pull in all that data. But rather you're going to go to the Shopify, right? And then on the advanced, these are the mapping accounts. So these are kind of like the defaults that it sets up basically automatically. So you've got a clearing account. This holds Shopify payouts before they're reconciled with your bank account. So just like we did in the manual method, we still have to deal with this clearing account, which means when the money is coming in and it's not going to deposit directly into the checking account, it's going to put it into the clearing account and then we can match it to the deposit that's going to clear the checking account, which will bring the clearing account back down. Same concept as we looked at with the manual method. It's just trying to automate it now. Then you've got the sales Shopify item sales is QuickBooks categorization Shopify sales income account shipping income. These are the charges that you're going to have for the shipping. It's going to go to the Shopify shipping income account. And then you have the discounts, promotions applied to sale Shopify discounts. It's going to break that one out and then selling fees, commissions and fees related to sales. It's going to go into the Shopify selling fees. If you want to adjust these accounts, then you can map it differently, right? I can go into here. Here's my list of accounts. I can add new accounts if I so choose. So I'm going to close that, but I'm going to keep the defaults here. So if you've been doing your system prior to this and you already have mapped out accounts, then you might want to use those accounts and you can change the mapping here to the accounts that you have set up. And then you've got subscription fees. What you pay for your Shopify subscription, Shopify subscription fees is where it's going to go. And then adjustments, channel adjustments and miscellaneous Shopify other adjustments. And then you've got the sales tax based on your region Shopify sales tax. And then we've got the reserve balance payout balance reserved by channel Shopify reserve balance. So that's the general mapping. So if I close this back out and I open up my chart of accounts, for example, if I go down to my accounting down here. And I look at my chart of accounts. When I set up the Shopify, it set up those accounts for me, right? So here's a clearing account. Here's the Shopify item here. And it set up some of those accounts. Shopify sales tax could have set that up, but it's good. Those accounts that are mapped out are now over here in the chart of accounts. I've added a bunch of other accounts as well when we've been doing the practice problems. So it's getting a little muddled at this point. Notice if I go in the sales tab and I look at the customers, then we've got our customers here. And it set up this Shopify customer. So it's not going to add all the customers. Of course, again, it's just made one customer. That's going to be our lump sum customer. Now also remember there's the same kind of issue with this system as we had with the manual system, which is that Shopify is now kind of connecting to QuickBooks, so that QuickBooks can pull in the detailed information breaking out the deposit. But if you're dealing with other payment processors like a PayPal or a strike, for example, meaning customers are paying you through PayPal or Stripe, something other than Shopify pay, like not using just their credit card, for example, on Shopify pay or something, then you might have other items that are going to be on the PayPal level that you're going to have to deal with for fees on that level. So this is going to be a common problem with many different kinds of integrations that we saw. So that's something that you've got to be mindful of as well. So the question would be, do you have PayPal turned on? Can they pay you through PayPal or can they only pay you through the Shopify pay? If you have the PayPal turned on, you want to make sure that you have a system that's going to be dealing with the PayPal component of the payment similar process or problem that we talked about with the manual system. All right, so then if we were going to approve these, let's go back on up top into the banking and say, let's go ahead and add this one. So this one came through, we analyzed it and we didn't auto add it. We said we want to add it manually. So let's go ahead and add it. Boom. The 715 is in place. Now I should be able to map that in the future. I would be able to see in the checking account that 715 and I can match it out. But let's see what happens over here. Let's run this for 010123 to 12312323 this time on the balance sheet. So note that it's got its clearing account here, but it actually puts the transaction in and out of the clearing account and does put it into the checking account ultimately. So if I go into the checking account, there's our 7.15. Note, however, if I go back on over here and exit out that if I go into this account, the clearing account, what it actually did was go in and out of this clearing account. So within this clearing account, you've got these transactions. It created a sales receipt and expense and then a deposit. And it basically increases and decreased the clearing account as part of the transaction, but ultimately did put it into the checking account, which does remove one added step on our end if the deposit comes in perfectly to the exact dollar amount. Because now it should be in our checking account over here. So if I go into my checking account, if I go into my bank feeds and go into my banking checking, if the deposit that hits my checking account matches exactly what that journal entry did, then notice it'll just match up here and it'll record a match. So when I record the deposit on the bank feed side of things, no new transaction will happen. It's just basically helping with the bank reconciliation. We're just saying, okay, we found that it matches out to the deposit that was put in there by the journal entry process that was done through the bank feed Shopify app connection. All right, so let's take a look at the income statement. If I go to the income statement and I'm running it for 2023 because that's when we put the information in. And now we've got the Shopify sales. So if I go into the Shopify sales, we've got our detail here. I'm going to hit the drop down on the Shopify sales and it created in essence a sales receipt, but it's a group sales receipt. So the sales receipt is the form typically used for a cashed based type of sale that you would use in a cash register, but note the customer is not each customer. It's just one lump sum customer that was used to create that sales receipt. So if I go back on over and exit, then we've got that. We've got the Shopify shipping income. So it broke out the shipping income that we charged for us. And once again, it did that here with the sales receipt form going back on over and exit. So instead of just having a journal entry, it did kind of a similar thing we did with a journal entry with the sales receipt. It's got the Shopify selling fees, which it did put under the categorization of cost of goods sold. So if I go into the selling fees and hit the drop down here, now we've got the selling fees that broke out individually per that data that we saw that's pulling in for all the different sales transactions that happened. Let's X out of that. And then we've got the the shipping selling fees here and then that is it. Now, if I go back on over to the first time and we go to our app transactions, notice we were in the for review, then we added it. So now it's in the reviewed section and you can see kind of the detail which will link to what it did here as well. You can go into this one. This is the the sales receipt that it generated. You can see what it built. It built a sales receipt, which is typically the form used for a cash based sales that like a cash register situation that we just saw sales receipt. And I'll close that back out. Here's the expense form that was generated. So expense form like a check form decreasing the checking account. But it's putting it into the the clear and account that it made up here specifically for this expense form. So we have those items and then we've got the deposit form, which is of course the form typically used to increase the checking account. So we made the deposit here, not with the bank feeds, but with the integration of the of the app. And then if I go back on over to the first tab, then we went to the banking and we can connect this out, which will just not record anything new because it's already in the checking account. It'll just verify helping us with like our reconciliation process. So it looks like, you know, they're doing a similar process that we did with the journal entry grouping everything together with this integration, which is kind of what is recommended. It works quite well. If you have a situation where your payment structures tied directly to the Shopify store in this case that the other payment processes just like we saw with the journal entry methods will add another wrinkle into the system that we'll have to deal with. You possibly can kind of work around with that system. They've recorded the transactions, not with like a normal journal entry, but rather they use the forms like the deposit forms expense forms and sales receipt forms, but they made group deposit forms sales that are combining sales together. So the general concept is much the same we saw with the journal entry method. So now the general idea then would be with the methods that you want to use would be, do you want to just do the simple method where you integrate the bank account? Do you want to then use a journal entry method, which has a lot of manual work to it? Of course. Do you want to use an integration? Possibly the QuickBooks integration here, which is still a new type of integration, but it does look like they're trying to do in essence what a lot of people recommend for the best practices of the third party integrations or applications like an A2X type of system. But even as you're doing these integrations, you've got to think about what's actually happening, which is similar to what we did with the journal entry. It's pulling in the information in a summary kind of format. It's not pulling in all of the information in terms of every sales type of transaction. And you would think that the QuickBooks system here looks to be set up the way the QuickBooks system is set up for the particular apps that Shopify, Amazon and eBay. And it's trying to make it as simple as possible, which means you might have less options than maybe you would have with a third party integration. So if you need to do more customization or do something different, maybe the third party integrations might give you more to do with that. But obviously you would think the QuickBooks having it in house here is kind of nice because then you don't have a third party. You don't have the payment for the third party. You don't have to deal with a third party application. So we'll have to see what QuickBooks does with it going forward. But there would be a similar process if you used a third party app. Usually most people recommend an app that does kind of a similar thing as we saw here. Pulling the information in like a group format, basically making a journal entry format and using a clearing account system as we saw with the manual method.