Calculating Ecommerce inventory and COGS using product cost catalogs

0:53 Quantity and value, the two building blocks

So the first thing I want to talk about with a product cost catalog is what you do with it and the purpose of gathering it. So one of the things I talk about in the videos is I talked about how in order to track COGS and inventory correctly, there's really two things you have to be aware of. You have to be aware of the quantity of all of the products that you have, specifically by SKU, and you have to be aware of the value of each of those inventory items by SKU as well. And if you have the quantity and you have the value, you can multiply those two things together to give you any level of inventory.

So for example, I can look at beginning inventory. I can say, what is the quantity that I have in my beginning inventory, what is the value that I've assigned to both of those, multiply those two things across, you get the value of your beginning inventory. Same thing with cost of goods sold. If I can look at the quantity of what I sold by SKU and I know the value that I've assigned to each of those SKUs, all you do is multiply those two things across and that gives me my cost of goods sold.

1:51

So if you're tracking these two things, the quantity and the value, you'll be able to successfully value your inventory and your cost of goods sold at any point along the game. We have another video where we talk about what we call the product master, which is where we track the quantity of inventory. This video specifically is about the product cost catalog, which is where we track the value. So quantity and value, being these two fundamental building blocks, then become everything upon which everything else is built upon.

2:24 Turning a vendor bill into a product cost catalog

So let's look at what this looks like in practical applications. So let's say I get a five thousand dollar bill from a vendor. In that bill I'm buying bicycles, okay. So if I wasn't tracking a product cost catalog, I would probably just pay the vendor, pay the bill, not really think about it again. But because I'm now encouraging you guys to level up your operations and to mature as an organization by becoming very diligent about tracking your inventory, I'm inviting you instead to use this bill to build a product cost catalog. So what would that look like?

So when we work with our clients and we're helping them with their product cost catalogs, the number one input that we require from them is bills and POs around every single item that they purchase. And the reason why is because this is the documentation that gives us the information that we need to be able to build a product cost catalog.

3:15

So if you look here, we have these three items. We have a yellow road bike, a pink cruiser, we have a red trike. We can see here the exact quantity on all of those and we can see here the exact price of all of those. So when we take that detail, we then use that information to build this product cost catalog. So really what I'm ultimately after is I want to be able to say, what are the SKUs that I have, not necessarily what is the quantity of each of those SKUs, but more specifically what is the value of each of those SKUs that I currently have on hand. So then what do I do with that information? Like I said, I use the value to multiply out times a quantity at whatever level I have, and I can then value the inventory and the cost of goods sold.

4:01 The simplest approach and landed costs

So there's a number of different ways of going about this, and I want to kind of start with the simplest approach to it and then I want to build upon it. One of the things we really try to do when we create content, or at least I try to do when I create content, is I want to give you guys a roadmap of like, here's an option, here's how you can improve upon it, here's how you can mature along the way. Everyone has to start at some point, and especially with inventory tracking it can be very complex and it can be very overwhelming. So let's talk about basic building blocks of how this looks.

So probably the simplest way of going about this is to actually just in Excel identify each of your individual products and develop a cost around it. So if I were to go back to the bill and I were to say, you know, what did I pay for this item, that would become my product cost for that. Now this option works really well if you have stable product costs. So we're assuming in this situation that we're buying our product from some sort of vendor, that I mean we've outsourced our manufacturing and that they're selling to us a finished product with a set price tag on it, and that that remains fairly stable. Even if you're outsourcing this from overseas, usually you have some sort of relationship with that manufacturer where they're able to give you a pretty stable fixed cost.

5:21

At this point, the only thing that's really going to impact a lot of volatility around your product costs would be landed costs versus the buy cost. So the buy costs in this situation are what I paid for the product, and the landed cost is how much I paid to get it shipped here, how much I paid in tariffs, how much I paid in these other things. So let me go back here just a little bit to this bill. So when I look at this bill, you can see here this particular bill doesn't include any shipping costs, but oftentimes it does. So if I had shipping costs that had been included on this bill, then I would take that shipping cost and I may want to allocate it across the products.

So this takes us to the problem, though, the fact that you may have more volatility in your product costs because of landed costs than this situation allows for. But this is by far the easiest, most approachable way of getting into this process, is just creating a spreadsheet where you identify each of your individual SKUs and you assign it a product cost based on the highest degree of your knowledge and information.

Now if you're manufacturing your own products, this process becomes a lot trickier. We have a whole video on how to develop your product costs if you're actually manufacturing. This video was assuming a simpler approach of looking at POs and bills to just give us a product cost.

6:47 Updating costs and why we use Excel

So let's assume that we have product costs that are not as stable. So one of the things that we do here at LedgerGurus, like I said, we track product cost catalogs for our clients. So in this particular situation, you see across the board the way we go about doing this is every single time we get a new invoice in, we actually update the product cost. So you see in the second column we see the invoice number, third column we see the product SKU, then we see the quantity, then we see the cost that was assigned to that. Then if we move over another couple line items, we see our landed costs, and then when we add all of that together and calculate it all together, it gives us our average costs.

This spreadsheet is one that I pulled out from one of our actual databases, and I changed all the product names and I changed some of the numbers. So don't look at this and try to mentally calculate it all the way across, because I didn't necessarily adhere to that. But you can see basically what we're doing is every single time a new product comes in the door, every time we buy something, every time our clients buy something, we are updating their product costs based on that new purchase, the landed cost that came in with it, what they actually paid for each of those individual line items. And then we're using that data to then plug in here. So this is a multi-page spreadsheet where this page is where we calculate the new average cost by SKU number, including things like landed cost, and then it updates this other spreadsheet where we now have a new product cost.

8:17

