This is last post in my 4 part series about how I do my inventory lists. This last post is about tallying what I sold after a fair or show.

This series is 4 parts:
Part 1 - To Do List
Part 2 - Master Price List
Part 3 - Packing for a Fair and Prep
Part 4 - Tallying after a Fair

This post uses the sample file that you can use for your own studio. Reminder, I recommend downloading and using Excel vs Google Sheets as some of the formulas may not work, or work differently, in Sheets.

As I'm at a show I keep the printed inventory list and a pen by my chair. As I make a sale I make a tick mark next to an item. I'll also make notes to myself. It might be that I want to change the price of something at the next show. Or it might be that I have an idea for a new item or that I need to make more of a particular item.


I go back to the tab for that fair in my workbook and I add how many I sold of each item in the sold column. This gives me a total sold for each individual item type as well as total sold for the show at the bottom.

The total sold should equal (or be really close) to the total of cash, credit card, and Venmo. I'm usually spot on, but when I have a really busy show I'm not concerned if I'm a few dollars off because I forgot to mark something when I was checking out a line of customers.

This is also when I look back over the last few fairs to see if there are items that aren't selling that I think should be or if something is selling really well. I start thinking about what price adjustments I might want to make up or down for particular items.

If I want to make an adjustment I would do so on the Master Price list. Since I want to keep a record of the actual dollars sold for each show, I don't want price changes to reflect in past shows. What I do once I've filled out the tally is copy all the rows in the table, right click, choose paste special - values. What this does is overwrite the formulas with the text that's in each cell. This means that if I change a price - it won't change the past. This step is optional, but it helps me when I look at the history.




That's it for this series on how I use Excel for my inventory lists. I do hope that it was useful.

This is part 3 in my series about how I do my inventory lists. The first two posts were about the To Do List and the Master Price list. Now we're going to get into the meat of what I use Excel for - packing and prepping for a fair or show.

This series is 4 parts:
Part 1 - To Do List
Part 2 - Master Price List
Part 3 - Packing for a Fair and Prep
Part 4 - Tallying after a Fair

This post uses the sample file that you can use for your own studio. Reminder, I recommend downloading and using Excel vs Google Sheets as some of the formulas may not work, or work differently, in Sheets.

The first worksheet or tab that we are going to look at in this post is Packing Count. This will look really familiar as the first 3 columns are the same as the ones in the Master Price List.


The big difference here is that the only thing that is typed in vs a formula is the Item. This is the magic of Excel. Since this list is also a table, you can paste or type in the items and it will use a formula to look up the Category and the Price. This means that if you change the price on the Master Price List tab - it will change it here (and everywhere else in the workbook). Are you feeling the magic yet? But wait, there's more! Since we're using a table Excel knows to apply the formula to all rows. So if you add a new row at the bottom, or in the middle, Excel will automatically apply the lookup formula to the Category and Price columns. MAGIC!

The new column in this file is count. The way that I use this is that every time when I'm packing for a show I print out this list and put it on my clipboard. As I am packing, I tally how many of each item I'm planning on bringing. I've done enough shows now that I know what to bring. However, you'll see in this post and the next one how you can use the data you collect to start packing better for shows.




These paragraphs are for anyone who cares how the formula works. If Magic is good enough for you then skip this and go below the next picture. I'm using two formulas to do a lookup - Index and Match. A lot of people have heard of vlookup, but since I learned the combo of Index and Match I've never looked back. The problems with vlookup is that it requires the column you're looking up to be in the first column, it requires your list to be sorted, and it uses a lot of memory.

Match looks for a match. It has options to look for things that are close, but since we are doing a text match we want to do an exact match. So what we're telling it to do is look up what is in B2 in the Inventory table in the column called Item and return an exact match (the 0). Match returns a number that is the row of the item that is the match.

Next we're using Index to tell Excel go find what's in the Inventory table in the column called Category where the row equals whatever the Match formula returns.

Really cool right? Also yes I am a giant Excel nerd.




So once I've packed all my stuff and I have my paper that tells me what I've packed now I can create a spreadsheet specific for that fair. The Prep for Fair workshseet can be copied over and over for fairs. Once I know that I'm doing a fair I'll create a tab for it.

