 QuickBooks Online 2023 E-Commerce Inventory, Excel, Weighted Average Practice Problem Part Number 2. 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 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, duplicating some tabs to put reports in like we do every time. Right click on the tab up top to duplicate it. We'll duplicate again, right clicking and duplicate once again back to the middle tab. Reports on the left opening up one of the favorites which of course is the balance sheet. Report as it's thinking tab into the right reports on the left closing up. Well let's hold on a second profit and loss then close up the hand boogie then we'll change that range from 040123 to 053123. That's the time frame we're working with this time. There's our profit and loss of thus far tab into the middle closing the hand boogie scrolling up and ranging to the changing the same range as before 040123 to 053123 and run it back to the tab to the left noting that we're doing an 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 software such as a Shopify or an Amazon for example. We're breaking out. Support accounting instruction by clicking the link below giving you a free month membership to all of the content on our website. Broken out by category further broken out by course each course then organized in a logical reasonable fashion making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems PDF files and more like QuickBooks backup files when applicable. So once again click the link below for a free month membership to our website and all the content on it. The sales side and the inventory tracking side tracking our inventory on a periodic type system with the help and use of an Excel sheet. That's going to be doing it in a weighted average method. We started last time doing this and if you weren't following along then you'd probably want to see that one first because we're continuing with month number two of this exciting saga of the inventory. So now we're going to go back in here now last time we entered this in but I didn't make it into a formal table. So now I'm going to do month number two but let's make this into like a formal table now. So I'm going to select this whole thing I want to make sure it picks up everything in here. I'm just going to make it into a table which has its pros and cons for adding new data. It's a little bit more secure when it's actually in a table format that it's not messing things up. But sometimes it copies formulas in a way that you don't really like as well so there's pros and cons. But we're going to select the whole thing and go to the home tab or not the home tab. I'm in the home tab I'm going to the insert tab tables and I'm just going to make it into a table. And so it's all selected that looks good. My header has it has a header good. And now it made it really wide because it it it extended all of the headers. So now I'm going to reformat all of my headers to be basically the length that I want them to be. So we'll redo all of the headers which yes is a little tedious but it's OK. It's OK we're going to just make this nice and tight. Boom bring that in a bit this one needs to be in a bit. I should probably make this exact to the other one from the other area. But there we go. Now notice it also on the table headers when I made it into a table it put a little number by each of the headers. So it's distinguishing each of the headers because I had repeat header names. Now you might want to go in into each of these header fields and change these numbers to be like product to like all of these. I might put all of these to correspond with a number two for product number two. And what that could do is it could help us if we were ever trying to create a fancy pivot table or something to distinguish our headers here. So I'll change all these to product number two. Pour a hamplow and so let's do that. And then let's make this skinny and this skinny and then this one I might change all the headers to like product number three. I'll just put a three next to all the repeat headers so that if I was to make a pivot table that I wanted to do something fancy with. Then I can know which headers go where number three. And there we go. Double click number three. Almost done. Moe Saruka number three. And this one number three. And let's bring these in a bit as well. All right. So there is our table. Okay. So now we did the first month of April of our operations. And now we're going to continue on the second month and we're just going to to buy stuff as we need it. So we're following along with our Shopify or online store and we're looking at the units and seeing if we have the units that we need to meet the need. And we're saying that that we're getting short on some of the units. So we're going to make a purchase in May. So we're going to make the purchase and just record them on a cash based system. So let's say on five one and notice as I add five one it added another cell to my table and it pulled down most of the cells that I want to pull down automatically didn't pull this one down. But notice it it's getting more automatic. That's the point. So this is going to be product number one which is going to populate kind of automatically once I start to populate it. Let's say that we're going to buy six more of these ones. So now this is populating automatically because it's this plus this which is just six now. And then we're going to buy them for the cost. And I'm going to say that we buy them for 23 units each $23 each. Now if I bought this and I paid $138 plus the sales tax generally it's going to be included in the cost usually then I could say 138 divided by six would be 23 per unit right. I might have to do that kind of the other way. And then I'm going to copy this down just so I can have my formula and it's not doing anything because this isn't a sales formula. This is the purchase side. So this is the total cost and here's our total ending balance. There was nothing in it before. So that's where we stand at this point. And then on product number two we're not buying any product number two or product number three at this time. So let's just make sure that everything copies down like they should on these other ones so all the columns copy down. So everything's copying down that needs to except for this one. Copy that down and then same thing over here. So let's just copy this one down and there we have it. Now the totals look good because now the the total is totaling up these ending balances. And then the change is going to be this minus this which also so so that's going to be the purchase that we're going to make which also ties out to this number. So let's record the purchase in QuickBooks now. And once we do the purchase then our ending inventory will change by that be up to 2948. So we're currently at 2810. So if I go back over here and say QuickBooks reports were at 2810 we're going to make a purchase which we would see come through the bank feeds if we're on a cashed based system. I'm just going to represent that with an expense form. And we're going to say that that we're paying. Let's just use vendor number one again. That's who we buy stuff from. And this is going to be on five one and inventors the other side and we just purchased the how much was it again. Do you remember how much it was. I forgot. I'll tell you that one thirty eight one thirty eight. Thank you one three eight. Okay. So this is going to increase inventory other side going to decrease the checking account. Let's save it close it check it out and go to our balance sheet running the balance sheet. We should have a decrease to the checking account and let's see this on a side by side by the way hitting the dropdown month by month. Side by side. And we can see kind of our inventory at the end of April and then we made a change in the inventory purchasing it here. And then the other sides on the checking account no impact on the P and L. Let's go to the P and L. And let's check that one out in a month by month side by side as well. So nothing on the this one should this is the sales. I'm on the cost side. So nothing's on the cost of goods sold for May thus far. Okay. So let's go back on over here. And the ending balance by the way is that two nine four eight. Let's just double check that and two nine four eight. Yes indeed. It is correct. Okay. So more time passes we're imagining and sales are happening over here on the Shopify store and they're taking down the sales numbers in terms of units as they happen. But we're not worried about recording a decrease to the inventory on our financials yet because we're only going to do that periodically at the end of the month. However we are concerned with the units of the sales as they tick down and we're going to make purchases as we need to to try to meet the demand. So we're going to say OK we think based on what the store is saying here that based on the units we need to buy some more units. So I'm going to go OK. Let's see which units we need to buy on five fifteen and this is going to be product number one again. Let's say on five fifteen we buy two more units and this time they cost twenty seven dollars. Notice the cost per unit is rising. I'm just it might not rise this rapidly but typically the cost will rise as time passes because of inflation and whatnot. And that's why you need the flow assumptions. So if I copy let's just copy this down and there we have it. So now we purchased two times twenty seven each. It's going to cost us fifty four dollars ending inventory was at one thirty eight plus the fifty four is now at the one ninety two for product number one. And then product number two we also purchased this time. Let's say we purchased three units of product number two. So let's say I'm just going to put it here product two and we're going to purchase three units. So the total units are up to five now for product number two. And then hold on a second. I don't want to hear that happened on five fifteen. So let's put it here product two and then here's my two. Okay. And then so everything's carrying down four units total now and then nothing's here. And then I'm going to copy this down and it's doing nothing because because this is a purchase and not a sales item. And then the the total cost actually this needs to be what am I doing here. This needs to be then what we purchased them for we purchased them for one one one. So the cost went up. We had to pay one oh five per unit. Now we're paying one hundred and eleven per unit. Okay. And then there's the total two units times one hundred and eleven gets us two two two ending balance is the two ten plus the two two two gives us the four thirty two ending balance for product number two. And then product number three did we buy any product number three on we did we're going to say product three. We also purchased and we're going to purchase three of those and they cost us now six seventy five to purchase those ones. So they went up again inflation happening hit us again. I'm going to copy this one down and then total cost is three units times six seventy five prior balance plus the current change gives us this number and then our totals which are the ending balances. There's where we stand each of the ending balance for product one two and three and here's the change that happened. In other words this is the purchase price. So we're purchasing two thousand three oh one of inventory which we're breaking out on a per unit basis for products one two and three. So that's what we have thus far. So I'm going to just pay I'm going to imagine we buy it all from vendor one we might have multiple vendors that we buy different types of products from. But I'm going to imagine we buy them all from vendor one here. So I'm going to say all right we bought all this stuff to put in our store. And so we're going to see it decrease the checking account again vendor number one and vendor number one is we're making vendor number one rich. And we're going to say this is going to be for the amount but we're getting rich too. So that's cool. I'm cool with that two three oh one two three oh one two three oh one. And so this is going to decrease checking the other sides going into inventory. So we'll save and close it and balance sheet it and run it checking account going down other side assets now up to five two four nine. And that ties out here to our ending balance in our worksheet five two four nine movie be to the end be in as they say. All right then. So then let's say that at the end of the year then or at the end of the month now we are going to we're going to do our physical count again. So what's been happening is we've been selling our stuff on our third party store here. And their inventory tracking has been going up and down on a perpetual system. And we're just recording the purchase side but not the sale side in our financial statement on a dollar amount using the weighted average method. And now we're going to say it's the end of the month. So we're going to do a periodic adjustment to make our financial statements correct periodically. We're going to look at the physical count as well as which could also be reflected or shown here on the Shopify as we manage our physical count of inventory. And the difference between the physical count and what's in our books at this point in time in terms of units. We imagine we sold those right. So that's the periodic system. So we're going to say OK then let's go back on over and say this happens on 531. We're going to adjust our books with a journal entry product number one. And we're going to imagine that we only have four units left. We've counted our inventory. We looked at the physical count and the Shopify count in units. We got four units left. I have eight units here. That means we decreased the units by four. We sold four units to get the unit count down to four. And then we're not going to have any buying thing because we didn't purchase anything. So I'll keep that at zero or I might just put a zero into the field. And then I'm going to copy this down. Now look what it's doing because we have our fancy formula. It's it's now saying hey look this because that's zero or less than zero. I'm going to do this number which is our which is our ending balance right before this this transaction divided by the number of units. So we've been buying stuff. It cost us twenty dollars twenty two twenty three and so on up to twenty seven dollars. The average at this point in time is the ending inventory for product number one divided by the number of units before we selling them at this point which is about twenty four dollars. Right. And then we've got our total ending balance which is going to be now it's this twenty. So it's C nine C nine times E nine but there's nothing in E nine. So it's C nine times F nine. So it's these two are what's being calculated here. The second half of that formula and then the ending balance is the one ninety two minus because it's a negative number. Gets us to the ninety six remaining. Okay. We also did we sell any we didn't even sell any of product number two which is a bust. No one wants it. No one wants product product two. No one wants you see all that rhymed product two. No one wants you. Oh burned burned product two. Okay. So we got product number three and we're going to imagine that we have two units left. So we checked out our physical count and what's on our Shopify physical count two units left. So we have to say seven minus two. That means we sold five units five units brings us down to two seven minus five gives us the two nothing here. I'm going to copy this formula down which is going to do this. I'm going to say that was zero. So what I want you to do then is to take the four six two five the total value of the inventory divided by how many units we had before this transaction. And that gives us an average cost of the six six oh remember they cost us six fifty and then six seventy five but the weighted average six six oh at this time. So then we're going to say that means that this times that is going to be the the the the the decrease three three oh three fifty seven ending inventory was at four six two five minus the three three oh three fifty five seven gets us the one three two one forty one. The ending balance at this point in time represents the ending balance in each of these products. And then the change is the journal entry we need to make to get to get our ending balance and our financial statements down to the current ending balance according to our calculations here. So that's going to be the journal entry number. So if I look at my financials we should be at five two four nine now. So if I go to five if I go to my five financials we're at five two four nine. We're going to bring it down to the one eight four nine forty three by decreasing inventory three three nine nine fifty seven assuming we sold that and recorded the other side to the expense account of cost of goods sold on a a cruel that's an accrual concept right. So now we're doing the accrual thing with a journal entry not the cash based thing. And so we're going to go back on over here and just make a journal entry. There it is journal entry as of the end of the month five three one five three one. And we're going to say this is going to be cost of the goods that are sold cost of the goods and the amount being what did I say again. Do you remember I forgot again three three nine nine. Nine nine point five seven that's about the maximum memory three three but there's two double digits three three nine nine five seven three three nine nine point five seven because if you memorize like thirty three ninety nine. Then those are like one digit memory so I can remember seven double digit numbers instead of so I should be able to remember more than seven digit whatever inventory decreasing three three nine nine five seven. All right that's it recording the expense other side decrease in the inventory. Let's save it let's close it let's run it the balance sheet that is that's what we're running and check it out. So if I go on to the balance sheet inventory going down as of the end of May to the one eight four nine four seven does that tie out to what we have here one eight four nine four three. We're off by some by a bit here some pennies. No no one eight four nine one eight four nine four three. No you're just doing some stupid you can't you're the numbers changed when I looked at them that it's not that the numbers changed or something I don't know but now it's correct. So then we'll run it this one and then down here we've got the cost of the goods sold at and notice I put this one into cost of labor again. That's not correct let's just change that change that that probably bother people people are going to be upset on that one so I'll fix it now. They're still going to be except they're still going to be upset but this will at least will fix it will fix it. Okay. Okay let's do that. So then now we've got the cost of goods sold here. Okay so there it is and then we record the cost of goods sold. So the bottom line is the income is going up separate from the inventory tracking. We're not doing it on a perpetual system. We're basically using whatever system we talked about in prior presentations to pull the information on the income side of things. And now we're using the weighted average method to get a proper cost of goods sold as best we can on a periodic system so that we can basically look at our financial statements. In this case on a monthly basis at the end of the month and get some numbers that are going to hopefully help us for better decision making purposes into the future. And as you track your information over here on your worksheet and whatnot as well you can also of course get a better idea possibly of your profit margins and whatnot on a product by product basis. So you can then determine which products are not just selling best but also which products are most profitable when you take into consideration the profit margin and whatnot. I mean obviously here if you've got a product over here that costs a lot more and whatnot you would think that maybe the profit margins might be better if you could sell. So the more data could help with those types of calculations. So in any case the two major flow assumptions that we can we can use will generally be the first in first out which we took a look at in a prior presentation and then a weighted average type of flow assumption we took a look at this time. Now just for the fun of it let's take a look at because I did this one in 2023 and the last one in 2024 I believe. Let's change our report to total only and then I'm going to go to the periods and I wanted to select the previous year previous year and then I'll do the change here so I can compare actually wait a sec let me do it this way. I've got to change the date up to 2024 2024 that's when we did the FIFO and then I'll do the previous year change in dollar change in percent and run it. And so so now we've got April and May 2024 and then April and May 2023 and if we go down to the cost of goods sold you could see the cost to goods sold is not exact right because we use I believe in the prior presentations we did the same the same flows sales and purchases using a first in first out method and here we used a weighted average method. So you can get into the details of which is going to be more advantageous if you have a period of increasing prices such as we had here with inflation which is typically the case meaning the cost of the inventory is going up and your flow assumptions will then have an impact on your cost to goods sold and the general idea would be if you use the first in first out kind of flow assumption then that would mean that you would be selling the items that are cheaper right and your balance sheet would be holding on to the inventory that you purchase latest which usually would be the most expensive if you're in an inflationary or increasing price type of system. So you would think that the first in first out system will result in a bigger number in the Indian inventory and then the ones that you sold would be a smaller number and you know the cost of goods sold because you're selling the items that in essence are cheaper right would be the general idea and then the weighted average is kind of taking the middle one and then you've got first the last in first out which would be the opposite of first in first out which no one really uses generally for practical purposes because it doesn't really make sense from a flow assumption but you know it could it could make sense from a tax a tax standpoint. So in the case those of the that's the general idea.