Now I'm talking about this situation only briefly, because if you are already like an Excel ninja master, just know that you can do a lot of things inside Excel to build a product cost catalog. Now here at LedgerGurus we are serious nerds, like we're accountants, so that's like a given that we're pretty nerdy and we're pretty good at Excel. So this scenario where you are trying to track fairly complex product cost situations is only really a great viable option if you're already really competent in Excel. Now, teaching you how to be Excel ninjas to the degree that you would need to know how to run this situation is a little beyond the scope of this particular video.

So what I'm going to tell you instead is that if you are in a situation where you don't have really stable product costs and you need to be able to update them on a regular basis according to the orders, you probably need to seriously consider an inventory tool.

9:12

And this is a big reason, like a whole reason why people have things like inventory tools, is because one of the things an inventory tool does really well is it tracks a product cost catalog. Now, one of the reasons why we do this ourselves manually inside Excel is because frankly inventory tools can be very pricey, and we can literally save our clients hundreds and hundreds and hundreds of dollars per month by maintaining a product cost catalog for them outside of an inventory tool. Like, we've developed spreadsheets and stuff where the systems and processes are very automated and we're very fast at it. And we like Excel anyways because we can control [unclear] control freaks too. So accountants are like nerdy and control freaks, so like controlling the data inside Excel makes it so that we have total eyeballs on everything at all times. One of the things we don't like about inventory tools is that data kind of gets lost in there and it becomes [unclear] as it moves through the process. So that's another reason why we like Excel.

10:13 Inventory tools and when value tracking is enough

So one of the things I want to talk about in regards to this is that when someone is selecting an inventory tool, there are several things that they would be considering. They would be considering whether or not they need it to value track, they would be considering whether or not they needed to do fulfillment management, whether or not they needed to do warehouse management, whether they need it to be an inventory master. And what I mean by that is the inventory master is the thing that communicates to each individual channel quantity that's available. So if I'm selling on Etsy and I'm selling on Shopify and I'm selling on Amazon, an inventory master will maintain in real time [unclear] I have available across multiple channels. And I also might be manufacturing, and so I'm using an inventory tool to manage the complexities around that manufacturing process.

11:02

Now the thing is that most of our clients are actually only really needing the value tracking aspect of an inventory tool. So if this is all you need, if you are like selling on one channel for example, or if you're selling on two channels but everything that sells on Amazon is in your FBA warehouse and the only thing that you really need to keep track of quantity available for is your Shopify channel that's pulling out of your 3PL, you don't really need an inventory master. So if you have outsourced your manufacturing, you've outsourced your fulfillment, you outsource your warehousing, that means all you really need an inventory tool to do is value tracking and possibly being an inventory master. So if that's the case, if you're in that situation, frankly most of the time an inventory tool is just overkill when it comes to just value tracking.

So you have a couple of options. First of all, there are some very inexpensive inventory tools that are just good at the value tracking side of things.

12:05

This video, I'm not necessarily going to go into like a list of inventory tools and the pros and the cons and which one would I pick and under what circumstances. I hesitate to even throw out names. What I will do is I will commit to actually doing a video specifically about inventory tools, what we recommend, why, etc. But just know that if all you need is value tracking, just QuickBooks Online alone allows for some value tracking within its inventory functionality. So does Xero. In both cases, the only thing they're really good at is the value tracking. They don't really support effectively the whole operational side of inventory, which is a really important thing. But if all you need is value tracking, I would actually encourage you to try to find a way to make it work within like building a simple product cost catalog and updating it according to like your knowledge when you get in new orders and stuff like that. Just getting approximately fairly close on product costs, it can be a really good situation if that's all you need an inventory tool for is just value tracking.

13:10 Start simple and stay thorough

So one thing you need to know about inventory is that it is an operational issue first and foremost, and then it becomes a financial issue after the fact. So sometimes people implement inventory tools because they need it to help them become more savvy operationally. They need a system that they're tracking POs in, they need to be able to make sure that they're receiving their inventory in, they want to make sure that they have eyeballs on inventory, sometimes it's in multiple locations. Your inventory is such an important asset to your business that if you need the operational support of tracking your inventory, I highly recommend an inventory tool. But if all you need an inventory tool for is for value tracking alone, I highly encourage you to try to find a way to simplify so that you can basically have just a product cost and run from there.

14:06

Remember, you don't have to be perfect. One of the things that we see people struggle with with product cost catalogs is that they become kind of perfectionist about it and then they lose data integrity around it, because the level of information they're trying to track becomes so complex that they can't maintain it. So especially if you're just starting, this is a great place to start, which is just an approximation of your product costs. I encourage you to watch the video where we talk about building product costs so that you're not missing costs that are part of that, because costs include more than just like how much you bought it for. Like I said, there's landed costs, there's warehousing costs, there's tariffs, there's packaging, there's all kinds of costs that are going to go around product cost. So you need to make sure you're thorough about this approach.

15:03

And if this is not adequate, I recommend either hiring us, because we can maintain a product cost catalog for you for significantly less than you would pay for an inventory tool, or consider implementing an inventory tool if this is a major problem for you. So just to give you guys some idea of some of the resources we have available, on our website we have a blog that talks all about inventory, all about cost of goods sold, all the things about this. We have a YouTube channel also, don't forget to subscribe, that will also talk about all these things. And also if you have a desire to like give us a call, talk to us about what it's like working with us, on our website we just have a call now button. You may end up talking directly to me, you may end up talking directly to some of the other members of our sales team, but I am still on our sales team, so therefore you might end up talking directly to me. Okay, I have been known to let people just pick my brain because I can't help myself.

So tracking product costs, very important thing. Understanding the value of each of your SKUs is one of the primary building blocks of being able to track cost of goods sold and inventory correctly and successfully. So good luck, and I hope that was helpful.