When I packed I counted how many of each item I packed. Now I go into the tab for that fair and put in the tally. Since it knows what the price is from the Master Price List, it can calculate the total of what you have for that item.

What happens if you have a brand new item that you've never had before? You're going to first go to the Master Price List worksheet and add the Category, Item, and Price for that item. Then you're going to come back to the worksheet for this fair and add just the Item to the bottom of the table and once again - magic - the Category and Price will automatically be filled in.

Sometimes as I'm packing I remember th at I want to change the price of something. To do that I change the price on the Master Price List worksheet and it will change everywhere.

I have a few items that I sell as sets. I put in .01 for those because technically I don't have any specific items designated for sets, but when I filter I want to see them on my list. Filtering is actually the next step. Click on the arrow next to the count and unselect the checkbox next to 0 and blank. This will only show you the items that you have packed for this show.




At the bottom of the total column you will see the Total for all the inventory you're bringing to a particular show. If everything goes amazingly well and you sell everything in your booth - this is the maximum amount of money you're going to make. I have never actually sold everything, but fingers crossed one day that will happen. I tend to lean towards the thought that I can't sell what I don't have and over pack so it may never happen.




The last thing that I do is sort based on Item. I found that when I was doing shows I couldn't find what I was looking for when it was sorted on Category first. This may or may not be how you're brain works. Sort whatever way is going to make sense to you at the show. Once you have it the way that you want - print. I put the list on my clipboard and as I sell things I keep a running tally. I also make notes to myself like - make more crab mugs. Or if I have an idea of a new thing to make I'll write a note to myself on the bottom of the page.

This is part 2 in my series about how I do my inventory lists. Last week I started the series talking about To Do lists. In this post I am going to continue and start talking about my master price list and how it drives all the other sheets in the Excel file. This series is 4 parts:
Part 1 - To Do List
Part 2 - Master Price List
Part 3 - Packing for a Fair and Prep
Part 4 - Tallying after a Fair

We are going to continue along in the sample file that you can use for your own studio. Reminder, I recommend downloading and using Excel vs Google Sheets as some of the formulas may not work, or work differently, in Sheets. I use Square for my credit card transactions and my sale web site. In Square I have Categories and Items setup and they are repeated in Excel. I use excel in addition to Square because I can visualize the detailed data better in Excel. It's also easier for me to see the history of shows in Excel than in Square.
This blog post focuses on the Master Price List worksheet (or tab) at the bottom of the workbook.


This worksheet is really simple, but it drives the rest of the tabs. The first column is Category. This is just a grouping of items. Your categories can be anything that makes sense for you. They can also change over time as your work changes. I find that things which may start in my Misc category often end up as their own category. I try to not have more than 10 or so categories because for me it gets difficult to manage when it's more than that.

The next column is Item. You'll see that for some of my items I repeat the category. You don't have to do this, but in the next post in this series when we talk about prep for the fair when I sort on Items it will make more sense why I do this. When I first started out, I had a whole lot of individual items. As I've done more and more shows, I've found that I can condense a lot of items which makes it easier to find when I'm at a show.

The last column is price, which should be fairly self-explanatory. It is important to keep the price here and the price in your credit card tool in sync.

When I was first pricing my items, I really had no idea what I was doing. I read a lot of posts on Facebook, but I really didn't know how to price stuff. So I started by putting all my items in Excel and then putting a price that I thought made sense. I know that there are calculators out there for materials + labor, but this isn't my primary job. I'm not trying to get rich making pottery; my goal is to be able to enable my hobby to pay for itself and continue to use it as my way to de-stress. Once I had the prices in for everything I sorted on price and wanted to see if things made sense. I found that I had weird discrepancies. Why would someone pay $30 for a mug and $45 for a bowl? I adjusted the prices so that items that were like sized and like amounts of work were like priced.

