Need to know how much your handmade products really cost you to make? Pull up your DIY workboots and jump on in! 🙂

If you need a refresher on the basics of Google Sheets (which I use now) or in Excel (which I used to use) please check out my brief tutorial post.

This does not currently cover how to track on-hand inventory, overhead costs, labor costs, or taxes. As I write those posts I will come back and update this post.

Raw Materials and Consumables

The very first step of the process is figuring out the cost of materials and how to allocate them to each piece. You need to include the material’s sales tax and shipping in these totals to get a true picture of the costs.

Some items cannot easily be allocated and will need to be captured as a Consumable instead (a part of the overall cost of doing business). For simplicity, I am not including overhead costs in this tutorial.

I will be using the Flat Cat Magnets for these examples, so the raw materials needed are:

• Plaster of Paris
• Water (I don’t track this cost)
• Wood Glue
• Gesso
• Acrylic Paint
• Sealer
• Strip magnets

The Materials tab captures information about each item as follows (green fields have formulas or functions in them):

• A – Type – this is the generic type of material. I use this so it’s easier to filter things. 
• B – Description – this is the field the lookups will use to find things.
  • If you change a description, you will need to manually update it on the other tabs!
• C – Cost – this should include the list price, taxes, and shipping costs. If this is true raw material, the number will be entered manually.
  • For sub-Items (items that are part of other items) this is a vlookup, see the section below
• D – UM – this is the Unit of Measure which describes how I will use the material (by inches, each, mL, pages, etc.).
• E – Units – this is the number of units in the package. You can use an exact measurement (inches, oz) or put in a best estimate for things like Paint or Sealer.
• F – 10% – this is a flag for if we want to remove 10% to cover loss due to accidents (bad pours), leftover scraps from cutting shapes, and other issues.
• G – Per – this is a calculated field that calculates the Per (number of usable units) based on the  10% loss flag.
  • =IF(F2=”Y”,(E2*0.9),E2)
• H – Notes – this is notes about the package
• I – Cost Per – this is a calculated field that divides the Cost by the Per. Lookups will use this value.
  • =C2/G2
• J – Bought From – this is a note to myself on where I bought the item
• K – Alternatives – is other places I could buy it and the prices there. Basically just another notes column.

Work-in-Progress Steps

Most of the items I make take more than one step to get from the raw materials to the finished product and some of the items share steps. I wanted to make a spreadsheet that was very modular to match this.

So I made the following layout template on a new Items tab. When I want to make a new item (or sub-Item) I copy and paste these rows at the bottom of the spreadsheet.

NOTE: Purple fields are pulling data from another tab and green fields only use values from the current tab. I manually colored these, it’s not a default feature of either program.

• A – Name – the name of the item
• B – Material – this will be an value from Description column (B) of the Materials tab
• C – Notes – more notes to myself
• D – Per – this is how many Units of the Material I am using
• E – Cost Per – purple fields are those with a vlookup in them. In this case we are looking on the Materials tab to find the Cost Per (column I) for the material. If the material is not in the list, it will show ‘Missing’. If there is no material listed it will show a blank cell.
  • =IF(ISBLANK(B255),””,IF(ISNA(vlookup(B255,Materials!B:I,8,FALSE)),”Missing”,VLOOKUP(B255,Materials!B:I,8,FALSE)))
• F – Calc Cost – this is the Per * Cost Per to get the total cost for this material. If the material is missing, just display a blank.
  • =IF(E255=”Missing”,””,IF(ISBLANK(D255),””,E255*D255))
• G – Total Cost – this will round up the Calc Cost to the nearest penny and will add it to the Total Cost in the line above.
  • =ROUNDUP(F2,2)  – for the first material
  • =G2+ROUNDUP(F3,2) – for all following materials
• H – Overall Total – this is just the last value from the running total copied up to make things look pretty when I filter.
  • =G11

Some of the steps here are purely cosmetic. Changing the #N/A and #REF errors to blanks and ‘Missing’ dosen’t do anything but look pretty.

If this is going to be a sub-Item and used by other Items, then I need to copy the information back to the Materials tab.

The main difference here is that the Cost cell is going to be a vlookup back to the value in the Overall Total cell from the Items tab. (The blue color is just so it’s easier to tell that it’s a sub-Item.)

Because this is a ‘sometimes’ field I just manually cut and paste the formula into the cell instead of having it preset.

=VLOOKUP(B29,Items!A:H,8,false)

The vlookup is going to try and find a match for the Description (B29) in the Name column (A) of the Items tab. If it finds it, then it pulls the value of out Overall Total (column H, position 8 in the range).

