How to Create a Journal Entry for Cost of Goods Sold in QuickBooks Online

If you like this video and you want to see more videos like this, then please subscribe to our channel and click the bell to receive notifications.

0:48 How inventory moves and three ways to track it

First thing I want to talk about is the way that inventory should move through a business financially. I'm just going to touch on this briefly, because you may have seen some of our other videos that educate about cost of goods sold as well. But basically, inventory at the time that you purchased it should not be expensed right away. It actually becomes an asset that sits on your balance sheet and is then expensed incrementally over time as that item is actually sold. Hence the name cost of goods sold. That's when it hits your profit and loss statement, in the category of cost of goods sold, is as it's actually sold.

If you're doing this method incorrectly, then this is what you end up with. If you are just expensing at the time of the purchase, you end up with huge hits to cost of goods sold in the month that a purchase is actually made, and then other months where they don't share their burden of the cost of goods sold, and it becomes very hard to see the profitability.

So this is the top half of your profit and loss statement, basically showing you your income and the cost of goods sold right underneath it. Income minus cost of goods sold equals gross profit. In this case here you can see we appear to have a hugely unprofitable January and then very, very profitable Februaries and Marches as well, because they don't have any of the cost.

This is how it should actually look if you're tracking cost of goods sold correctly. Each month actually shares its burden of the cost of goods sold incrementally over time, and you can see each month that we show a sustainable, predictable profit margin that then allows us to plan for the rest of our business.

2:08

There are really three different options you have of ways that you can track inventory. It's this bucket to bucket method that I'm going to talk about in this particular video, that's what this video is going to focus on. The tracking item by item method in QBO, which again the video can be found here. And last of all, you could be using an inventory tool, like a third-party inventory tool that either integrates with QuickBooks Online or doesn't integrate with QuickBooks Online. Either way, an operational inventory tool that also tracks cost of goods sold. There's a lot of pros and cons to these three different methods. This particular method will focus on this first one, the bucket to bucket method.

2:41 Pros of the bucket to bucket method

What are the pros and cons of this particular method? Number one, it keeps your QuickBooks file very clean. Now I am not a huge fan of the functionality for inventory inside QuickBooks Online. It's not very robust, and as a result it becomes very hard to track inventory successfully if you're trying to use this item by item method.

And if you're trying to track inventory using this way, using the item by item method, there are advantages to that, but one of the disadvantages are if it ends up getting messy, you now may have like hundreds of items inside QuickBooks that have a quantity that's off, that have a value that's off, and all of that is affecting both your cost of goods sold number and your balance sheet item inventory as well.

Versus this method, basically, because you look at the inventory balance on your balance sheet as a lump sum, and you look at the cost of goods sold amount on your profit and loss as a lump sum, it becomes very easy to keep those two things clean.

3:35

Second of all, I like this method because it's easy to validate the numbers. Because you're doing all of this inside of a spreadsheet, you can actually see exactly what the cost of your items are, and you can see why you're getting the answer you are getting regarding what those costs are. You can see what the formulas are that got you there, you can see what the inputs are that got you there.

Inventory inside of QuickBooks Online is built based on, like, every time you buy more inventory it basically pulls in the value of that inventory from the bill or purchase order that was created when the inventory was received. And so whatever value your inventory is, is a moving target, without necessarily like a whole lot of clear visibility on why that value ended up being what it is.

Whereas the spreadsheet method, it becomes very easy to validate all the numbers, and you can also be very sure that every single item has a value assigned to it. Whereas sometimes in QuickBooks Online, inventory will be put into the system without a value, and unless you know how to look for whether or not it has a value associated with it, a lot of times the cost of goods sold will be missing value completely and it's a lot harder to see. So that's another reason why I like this method, because it becomes very easy to validate the numbers.

4:44 Where this method falls short

Some disadvantages of this method is that it requires some comfort level with spreadsheets, which I try to go through in this video, show you what you need to know primarily, but still sometimes spreadsheets scare people and I don't really blame you. So that is one disadvantage of this method.

