 QuickBooks Online 2023 E-commerce inventory and cost of goods sold cash basis practice problem 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 then 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. 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 switching the view down below we're gonna duplicate some tabs to put reports in like we do every time we're gonna right click on the tab to duplicate it and yes we're gonna right click on the tab to duplicate it and then back to the tab to the middle as the one to the right is thinking and reports on the left we're gonna open up the balance sheet as always tap into the right as it's thinking even though it's done thinking and reports on the left this time the profit and the loss closing the hamburger I'm gonna change the range this time for the frame of let's go from 0401 to 5205 3125 that's where we're where the works gonna fall here gonna hit the drop down and see this in a month by month side by side run it nothing's in it yet that's good tab to the left close the hamburger scrolling up same range that we're gonna change here 0104 0125 05 3125 we could also see this on the side by side in the month by month run it and there we have it tab into the left we've been looking at e-commerce situations selling inventory but not on ground in a store but rather online in the cloud with the help and use of third-party software such as like an Amazon or Shopify amongst others so in prior presentations we talked about how we can pull in the sales side of the transactions because generally most of the time we're gonna be separating the sales side from the inventory and cost of good soul tracking which means we're gonna be using in essence a periodic inventory system for the most part and now we want to think about a system on the inventory side where we're doing the easiest thing possible just expensing the inventory and then possibly making a year-end adjustment to comply with our tax compliance so that we can get a cost of good sold and ending inventory calculation so what might that look like well if we had my Shopify store over here we've got our Shopify store and it gives us some inventory tracking so you're gonna have some logistical information to track the units of inventory so that you can make sure that you have your stock in place to cover the sales that are happening in the future what most people aren't doing or what takes a little bit more is to track the inventory not just on a unit by unit basis to cover what you think the potential needs might be in the future for sales but also to convert that to a dollar amount which usually requires like a flow assumption as well like a first in first out or a weighted average type of flow assumption but we're just going to expense it when we buy it and though and this results usually in some kind of timing issues and so that's that's what we want to kind of focus in on so let's imagine we first just buy our inventory so we're gonna buy our inventory when we buy the inventory we might see it eventually whatever our purchase structure that we're using to purchase the inventory is would eventually go through the bank feeds because we're gonna be purchasing it with cash so let's imagine we're gonna have a cash transaction goes through the bank feeds for the purchase of inventory so we'll start off with like two units of of our product number one so most likely we've been saying hey we need to we need more units of product number one so because we're getting low so then we go through our purchasing process and eventually cash is going to go out of our checking account so I'm just going to enter that instead of with the bank feeds the expense form which is a decrease to the checking account so decrease to the checking account because we're gonna be paying for this I'm gonna just gonna call it vendor number one generic vendor and the account is gonna be the checking account it's gonna be coming out of the checking account let's say this happens on 040125 and we're purchasing notice I'm not gonna use items down here because I'm not tracking inventory within the system but rather I'm just gonna use an account and record it not to inventory but rather just to the cost of goods sold so I'm just gonna dump it into cost of goods sold boom and that's it so I'll dump it right there and the amount let's say that the amount is going to be for $40 and so we purchase two units we're gonna imagine at the $40 total so they're $20 each but obviously when we pay for it it's just gonna be a lump sum you know that we're purchasing for the multiple units so let's save it and close it and if I go to my reports here we're gonna say all right what did that do on the balance sheet side of things we should of course have cash that is going out of the checking account cash is going out of the checking account right there $40 the other side simply going to the cost of the goods that are sold now they haven't been sold that's the problem because normally we would put them on the books as an asset and then expense them in cost of goods sold when we actually sell them so that's why we end up with this timing difference but of course that accrual component of putting it on the asset of inventory and then expensing when we sell it means that we can't just rely on the bank feeds which was the easiest thing to do to just expense things when they leave the bank account all right so that's gonna be the issue but that allows us then to go over here and say okay now I've got enough products and I can adjust my products in my Shopify store to line up to meet the current needs let's also track this in Excel noting that we are imagining a scenario where you are not tracking this information in Excel but for the practice problem we want to put it in Excel so we can see what is actually happening over time so I'm gonna zoom in a bit by holding control and zooming in you can see you can also zoom in over here I'm gonna select the triangle right click on it and format the sales I'm gonna choose currency bracketed numbers no dollar sign and no decimals I'm gonna put my headers over here which are gonna be product one we'll just put keep it at that and then units and then unit cost and then the total cost so let's make these are headers home tab font group and black and white and then I can center them let's say and then these ones are gonna make them a little bit skinnier like that and then I'm gonna select these as well and wrap them home tab alignment and wrap them so we can fit it there I'm gonna make this a date column you can do that by going to the numbers and then use the short date but I'm I'm gonna get a little bit more fancy right clicking on this and format and I want to go to the date format that has no year in it so I'm just gonna pick this one that has no year and there we have it so it's gonna be on for one we purchase two units at $20 multiplying that out that comes out to the $40 so what we're tracking in our Excel system is just the the $40 again I'm imagining we're not doing this Excel sheet in our if we were doing this in practice we're imagining a scenario where we're not tracking it in Excel but for the practice problem we'll actually track it in Excel so we could see what's happening okay so if we go back on over notice that we have no other schedule within QuickBooks that's tracking the the units of inventory on hand because we just expense them at the point in time that we purchased them now let's imagine sales happened and whatnot and the sales are coming through and being recorded into QuickBooks in one of the ways that we looked at in prior presentations but as those sales are happening we're not recording anything related to inventory because we've decoupled them we're not recording the decrease to inventory as the sales happens but rather we're just expensing the inventory as we purchase it so then let's imagine sales are happening and we decide that we need and the in our in our Shopify store the inventory is going down and whatnot in units and we decide we need to purchase more inventory so let's do this again and let's first put it into Excel and imagine what we're gonna do we're gonna say this time I'm gonna say on 415 we're imagining we need more inventory and we're gonna say just one more unit of this one let's say at $22 the point I want to point out is that the cost of the same units of product could most likely go up but possibly could go down over time as well because of inflation if nothing else other changes as well so that's gonna give us $22 that's where the flow assumptions are gonna come in when we talk about more complex methods in the future and then let's say that we also purchase product number two so I'm gonna copy this I'm gonna put it over here and say we purchased another product number two and we didn't have any any first row here product number two we're gonna say we purchased two of them and they cost $105 for a total of $210 that we're purchasing so if we purchase this from the same vendor we might just be paying a bill that would come through the bank feeds at $232 and that's all we would do in QuickBooks and so we'd say okay I'm gonna purchase these and they cost whatever they cost I gotta pay these people $232 right so I'm gonna go okay let's go into QuickBooks here and do that so I'm gonna go QuickBooks and it might go through the bank feeds you might use the bank feeds but I'm gonna do an expense form here which is the form that decreases the check-in account let's say it's vendor one again and this happened on $415 and if these were products I would have the two items down here that I would have to set up but I'm not doing that I'm just and I'm not even tracking it I'm not even going to record it to inventory I'm just going to expense it as we purchased them so I could put the units here I won't even put the description I'll just say yeah it was we got to pay this guy uh $232 so I'm gonna say $232 and that's it so this is going to decrease cash and the other side is just going to go to the cost of goods sold and we're not tracking the number of units or anything in QuickBooks although we most likely are in our logistical way in some way shape or form in Shopify because we're going to adjust the inventory units here that are going to be increasing as we purchase the inventory and then they will tick down as the sales are happening on the Shopify website but Shopify or whatever servers you're using is not tracking the flow assumptions oftentimes like like the dollar values and note the date should be 2025 is what I'm working in to be in the same year I was doing here so let's save it and close it and then if I go to the balance sheet there should be a decrease in the checking account and the other side is just simply going to the cost of goods sold now notice you might be thinking well what's happening with the revenue side of things with the revenue side of things I'm not focused on the revenue side of things but that would be handled the with the the methods we were taking a look at before most likely pulling in the information from the third party platform using the bank feeds with a deposit the deposits coming through or getting the more information with a journal entry or or integrations but as the revenue comes in it's not it's not linked to the inventory and cost of goods sold because we're not using a perpetual inventory system but rather a periodic inventory type of system okay so then so that's that one and let's so now let's say that time passes more revenue happens and when the revenue happens the revenue would be pulled in up top and then of course your Shopify units within Shopify or whatever your online store would be going down again and whatnot and then you would have to say that you're going to be purchasing more units of inventory at some point also let's say that we also purchase another product and and let's say that we're going to purchase product number three so and let's say this happens on four fifteen so now we're going to add a product I'm going to say we're going to sell a new type of thing and say expenses and let's say this is going to be another vendor vendor number two that we're going to say our product is and this is on 041525 as well and so this one I'm just going to put it once again into cost of goods sold even though I've got a completely different vendor and a different product and I'm going to say that we're buying uh two six oh of these let's put the calculation just so you can see what I'm thinking on the new vendor so the new vendor I'm going to copy this whole thing from the skinny let's just do this I'll copy this over and put that right there and let's make this a little bit smaller so we could see it on the screen and this will be product number three and then happens on 415 we're going to say that we buy four units and these are more expensive stuff at 650 so four of them at 650 gets us up to 2600 so I'm just going to say that we're paying 2600 to purchase these units of inventory that's our purchase price not the sales price and so this is going to do the same thing I'm going to save it and close it doesn't track the inventory or anything it's just a decrease to the rev to the cash account the other side going into our cost of goods sold expensing them as we purchase them and then we're going to imagine time passes our inventory goes down and whatnot when sales happen and we're saying okay I got to purchase more inventory because I'm getting low on the inventory so I'm just going to say let's imagine that on the next day that we purchase is going to be on five one we're going to purchase six units and now they cost twenty three dollars notice the price is going up for the same units of inventory and then that's all we're going to purchase at this level so let's say on five one that happens we got to pay 138 so I'm going to say okay 138 boom uh expense and so I'm just going to say this is vendor one and this happened on 050125 and cost to get sold and we're just going to pay what did what did I say uh what did what did I say here uh 138 138 boom 138 and that just does the same thing saving clothes cashed based system nice and easy decreases the balance sheet the other side's going to the cost to get sold now we purchase those in May and then if I go back on over we're going to say all right now time has passed again and we're going to do one more purchase on 515 we're going to purchase two at 27 notice the price is going up and then we're going to be purchased on 515 also 515 here and I'm going to format paint this down 515 we're going to purchase three and now these costs one one one price went up that's what often happens over time even though they're the same units and we're going to purchase more of these let's format paint this one down and say this happens on 515 and we're going to purchase three of these and again the price went up here so now if I total that up let's pretend we purchased these all from the same vendor now I could just say okay now I'm going to make a big purchase because Christmas is coming or whatever and so it's going to cost me 2004 12 so over here on I'm going to say that's what I need for my to cover my Christmas the Christmas sales or whatever so I'll hit the drop down and let's just say this is all coming from vendor one again and this time we're on 05 15 25 cost of goods sold the total amount is just simply going to be 2412 so sarin 2412 same transaction save it and close it it's going to decrease the checking account we don't have we're overdrawn that's not good we better get some sales going and then we'll run over here and there we have it on this side just recording it to cost a good sold now the reason this is easy is because of course cost of goods sold will just flow into the balance sheet it'll close out and everything but these cost of goods sold although we expect to have them expenses are not tying out to the same period necessarily that we actually sold the inventory and that's why the accrual method is generally better because we would like to match out the cost of goods sold to the time frame that we sold the inventory but this would be the easy thing to do because when I make the inventory sales then like these units that I purchased in April may I may not be selling them until you know three months into the future or something like that so now I've got this difference between when I purchased them and when I sell them and that could be difficult for decision-making purposes and notice that accounting is principally based on decision-making purposes but on the plus side this is the easy thing to do but at the end of the year we're going to have to file the taxes so that and just remember the income would still be being populated i'm not going to populate the income here because we looked at that last time but it would be being populated separately using the methods that we talked about on the inventory side of things pulling in from the bank feeds or something like that so now at the end of the year we're going to say well I need ending inventory for my if I'm a sole proprietor or for whatever tax return I'm doing generally to to calculate my cost to good sold calculation so one way you can you can do this isn't is then at the end of the year you could try to say okay let me think about how many units I have left and the units you have left you would you would kind of know because you might be tracking them of course in your Shopify for the logistical purposes what you don't know typically is going to be what should be the cost of those units of inventory but if we assume that we're just going to use the last the last purchase price I can say okay as of let's pretend let's pretend the end of the year is is five thirty one even though you know usually be a 1231 year end but let's just do it at the end of five thirty one and say I've got it I have to have an ending inventory at this point in time well I could say well how many units do I have left and let's imagine that we have four units left of this product and then let's imagine we have five of these we didn't even sell any of these and then let's imagine that we had two left of this product so if that's the case then we have to value those those amounts now we can kind of basically just assume that the last purchase price is the cost of the inventory so here notice the inventory was worth 20 we bought it for 20 22 23 and then 27 the easiest assumption would be I'm just going to use the 27 now this is kind of trying to back into a first in first out method because my assumption is that I sold these cheaper ones already and the more expensive ones I bought later I still have however it's not perfect because really if I have if I have four units left and there I only bought two at 27 and the other two were bought at six dollars right so if I was really to back into it I should have two at 27 and two at six but we'll talk about a flow assumptions later this is just the easiest kind of approximation that you can do for this one time frame which is kind of approximating a back into the first in first out method so let's say on on here we're just going to use the 111 the last purchase price right so this is going to be this times this and then here we're going to say that this is going to be 6575 which is this times that that would be the easiest thing to do and then I can say well the total then is going to be product number one two and three so that would be then my ending inventory that I would I would need so if I think about what I have in my books I can say that my cost of goods sold on the on on this side is overstated as of this point in time by the 2013 because this is the amount of inventory in dollars that we are imagining is still on the books right so then I can go over here and make a journal entry periodically and say say okay let's just fix that I'll just make an adjustment as of that time frame going back on over and say plus button and this time I'll just make a journal entry and I'm going to say as of the end of the period which we are imagining 05 3125 we will say it's going to be a deposit to inventory and for the amount of of two zero one three two zero one three and then the other side is going to go to cost of goods sold cost of goods sold two zero one three boom increases inventory decreases cost of goods sold let's save and close it and then if I go to my balance sheet now as of 531 we've got our there there's our inventory number I thought it wasn't there and there's our inventory number so now we have it on the balance sheet and then on the on the profit and loss we've reduced the the cost of goods sold by the amount that we think is not sold based on our our backing in to the inventory calculation so there's our journal entry if I go back on up exit out of that and there we have it now the problem with this of course is that this is only making like the profit and loss correct as of the point in time as of the end of the year and that means that that our cost see my I didn't make an adjustment as of the end of April we could have we could do this on a monthly basis for example and do an adjustment similar to this on a monthly basis but if we just do the easiest thing and we do it at the end of the year then this might get us by for tax purposes but it's not going to give us the best information because we have these timing differences in terms of the cost of goods sold being when it's recorded to the cost of goods sold and when it's related to the sales so so that's and we'll get more into the flow assumptions to think about that conceptually later notice that if for taxes then when you're doing this for taxes usually the second half or page of the schedule c has a cost of goods sold formula so the basic cost of goods sold formula has has a beginning inventory beginning inventory and then that should match what you had on last year's beginning inventory plus purchases less and that's going to be well the amount available for sale let's put an amount available for sale less ending inventory gives the cost of goods sold right that's going to be our calculation that you got to basically put into the tax formula and in this case if this was the first year the beginning inventory would be zero if there was no prior inventory we don't really know the purchases because i wasn't tracking that what we do know is the ending inventory right i know the ending inventory is this amount is that what we came up to yeah that's the ending inventory and so and so uh and then we also know the cost of goods sold now because i can pull that from my sheet over here and say that my cost of goods sold is currently at the three four oh nine so this is at three four oh nine so using that we're only missing the purchases we can basically back into the purchases right so we've got zero in essence plus x uh minus the two oh one three equals the three four oh nine and we can basically do our algebra to solve for x and that basically means that in our in our purchases we're going to say this is going to be equal to you know this plus uh this and then this is equal to this plus this so if i double check this my beginning inventory is zero my purchases is that if i sum this up this is the amount that's available for sale and then my ending inventory is that and if i subtract this out i get to the three four oh nine so you can you can you know populate your worksheet because basically you will know in essence the ending inventory and the cost of goods sold and you can back into the purchases now note that if it's your second year or multiple years of doing your your your uh schedule c then you will have beginning inventory how do you know what the beginning inventory is it's the ending inventory from last year so if you look at last year's return and you had ending inventory of a thousand dollars then you again you could do the you know the similar calculation but then accounting for the thousand dollar beginning inventory which means you'd basically just subtract out if i do my algebra here it would be this this minus that right and so if i double check it there's the thousand dollars plus uh the purchases gives us the amount available which is spelled wrong for sale lest the ending inventory gets us to that three four oh nine so in essence if you can get your ending inventory then you can make your you can make your journal entry in uh your your quick book system which can give you basically the cost of goods sold that's being calculated within the quick book system and then you get the beginning inventory because that's the same as last year's ending inventory and if you have all that you just have one unknown in your little algebraic equation which is the purchases that possibly you could back into so that's kind of the easiest way that you're making some assumptions and whatnot to to deal with this whole thing and that way you could basically just focus all your time on trying to cover the amount of inventory like most new people might be doing in terms of units to cover the sales uh although again as you as you get more sophisticated you're going to want more accounting needs for internal uses because this matching system doesn't give you the best the best results to to know which products are giving you the highest profit margins and whatnot so we'll talk about some other methods in future presentations