I didn’t pretty this one up (hence the lack of IFs), so it will return a #N/A error if there is no match. If you would prefer to use the ‘Missing’ again, you would want to use this:

=IF(ISNA(VLOOKUP(B29,Items!A:H,8,FALSE)),"Missing",VLOOKUP(B29,Items!A:H,8,FALSE))

Finished Items

A finished Item will be a collection of sub-Items and/or Materials and it uses the same template to calculate the total. In the example below you can see each of the steps towards the final product (except the boxes).

I’m actually in the middle of revalidating my actual costs and usages for these magnets, which is why shipping is missing from the 3-pack list of materials. But once I have the new numbers, all I have to do is fix them once and they’ll update everywhere! 🙂

Shipping Costs

Etsy would really really like people to offer free shipping, but the only way to do that without going broke is to combine the shipping costs into the list price.

Thankfully the spreadsheet lets us easily setup shipping costs as just another Material! I don’t include gas or time spent at the post office since I count those as overhead costs.

The Shipping Box is is an Item since it has components (paper, toner, tape, packing materials)

Right now I’m only using the 5x5x5 boxes, but if I add different sizes in the future they’d be entered as new Items. (And I’d have to update the Materials list if I want to change which box I’m using for an finished item).

Listing and Selling Fees

Now that we have a good idea of how much it costs in materials and shipping… it’s time to calculate how much we’ll be paying in fees. I sell primarily online so I lose a large chunk of my profits (8-10%) to this. *sighs*

First we need to setup a new tab (Settings) with a section showing the various fees associated with each method. We’ll use this table for our Sales tab vlookups.

Not all of the methods will have values in all columns, since they only cover part of the process. You could have separate tables for the selling and payments, I just lumped them together for simplicity’s sake.

If you have listing methods that charge a percentage of the list price instead of a flat fee, you could add in another column for that. (You’d need to make some other changes as well, so let me know if you want to see that! 🙂 )

• A – Channel – description
• B – Listing Fees – flat dollar amount
• C – Selling Fees $ – flat dollar amount
• D – Selling Fees % – percentage of selling price
• E – Payment Fees $ – flat dollar amount
• F – Payment Fees % – percentage of payment amount

Now that we’ve got the data setup, it’s time to build the Sales tab.

This is where I play around with various list prices to see how much profit I will be making from an item! I can easily figure out how low I can go before I start losing money.

NOTE: This does not include the cost of labor or any taxes!

• A – this is just so I can see how many products I have figured out prices for
• B – Name – name of the Item from the Items tab
• C – Total Cost – this pulls the Overall Total of the item from the Items tab
  • =if(isblank(B14),””,vlookup(B14,Items!A:H,8,false))
• D – List Price – this is the price I choose to list it at
• E – Channel – this is the sales channel (from the Settings tab)
• F – Listing Fees – this is the cost to list the item for sale
  • =if(isblank(E14),””,vlookup(E14,Settings!A:F,2,false))
• G – Selling Fees $ – this is the flat fee price charged when an item sells
  • =if(isblank(E14),””,vlookup(E14,Settings!A:F,3,false))
• H – Selling Fees % – this is the percentage of the amount the item sold for charged when an item sells
  • =if(isblank(E14),””,vlookup(E14,Settings!A:F,4,false))
• I – Fee % – this is the amount of money equal to that percentage
  • =if(isblank(B14),””,(H14*D14))
• J – Method – this is the method of payment (from the Settings tab)
• K – Payment Fees $ – this is the flat fee charged for taking a payment
  • =if(isblank(J14),””,vlookup(J14,Settings!A:F,5,false))
• L – Payment Fees % – this is the percentage of the payment amount charged for taking a payment
  • =if(isblank(J14),””,vlookup(J14,Settings!A:F,6,false))
• M – Fee % – this is the amount of money equal to that percentage
  • =if(isblank(B14),””,(L14*D14))
• N – Total Costs – this is the sum of all the costs
  • =C14+F14+G14+I14+K14+M14
• O – Profit – this is the List Price minus the Total Costs
  • =D14-N14

I didn’t do as much cleanup of the errors on this tab either. I’ll come back and clean up the #N/As and #REFs when I do my next update…

I am currently working on labor, taxes, and overhead spreadsheets so once I have those complete I will come back and update this post.

Any Questions?

Anything that you’d like more explanation (or screenshots) for?

Want to see the spreadsheet tweaked for something specific?

Just let me know in the comments and I’ll get you an answer! 🙂