And second of all, another disadvantage is that the quantity is not available in QuickBooks Online. So sometimes people are using QuickBooks Online as sort of a like an operational tool to help them track the quantity of inventory that they have available, and the method that I'm going to show you isn't necessarily tracking quantity. Whereas if you are tracking quantity inside QuickBooks Online and you are managing to keep that system clean, then it becomes not just a value tracker but a quantity tracker as well, which can be very useful.

5:28 The four step process

So the bucket to bucket method that I'm going to talk about right now, I alluded to just a moment ago, but basically what you're going to be doing is you're going to be calculating outside of QuickBooks what your cost of goods sold is going to be. I'll show you the steps of that in just a moment. And then you're going to make a journal entry that moves the balance over from inventory on the balance sheet over to the profit and loss as a lump sum. And so you're basically really just dealing with two accounts. You're dealing with inventory and you're dealing with cost of goods sold.

So what does that process look like? Here's the step by step. Number one, you're going to need to create a product cost catalog. Number two, you're going to need to identify your cost by SKU for the month. Number three, you're going to need to look at those costs by SKU and multiply it out by the quantity that was sold to give you a cost of goods sold. And number four, you're going to need to make the journal entry to record COGS at the end of each period. Down here you see the structure for that journal entry. The inventory asset is credited and the cost of goods sold is debited.

One last thing I'm actually going to mention that's not listed here, and this is your monthly methodology, but once a period, whatever time that may be, whether it's once a year, once a month, you're also probably going to need to true up against an inventory count, an ending inventory count, and correct whatever balances need to be adjusted. So you usually look at that ending count and you compare it to your ending balance that's sitting on your balance sheet, and you true up that balance at the end of each period using cost of goods sold as the wash account. So let's dive in and look at how this is actually done in practice.

6:57 Building the catalog and posting the entry

Let me show you what this process would actually look like in practice. So if I had five SKUs and I was building what we call a product cost catalog, I would list all my SKUs out and I would list beside them the cost. Now for the sake of simplicity I'm keeping this pretty straightforward. This can actually get pretty complex if you have costs that are moving, and if your manufacturers are charging you different costs, or if you're trying to add into all of your costs your landed costs of shipping and freight and all those other things. But for the sake of simplicity I'm just going to keep this straightforward. So let's say that you had all these items and that these were all their costs.

Now one thing that you could consider is if you're selling on multiple channels, one of the great things to do is to actually create what I call a product cost catalog SKU match bible. That's quite a mouthful, but basically what that means is you have all of your items, you list all of the SKUs associated with that item and every channel you're selling, and then the cost as well. This comes in incredibly handy, like, all the time. So this is just a great thing to be building if you are tracking a product catalog and you are selling that product on multiple channels. Consider associating all SKUs [unclear] in a single spreadsheet.

8:09

So what I'm going to do is I'm going to go into my channels and I'm going to identify at the end of a period of time what actually sold on that channel. So here's an example of what that would look like. This is an example of a report that was downloaded from eBay. Every channel has different options available. All of the data basically comes down looking slightly different, but really what it's getting at is the same thing.

What I'm interested in here, then, is that I could basically delete every other row that doesn't tell me this. First of all, I'm interested in the date of when it sold, because that basically tells me, like, I want to make sure that I'm associating the sales, the cost of goods sold, in the correct month that I'm recognizing the sales in. So I want to see when this actually sold. I'm looking at the quantity of what sold, and I'm looking at the SKU of what actually sold.

8:54

So then I'm going to take that data, I'm going to take it back to this other spreadsheet, and now I'm going to do a cost calculation. So I'm going to say, if these are all of my SKUs, maybe I sold 10 of these, and I sold eight of these, and I sold 15 of these, and I sold nine of these, and I sold 30 of these. So this is over here doing a simple calculation where it's basically looking at this column times this column.

If you're not familiar with Excel, the way you're going to build that is you're just going to say equals, and you're going to click on this cell, this times this, and hit enter, and it will build this calculation right into here. And the great thing is then if I click on this and I say 15 instead, you see that that automatically changes. So then I built a sum at the bottom of this, which is just equals the sum, parentheses, I select all the columns and I try to sum up in parentheses, enter, and now I have the total amount of what this particular journal entry's cost of goods sold will be.