Now it's time for me to geek out on Excel for a bit. Excel tables are one of the most powerful tools you can use. This sheet is a table that is named Inventory. You can see the name by clicking anywhere in the table then selecting Table at the top and then the name will be on the left. Tables are powerful because now anywhere in this workbook I can use the name Inventory in a formula, and it knows that I mean this table. You can name your tables anything you want as long as it starts with a letter, has no spaces, and isn't an Excel reserved word (e.g., you wouldn't want to name it something like Sum since sum is a formula to sum numbers).




This is an example of a formula on another worksheet where I am referencing the Inventory table. We'll talk more about this formula in the next post in this series Part 3 - Packing for a Fair and Prep.

Just a quick reminder that tomorrow August 13th is the second Sunday of the month and that means it's time for the Belle Aire Market from 9-2. I am going to try out a way of doing a Seconds Sale this weekend. I have some pieces that just aren't what I envisioned for various reasons. They will be out on a table and I'm asking people to pay what they think they're worth. Minimum price is $1 only because I don't want to do anything with change.

A couple of weeks ago I responded to a post on Facebook where someone was asking how you don't let ADD take over in the studio and get intentional about making. I briefly described my process, but now I'm going to share with you how I use Excel to manage the process.

I love Excel. I don't really know when I learned it, it was just something that always made sense to me. I've ended up teaching basic Excel skills first at college and then at work. There are people who I haven't worked with in years who will text me when they have an Excel problem. I don't feel like I do anything all that fancy, but it is a really helpful tool.

I use Excel a lot when I prep for shows and when I am deciding what I need to make. This is only my second year of selling, but because I kept track of what I sold last year I had a good idea of what I needed to make the most of this year. I'm going to share with you how I use Excel to keep my prices, inventory, and pack for fairs. I absolutely know I could get more anal about this than I am. I could keep a lot of this in Square, but this way makes sense for me so it may make sense for others.

I've broken this down into 4 parts:
Part 1 - To Do List
Part 2 - Master Price List
Part 3 - Packing for a Fair and Prep
Part 4 - Tallying after a Fair

I've also created a sample file that you can use for your own studio. I do recommend downloading and using Excel vs Google Sheets as some of the formulas may not work, or work differently, in Sheets. The images and descriptions for this blog post are in the ToDo worksheet (or tab) at the bottom of the workbook.

Last year was my first year doing shows so I started by collecting data. I kept a tally of what I sold at each show. I also paid attention to what was selling vs what wasn't. I adjusted prices show to show until I found what felt like the sweet spot for a particular item. At the end of the year I summed each item across all the shows I did and that's how I created my initial To Do list for this year.

Last year I tried to keep track of inventory as I made things, bisqued them, and then glazed them but it was just too complicated. This year I've simplified it to just look at how many do I intend to make (the count column) and how many have I made to date. This gives me my "to do" as Excel automatically does the math (yes it's simple subtraction but sometimes I really fail at that). As I have sold things at various shows I will make notes on my clip board about what I need to add to my To Do.

I made probably 6 crab mugs last year and sold them all. I made 12 to start with this year and I have found that they are one of my most popular items. I make those in batches of 6-12 now and just add to the count when I need more.




The priority column is there just to help me focus. I can filter on To Do and unselect the 0 then filter on Priorty = 1. This gives me my first to do list that I can write on my small whiteboard in my studio. When I make an item, I wipe it off the board and then update the Excel on my phone or later at my desk. Once all the priority 1 items are done, I can move onto priority 2, and so on.

As I've been getting more intentional about what I make and my time to make it I have found that by making the various styles that I do in groups that glazing goes faster. I will make a bunch of crab things together so that when it comes time to glaze, I do all the crabs at once. It can feel a little tedious at times, but I'm also finding that I'm getting better at making pots and at glazing them by doing things in bulk like that.




I use multiple clays in my studio because I like all the different looks. What I don't like is getting brown clay on my white clay. So I try in a given week to only be using a single clay body so that I can do a deep clean before I move onto the next clay. This helps limit cross contamination.

If I go into the studio and abandon my list and randomly make 5 soap dispensers with a new cutter I got from De La Designs then I will add that to my To Do list. It's like writing something on my list and immediately checking it off. If I make just one thing and I don't know if I'm going to like it or sell it, then it doesn't immediately go on the list.

In the next post in this series I'll talk about the Master Price List and what it drives in the rest of the spreadsheet.