QuickBooks Online 2023 E-Commerce Inventory Tracking 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 We're thinking about E-Commerce situations where we sell inventory but not on-ground in a store but rather online in the cloud with the help, use and support of third-party applications for example like a Shopify or Amazon amongst others giving us that online presence where we can facilitate the sales and helping us with the logistics of facilitating the sales We're now looking at this from the accounting perspective and more specifically with the use of QuickBooks Online thinking about how we can pull the data into our accounting system so that we can get that data in a relevant form financial statements generally being a balance sheet and an income statement at the least so that we can do our tax preparation for federal income taxes if we are in the United States We talked about the concept in prior presentations of decoupling the inventory side of the transactions and the sales side of the transaction and related fees on the sales side because that could help us to pull the information that's already being generated with the help of third-party applications like the Shopify's and the Amazon So instead of using a perpetual inventory tracking system where you're generally going to be using a periodic inventory tracking system although we'll touch on the use of a perpetual inventory tracking system as well So now we are not thinking about trying to get the sales information in but we are now focusing in on the inventory and related expense, the cost of goods sold So what are the different kinds of tracking methods that we might use in an e-commerce situation to track our inventory and the related expense of cost of goods sold One method is the easiest method, we could use just a cash-based system and just expense the inventory as we purchase it using cost of goods sold as the expense account We'll go into each of these in more detail, I'll just list them out first and then we could have a perpetual inventory system using third-party application integration meaning we could use some integrations that would be pulling every transaction in from the third-party platforms such as the Shopify and Amazon into our QuickBooks system and try to track on a perpetual inventory method Then we could have a periodic inventory system using Excel or another spreadsheet In other words, we might use Excel or some other spreadsheet to be tracking our units of inventory in typically a flow assumption like FIFO or weighted average method and then putting periodic adjusting entries into our financial statements just to get the ending inventory and cost of goods sold correct but not to track the actual units in QuickBooks online because we're going to be tracking them elsewhere in a spreadsheet And then we could have inventory system using third-party app integration In other words, if we have more advanced inventory needs we might be having our Shopify or our Amazon helping us to generate the sales and we might then have a third-party app which is helping us to basically manage our inventory exclusively integrated into our system along with our QuickBooks system where we're going to have the financial statements Okay, so let's think about these methods in a bit more detail Easiest to most complex The easiest system is a cash-based system in which case when purchasing inventory possibly with the help and use of bank feeds In other words, you'll have the bank feeds turned on When you buy inventory there's going to be a decrease in the checking account which you will see in the bank feeds When you add that to the books instead of recording an increase in an asset account of inventory you will just expense it in the form of cost of goods sold Now the problem with this method is that it's not tracking inventory the way we're supposed to track it and it usually is supposed to be using an accrual component an accrual concept to it In other words, we're supposed to put it on the books as an asset when we purchase it and not expense it in the form of cost of goods sold until we actually sell the inventory That generally happens when we actually basically ship the inventory That's the general concept when the inventory goes out So for example, if you were using QuickBooks the triggering event in a perpetual inventory system will be when we create the invoice or the sales receipt That's when QuickBooks, if using a perpetual inventory system will generally decrease inventory and record the cost of goods sold However, normally for most of the methods we look at we're not going to be using a perpetual inventory system so we're going to have to come up with some other way to do this The easiest way would be to have a cash-based system although you're going to have that timing problem That timing problem may also be a problem for taxes if nothing else because for taxes, even if you're a sole proprietorship you usually have to do a cost of goods sold calculation which means you're going to need to know ending inventory and then possibly you can back into the cost of goods sold calculation with that You might be able to do a workaround for taxes In other words, once a year you might be able to back into your cost of goods sold calculation by counting the units of inventory you have at the end of the year looking at the current price of the inventory at that point in time and use that as your ending inventory number and then back into basically the cost of goods sold calculation So that would be the easiest thing to do It might get you by for your taxes However, it's still going to be somewhat limited on the details So let's take a look at the pros and cons in a bit more detail of the simplest method The pros! It's easy! That's the point There's no need for third-party integrations to track inventory so obviously the more kind of integrations that we have the more complex our system So it's nice to keep it easy if we can The only thing we would be using here is the bank feeds that most people are feeling pretty comfortable with at this point in time On the cons side of things we do not have as detailed information for decision-making So in other words, we're not actually tracking the inventory as we go If we were using a periodic inventory system we would possibly do that at the end of each month We might have the information more relevant for decision-making for actually purchasing of the inventory and taking a look at our profit margins for internal decision-making Here, we're making the adjustment at the end of the year for ending inventory primarily for taxes The point of the accrual method system isn't simply for tax preparation The point of the accrual method system is best practices for internal decision-making purposes We still need to make adjusting entries at least once a year to properly calculate ending inventory and the related cost of goods sold for income taxes In other words, we're going to be doing the easy thing We're just expensing We're not even dealing with the asset account of inventory or the accrual concepts At least once a year, the tax code may force us to deal with the accrual concepts so we're still going to have to deal with valuing the inventory counting the inventory, dealing with not just the units of inventory but also the dollar amount of inventory So we might talk about methods to do that in future presentations as we dive into the cash-based method Let's move on to the perpetual inventory system using third-party application and integration Now this is the one that most people that deal with Shopify accounting don't generally recommend So I want to point that out first before I dive into it So now let's think about what this contains Find third-party application that pulls in each transaction allowing us to use QuickBooks Online to track inventory on a perpetual inventory basis In other words, there's many different types of integrations out there that can allow us to help us pull information in from the third-party platforms like the Shopify's and the Amazon into the QuickBooks system Now QuickBooks itself has an integration in the QuickBooks system which possibly could be used for Shopify, Amazon, or eBay But like with many other applications, that one's trying to simplify the method by not pulling in all of the data, sale by sales not making a sales receipt for every transaction that's happening or tracking inventory on a perpetual inventory system That's what most people kind of recommend not to track all the time every single transaction We'll get into that in a little bit more detail But some other softwares you might find some that do allow you to pull in the full transaction And so you have to be careful in terms of if you're using third-party integrations what the integration is doing, is it doing the format that you want So perpetual inventory system using third-party application integration pros and cons So it provides a lot of detailed information including customer data That's going to be one of the pros We're pulling it over not just a summary of the transactions but the entire thing So we're going to have all the customers in our system which could be nice because then we can kind of look around and have everything within one QuickBooks online Tracks inventory and cost of goods sold on a perpetual basis So if we were in an on-ground system then what would happen is when we purchased the inventory we would have an increase not only in the dollar amount and unit of inventory but also a subsidiary schedule which would be tracking it by units using kind of a flow assumption method like a first in, first out or a weighted average method and then when we sell the inventory with a sales receipt or invoice it will actually reduce the inventory for us And that's similar to what happens when you do a self-checkout at say a grocery store The grocery store is actually recording the decrease in the inventory and the related cost of goods sold at the point in time you do the scan even though you only see the sales side of things all set up properly that would be great However, the cons are it's complicated to set up requiring the implementation of inventory items into QBO In other words, if you were going to set up a system where you pull over every sales transaction from a Shopify or an Amazon into your QuickBooks system you would have to set up inventory items that match the data that's being pulled in so that when you create the sales receipts QuickBooks Online knows which inventory items to be tracking with that That gets quite complex and detailed depending on how much stuff you're selling on these online platforms and how many different types of inventory you have on it You may not need data in both QuickBooks Online and third party platform In other words, you already have the customer data, the sales data you can put the customers on your mailing list possibly from the data before it gets into QuickBooks so you probably don't need to track every customer transaction because you already have that data in another system Over time, large volumes of sales can slow down QBO This is one of the big problems because if you're on Shopify or Amazon usually you're trying to sell large volumes of inventory and if you pull in large volumes of inventory into QuickBooks a bunch of heavy loads of transactions it could start to bog down QuickBooks and slow down QuickBooks at some point in time Also, just note that QuickBooks Online isn't really designed for more complicated inventory tracking systems It's a fairly basic inventory tracking system when you're using the perpetual inventory system mainly designed for basically on-ground inventory tracking Once your inventory needs gets more complicated the QuickBooks Online inventory tracking system might not be sophisticated enough to deal with it and if you want to track it in more detail you might then need an integration of like a third party application Next method, periodic inventory system using Excel or another spreadsheet So if we want to step up from a cash-based type system most people that deal with e-commerce situations would recommend something like this rather than trying to do a perpetual inventory system with an application that's going to be pulling all the data into the system And because we're using a periodic inventory system here once again we're kind of breaking out the transaction from the sales side of the transaction which we might integrate and we've talked about in prior presentations to the inventory tracking side of the transactions where we have to track units of inventory as well as the cost of inventory So we can track unit of inventory purchase and sales in an external spreadsheet using a flow assumption like first in, first out, FIFO, or a weighted average which are common types of flow assumptions So in other words, when we purchase inventory for like a third party platform such as a Shopify or an Amazon oftentimes people are fairly good at tracking the inventory that they need in terms of units possibly with the help and use of those third party platforms to make sure they have the inventory needed in order to cover any kind of sales that are going to be taking place So we're kind of tracking inventory in units on the third party platform and when you purchase the inventory you can obviously see the transaction of purchasing inventory because you're going to be paying for it in some way which means it's going to be coming out of the bank feeds So when you purchase the inventory that's a pretty clear type of transaction because the cash method kind of ties out to it you can see the purchase when you purchase inventory you increase the inventory account for the purchase generally it's on the sales side of things that's often the problem because these third party applications are usually tracking only the units of inventory and they're not tracking the dollar amounts of the inventory So that's where the problem happens and when we sell the inventory we're not pulling that data into our QuickBooks system in order to decrease the inventory per sale because again that would be quite tedious to do to pull that information in and therefore what we need to do is periodically possibly every month for example determine how much inventory we have left and therefore how much inventory we have sold in other words we can do the cost of goods sold calculation beginning inventory plus purchases minus ending inventory gives us the amount of inventory we sold and then we can basically determine what the cost of goods sold adjusting entry should be and just enter a journal entry into our QuickBooks system for that adjustment and so that we have the correct ending balance in terms of inventory for dollar amount as well as the cost of goods sold but we're not tracking units in QuickBooks we're just adjusting the financial statements periodically and we have to use a flow assumption by the way because inventory even if we buy the same units of inventory the price might go up in an inflationary time or it could go down but usually it'll go up and so the same units of inventory could have different prices so that's going to complicate our calculation of the value of the inventory so we'll talk more about that in an example but the idea would be that then we can just make periodic adjusting entries to the QBO QuickBooks online to properly record ending inventory and cost of goods sold possibly on a monthly basis on a periodic basis possibly monthly so what are the pros and cons of using this periodic inventory system with the help of Excel or another spreadsheet on the pros side it's fairly easy it's not as easy as the cash based system because we got to track this stuff in another worksheet but it's still fairly easy it provides detail for units of inventory and dollar amount of inventory in other words for internal decision making calculating the inventory using this method is going to give us more detailed information than not having that inventory system and remember that often times small businesses they do this kind of stuff to comply with taxes and they start to think well this is just red tape taxes they're just doing this but really the tax code is kind of built on general inventory principles so these things are the reason you track inventory as an asset and then expense it using that timing concept is because it's better for internal decision making so when you get more detailed on your internal decision making it's useful of course to have the proper cost of good sold calculation in the same time period as the related sales so that you can try to figure out your estimated sales in the future and your profit margins and all that kind of stuff so more comprehensive financial statements for decision making periodically possibly monthly instead of just yearly in other words when you use the cash based method you're probably just making an adjustment at the end of the year to comply with the taxes however you would like to be doing it periodically at least just monthly so that you can see your financial statements again for the internal decision making which you can do in a periodic system CONS there's going to be a learning curve to track inventory in Excel and learn flow assumptions like first out and weighted average in other words clearly you're going to have to use some Excel concepts to be able to track your inventory in Excel so that you can do the calculations to do the monthly adjusting entries into the QuickBooks system and if you're going to be tracking you're going to have to use some kind of flow assumption because the inventory is usually going to change in value so if you sell the same widget it might cost $20 today and $22 tomorrow and so that means that when you sell the inventory you've got this valuing problem in terms of how much was the widget that you sold cost because you had all these widgets which cost different amounts because of inflation or whatever the cost went up over time and that means you have to use some kind of flow assumption like a 5 or weighted average inventory system using third party app or integration so your next method would be I'm going to have my Shopify or Amazon store where we're facilitating sales I'm not going to try to pull all the inventory I want to have a more sophisticated inventory system possibly but I don't want to pull it all into QuickBooks item by item because again it will overload the QuickBooks system especially if I have a lot of volume of inventory so you could then integrate third party applications which are designed to handle inventory which are usually more sophisticated than QuickBooks in and of itself to handle more sophisticated inventory needs so this would usually be something that most I believe accountants for e-commerce would be recommending for more higher volume people that are selling on e-commerce like an Amazon or a Shopify or something so we can use third party integration apps to manage inventory often useful for higher volumes and more complicated inventory needs so we're not going to go into these in as much detail here because there's going to be more customization so when you're looking into inventory applications for more complex needs part of the reason that you're doing that is because you have more specialized needs and you're looking for software that is flexible enough to cover those specialized needs so then you can go in a bunch of different directions in terms of what your specific inventory needs are and the proper application to deal with those could vary depending on what your needs are and if you have a higher volume of inventory then it's worth your time of course to do the research and figure out what's the best fit for that situation but some inventory apps you can look at to get an idea as SOS inventory Zoho Inventory Ecom Dash Earplane Locate Inventory Final and Send 7 You can dive into that in more detail if you so choose We'll work some example problems in the future thinking about a cash based system and then thinking about the periodic inventory system and we'll talk about flow assumptions like FIFO and weighted average in our practice problems