 QuickBooks Online 2023, e-commerce inventory, Excel first in, first out, or FIFO practice problem part number one. Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 2023. Here we are in our QuickBooks Online test company file using the account 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, duplicating some tabs to put reports in like we do every time, right-click in the tab up top to duplicate it, right-click in the duplicated tab to duplicate it. Back to the middle tab, reports on the left. We want that balance sheet report. Go into the tab to the right, reports on the left. This time we want the P and L, the profit and loss report. Scroll up, close the hamburger, change the range. This time let's go from 0401 and let's go to 24 to 053124 and then we'll run that one. Nothing's in it. That's thus far. That's what we want. Middle tab, closing the hamburger, the same range. We're going to go from 0401 to 4 to 053124 and run that one. Let's tab to the left. Now we're going to imagine ourselves in that e-commerce situation selling inventory but not on ground in a store but rather online in the cloud with the help and use of third party applications for example being like a Shopify or an Amazon. 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 talked about decoupling the sales side from the inventory side using a periodic instead of perpetual inventory system and we thought about how to pull in the revenue side of things. Now we're talking about the inventory and cost of goods sold side of things. Last practice problem we did a cash based system where we're trying to just expense the inventory as we purchase it. Now we want to do a flow assumption and we're going to be using an external worksheet to help us with the flow assumption. So in other words the third party platform like the Shopify or like the Amazon will possibly help us out to know the number of units that we currently have on hand meaning the units will decrease by product as we sell them and we're going to have to increase and make sure to track the units on the storefront in essence so that we can logistically meet the needs of having the inventory necessary to cover the future sales. However usually we don't have the capacity here to then value it in terms of dollars and figure out the cost of goods sold and whatnot because that usually takes a flow assumption of some kind and then on the QuickBooks side of things we're not going to pull in all the data so that we can do a perpetual inventory system but rather we're just going to adjust our inventory periodically possibly at the end of each month would be a common type of system. If we were to do that properly we really would need to have a flow assumption like a first in first out or weighted average. We're going to start with a first in first out type of flow assumption and we'll just build it in our Excel worksheet. So here's going to be our Excel worksheet. We'll do a similar kind of set of processes where we purchase items that we did on the cash based system but then we'll periodically deal with the cost of goods sold at the end of the months for example. Alright so let's format this worksheet. I'm going to put my cursor on the triangle right click and format the sales and let's go to currency bracketed negative numbers. Let's go no dollar sign. Let's keep the decimals this time so we'll be dealing with pennies this time. We'll close this out. I'm going to zoom in. I'll put my cursor up here to zoom in to make sure I'm still on a one. We're at 235 so that looks good on the zoom in. Now there's a couple different ways you can kind of put together like a first in first out flow type of schedule. This is one method that I'll put together so that we can try to track it in a table format that hopefully we can sort quite easily and just add data to as we go. It's a little bit tedious. Most inventory methods are but it works here. So let's check it out. Let's title it first in first out. And that's otherwise known as FIFO FIFO is the method that we will be using. And let's go ahead and make this black and white as is our headers typically. And then I'm going to put my headers up top date in and I'm going to call this the total dollars be bought. These aren't maybe not the best descriptions on some of them but description let's call this product and this is going to be the units in and unit price. All right so that's going to be the amount of the items that are coming in. I'm going to color these like differently so we can see the difference between the two. Select these and I'll make this a green for example. Let's do it like this and maybe green and white. And then over here with the with the amounts that are going out when we make sales we're going to call it the date out total number sold units out and then cost and that's going to be when we're selling the items which will figure out on a periodic system not as of each sales item which we'll talk about shortly. Let's make that a different color let's make that like this color and it's like brown or orange is brown and then I'm going to call this the unit balance and ending inventory or something like that. Let's make these blue and I'll go here and make this like a dark blue maybe something like that blue and white. Let's check the spelling on it and it's spelled correctly spell check must be broken that can't be right. Let's go ahead and highlight all of these. Let's wrap the text so I'm going to go up top home tab and alignment wrap it and then I'll center it so that we can see these items more clearly so that looks pretty good for our starting point. So this first half is going to be on the purchasing side and then we're going to count our inventory periodically and record the cost of goods sold or sales of the decrease in the units and then we'll have our unit balance and our ending inventory in dollars is the general idea. So let's kind of build this worksheet as we go and we can kind of think through it as we go here. So if we go through the same thought process we would be saying OK here's my Shopify store I've got my products I'm going to buy the products that I need I'm going to look for the number of units that I think are going to be necessary in order to cover the sales and let's say we start off with the number of units that we think are necessary at what did we say product product one we're going to buy two of them to start off with. So this is going to happen on four one I'm going to have to format this date sale in a date range again. So I'm going to put my cursor on the A column. I can go to the number group and format it with a short date this way. But I like to right click and I'm going to get rid of the year so I can make it a generic problem and format this. I'm in the date area and I have this three four without the year. That's the one I'm going to use. And so I'm going to say this happened on four one and let's say the total purchased. We're going to say two. I'm just referencing the total that was purchased because I'm going to put them on the books one at a time. But I think this kind of helps us to see the grouping of when the purchase has happened. So in other words this is going to be the product which is going to be I'm just going to call it product one very generic. And then I'm going to say that the units are always just going to be one. I'm just going to put them on the books one at a time. In other words we're just going to put them on the books one at a time. And actually I don't think I need the decimals. Let's select the whole thing and remove the decimals. I think I've rounded everything. It won't let me do it anymore. Select these without the date range and remove the decimals. That might clean it up a little bit. And then the unit price I'm going to say is 20. Now instead of multiplying out the 20 times the two units, I'm going to put them both on the books because that's going to help us to filter a little bit more easily. So I'm in essence just going to copy this and put it down here to show that we bought two of them at the 20. So we paid a total of the $40. So this will make more sense once we start to get into the sorting side of the problem. On our side in QuickBooks, I'm just going to do the same thing we did last time, which is basically just enter it as a cash type of transaction. Whenever we make a purchase, which might happen through the bank feeds, but that would basically create an expense form. So we'll do the same thing. I'll just say like vendor one. And this happened on 05 or 04, 01, 2, let's say, 24. And we're going to say it's going to go to cost of goods sold. And I'm just going to write in here that it was for the amount of $40, $40. Same thing, $40. And actually, it's not going to cost a good sold this time. However, it's going to go into the inventory account. So now we're going to put all of our purchases in inventory and reduce the inventory periodically. So I'm going to say this is going to be inventory, not cost a good sold inventory. And there we have it. So this is going to decrease the checking account. The other side is going into inventory. Let's save it and close it, go to my forms and check it out. We're going to say run it. So now we decreased inventory. And the other side is going to, I'm sorry, we decreased the checking account. And the other side is going into inventory assets. Nothing is happening over on the income statement. We're doing more of the accrual component here, but we don't have the supporting documentation in QuickBooks, the way we would if we were doing a perpetual inventory system, because that supporting information is over here in our Excel worksheet. OK, so then let's imagine time is passing, sales are happening. But as the sales happen, I'm not recording an adjustment as the sales happen. That would be a perpetual inventory system. But rather, we're just going to adjust this at the end of the month. So sales are happening. I'm looking at my information in my Shopify, which is going up when I make the purchases, because I'm adding the units to reflect the units when I make the purchases. And then Shopify, in essence, would be decreasing them when we sell them on a perpetual inventory system basis for units, but not for the dollar amount. They're not tracking the dollar amount on a first in, first out, generally. They're just going, but they will give us a track of the inventory, in essence, because we're increasing the inventory when we purchase it. And then they're going to automatically decrease it when the sales happen. So we're going to keep on making our adjustments to try to satisfy what we think needs to be sold within the month to meet the sales needs. So let's imagine then that we're making another purchase on during the month of 4, 4, 15 April. We're going to say on 4, 15, we purchased another one of these. And it's going to be, once again, product one, product one. And we purchased one unit. It's always going to be, I'm going to put it on these books one at a time, but this time it costs $22. That's where the issue comes in in kind of our first in, first out system. And we're going to imagine that we purchased on 4, 15, two units of product two, product two. So now I've got product two in the same area here. And I'm going to say that we purchased, and I'm going to put them on the books one at a time, right? One unit at a time, even though I purchased two of them. And we're going to say that product two costs $105, we said. And then I'm going to copy that and put that here. So we've got two of these items that were purchased at 105. So we're going to spend 210 for product number two. And then we're also going to say that we had product number three on 4, 15. And we're going to say that we purchased how many of those four of product number three. And so I'm just going to call it product three. But I'm going to put them on the books one at a time. And they cost $650 currently. So I'm going to copy that down, copy to four more cells so that we have four of these put on the books one at a time at a unit cost of 650. If I add the four units that comes up to the 2600. So hopefully I got that all correct here. So that means everything that we purchased on 415, if we purchased it from the same vendor even though we purchased these three different products would add up because these are all the unit costs one at a time to that 2832, 2832. So let's imagine we purchased that from the same vendor. And we're just going to say let's stock up on that. And I could just imagine that goes through my bank feeds. And I'm going to say let's make an expense form. And we'll say it's for vendor one again, let's say. And this happens on 041524. And we're just going to put it into inventory instead of cost of goods sold. But other than that, it's much the same process we did last time within the current month because we're purchasing as we need it. And I'm just going to put the lump sum that once again came to 2832. So we'll say 2832. And so this is going to increase inventory, decrease the checking account. So let's say, all right, save it and close it. And over here, balance sheet, checking account goes down. Inventory goes up. Nothing is happening over here to the cost of goods sold. Even those sales have been happening during the month of April. And on the sales side, the sales would be pulled in using the method we talked about in prior presentations because we decoupled the sales half of the transaction from the cost and inventory half. And the related costs that goods sold will be updated periodically at the end of the month after we do our physical kind of count at the end of the month. And then in Shopify, we saw the inventory going down in terms of units. And we're going to update the inventory for each of the units within our online store so that it can keep tracking on just a physical unit basis, even though it's not generally tracking in terms of a cash flow assumption, such as first in, first out. OK, so that's what we have thus far. So if I go back on over here, this is what we have. Now, I'm going to add a little bit of formatting over here. I'm going to actually put this into a table so we can start to filter this. And I feel a little bit safer putting it into a table because then the filters are kind of more static. In other words, you could select this whole thing and go to your data tab and add filters up top. But I kind of feel better when it's in a table format. So I'm going to select the whole thing, even though I have a lot of empty cells over here. And then I'm going to go to the insert. And I'm going to make a table out of it. This is going to adjust the formatting a little bit. But I'm going to say, OK, table. And what it does is it flattened out my headings again. So I'm going to select the headings or let's select the whole thing up top. And I'll squish this back down again so it fits on a page. This one maybe can be a little bit longer. And so now we've just got the same thing but in a table structure. OK, so then we'll add some more components to the table later. But right now, I just want to point out that we can sort by the product. So if I go to my product over here and I could sort by, say, product number one and say, OK, and so now I've got the units, each of these units, three of them, in product number one. I can count the number of products that we have on here, even though they're on there and they were purchased for different dollar amounts. So let's say it's the end of the month now and we're trying to figure out our periodic adjustment monthly, which will decrease inventory for the amounts that were sold. And the other side is going to go to cost of goods sold for the cost of the goods are sold. One way we might do that is within Shopify, we might be saying, I believe these numbers are tying out to the physical units of inventory. In other words, we've been increasing them when we purchase. We've been decreasing them or the software has on a perpetual basis when the sales happened. We could tie these into a physical count, for example, to determine the physical number of inventories that we have as of, let's say, the end of the month. These don't tie out to what our numbers will be, but that's the kind of concept. So we can say, okay, I know what the ending units of inventory are. So if I go back on over here, let's imagine that the ending units for product one are zero. We sold all of the units out. All three of these have been sold. We have no units as of 430, we sold them all. So we're going to make our periodic adjustment as of the end of the month. We're always going to do it as of the end of the month or the sale side on a periodic basis. You might do it weekly, but monthly is quite common. Let's say it's at 430. I need to format this in the date format. So I'm going to right click on it, right click, and format this as a date format. I'm going to take the years off. So 430, 430, okay. And then we're going to sell, we sold the total amount of three units, but once again, I'm going to put them on the books one at a time. So I'm just going to say one unit at a time. And I'm going to pick the cost up each line item. So there's the $20 that we are picking up. And basically I noticed it's trying to copy down. You got to be careful with the table when you add formulas because the table will try to basically copy it down. I didn't want it to copy that down. So I'm going to undo that bit. So I undid that. And so now I'm going to copy the same thing just to those couple cells. I'm going to copy this to these cells. So there we have it. So now we've sold all three units. So the unit balance at this point in time, I'm just going to say was one, we sold the one. So it's going to go down to zero. Once again, it's trying to copy down. So I could undo that. So it doesn't copy it down. And then I would like to be the one that copies it down just to those number of units. And then the ending inventory is going to be then the, I usually do it as the cost minus the cost. So it goes back down to zero as well. And then it's copying it down. So I'm going to undo that. And then I want to copy it down to make sure it doesn't mess up if I had any other rows in the table. So we sold all of those basically have been sold out. And then we're going to say on the other two items that we didn't sell any of the other items at this point in time. So now if I look at my total inventory, I can say let's bring this back up and bring in all of my inventory. I'm going to remove the filters or select all of them. So there we have it. And let's pull these ending balances all the way down now. So I'm going to say, let's pull these down. And so there, there they are. So now we've got our ending balance. Here's our ending inventory, summing up to two, eight, one, zero. Okay, we can summarize this in a table. I'm going to go over here and try to make this, I'll make this a skinny column. And then let's put a little table together and for our summary table. And I'm going to put my headers over here. I'll say this is product one, which I might be able to get by just, well, let's just type it in here, I'll say. Product one, product two, and product three. And what do we have thus far? We've got all three products. And then I'm going to say this will be the units balance, and then ending inventory. And let's do this. And let's make this a header. I'll make this black and white, black and white. And I'll make this black and white, and this black and white, or I missed it. I didn't click on it. All right, and then we're going to say the number of units. We're going to use a fancy formula here to do this, but it's not too bad. It's going to be equals to the sum if, and I'm going to use the ifs with an s brackets. And then I'm going to say that I want you to sum the units, these units. I'm just going to select the entire column, or I could select the entire table, let's do the table. I want you to pick up this table. I could see the criteria, there's my range, and then comma, the next argument, and then the criteria range, that meaning sum everything in that area. If this area, I'm going to pick up this table, and notice I'm putting my little pointer there where it has a down arrow, because it's just picking up that table. If that and then comma, and then the next criteria ties into product number one. So in other words, the argument is, I want you to sum up each one here, which is just one unit, if it's on hand still, if it hasn't been sold, if you can tie it out to this side where it says product number one, there's not going to be any ones here because they've all been zeroed out. So if there's zero, let's do the same thing here. Equals, I'm going to say sum if, and I'm going to take the one with the s ifs, and I want you to take this range, sum up this range, comma, criteria range, which is going to be this range, and you have to have that tie out to comma, this criteria product number one. And so if I say enter, it picks up two items, because we have these two that are summing up, it's summing up these two that correspond to those two product, product two. Let's do it one more time. Equals sum ifs, brackets, the sum range is going to be these unit ranges. You want to sum those up, comma, to get to the criteria range, this range, and then comma, if you see in that range a number three. So then it's going to give us four of those because we had four of these with a number three. And then let's do the ending inventory, similar kind of process. We're going to say equals the sum ifs, brackets. I want you to sum the ending inventory range here, comma, if similar process, if this product range, comma, criteria has product one in it. So once again, it's zero because it's summing these up which are gone and there's nothing of product number one left. Do it again here, equals the sum ifs, brackets, sum range, ending inventory, comma, criteria range here, finding the products, comma, and then the criteria is product number two. So there's the two 10, representing product two, product two, adds up to two 10 for those two line items on product two. And then once again, equals the sum ifs, bracket, sum range here, comma, and then the criteria range here, comma, and then if there's product number three, enter. And so that's going to be 2006 which is basically the 650 per unit times four units of product number three. And then we could have a total at the end to make that black and white and centered. And we could say sum, just a normal sum this time. This way, sum that way. And then we could also put some brackets around this. So that's where our ending inventory should be. And therefore we can just do an adjustment now. I can go back on over. Now we can also do that table with a pivot table. So we'll get into that more later. But right now I just want to do the journal entry real quick because we're running low on time. We've got 2872 over here. So 2872 is what we currently have in the inventory. And if we subtract this out, that would be our adjustment. We need to decrease it by $62 which kind of makes sense because we sold these three items cost a good sold is the $62, right? So I'm going to go back on over and say, all right, let's go back on over here and just do it with a journal entry, new button. And we're going to do the journal entry monthly instead of just like at the end of the year I'll just do a journal entry and say this is as of the end of 04324. And we're going to say that the cost of good sold is going to be here and that was for $62.62. And then we've got inventory is going down at $62. And boom, we'll just say save it and close it. And so now on a periodic basis at the end of the month we've adjusted our inventory. And so now our inventory is being adjusted that inventory is not supported within QuickBooks with sub ledger reports breaking it down by inventory item but rather is supported by our external schedules over here. So there's the 2810 in terms of dollar amounts of inventory and the 2810 and then on the income statement we have the cost of good sold, which is now in essence hopefully correct on a periodic basis, meaning that number reflects the amount of units that were sold given a first in first out flow assumption periodic inventory method for that for the month of April. And so that's going to be the idea. So then and that hopefully will give us more information so that we're actually matching up the cost of good sold to the sales that we have. And so that's the idea. Now note, you could even get more fancy and try to give it a cost of good sold for product one versus cost of good sold for product two and whatnot to try to break out the cost of good sold but you have that information over here. I mean, you can do that information on your worksheet as well. So that's the general idea. So in future presentations, we'll continue on with this for month number two.