 QuickBooks Online 2023 e-commerce inventory Excel weighted average 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 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 looking at e-commerce situations selling inventory but not on ground in a store rather online in the cloud with the help and use of third-party platforms such as the Shopify or an Amazon for example we're focused here on the inventory tracking side of things imagining a method where we have a periodic inventory tracking system method as opposed to a perpetual tracking method we have decoupled the sales side of the transactions from the inventory tracking side of the transaction remembering that when we have this third-party platform such as a Shopify for example that's when the point of sale is happening basically like in the cloud and we're pulling in the sales information into our system using one of the methods we've talked about in prior presentations then now we're focused on the inventory tracking side and the related cost of goods sold for the inventory now remember that in Shopify or whatever platform you are using you're gonna generally be tracking the units of inventory so that you have the inventory necessary to meet the demand going forward so you're gonna be purchasing inventory when the inventory gets low you're gonna be purchasing inventory tying it out to the physical count in order to logistically 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 u2 page we also include added resources such as excel practice problems pdf files and more like quickbooks backup files when applicable so once again click the link below for a free month membership to our website and all the content on it we meet the demand that is coming up and as sales happen the third-party platform will typically be decreasing the units of inventory on a perpetual inventory system however that information isn't pulling into quickbooks and even if it were the what we really need on the quickbook side of things is the financial information the dollar amount we got to convert the units uh two dollars so that we can track the dollars and we might have to do some tracking of the units as well to make sure that our units are tying out so we're going to do that in an excel worksheet and last time we worked a problem using a first in first out method same kind of concept here but now we're going to use the other kind of common inventory tracking method which is a weighted average method so we'll build a different worksheet uh same concept different inventory tracking methods so the worksheet will look much different uh due to that due to the way we're tracking all right so let's go over here and do a similar problem we're going to we're going to scroll in i'm going to scroll up in our worksheet i'm just going to build this uh from scratch here so we're going to say let's put up top let's just call it a weighted average method and let's see if i spelled that correct weighted average i almost never spelled that right all right i did it weighted weighted okay so then let's put our headers up top let's make this actually black and white for our header black and white because that's what i normally do let's keep with tradition here on the headers and then i'm going to put the headers of our table so i'm going to say we're going to have a date column and then i'm going to put the product now i'm going to have a different table for each product now so and so we're going to put these on a side by side so you'll see how this works for the table i'm going to build for the weighted average which will look different than the first in first out so i'm just going to call this product number one let's call this the unit change and this will be the total units and then i know i'm going over the cells we're going to wrap the texts so it's okay if we go over on this we'll fix it later we're going to say buy cost per unit the purchasing i said buy because it's it's a skinnier word to fit in our column and then we've got sales cost per unit and then i'm going to say tab total cost per uh per date i'm going to say and then ending inventory balance i'm abbreviating inventory so let's wrap the texts and see what that looks like so i'm going to select all of these that i've done here i don't need that i one i'm going to go up top to the home tab and we're going to go to the alignment and wrap the text let's also center it alignment and center the text and there's our headers i'm going to make it a different color because we're going to we're going to do this product by product side by side so i'm going to make the headers like green let's say them i'll make them dark green and then i need white text so it can kind of show up there and maybe i should make the whole table bold and i'll embolden the whole table everything has been emboldened okay so then let's say that we're going to do some purchasing here so i'm going to select column a i need to format it like a date so let's go up top to the general up top and i could format it here with the short date but i don't really want the year because i want to make it a generic problem so i'm going to right click on it and format the sales and i'm going to go into my date field and right there i've got one that doesn't have a year on it so i'm going to pick that one so it's just a generic date and then we're going to say i can make the date probably smaller just to save some room here product one i could probably make this one smaller this one can be smaller this one could probably be smaller and i'll just make a skinny skinnies here so it's taller but skinny tall and skinny okay so let's say four one and i'm just going to name these all product number one this is probably redundant because i have i've named this whole section of the table product one but if you ever wanted to convert something into a pivot table or something like that sometimes it's useful to have the columns populated so i tend to do that so i'm going to say all right then the units i'm going to say unit change let's say we purchase two units now the total units this is going to be a formula which is going to be equal to the two units now we're going to have to do two rows before this makes complete sense because this is going to be a running balance so next time it's going to be equal to this plus this right as we i'm sorry it's going to be equal to this plus this as we as we have a running balance going down but let's not complicate things now there's going to be the two units and then the cost we're going to say we're purchasing them for twenty dollars and then the sales cost uh per unit shouldn't there be a unit there yeah it's this is a taller column sales cost per unit uh there is no sale i'm going to put a zero here uh because i'm only going to be you because i'm either purchasing or selling and i'm putting them all in the same graph instead of having a separate item for the purchasing and selling so if this was a negative change that would indicate that it was a sale and if it's positive that means that we're purchasing it so then we're going to say the total here is going to be now this gets a little bit tricky the total uh cost per date is going to be this times this that's the unit change which of course is 40 but if there was a decrease then it would it would be the change which would be negative times times the sales cost per unit okay so so what i'm going to do is say it's going to be this plus this and then i'm going to say plus and then this change if it was negative times this now because one of these will be zero the second part of the formula will be zero it's not going to change the outcome which is just going to be 20 times two and if this number was negative which we'll see in the future when we sell the units then then this by part is going to be zero so the first half will come out to zero and the second half will calculate the formula so so it looks a little little weird but it'll it'll i think it'll work for us and then the ending balance is going to be another kind of running balance type of formula so i'm just going to say that equals the 40 here so that's going to be our starting on this table looks a little bit abstract but i think it'll make more sense as we buy more units for for now let's imagine that we are then purchasing these units let me just make sure i don't have any more purchases here yeah on in quickbooks now so we're imagining in quickbooks we're buying more units so what would happen we'd see in Shopify we need to buy more units or whatever in units and then we would populate our table over here in order to determine you know for our table or we might first come up to the units we're going to buy two units or whatever and then figure out the cost the total cost which might be 40 and then back into the cost per unit which would be the 40 dollars we're paying and we might have to include sales tax and that kind of stuff divided by the two units to get the unit cost for our weighted average tracking okay so let's go ahead and just plug it in here so we're going to imagine that this is happening as we go so i'm going to hit the drop down and we're going to say that we have an expense so when we do the purchases no matter what method we use we're basically on a cash-based method we'll see it clear the bank account in essence we're paying for inventory so we're going to say vendor one we don't have that accrual kind of issue and let's say this happened on uh 040123 i think we're on 23 at the year and then inventory so we're going to say the inventory i'm just going to put the amount and it's going to go into the inventory we said 40 i believe is that right i believe that is right so this is going to decrease the checking account i'm not using items i'm just plugging it into the inventory account we're not tracking units in quickbooks we're only tracking dollar amount so let's save it and close it for some reason i didn't i didn't open up my financials like we usually do every time let's do that now right click and duplicate right click and duplicate i'm a little bit off this time what is going on here the rhythm has been broken middle tab we're going to go down to the reports on the left hand side open up the balance sheet tab into the right down to the reports on the left this time the p the l the profit the loss let's change the range close the boogie and we're going from let's see 040123 what kpasso 040123 to 053123 and then run it and there's stuff in it already so i have this is where i ran some of the sales before but i don't want any stuff in it that's okay it's only it's it won't bother us it's just a little bit of stuff so let's go back to the tab to the left close the hamburger and do a range change and go from 040123 to 053123 and run that one as well so what we have thus far is that uh we made a transaction with the checking account going down checking account going down by the 40 so there it is and then the other side is not going to the income statement but rather is going into the inventory account but we're not tracking the units of inventory in quickbooks we're doing that externally in our excel worksheet all right so now we're going to say stuff happens products are selling but we're not going to we're not going to do it an adjustment to this or record the sales until the end of the month because we're going to do it periodically so all those sales are happening on Shopify we're just tracking possibly the units within Shopify to make sure that we have enough to cover whatever sales are happening so then we're going to say that we purchase more so so sales are happening blah blah and we can see in Shopify our unit sales are getting low for particular products and so we're going to just buy more units we're not going to do on it on more of a perpetual system here right but if we're on a periodic system for our financial statements when we're going to record the find the financial costs of the inventory so let's purchase more inventory on 415 product one and we're going to say that we purchase one more of these uh now and now I'm going to have a running balance here so this equals the cell above it plus the cell to the left of it and enter so now we have we have three units we've got a running balance going down and we're purchasing again so we're going to have something in this cell so I'm going to say 22 and so they've going up and cost they did cost $20 a unit now they cost $22 unit because we want to do with the fact that there might be inflation over time we have the same units with different costs that's why we need a flow assumption like weighted average lifo-fifo and whatever so then we're going to say the sales we didn't sell it so I'm just going to put zero there and then the total is going to be the same formula I'll just copy it down and then the ending balance is going to be a running balance the 22 plus the 40 so we've got uh the 62 is our ending balance and inventory now we're going to say that we bought another product now and what I'm going to do is keep the dates all the same and I'm going to put all my new products side by side with basically the same table structure so I'm going to copy this whole thing I'm going to put my cursor on a copy the whole column over to H control C and put this in column I control V and we're going to do the same thing here I don't need the weighted average thing up top so let's format paint and delete that and I'll make this a different color so we can distinguish one uh from the other and so I'm going to make this blue let's make it blue on the headers and then I'm going to try to freeze my pains now so I'm going to go down freeze my pain because I hate when pain comes up and needs to be frozen so I'm going to freeze the pains uh because I want to see the date I want to see the date as I go to the right because I don't really need this date column here I'm going to actually delete this date column let's go to column I right click and delete that because I'm just going to put everything on the same date from over here so I'm going to put my cursor on uh let's just do it on this date on this cell home tab not home tab view tab freeze pains in the window group and freeze the pains so now when I go to the right I'll have that date that kind of follows follows along here even though it's a different color okay that doesn't bother me and then I'm going to delete this stuff or maybe I can just delete the second row or hold on let's delete the first row sorry about that and I'm going to call this product number two instead of product number one product number two and then on product number two we're going to say that we're going to purchase two of them so two of them so the totals are totaling up okay now because I'm I used that same formula I didn't delete the formula and then the cost of it is going to be for product number two 105 and then the sales is zero and this formula is the same and then ending inventory is doing that calculation again let's do the same thing I'm going to say we purchase another one product number three so you can see this gets to be a very long table horizontally because I'm going to say copy and I'm going to put this over here what what's your problem man it doesn't like that I'm going to copy it again I have to be at the top I wasn't at the top I have to be up here on p1 and then put it there and I'm going to make this purple this time to change the color because we're on product number three multiple products being put in place here double clicking I'm going to call this product number three product number three here two and so then this is going to be we're going to say that we purchased four of these and so my running balance comes out to four because this is the first one we purchased and they cost us these are the expensive one 650 and no sales per unit so we come up to total cost per date 2006 and the total is at 26 now I would like to my four I didn't format the numbers the way I would like to hear so let's go I'm going to select from K not including the date field right click on the format it sells and let's do some formatting of currency negative numbers bracketed no dollar sign I'm going to keep the pennies because when we use the weighted average method it's going to round to pennies let's say round it all right so this one needs to be a little bit wider when I add the pennies that one needs to be a bit wider this one needs to be a bit wider this one these are the big numbers over here this is the ones we try to sell these are the ones we get them in the store with those other ones and then and then we steer them to these these no I'm just kidding any case we're going to say this is going to be the total now I'm going to sum all these up the total ending inventory and this is going to be the change or the journal entry entry for cost of goods sold when it's when it's negative so let's make I'm going to make this one home tab alignment let's do that wrap it and center it I'm going to make it black and white for the totals and then and then change this is the journal entry when it's negative so when it's so I'm going to make this red so when it's negative we ensure that's the journal entry okay so the total ending inventory then is going to be the ending inventory of all these so I'm going to say it's this ending inventory for the green product one this one for the blue product two that one for the purple and then the change basically up here I should have done the same let's copy that up here so it was at 40 this is the same formula and then the running the running balance then what or the change that's happening the first one let's just say was 40 and this one is going to be a running balance that looks like this it's going to be this minus we could say the one above it or we could say minus this one so that's going to be then hold on a second no it's going to be equal to this minus the one above it that's the change that we're going to put in place okay so let's just break this down and see what this means here we're going to say okay this is my this is what we purchased for product number one I'm holding down I'm going to hold down control when I click on this one this is what we purchased for product number two and then over here I'm holding down control to point to this one this is what we purchased for product number three that comes out to the two eight three two and that's going to be this number here that's the change two eight three two and we had product number one of forty dollars right and so that's why that's why we've got the two eight seven two for our total inventory which we can also see is going to be then this number ending inventory plus the end end inventory here plus the ending inventory here that's the two eight seven two right so every time we add a new product this is going to be a very horizontally long worksheet so the bottom line though is that we're paying in this case we're buying the inventory so we're going to be paying the two eight seven two now note that if you're buying all this inventory from one vendor then you're going to have to come up with some way to break out you might just see the the sales price of the two eight seven two which you're going to have to break out to the number of units which would be easy if it was one thing that you're buying but if you're buying multiple products then you're going to have to you know break out the cost per each product and possibly the sales tax can be a little bit complicated to get the unit amounts right so I'm I'm just going to put this into our worksheet now we purchased again so I'm going to go back on over and say new and expense form another purchase and the purchase price should be for this amount right the two eight three two which represents the the change per date the amount we purchased per date not the ending inventory so that's the two eight three two the two eight three two okay so let's do it and we're going to say I'm just going to say we purchased it all from vendor number one again this happened on uh 415 let's say 415 and then we're going to say assets are going up by the two eight three two two eight three two same transaction we're going to save it in closest this would be just a cash transaction the easy side of the inventory because we're just purchasing it basically on a cash based system in essence and we'll run the balance sheet so that would be decreased in the checking account now our ending inventory is at two eight seven two which is consisting of those two purchases that we have made right there the 40 and a two eight three two back to the report exit so there's the two eight seven two and if I go into here there's the two eight seven two okay so next thing that happens we're going to say that that then on the end of the month comes around and that's when we do our periodic inventory adjustment so we can imagine what's happening here sales are happening over here we've been adjusting our inventory in our third party platform to meet the sales they've been decreasing it as sales happen on this third party platform and and now we're trying to figure out what the what the value should be on our financial statements for the sales half meaning we're going to decrease our inventory for the sales that have happened in dollar amount not in terms of just the units so we could do it we could double check our numbers here in basically a Shopify system as of a period as of the end of the month and make sure that we're and we could do a physical count double checking basically our inventory in terms of physical count and then once we have the physical count we can determine the dollar amount to do an adjustment decreasing inventory recording the related cost to get sold the expense so let's say that we do our our physical count here and we're going to say that for this inventory we actually sold all of product number one we don't have any left so on 430 we'll do our adjustment it's going to be product number one and now it's going to be negative they're all gone negative three and then the the uh totals per unit i'm just going to copy this down and notice that this one is always going to be a copy of the formula down one and this one's always going to be something I have to put data into so sometimes I'll make this one I like to make where I do the data input a different color I'm going to go to more colors and standard wheel I usually make those blue to indicate I'm going to have to do data input into these cells I'm going to put brackets around this whole thing too let's bracket it let's put some brackets around it font group brackets so I'm going to indicate that I have to do a data input there this one's just going to be copied down so I'll keep it white and then this one is the buy cost per unit now again I'm going to put a blue formula here because we would have to do the data input into the buy we're not buying anything in this case we're recording the sales now we have to figure out the sales cost per unit using our weighted average inventory method now as of this point in time we had ending units of 62 and we had a total units on hand of three so we can basically just take the average of that now now note the problem here of course is these are all the same three units that we sold but some of them cost us $20 and some of them cost us $22 so we're just going to take the average at the point in at this point in time which is going to be equal to 62 divided by the divided by the three units so some cost $20 some cost $22 they cost on a weighted average $20 and 67 cents see that's how the system is going to basically be working here and then we can copy this formula down here which is which is multiplying these two out so if I copy this down now we have the negative number because remember this was C6 times E6 which is now zero so this first half is doing nothing and then we've got C6 which is this number time F6 which is now the sales cost so this is what's being calculated this times this gets us to the total cost per date which is a decrease now and our ending balance if I copy this down is back down to zero now note I had to put the formula in here I'd like to be able to copy this formula down so what I'm going to do is put a kind of a fancy formula in here and say hey look uh you're only going to do this if uh if it's a purchase side of things I'm going to do a conditional formatting so that I can copy this down going forward so what I'm going to do is I'm going to double click on this and I'm going to say uh I'm going to say if and put the brackets in and I'm going to add this to the front part of the formula or maybe it would be easier to build this from scratch let's just build it from scratch if so we're going to say then equals if brackets and I'm going to pick up this cell which represents us selling units if that's less than zero that means we sold stuff instead of purchasing stuff then which is a comma what would do we want them to do I want you to take that 62 divided by the three just like we did however comma that's what the comma means if it's false if that's not less than zero I want you to just put a zero there and then enter so there we have it so now if this was like zero or above it's not going to do the calculation and so that's the idea so then I'm going to do the same thing on this side we didn't sell any of product two but I want to be able to carry everything over on the same date line so I'm going to copy down any cells that needed to be copied down so there's nothing in here I'm going to make this blue for our formatting purposes because that's our data input I'll make that blue and then this is just going to copy down as we add new cells here because that's our running balance and nothing's in here this one I want to copy my fancy formula so I'm going to copy that fancy formula and it puts a zero there because if this was negative something then that would represent I say that's not my fancy formula hold on a second undo that wrong cell copy my fancy formula from here and so now if this was negative two it would do like a calculation in there right so I'm going to delete that and then this needs to be copied down so let's just copy these down and I'll do the same for product number three now once we have these all set up we you could see we just do the we could just copy these down is the idea so I'm going to select these two and then okay let's copy this one down and then I'm going to copy my fancy formula just do a double check that if this was negative two that it does a calculation here okay back to zero and so there we have that and then our ending balances should copy down as well so these need to copy down and I'll copy these down so we could just copy everything down except the blue areas that's the idea so and I'll try I'll convert this into a table shortly so hopefully it'll copy down a little bit easier as we go and you can see here that we have the 62 change thus far and I think did we purchase something in the over here on did we sell anything on the blue no that's it so we had a change of the negative 62 which just represents the change that happened on product number one that's what we sold so we're going to say all right after the end of the day here here's our journal entry we're going to make inventory is going to be going down by 62 bringing the ending inventory to this 2810 which is this 6226 holding control and that 210 holding control and this zero which is the 2810 and that's the 2810 so if I look at my QuickBooks I'm at 2832 let's just double check that 2832 hold on a second wait wait a second I should be at no I'm at 2872 in QuickBooks 2872 and we're going to bring it down by that 62 so I'm going to do a journal entry now first tab new button let's do a journal entry so this is going long so I'll try to do this fairly quick here this is going to be 05 no 0404323 and we're just going to write it to cost of goods sold for the 62 and the other side's going to go to inventory so inventory 62 and there it is so it's going to decrease inventory increase the cost of goods sold let's save and close it and if I go to my balance sheet now and run it we've got our inventories at the 2810 and that's what it should be so that looks good and if I go into it there's our detail and so we're just increasing it with normal purchases and then we're adjusting it periodically at the end of the month periodic method as opposed to the perpetual inventory method tracking the units and the flow assumption in our excel worksheet and then in the the profit and loss we've got the cost of goods sold at that 62 now now remember the sales would be being recorded on the other methods we talked about because we decoupled recording the sales half from the inventory half that's what it means to basically be using a periodic inventory system so we'll do another month next time and then we'll convert our table here into a table we'll make this into a table so that it might be a little bit easier because it looks a little tedious to put this thing together when you start out but hopefully that once you have it going then we can add line items one at a time it'll be a very long horizontally table as we add products and whatnot but it should be easier to add them as we can kind of automate everything except for these the blue areas for the items that we are adding so we'll continue with that next time