 QuickBooks Online 2023 E-commerce sales data to QuickBooks Methods Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 2023 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 In prior presentations we've been looking at e-commerce situations where we sell inventory but rather than selling that inventory on ground in a store location We're selling it in an online cloud based location possibly with the help, support and use of third party applications Popular applications being for example a Shopify or Amazon type of situation So we want to think about this e-commerce from the perspective of an accounting perspective Which means we would like to be taking that information and putting it into a useful format That usually being financial statements, balance sheet, income statement at the minimum to help us with our federal income tax obligations which we would have if located in the United States This is usually going to require integrations with financial software like QuickBooks Online which will be our primary perspective In other words we're thinking from an accounting perspective of using the software of QuickBooks Online And we want to be able to pull in the financial information that is largely being generated from third party applications such as a Shopify or an Amazon So it's useful to think of two sides of the e-commerce types of transactions and we've gone into this in a bit more detail in prior presentations When you think about a full service perpetual inventory system then these transactions happen at the same time Meaning when we sell inventory we're having an impact on cost to goods sold and inventory as well as the sales and cash side or accounts receivable But when we're trying to pull this information in from a third party application it's useful to break those two up and instead of thinking about a perpetual inventory system Possibly thinking about a periodic inventory system So now we're going to be focused just on the sales side of things So the sales side of things and rather than the inventory side of things which we'll talk about later Meaning the sales are happening on the third party platforms like a Shopify or an Amazon They are facilitating the sales We want to pull that information into our system so we have the applicable revenue We can track our bank account and then there's these other fees that are going to add some more complexity into the problem Of just dealing with the sales side not dealing with the relationship of inventory of cost to goods sold at this point So data can be pulled in with the bank feeds that's one method that we could use We could just say hey look I'm going to let the third party platform do whatever the third party platform does in order to facilitate the sale Meaning of course you have a Shopify or an Amazon web based location Somebody buys the goods not in a store but online in the web based location They put it in their shopping cart and they purchase it and hopefully these third party platforms are dealing with all the logistics and everything of it And at some point you get a revenue that's going to go into your checking account We're going to have our bank feeds set up in QuickBooks to the checking account So even without an integration to these third party platforms which can get a little bit more messy It's more secure it's more we've got down pat or it's an easier integration to integrate just to your bank account Which is going to be something that pretty much every business is going to do And if we could just pull the information in from the bank that would be the easiest thing to do but there's pros and cons to doing that So more sophisticated integrations may be needed to get more detail like fees charged back and refunds That's the problem with just using the bank feeds So remember that when we're looking at the people that are involved the systems that are involved in order to get to the point in time of a sale We have the store location making the sale Amazon or Shopify or eBay or whatever the web based location Then we could have a separate payment processor that being like a PayPal or Stripe or whatever is processing the payments And then finally we've got the QuickBooks or zero or whatever accounting system These are common to accounting systems that could be linked to an e-commerce type of situation Our focus is QuickBooks online here So that means that when we're thinking about our integrations of softwares you might be saying Okay look I just want my accounting software QuickBooks to be connected to the bank And then Shopify and PayPal or Stripe can do whatever they do And I'll wait till it hits my bank and I'll deal with it then but we're going to lose detail if we do that We can think that I'm going to connect my QuickBooks instead to both the bank and to the store Like a Shopify or Amazon and that will be a little bit more complicated of a method but it might give us some more details There could be different ways that we can do that But if we also have an intermediate payment processor which we may not always do Like if you start a Shopify store you might just use the Shopify payment methods and not allow other processors like a PayPal in there And if you do that then you may not have this middle connection But most people want to open up all the payment possibilities because it might increase the possibility of having a sale But in any case then we can think about pulling the information in here But if we have PayPal and Stripe then these intermediary payment processors could have transactions happening at their level At least the fees that they're going to charge and possibly chargebacks can happen at this level Instead of at the Shopify or Amazon or eBay level So that's where the complications come in to pulling this data into the QuickBooks software And that's where we come up with these different methods that we might use to pull the information in So methods to get sales data into QuickBooks Well one, we can just use the bank feeds Or we can have a manual entry of reports from the commerce platform and bank feeds In other words, I can run a report from the platform and then pull that information into QuickBooks Summarizing it with a journal entry that will ultimately tie out to the bank feeds The bank feeds will always be in an integration typically We could use the QBO QuickBooks Online Commerce Connect to Sales Channel and bank feeds In other words, QuickBooks Online is putted into QuickBooks Online connections through QuickBooks Online Instead of using third party applications to things like a Shopify, Amazon and eBay But that's still a new process, so we'll talk more about that in a second And then you've got third party apps like A2X and bank feeds So then you can go to a third party app which can help pull in this information into the system Let's go through each of these in a bit more detail The first one, the bank feed method, the simplest type of method What are the pros and cons of it? On the pros side, it's easy because you're just going to wait until the revenue We're talking about the revenue side right now You're going to wait until the revenue that happened from the third party platforms hits your bank So they do whatever they do to facilitate the transaction, they sell it and whatnot It hits your bank, we're not talking about the inventory, the purchasing side now We're just talking about the revenue side, you wait until it hits your bank and then you record it as revenue So no need for multiple integrations Obviously the more integrations we have, whether they be through QuickBooks or outside of QuickBooks Does add a level of complexity The bank feeds, of course, many people are pretty comfortable with at this point in time Even though it's an integration with the bank But then when we integrate also to third party platforms and to like a Shopify or Amazon Those different integrations pulling in can add obviously more complexity quite quickly So if we can limit that, that would be great, obviously But there's pros and cons to limiting the information So what are the cons then? We'll pull only the net sales So you're going to have very limited information Because remember that on the Shopify side of things or on Amazon side of things They might be doing a whole lot more than just facilitating the sale Like if you were making the sale in your store Then you would charge the sales price and you might still have to do a sales tax And you might have charge backs and those kind of things that happen And then if you do it in an online store You have those things and you have to add on the fact that you're going to have fees That are going to be processed by possibly the Shopify's and the Amazon's and whatnot And then possibly fees for the payment processors So by the time you get the revenue on your books You're recording revenue as the net amount after all those fees, charge backs and whatever happened As opposed to having sales recorded at the gross amount Minus the fees, the charge backs and all that kind of stuff Now you might say I don't care what's the difference I'm good with that There could be an issue with that for taxes Because when you get a 1099 Either from the store or from the payment processors Then it's going to show you the gross sales So you might be able to work around that Because then you can just assume the difference between your 1099 and your net sales Are charges We'll talk more about that in the future But at the minimum You're going to have that issue that you need to properly report your taxes And you need to properly deal with your sales taxes So it does not account for fees, charge backs On either the commerce platform or the payment processor level Because you're not dealing with anything that's happening until the deposit actually hits the bank Resulting in total sales possibly being less than 1099 reports And then you'll have timing issues as well Because the sale could have happened in the prior month or year And then it didn't hit your bank until the current month or year If you're consistent Then it might not be a big issue as long as you're doing a consistent reporting method But that timing method is another issue This 1099 issue often causes problems Because if you get a 1099 that says Hey, you have income for federal income taxes And you report something on your schedule See that is less than the amount on the 1099 Then it's likely the IRS is going to question your return Because they're going to say Hey look, the 1099 says you had more sales than that So one workaround on that would be to assume That of course the 1099 is correct And the difference between your net sales that you recorded at sales And the 1099 amount is the returns and charge backs And therefore increase the sales amount to be equivalent At least to the 1099 amount And then have fees and charge backs To reduce it back down to the proper net income amount Then we have the manual entry of reports from commerce platform and bank feed So how could I get a little bit more detail than you might ask Alright, so we could go actually to the e-commerce platform Or which is like a Shopify or the Amazons And print out and try to get the reports that they are giving to us As they make the payouts So they're basically going to make the payouts They might make the payouts on a daily basis Or like every two weeks or something like that And we can go in there and get the reports Which should give us not only the bottom line of the payout But also the detail of the payout Like fees and charge backs and all kinds of other stuff That could be included So what's the pros? It's still fairly simple to do this method Although it does require of course a journal entry Provides more details Breaking out gross sales and charges to get net deposit That's the goal of doing this Instead of just having the net amount hit your bank account You're looking to break out all the detail to more properly report sales And then all the fees, revenue and expenses As opposed to just net revenue as sales on the income statement More accurate data for taxes for decision making So if you do this method it's more likely That your sales line will be more accurate And your expenses will be more accurate And therefore when you get the 1099 for example That's trying to tell you you need to report this amount For taxes on your gross sales The top line of the income statement You should have a number that's equal to or greater Than that amount which you would think would be a more proper number Because you're reporting more of the detail in the gross sales line Less integrations needed with other methods So this method is an attempt to try to simply just use the bank feeds still But pull in more information at the same time So you get more financial data for decision making and for taxes While not having to more complicate the system with other kind of integrations And then it's less costly Sometimes third party applications can cost more to pull in the integration So obviously this is a cheaper method to try to do a little bit of a step up From just the bank feeds and it can help with timing issues Meaning you can try to look at when the sale actually happened Versus when it hit the bank account and properly record the sales transactions In the time period they happened So if there's a cut off issue the sale happened in December But you didn't get the deposit till January Then the sale on an accrual basis should happen generally in December Cons, much more time consuming Obviously if the reports come out every day Then we got to do a transaction on a daily If they happen every two weeks then we got to go in there and update the transactions You got to make sure to do that because if you don't Then it's going to be quite messy to go in and kind of try to fix everything At one point in time it's not just an automated You're not just going to turn it on it's just going to be automated You got to go in there and do the updates periodically There's a learning curve to understand what to do So clearly there's going to be a journal entry Most people are quite overwhelmed with journal entries Once you understand the process it's not too bad But you got to first think about what's going to happen If you have a little bit of accounting background That would be quite useful when you're doing the journal entries Alright, the next method you got the QBO QuickBooks Online Commerce Connect to Sales Channel and Bank Feeds So this is a newer thing at the point in time of this recording Where QuickBooks Online, my interpretation basically said Hey look, these third party integrations are coming up with these integrations And a lot of people are confused as to what the best integrations are And of course there can be costs with the integrations So QuickBooks Online is attempting to put their own e-commerce integration solutions Into the standard purchase of QuickBooks Online And that would be integrations with some platforms which would include I believe Shopify, Amazon and eBay So there's pros and cons of this Remember that this is still new So that's one of the things that we have to kind of keep in mind So what are the pros and cons? It's simple to set up So it's obviously within QuickBooks Online So you've already signed up to QuickBooks Online You can have your bank feed set up And then it's pretty easy if you have like a Shopify or Amazon To turn on the e-commerce within it Although it is another integration It is not using a different third party app So the fact that we're not going to another company Another third party app is nice It's still kind of an integration Because now we've got the bank feeds that are pulling in from the bank It's still pulling in from a third party But we feel fairly secure with that at these point Most bookkeepers count and stew But now we're pulling in data from another system Again, which is the Shopify and whatnot And we're doing it not with a third party app Which kind of makes us feel a little bit more secure Possibly but we're still doing that integration And the QuickBooks Online, it's still a new thing You know, in QuickBooks Online So we don't have a whole lot of history to go on In terms of exactly how well it's panning out So it provides more detail Breaking out fees and other charges That's the point So it's going to be pulling in this information To break out the fees and the charges properly It bundles sales transactions to match deposits Sent from commerce platform So note, a lot of people when they see it Within QuickBooks you might think Because it's in QuickBooks It's going to pull in all the e-commerce transactions And integrate them as if it was an on-ground store Creating items and sales receipts And so on But that's not what they're doing at this point in time They might, again, they could change this app As it goes forward It's still kind of new at this point in time But from my understanding They've been talking to people that have been Dealing with this commerce situation And they've come to the general conclusion That many people do Is that a lot of most e-commerce businesses Probably don't want to pull in Every transaction information into QuickBooks Online And use that as part of the integration To put into their financial statements Because it would overload the system Because you already have that information on the other software Because you would have to enter the items and whatnot To make sure that those properly And exactly line up to what's in the other system It could bog down your QuickBooks system Because of volume of transactions and sales Which is volume is what you're usually going for So what they're trying to do Is bundle the transactions periodically So that they can kind of group them together So they can summarize the data Which makes it a more of a simplified thing And that's what some of these other third-party applications Do as well So it can help with timing issues So in other words The app actually might break out Like if a sales transactions happened in December And in January of the following year Then it could account for the transactions Of the sales that happened in that cutoff In December versus January And then the cons It is still new So there's not a lot of feedback at this point in time One of the great things about QuickBooks Is that it's so widely used That we have a lot of people That are commenting on it And making reviews on it And we feel pretty comfortable With a lot of the general functionality Of QuickBooks because of all of that This is still a fairly new thing And a lot of the people that are experts At e-commerce at this point in time Are probably still using whatever systems They were using before Whether that be bank feeds Manual reporting systems Or other third-party apps And so I think it's going to take some time Before people move from using The third-party apps that they're comfortable with Possibly to integrating with this one And obviously we'll have to see What develops from the integrations That they currently have So it does not currently track inventory This isn't necessarily a con Because they might in the future Have it so it tracks inventory But I believe that the rationale For not tracking inventory is the same rationale That we've talked about before Is that that's going to cause you Or force you to pull in every transaction And then set up your products and whatnot Which could weigh down your system So I put it in the con side of things Because a lot of people feel like They're just going to turn on this app And it's going to be reporting everything In a perpetual inventory system In a similar fashion as if we were running Inventory in an on-ground setting At this point in time That's not generally the way that It's being set up to do It's being set up to simplify The sales transactions that we're Pulling into the system Which is really what's been recommended By a lot of people that do a lot of e-commerce Because of the reasons we talked about So it works best for getting paid Directly for e-commerce payment portals When adding other payment portals Like PayPal and Stripe It generally will not pick up fees at that level So notice that we've talked about We've talked about the fact that Like if you set up your Shopify store And you only accept Shopify payments Therefore you don't use a PayPal Or something like that for people to pay you But you use Shopify's payment methods Then it should pull all the information In from Shopify quite well And the fees and the charge backs Will be linked because it's linking To the Shopify in that example But when you add that extra level of Complication such as an intermediary Payment processor like a PayPal Now you have some fees that are Happening at the PayPal level And this connection is connecting To the Shopify level So now you're going to have to deal with That problem which is a common kind of Issue with a lot of different integrations Because there's another level in there That we have to deal with So if you had like a Shopify store And you only turned on the Shopify payments And didn't let people pay you from like PayPal and other payment sources That's when it would work most smoothly Most likely But most people when you start a Shopify store Or something they want to turn on Any payment processor possibly because The thought is that will increase The likelihood of people facilitating Or buying stuff And then you end up with these Third party platforms and they have Their own fees and charge backs that could happen At that level which you might be able To deal with by having the PayPal Integration for example Which is kind of like the bank feeds But a modified bank feed thing So in any case And then you could have third party apps Like A2X and bank feed So this is similar to having The system within QuickBooks But now you have A third party app doing it So you can search for an app That fits your specific needs Now note when you're looking for third party apps There's a whole This is one of the reasons I think QuickBooks Online started their own Like integration internally Because the fact that you have Different types of apps Means that you know It's an overwhelming choice and you could use You could pick apps that aren't Best suited for what you're Trying to do. So some apps for example Will pull, will try to pull in All the information and again Track the inventory and all that kind of stuff But a lot Of people recommend that that might not Be the best route to go because You'll overload the system because You want to make sure that you understand Inventory because QuickBooks is not best suited For more advanced Inventory tracking oftentimes And so you want to be careful On which app That you're going to use. So some may Have inventory integrations But be careful which one You want to use. So Oftentimes we're trying to break out In our general thought process Instead of having a full service Perpetual inventory system We're thinking about possibly apps That break out the sales side And then deal with the cost of goods sold side Separately in a periodic inventory system So a good app will generally break Out fees and other charges Similar to what we're trying to do Here is break out the fees That's one of the points of the app Instead of just recording it as net amount On the income statement Which would be done if you just use bank Feeds and it can help with Timing issues meaning Those transactions that happen like in December In sales but you didn't get the deposit Until say January For example Also just note that some of these apps Like at like the A2X For example has A similar kind of process that we talked About with QuickBooks. So it's Not going to try to pull in every Transaction oftentimes But rather try to group the transactions Summarize the transactions And then pull them into your system So you have more detailed information We have similar kind of problems with That scenario as with the QuickBooks one in that if you're connecting To the store Itself and Then it works quite well but When you have that middle tier With the PayPal or something like that That could muddy up Muddy up the connection a bit But also on the Plus side I just want to point out that Of course some of these applications like the A2X have been In use for some time Whereas although the QuickBooks Is these accounting software you're using These integrations Are still new Within QuickBooks and you would think it would take some time For them to kind of work out the bugs Or do whatever they want To do with it although It looks like they're taking a strategy That is similar to Like an A2X where they're trying To kind of summarize the data that they're pulling into The system. Okay cons Working with another outside third party Integration so clearly now You're pulling in data from Your bank feeds likely And now you're pulling in data from The third party platforms And you're doing so With a third party app which isn't Like a big problem but again it's another Now you've got another Kind of company That you're basically Dealing with which is Kind of an issue. The wrong app could Pull more data than you need So this is where you want to be very careful On the app that you're choosing And make sure that you have a plan going into it Because if you choose an app That just pulls in every transaction Every sales transaction you make And you don't know what you're doing From an accounting perspective you will probably Be quite overwhelmed With a massive amount of data in QuickBooks And over time that'll Clog down way down your QuickBooks Most likely if you have a high volume Of sales so you want to make sure That you have an idea Of what the app is doing When you're pulling in the data May still have problems integrating fees At both platform level And the payment processor level Same kind of issue that we talked about With the QuickBooks integration If the app is connecting to Directly to like an A2X Is connecting directly to Shopify or Amazon or whatever Then it can Pull in that detailed information Usually in a summarized way And that's what we generally Are recommending here We summarize the data, we pull it in But when you have that third party Platform like a PayPal or whatnot That sometimes has the fees on its level That could add a level of complexity Which you might be able to solve Possibly by having a PayPal Integration which is basically Like a bank fee type of thing So those are the general Ways that we can go about doing this And now we'll go into some examples And play out some scenarios with them