How to Create a Product Cost Formula | A Step-by-Step Guide

Now before I get started, don't forget to like this video and to hit the subscribe button if you're interested in more tips and tricks on accounting for your ecommerce business.

0:37 Three reasons product costs matter

Before we really dive into the process, I just want to give you three reasons why I think this is really important.

The first one is visibility. If you don't have really good visibility into what your product costs are, not only does it make calculating cost of goods sold at the end of the month much harder, but it actually also impacts your confidence when it's time to price your product.

The second is convenience. So this cost of goods sold process is one of the most complicated parts of accounting at the end of the month. And if you're trying to gather all this data from all over the place, or you don't have it in one easy spot, it actually makes this process a lot harder.

And the third reason is foundation. So whether you're doing this in spreadsheets or whether you're preparing your data to actually implement an inventory tool, which we can help with if you get to that point and need help with it, all of it begins with having accurate product costs.

1:18 Decisions to make before you build

One of the things you need to be considering when building your product cost formulas is which costs you'll be including into inventory costs, meaning that those costs will go onto your balance sheet and move through your financials through cost of goods sold, or which costs will be expensed right away.

For example, will you be including just product costs in your inventory, which we refer to as buy costs, or will you also be including things like shipping costs, packaging, tariffs, and fees as well, which we refer to as the landed costs? And last, are you going to be manufacturing the product, or will you be buying a finished good from somebody else? In this particular video, the example that we're going to go into assumes an outsourced manufacturing situation where you are buying a finished product from a vendor that is supplying it for you.

1:58 The sample invoice and the spreadsheet

In order to show you what this process looks like, we're going to use this sample invoice that should really be pretty familiar to you. It should look a lot like the documentation that you get from your vendors, and it includes a column that just tells you what the SKU is, gives you a price per SKU, usually the quantity of what you've ordered, and then the price times the quantity usually equals the total [unclear] for that particular product. And then down below you may have different additional things like sales tax or shipping or things like that.

These rows over here to the right that are in orange, these are calculating columns that I've added to show you how this works. We'll get back to that in just a second. But what all of this is feeding into is this tab called product cost catalog. This whole spreadsheet, incidentally, is available as a download off of our website, and includes not only this but also some other tabs as well that help with COGS calculation things. But the two we're going to be focusing on in this video is this invoice sample and the product cost catalog.

2:50 Building the catalog and allocating shipping

So this is where the product cost catalog is actually built. We have a column that shows our actual product SKU. We start with our buy cost, and then these are all the additional costs that you may or may not choose to add to your SKU, like I mentioned before, giving you a total landed cost.

So how does this work? So where these buy costs come from is directly off of this invoice. So you notice all of these SKUs here, these match all of the SKUs that are sitting right here. So this buy cost is just a per unit cost of... That's exactly what it is. You just put in here the per unit cost of what you actually bought.

3:21

Now in this case, I have made a decision to decide to allocate shipping as well. So let's talk a little bit about what that looks like.

First of all, I want to make it clear you're not marrying an approach on this right now. If you choose in the future to change the way you're doing this, you can always adjust any time. So if you want to start with a simpler approach and then build upon it as you gain a higher level of mastery, feel free to start there.

Another thing to consider is how significant and material those additional costs might be. So in this case you can see that the total amount of my product cost was about $1,400 and the total amount of my shipping was about $278, which represents about like a just less than a 25% increase for product costs. I mean, that's pretty significant. So in this case, I have decided to go ahead and add my product cost as well.

So the way that this works is you have to come up with a method you want to use for allocation of additional costs. Some common ones are you can either allocate by quantity or you can allocate by total price. Some additional options that you have, especially if you're already using like an inventory management tool, is you can allocate by weight, you can allocate by size, you can allocate by like they actually usually have quite a few options available for additional landed cost allocations.

4:30

In this case, I'm actually going to allocate by total price. So what I'm going to do here is I'm going to do a calculation that it's like a weighted average where I'm going to say, what percentage of this total price does each of these line items actually represent?

So the formula that I'm going to use, [unclear], it basically says this divided by this gives me a percentage. So these are all percentage lines as I go down. I say this divided by this, this divided by this, this divided by this, and this shows me what percentage of this total dollar amount here is represented by each of these line items.

So then I'm going to go back and I'm going to say, okay, now take this percentage and multiply it out by total shipping cost to give me, basically this one represents 47.9% of 278, and I'm going to do that all the way down as well. Let me show you what this formula looks like. So I'm saying E3, which is this line here, times whatever my total shipping cost is. So I'm saying give me 47% of the total shipping cost, which gives me this here.

5:31 Per unit costs, packaging, and later invoices

Now one thing to keep in mind is that we're always looking for a per unit cost on this. So this represents like how much of the total shipping, but this is not a per unit one. This actually represents the shipping cost. They're going to be spread across eight units.

So this last row here, I'm basically taking this total amount and I'm dividing it by the total units that [unclear] to give me a per unit additional shipping cost. So if you look at these numbers here and we go back to our product cost catalog, you notice they exactly match these numbers here.

6:04

So here's what I have built so far. I have my buy costs, I have my per unit shipping costs that I'm adding to that as well. And then in this case, I know that I have like packaging costs and I'm not necessarily trying to like track packaging costs exactly the same way, but in this case I know that it costs me about $1.56 for each unit for additional packaging costs. So I'm actually going to add those costs as well.

Now, one of the things I want to point out when you're adding additional costs is that sometimes those additional costs are right here on the original invoice, but sometimes they're not. Sometimes you're receiving invoices after the fact that have to do with this particular shipment. So whether it's costs that are directly on this invoice or whether it's costs that come afterwards related to this shipment, those are all costs you need to be considering, whether you want to add as landed costs or not, or whether you want to just stick with your original buy cost.

So when you calculate all this out, now that you have all of this established and you have a per unit landed cost, this is what you'll then use to calculate at the end of each month in order to capture correct cost of goods sold.

7:07 Coding these costs to inventory

One thing I want to point out, if you haven't necessarily already thought this part through, is the way that this is going to be handled from an accounting perspective. So all costs that you've decided to include in product costs need to actually be coded to inventory at the time that you see them pass through like your bank accounts and credit cards, because you don't want to like expense them instead and then include them in your product costs, because then it'll throw off your cost of goods sold in your inventory.

So keep this in mind when you're actually coding transactions that you see show up on your bank account and credit card feed, is that if you're going to include them in product costs, they need to go to the balance sheet, to the inventory account. And if you're going to bypass them and you're not going to include them to product costs, then you can go ahead and expense them right away.

Thank you so much for watching. Please remember to hit that like and subscribe button and comment if you have any questions. Also, don't forget that this particular spreadsheet that I use to show this process is available as a download off of our website. And if you're interested in working with LedgerGurus and having us do your ecommerce accounting for you, then reach out through our website. I'll see you in the next video.