9:46

I can then take this over to QuickBooks, and I can enter that in to QuickBooks in journal entry form. So this is an example of what that would look like. Let's see what my amount was. So I had $240.33 that needs to be moved over from my inventory asset. $240.33, it needs to be taken out of inventory and it needs to be moved into cost of goods sold. This is what that journal entry would look like.

I'm going to show you how you get into this journal entry. So I'm just going to come up here. This is what QuickBooks Online looks like. I'm going to click on this gear icon, I'm going to click journal entry, and it's going to bring up this screen. So all of here I have the accounts that I would select. In general, this particular journal entry is going to move from inventory asset to cost of goods sold.

Here's the journal entry that I just created, so let's go back to look at that one. So the credit to the asset accounts removes it out of the inventory asset. The debit, so it needs to be on this side of it, moves it into the expense account of cost of goods sold. So this particular journal entry, with the inventory asset being credited and the cost of goods sold account being debited, removes it from the inventory asset and records it through cost of goods sold on my profit and loss statement.

10:57 Automating it with A2X

I have mentioned in other videos that we're a big fan of a tool called A2X, and part of the reason why we really like A2X is because it takes this process I just showed you and it basically automates it and makes it very quick and easy.

Let me show you what that looks like. If I'm inside A2X, I have the ability to set up costs. I'm going to show you how this works with our inventory sandbox. I have the ability to go in, and if I have cost of goods sold set up inside A2X, this is what it does inside this cost tab here. I can import these all from a spreadsheet, or I can enter them all by hand if I choose to. But basically inside the spreadsheet inside A2X, what I'm doing is I'm identifying all of my SKUs, whatever they're called on whatever channel I'm selling on.

So let's say that this is an Amazon channel. After I sync this to whatever channel I'm selling on, A2X will actually pull in all of the SKUs that are currently being sold on that channel. It'll pull in a SKU, it'll pull in whatever name you have associated with it, it'll pull in all this other additional information, including incidentally what you currently have in stock, what's been sold, et cetera. And then your job is to basically go in here and just enter costs. Say this one cost me this much, this one cost me this much, this one cost me this much. You can also do this in spreadsheet form and upload it if you want to, but you can also just, depending on how many SKUs you have, you can just enter it line item by line item.

12:16

This then becomes A2X's version of your product cost catalog, so that it becomes this to A2X, where they're saying, here's your SKU, here's what the cost is.

And then at the end of each period when you're ready to make your cost of goods sold adjustment, it makes it very easy for you. This is what this looks like if I had already set up all of my costs inside here and I turned on the cost of goods sold inside A2X. You see I have this additional tab now that says cost. So when I want to go and I want to push this over, I'm going to say review, and underneath the area that we're already looking at sales data, we have this option down here of pushing over cost of goods sold data now as well.

So this is so, so, so useful. Basically what A2X does is it goes in and it says, hey, these are all the SKUs that you sold, this is how much you sold of every SKU. And then it says, this is what our unit cost is, and it extrapolates it over and calculates it. But then here in the bottom, when I'm ready to push over, it says send to QuickBooks. It tells me what my total cost is that's going to be sent over to QuickBooks. It will actually make this journal entry for you inside QuickBooks, exactly the journal entry that I just showed you, without you having to do any of that except inputting your cost to begin with and then pushing this blue button. It will do exactly the same thing I just showed you.

13:34

So what you're skipping is this part of having to calculate it all out, and you're skipping the part of having to make the journal entry yourself. All you're having to do, if you're using A2X to do this process, is just make sure you have an accurate cost that you're putting in. You can actually update your costs every single time you go to make this push. You can review your costs and decide whether or not you need to upload new fresh costs. If you do need to upload new fresh costs, then go ahead and upload it and then push over the entry and it'll do everything else for you.

Thank you for watching our video. LedgerGurus is a full service accounting solution for your ecommerce businesses. If you would like to just outsource this to us instead, feel free to go to our website and reach out to us through the call now or the contact button. We also have lots of additional resources here on our website as well, including downloads. We have some courses available. Go to our website for additional information, and please subscribe to our channel.