 QuickBooks Online 2023, e-commerce inventory, Excel first in, first out, FIFO practice problem part number two. 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 on down below duplicating some tabs to put reports in by right click at the tab up top to duplicate it right click at the tab up top to duplicate it back to the tab to the middle we're going to the reports on the left hand side we want once again that balance sheet report as it's thinking tap into the right as we do every time reports on the left this time the profit and loss the income statement I'm going to close the hamburger change the range let's make this time the range is going to go from 040124 tabs to 053124 tab for that two month period let's also see it on a side by side with the month by month so I'm going to make it month breakout over here and then run it and then tab in it to the middle closing up the hamburger put the range to the same here at 0401 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 124205 3124 let's do it a month by month side by side as well and run that report back to the tab to 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 use support of third party applications for example being like a Shopify or and Amazon we've been looking at inventory flow assumptions breaking out the inventory from the sales side of things using a periodic system for the inventory method and we're using a first in first out with the help and support of our excel worksheet which we started out last time so if you didn't if you didn't see the last time presentation with this particular practice problem you probably want to look at this because we're on part number two at this point so we entered the first month of of data into our excel worksheet just as a recap we've been buying the inventory as we need it and then periodically at the end of the month we counted the inventory that we have left we determined then using our first in first out method what then ending inventory should be on i'm going to delete these items here on a first in first out periodic based system and then we went over here into our worksheet and we adjusted the ending inventory with a journal entry to match out and we recorded the other side to cost of goods sold quick books now reflecting properly hopefully our financial statements but the subsidiary reports in order to in order to sub to to back up those numbers are basically being done in excel now we're imagining we're just going to move on to the next month and we're going to do the same kind of thing i'm going to i'm going to imagine we have our inventory that's in our third party platform that we're selling on like a Shopify or amazon we're tracking the units of inventory as we purchase inventory we're going to say we're low on inventory in terms of units we're going to then purchase more inventory and we'll increase the number of units as we do so and then on a perpetual inventory system just for the units the the online platform will decrease the number of inventory as we make our sales and then of course our goal on the excel worksheet is to convert that from a unit basis to a flow assumption so we can get a dollar amount for the similar kind of process so we're going through we're saying okay we need to purchase more stuff in the following month of may now so i'm going to go back on over and say all right let's imagine that according to to our estimates we need more stuff to sell and so i'm going to say on five one now notice i put a table around this as i add each line item to the table it should extend the table as i add a line item so for example we're going to imagine that we're going to add uh six more items of item number one now note i can i might want to sort this you can also sort it over here it's sorted by date for example uh with these filters that's why those filters are quite nice so i'm going to say all right on five one and notice when i hit enter it extended the table i'm going to say that we purchased six more of product i gotta make sure i get the exact name so that my filtering works well product number one i've got product number one but i'm going to put them on there one at a time so and i'm going to say that the unit cost of them this time is going to be twenty seven dollars so the no it's going to be twenty three dollars so the cost was twenty dollars inflation happened or something it went up to twenty three so i'm going to copy this down one two three four five because i want to have six lines that have that same information in it i'm going to copy these down we uh here because those are our totals that need to be brought down as well and so if i look at those does that make sense we've got six items i put them on there one at a time one two three four five six of them you can see the physical count over here as well as the summing therefore the total dollar amount for those would be 138 now if you if you bought these in one lump sum and you had other expenses that were related to it for example you might say hey look i paid 138 for it and then you would just divide that by six right and that would be in essence your unit cost for uh those products let's also say that we bought uh on five three so so well that's that's going to happen on five one did we purchase anything else on five one no let's keep it at that so now we're going to go into our system and i'm just going to say this would come through the bank feed so the purchasing side is in essence on a cashed based system in that we should see the money going out of the checking account to tell us when to record the transaction as opposed to the decrease big to to inventory because we have to do that on a basically a a periodic basis using our accrual concept so i'm going to say this is going to be vendor one and we'll just say this happened on 0501 uh 24 we're making a purchase here it's just going to increase inventory like it does every time and i'm just going to put the new dollar amount which happens to be what did i say it was we're going to sum up these items it costs 138 dollars so one one three eight boom this is going to increase inventory the other side's going to go to the the uh checking account decrease it's going to decrease checking account increase inventory all right save it and let's run it so checking account goes down inventories are going up now so inventory if i look in maze inventory then we've got that increase on 5 1 very good nothing if i exit this out if i go into my my uh income statement no cost of goods sold yet for may because we're going to figure that out at the end of may even those sales have been taking place possibly already we're not going to we're not going to do the decrease of inventory and cost of goods sold until the end of the month all right and then let's say that time passes and over here on our platform we're still tracking on in essence a perpetual inventory system with regards to the units of inventory because when i purchased those inventory units i would have increased them to you know product number one and then sales would have happened over time but i'm not decreasing the inventory in my worksheet for sales however they would be decreased over here on a perpetual method because because this is tracking just the units on a perpetual method and i might say okay now i need to purchase more stuff and in the month of of may so i'm just going to say all right i need more stuff to to deal with logistically uh keeping up with demand over here so this is on 5 15 on 5 15 we're going to say that we purchased two more of these product ones product one we purchased two more of those and i'm going to put them on one at a time but now they cost $27 inflation killing us so they're going up in cost even though the same units i put them on there two times so that cost me $54 for those two i could copy these down these need to copy down and then i'm also going to buy on the same day 5 15 we're going to buy three units of product number of product number uh two so product two we're going to buy three units of those and they now cost one one one uh one hundred and eleven dollars of product for product number two they used to cost 105 so now they cost 111 i'm going to put them on the books individually so two more of those so they cost us uh $333 we can copy these down again and then we've got also on 5 15 we purchased three of product number three and we're going to say that they cost us 675 so once again inflation happened hold on these need to go on the books one at a time 111 and then one and then these cost 675 hopefully i didn't confuse you too bad there they used to cost 650 so again inflation hit us again and we're going to copy those and paste them here boom and we'll copy these down so now we've added these to the books sales have been happening but we're not recording the sales yet for may we're just going to record the sales at one time just like we did over here at at the end of at the end of this whole process so let's say okay then what does that add up to i'm going to say all this stuff happened uh i could say from here to here so it cost us uh two four one two two four one two and and again if if you were buying product by product product one two and three and so on like if i was buying these three products and it if the total came out to 2025 total cost including everything sales tax and whatnot that's included i could take that number divided by three and that would be basically my unit price that i would be putting into my worksheet per unit you can get a little bit more complex of course if you're if you're buying multiple different products at one time to try to figure out what the unit cost is for each product when you get into sales tax and whatnot but you can come up with a with a ratio method to kind of allocate you know sales tax and whatnot properly uh to the products even though you're paying possibly you know a lump sum amount we want to break it out by product so that we can put it on our sheet over here okay so now we but in our excel sheet we're just going to pay let's say one vendor again for the 2004 12 so i'm going to say all right let's go back on over here it's going to come out of my checking account so i will see it hitting the checking account expenses and we'll just say this is going to be vendor one again and this happened on 5 15 and we'll say that this was inventory is going going up by the number i can't remember i just said it 2004 12 2004 12 2004 12 and this will decrease the checking account increase inventory once again so let's save it and close it time has passed and now we've got the inventory we boosted it back up here in may for the two purchases that happened in may we don't have any sales yet in may meaning we did actually have sales we haven't recorded them in quickbooks because we're going to record and notice we would have recorded the sales half of the sales but we're not recording the decrease of the inventory part of the sales of inventory that we had because that we're doing on a periodic system if i then go on over to my profit and loss nothing's over here for may in the profit and loss notice that the income line would have income because the income is tying in to the to the format that we pulled in the income from which might be quickbooks integration or using a manual journal entry method or whatever method we use that we talked about in prior presentations we're focusing now on the cost of goods sold and notice i put this one into cost of labor i think it should have gone let's actually fix that i'm going to go into that it's not labor what are you talking about i think i have a cost of goods sold account let's put it into cost of goods sold straight up cost of goods save it all right that's probably what's bothering people i don't want to bother people unnecessarily all right so there's that and then we're going to imagine the end of the month rolls around again and if i go on over to my my Shopify then i can see the units that are basically being tracked on like a perpetual system even though i'm only updating my worksheet on a periodic system monthly in other words over here i've been i had my beginning balances from last month i added the purchases when i purchased them so that it's reflected in my Shopify so i can cover my sales and it's being decreased in terms of units when the sales actually happen by Shopify or whatever online platform you're dealing with and you can tie that out to the physical count as well and make sure that this number you know ties out to the physical inventory and then we're going to take out whatever the physical inventory is which should be reflected here in this number on your online platform and say i'm going to assume the difference between that inventory and what i currently have in my worksheet our sales right so i'm going to go back on over here and say let's break this out one by one again product by product so then i can say i can adjust my products because i have them all in this nice worksheet this is what the nice thing is about the worksheet i could say let's sort my products by just product number one and then say that that as of the end of the time frame we have units left to product number one four units left of product number one so i'm going to say okay that means that i sold and maybe it would be easier to do in the table over here if i said i had four products left right i have eight in my little table minus the four so i sold uh four products would be the general ideas i'm going to go back on over here and say okay uh we sold four now notice if you had more information than the end of april you can also filter by date i mean at the end of may so if you had more information in june and you're trying to figure out where you stand at the end of may you can use your filtering options here to see your table for this this time frame but now we're going to say okay so we sold four of these items on five let's say five thirty one so five thirty one and i have to format my date fields here so let's go ahead and just copy the formatting here and i'll put that all the way down to my table and i'll say this is on five thirty one we sold four of these they're all going to be on there one at a time the cost is going to equal the cost of this line item so it'll it'll tie out and map out so the the number of units left of course is zero that's this minus this and the ending inventory is this minus this there's nothing left because we sold this ending inventory these two need to reflect nothing and we're going to say that we sold let's do that four a couple more times i'm going to copy this one two three more times so there's the four that we sold putting them on there one at a time and it's picking up then the reflected amounts which are all the same here because they all cost $23 it wasn't until we got down here that we had higher costs per unit notice i'm filtering by product and i'm also sorting it by date in order to to pick up the right line items on a first in first out inventory method okay so now let's imagine that product product number two we had five we still have five on hand so we didn't sell any a product number two and then product number three let's imagine that we have two on hand on the physical count that means we sold five of them so we sold five of those items so i'm just going to go back on over here and say let's sort this by product number three i'll filter it in other words or more properly product number three and we sold i said what did i say we sold now i now i deleted it but we sold five of them all right so we're going to say on five thirty one we sold five and i have to do the date thing again let's select this whole column and format this thing so that i have the date like that okay so this is five thirty one and we sold five of them and the units out are going to be one each time and the cost is the six fifty i'm going to copy that down four more times one two three four so that we have five of them one two three four five counting at the five and so this is the cost of those particular items and notice that there's a difference in the cost for right here that's the problem because now we sold five of them and some of them cost six hundred and fifty some of them cost six seventy five and so we're saying that we sold the ones that cost six fifty first and then we're eating into the six seventy five ones noting that in an inflationary time frame we're left with the ones that are more expensive the higher priced ones so so meaning our our balance sheet is going to be reflecting the the items that are cost more even though they're the same units in a first and first out inflation if there's an inflation or increase in time in price time frame all right so that's going to be that so now i'm going to let's unfilter this again i'm going to say let's take the filters off boom and so now i'm going to delete this stuff we're going to say this should tie out to what we have in our indian units like i've got four units of product one five product two and product three has three and this is according to our our formulas that we put up last time we could also do it like a pivot table so we have that we might do a pivot table in a second just for the fun of it but we've got that and then uh and then now we've got the total this is what our indian inventory should be so our indian inventory over here in quickbooks is currently at five three six zero it's going to be higher because we've been purchasing stuff but not recording the sales five three six zero so we're gonna have to bring it down this minus this adjustment it's going to be three thousand three sixty four to get indian inventory to that so i'm going to say all right let's just do a journal entry periodic a journal entry new button and we're going to do a journal entry and we'll do it as of the end of may this time and we'll say that it's going to be a debit to cost of good sold see if i can pick the right one and we'll say that was for the amount of three three six seven so three three six seven and the other side's going to go to inventory for three three six seven let's go ahead and save it and close it and the balance sheet running it so now we've got our indian inventory over here at the one nine nine three and does that title one nine nine three so that's what we wanted it to be and then the cost of good sold is going to be over here cost of good sold run it so now we've got our cost of good sold for may of the three three six seven which is of course the calculation that we made for this item you can also basically see that in our table over here if i was to filter on on this date field for the sales dates and i just want to see the items i'm going to that were in may so i've got the may items and i'm going to say okay and i've i've unfiltered everything else and i can go in here and say okay this is the cost column if i select the whole thing that comes out to the three three six seven so that's another way you can kind of double check and sort your data so now over here notice the sales would be happening using the method we looked at before possibly with like the the intuit integration with a Shopify or amazon or or ebay or whatever and then these cost of good solds will hopefully match up more properly to the proper time periods which will give us better you know matching information so that we can make decisions on what we're going to purchase in the future and what our costing our costing should be and again we could try to map this out by product for example which gets a little bit more detail so we can think about our profit margins you know on a per product type of basis and whatnot so we can go through our inventory decision making but the bottom line is that now we've got these hopefully timed out properly we're doing it on a periodic mace method adjusting at the end of the period we have our inventory item number over here which should be correct from a financial standpoint but doesn't have the backup or support within quickbooks of the unit amounts and and the flow assumption because that is being done on a periodic method in our worksheet on this side now before we wrap this one up let's just note that you could make like a pivot table out of this data over here as well which sometimes could be a useful way to see it so let's get rid of the filters right here and say let's remove the filters and everything is selected and i'm just going to add a pivot table so we can kind of see that format as well so we're basically going to rebuild this table but do it with a pivot table which is kind of easier to do in some ways but i kind of like the hard formulas as well but you know different people have different options and so i'm going to select any cell in here just one cell though i've got one cell i'm going to insert i'm going to go to the insert tab up top and i'm going to go to the pivot table it's already in a normal table over here but i'm going to create a pivot table and then it creates the range and it's basically selecting the table i'm going to put it into the existing worksheet and i'm going to delete this bit right there and i'm going to put it right here in s1 and say okay and it gives us our pivot table thing and i'm basically going to reverse this so it has the products on the left and the unit and the ending balance up top so i'm going to say all right let's pick up then the product so i'm looking for the product field and it pulls that into the rows so that looks good i'm going to pull that into the rows very nice and then i want to pick up the unit balance and the ending balance so unit balance it put it where i wanted to go which is on this side and it's using the sum so there's the four the five and the three the four the five and the three and then the ending balance and so there we have the ending balance of the 100 the 543 and the 1350 now if you make adjustments if you have a pivot table for example if i close this out it should it's a nice easy thing to build once it's built up it's nice it's nice and but then if you make adjustments over here your pivot table you have to right click on it and possibly refresh the pivot table so that so that you can refresh the numbers and if anything gets wonky on the pivot table then you can basically recreate the pivot table once you've done it a few times it's not too difficult and you could format it and whatnot but i'm gonna bring this in a bit maybe i'll do the headers and wrap it over here and center it so i won't get into much more detail on formatting the the pivot table but that could be a little bit faster once you get used to them than creating a table like this and then you could refresh the pivot table as needed as you're updating your data to the left and again if the pivot table something goes wrong with it you can always just you know recreate the pivot table from your data set over here once you get the hang of creating a pivot table