Lesson 5 – Updating the Master Material List

Transcript:

00:00:00:00 – 00:00:33:12
Unknown
Welcome to module three where we’ll get started using your Etsy Business Intelligence workbook. In this first lesson, we’re going to go over your Master Materials list. We’ll talk about what goes into that master material list. We’ll talk about how to enter material into that list. How to calculate the per unit costs and why small items matter. This is going to be a project based illustration of how to use the software.

00:00:33:15 – 00:00:48:05
Unknown
So in order to do that, I’ve selected this really cute wooden Daisy welcome sign as our fictitious project. Let’s get started with the workbook.

00:00:48:08 – 00:01:31:02
Unknown
After opening your Etsy Business Intelligence workbook, navigate to the Master Materials list. You can see that named tab at the bottom of your screen. Once here, you’ll see that each column has a designated heading. Column A says material ID. Column B says material name. Column C is shape, D is size, thickness, material type, and so on. Consider this tab really your materials pantry like your pantry in your kitchen where all of your supplies are.

00:01:31:05 – 00:01:54:24
Unknown
And when you pull out your recipe, you just go to your pantry and you take out what you need. But before you can do any of that, you need to really build your pantry. So when we look at the picture of the daisy, welcome sign, we know a couple of things. We know that it’s made out of wood.

00:01:54:27 – 00:02:23:28
Unknown
So I already have a sample item here which says the material is wood. The material name is base wood sheets. The shape, the size, the thickness. And also this cost. Now, the cost is interesting because I don’t buy them one at a time. So let’s take a quick peek at my Amazon account so that you can see how we figure out the cost.

00:02:24:00 – 00:02:56:20
Unknown
Just going to pop into this listing. And you can see 24 pack of basswood sheets 12 by 18 is $60. Right. So you can do this a couple of different ways. You can do this with a calculator or you can actually do this in the cell. I know if you look at your formulas cell here, you’ll see that I’ve already done some of the math.

00:02:56:22 – 00:03:33:29
Unknown
So this particular base wood when I put this in it was 2984 divided by 12. Now what I just showed you was essentially $60 divided by 24. So I’m just going to click over that formula. I know it’s scary. And I’m going to start with the equals sign. And I’m just typing the equals sign on my keyboard. And I’m going to start with the amount that I paid, which was $60.

00:03:34:01 – 00:04:05:02
Unknown
It’s 59 and change. But I’m just going to type $60. Now in order to divide that by 24, which is how many I got for $60. I need to use the fact slash. And that’s the backslash that is either below the question mark on your keyboard or above the eight. If you have a numeric keypad. And I got 24 of them for $60.

00:04:05:04 – 00:04:36:05
Unknown
So I’m going to say start this. The cell with the equals symbol, which tells Excel that I will be typing in a formula. And then the formula that I’m going to type is 60 divided by 24. And that’s the formula that you see right there. And I’m going to choose enter. And now what I have is my cost is $2.50.

00:04:36:08 – 00:05:12:20
Unknown
The alternative is I could just type in $2.50. Now the difference is here in my formula bar and my formula bar I only see the number now. I don’t know how I got that number. So for me personally, I put in the formula that shows how I arrived at my cost. And that’s important when you’re purchasing things and you’re only using one at a time, but you’re spending more money because you’re buying more at one time.

00:05:12:22 – 00:05:36:27
Unknown
And then for here, I may have put ten for the quantity on hand, because by the time I had entered this into my, master materials list, I may have already used 14 or 15 of the sheets. Notes. Is just anything you’d like to type in. About the product. Doesn’t matter to me where I have this here.

00:05:36:27 – 00:05:56:12
Unknown
I’m just going to leave this here. I always put in my vendor, and then I always put in my purchase link. And here’s why. Because I want to know. I want to get this same price, and I want to get this same product. And here’s how you get that link. I’m going to go back to my, Amazon account.

00:05:56:19 – 00:06:24:24
Unknown
I’m going to go to the actual item. So now I have the item up here and I’m going to click here in the you are al area where the actual hyperlink is located. And I can right click my mouse and choose copy. And then go back to my spreadsheet. And I can right click my mouse and choose paste.

00:06:24:29 – 00:06:49:24
Unknown
Let’s take another look at the Daisy welcome sign and just look and see if you can figure out what else needs to be on this materials list.

00:06:49:26 – 00:07:16:10
Unknown
Welcome back. Did you guess? Lots of stuff. Glue. Paint. And you can see that I’ve updated my master materials list. Let’s take a look at what I’ve added to the list. I’ve added wood circles. So clearly, I’m going to be cutting the welcome and daisies on the square wood. And then I’ll be gluing that to the round wood.

00:07:16:13 – 00:07:50:15
Unknown
So I added glue. Glue is an interesting thing to add to a pricey because it’s kind of hard to measure. You buy a container of glue, you use glue. So what I’ve done is I’ve added an on hand quantity of 100. And I’ve costed my glue at $0.25 per used unit. And that’s important because when we take our pantry items and we put them into our recipe, we’re going to decide how much glue we used.

00:07:50:22 – 00:08:21:29
Unknown
And that’ll be how many units at $0.25. Spray paint is another interesting, item to to quantify as far as units are concerned. Spray paint costs about $7 per container, and some designs may use more or less. But I know that I’m going to need four different containers because I need four different colors. So I need to be able to quantify the unit cost.

00:08:21:29 – 00:08:52:09
Unknown
And then when I build my recipe, I can decide the quantity that I’ve used. And picture hangers. Let’s take a look at picture hangers. Here are the picture hangers that I’ve added to my materials list. And I’m bringing this up because I want to really bring forward how important it is to be focused, and to try and be as accurate as possible in your costing on your materials.

00:08:52:12 – 00:09:21:18
Unknown
In this case, I’ve purchased 415 pieces for 1295, and I have a couple of choices that I can make here, because I can clearly price out the pieces by dividing 1295 by 415. But when I look at the pieces, I can also see that a picture hanger is one piece, a screw is another piece. Some of them use one screw.

00:09:21:18 – 00:09:45:03
Unknown
Some use three screws. Some of my projects may use more than one picture hanger. What to do, what to do? I’m bringing this forward because I just want to help you do this as easily as possible. And what we’re going to do for picture hangers is in the unit cost area. You can see I’ve used a formula so it’s figured it out for me.

00:09:45:05 – 00:09:56:15
Unknown
I’ve typed in equal sign 1295 divided by 415.

00:09:56:18 – 00:10:26:07
Unknown
And now we’re done. That was your master’s materials. And what we covered was what gets entered, how to enter it, how to calculate unit costs and why those small things matter. And coming up next we’re going to take a look at putting those pantry items into a recipe by updating your product template.