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.